Wednesday, February 9, 2011

Solaris Technotes

  • Enable rsh in S10:
http://unixadministrator.blogspot.com/2008/12/how-to-enable-rsh-on-solaris-10.html
Also, verify that the account from where you are setting rsh has a password ....
  • corestat does not show output
verify that cpustat command is giving the right output.if cpustat says resource currently unavailable, reboot the system.The cpustat command that corestat executes can be found in corestat file ....
  • Kill all oraclebench process 
  ps -ef | grep oraclebench | awk '{print $2}' | xargs -n1 kill -9

  • List ora_smon_test process

pgrep -fl ora_smon_test 
  • To remove all the files from  a directory , when rm gives "list too long" error
        find . -name '*' | xargs rm
  • To enable ssh for root  

  • Edit /etc/ssh/sshd_config file 
      • search for: PermitRootLogin no 
      • change the 'no' to yes.
  • Edit etc/default/login 
    • comment out "CONSOLE=/dev/console"
  • Restart sshd 
    • with following command
      •  # svcadm restart svc:/network/ssh:default
    • Or as under
      •  # /lib/svc/method/sshd restart
 
  •  Sqlplus cheatsheet:
 
  • http://www.adp-gmbh.ch/ora/sqlplus/use_vars.html
  • http://www.cs.csubak.edu/~wang/CS342/SqlPlus.htm

     
Posted by at No comments:
Labels:

Connecting to database from remote client

  • Start listener on server machine
  • Install adminstrative client s/w on the client machine and add server name in tnsname.ora in client machine
  •  Verify on client machine:
    • tnsping <servicename>
    • sqlplus tpcc/tpcc@bench (where tpcc is the username and bench is the service name)
    • setenv TWO_TASK bench
    • sqlplus tpcc/tpcc (you should get the prompt)
    •  
  • What is the use of TWO_TASK environment variable ? 
If you are in a Unix environment, you can normally set 2 environment variables:
  • ORACLE_HOME 
    • 

  • ORACLE_SID 
    • 


that will connect you to a local database identified by that home and sid. If you wanted 
to be able to connect to a remote database or a database on that machine via SQLNet, you 
can also set:
  •  TWO_TASK = <tnsconnect string> 
    • 

The setting of TWO_TASK overrides the ORACLE_SID when set. You will not connecting to a 
local database with two_task but rather using sqlnet to connect to a remote database.
So, instead of: 
I can: 
$ setenv TWO_TASK some_db
$ sqlplus scott/tiger
and the @some_db is implied.
Posted by at No comments:
Labels: ,

Tuesday, February 8, 2011

Whitepaper links

  • Oracle Database on Sun SPARC Enterprise T-series Systems : Suitablilty Analysis
  • Optimizing Single Instance Oracle Databases on Oracle's T-Series Servers:
  • Deploying Oracle Database on the Oracle Solaris Platform -Best Practices:
  • Best Practices for Runing Oracle Databases in Oracle Solaris Containers
  • Session in MySQL Conf 2008 : Best Practices for Deploying MySQL on the Solaris Platform
  • Session in Oracle OpenWorld 2010:
http://www.oracle.com/technetwork/server-storage/solarisstudio/oow-focus-on-solaris-173299.pdf

  • Oracle Optimized Solution
How to Consolidate, Optimize, and Deploy Oracle Database Environments with Flexible Virtualized Services
Oracle Optimized Solution for Oracle Database
http://www.oracle.com/technetwork/server-storage/hardware-solutions/o12-032-howto-db-throughput-avail-1592601.pdf
 
 
  •  Oracle Optimized Soltuion:
 
How to Accelerate Test and Development Through Rapid Cloning of Production Databases and Operating Environments
 
http://www.oracle.com/technetwork/server-storage/hardware-solutions/o13-022-rapid-cloning-db-1919816.pdf
  •  Session in Oracle Open World 2012

How to Configure HCC for Optimizing Query Database Performance and lowering TCO
  • Deploying MySQL Database in Solaris Cluster Environments
Posted by at No comments:
Labels: ,

Looking at another FC storage

Storage configuration is as under:

  • luxadm  -e port

4 HBAs are connected:
/devices/pci@1,700000/SUNW,qlc@0/fp@0,0:devctl                     CONNECTED
/devices/pci@1,700000/SUNW,qlc@0,1/fp@0,0:devctl                   CONNECTED
/devices/pci@13,700000/SUNW,qlc@0/fp@0,0:devctl                    CONNECTED
/devices/pci@13,700000/SUNW,qlc@0,1/fp@0,0:devctl                  CONNECTED

You can find out the WWN number of each HBA as under :


Pos AL_PA ID Hard_Addr Port WWN         Node WWN         Type
0     e1  4     e1     200700a0b832f4bd 200600a0b832f4bc 0x0  (Disk device)
1     1   7d    0      2100001b320b6958 2000001b320b6958 0x1f (Unknown Type,Host Bus Adapter)


Pos AL_PA ID Hard_Addr Port WWN         Node WWN         Type
0     e8  1     e8     200600a0b832f4bd 200600a0b832f4bc 0x0  (Disk device)
1     1   7d    0      2101001b322b6958 2001001b322b6958 0x1f (Unknown Type,Host Bus Adapter)


Pos AL_PA ID Hard_Addr Port WWN         Node WWN         Type
0     ef  0     ef     202400a0b83892c4 200400a0b83892c4 0x0  (Disk device)
1     1   7d    0      2100001b329bdf46 2000001b329bdf46 0x1f (Unknown Type,Host Bus Adapter)


Pos AL_PA ID Hard_Addr Port WWN         Node WWN         Type
0     e8  1     e8     203400a0b83892c4 200400a0b83892c4 0x0  (Disk device)
1     1   7d    0      2101001b32bbdf46 2001001b32bbdf46 0x1f (Unknown Type,Host Bus Adapter)


Additional info about HBAs including their speed can be found by issuing the following comand
  • fcinfo hba-ports
# fcinfo hba-port
HBA Port WWN: 2100001b320b6958
        OS Device Name: /dev/cfg/c1
        Manufacturer: QLogic Corp.
        Model: 375-3356-02
        Firmware Version: 05.03.02
        FCode/BIOS Version:  BIOS: 1.24; fcode: 1.24; EFI: 1.08;
        Serial Number: 0402G00-0814437006
        Driver Name: qlc
        Driver Version: 20100301-3.00
        Type: L-port
        State: online
        Supported Speeds: 1Gb 2Gb 4Gb
        Current Speed: 4Gb
        Node WWN: 2000001b320b6958
HBA Port WWN: 2101001b322b6958
        OS Device Name: /dev/cfg/c2
        Manufacturer: QLogic Corp.
        Model: 375-3356-02
        Firmware Version: 05.03.02
        FCode/BIOS Version:  BIOS: 1.24; fcode: 1.24; EFI: 1.08;
        Serial Number: 0402G00-0814437006
        Driver Name: qlc
        Driver Version: 20100301-3.00
        Type: L-port
        State: online
        Supported Speeds: 1Gb 2Gb 4Gb
        Current Speed: 4Gb
        Node WWN: 2001001b322b6958
HBA Port WWN: 2100001b329bdf46
        OS Device Name: /dev/cfg/c6
        Manufacturer: QLogic Corp.
        Model: 371-4325-01
        Firmware Version: 05.03.02
        FCode/BIOS Version:  BIOS: 2.02; fcode: 2.03; EFI: 2.01;
        Serial Number: 0402H00-1002804353
        Driver Name: qlc
        Driver Version: 20100301-3.00
        Type: L-port
        State: online
        Supported Speeds: 2Gb 4Gb 8Gb
        Current Speed: 4Gb
        Node WWN: 2000001b329bdf46
HBA Port WWN: 2101001b32bbdf46
        OS Device Name: /dev/cfg/c7
        Manufacturer: QLogic Corp.
        Model: 371-4325-01
        Firmware Version: 05.03.02
        FCode/BIOS Version:  BIOS: 2.02; fcode: 2.03; EFI: 2.01;
        Serial Number: 0402H00-1002804353
        Driver Name: qlc
        Driver Version: 20100301-3.00
        Type: L-port
        State: online
        Supported Speeds: 2Gb 4Gb 8Gb
        Current Speed: 4Gb
        Node WWN: 2001001b32bbdf46

You can find out which disk are attached to which HBA by the following command:

(where P is WWN of the HBA port found by luxadm dump_map command or fcinfo hba_port command)
  • fcinfo remote-port -sl -p 2100001b320b6958
Remote Port WWN: 200700a0b832f4bd
        Active FC4 Types:
        SCSI Target: yes
        Node WWN: 200600a0b832f4bc
        Link Error Statistics:
                Link Failure Count: 3
                Loss of Sync Count: 9
                Loss of Signal Count: 133
                Primitive Seq Protocol Error Count: 0
                Invalid Tx Word Count: 0
                Invalid CRC Count: 0
        LUN: 0
          Vendor: SUN
          Product: CSM200_R
          OS Device Name: /dev/rdsk/c1t4d0s2
        LUN: 1
          Vendor: SUN
          Product: CSM200_R
          OS Device Name: /dev/rdsk/c1t4d1s2
        LUN: 2
          Vendor: SUN
          Product: CSM200_R
          OS Device Name: /dev/rdsk/c1t4d2s2
        LUN: 3
          Vendor: SUN
          Product: CSM200_R
          OS Device Name: /dev/rdsk/c1t4d3s2

  • # fcinfo remote-port -sl  -p  2101001b32bbdf46
Remote Port WWN: 203400a0b83892c4
        Active FC4 Types:
        SCSI Target: yes
        Node WWN: 200400a0b83892c4
        Link Error Statistics:
                Link Failure Count: 13
                Loss of Sync Count: 2167
                Loss of Signal Count: 3158
                Primitive Seq Protocol Error Count: 0
                Invalid Tx Word Count: 0
                Invalid CRC Count: 0
        LUN: 0
          Vendor: SUN
          Product: LCSM100_F
          OS Device Name: /dev/rdsk/c7t1d0s2
        LUN: 1
          Vendor: SUN
          Product: LCSM100_F
          OS Device Name: /dev/rdsk/c7t1d1s2
        LUN: 2
          Vendor: SUN
          Product: LCSM100_F
          OS Device Name: /dev/rdsk/c7t1d2s2
        LUN: 3
          Vendor: SUN
          Product: LCSM100_F
          OS Device Name: /dev/rdsk/c7t1d3s2
  • # fcinfo remote-port -sl -p 2101001b322b6958
Remote Port WWN: 200600a0b832f4bd
        Active FC4 Types:
        SCSI Target: yes
        Node WWN: 200600a0b832f4bc
        Link Error Statistics:
                Link Failure Count: 3
                Loss of Sync Count: 9
                Loss of Signal Count: 69
                Primitive Seq Protocol Error Count: 0
                Invalid Tx Word Count: 0
                Invalid CRC Count: 0
        LUN: 0
          Vendor: SUN
          Product: CSM200_R
          OS Device Name: /dev/rdsk/c2t1d0s2
        LUN: 1
          Vendor: SUN
          Product: CSM200_R
          OS Device Name: /dev/rdsk/c2t1d1s2
        LUN: 2
          Vendor: SUN
          Product: CSM200_R
          OS Device Name: /dev/rdsk/c2t1d2s2
        LUN: 3
          Vendor: SUN
          Product: CSM200_R
          OS Device Name: /dev/rdsk/c2t1d3s2
  • # fcinfo remote-port -sl -p 2100001b329bdf46
Remote Port WWN: 202400a0b83892c4
        Active FC4 Types:
        SCSI Target: yes
        Node WWN: 200400a0b83892c4
        Link Error Statistics:
                Link Failure Count: 10
                Loss of Sync Count: 1776
                Loss of Signal Count: 3331
                Primitive Seq Protocol Error Count: 0
                Invalid Tx Word Count: 0
                Invalid CRC Count: 0
        LUN: 0
          Vendor: SUN
          Product: LCSM100_F
          OS Device Name: /dev/rdsk/c6t0d0s2
        LUN: 1
          Vendor: SUN
          Product: LCSM100_F
          OS Device Name: /dev/rdsk/c6t0d1s2
        LUN: 2
          Vendor: SUN
          Product: LCSM100_F
          OS Device Name: /dev/rdsk/c6t0d2s2
        LUN: 3
          Vendor: SUN
          Product: LCSM100_F
          OS Device Name: /dev/rdsk/c6t0d3s

The above output shows that each HBA has four disk attached
1 HBA : 200700a0b832f4bd : c1t4do-d3
2 HBA : 203400a0b83892c4:  c7t1d0-d3
3 HBA :200600a0b832f4bd: c2t1d0-d3
4 HBA: 202400a0b83892c4:  c6t0d0-d3

On the storage side:
There are two arrays:
One is 6140 with 1.7 terabytes of storage with a total of 13 disk each of 136 GB . It has two controllers
6140 is divided into four volumes of 442GB each. Two volumes are connected to controller A and two are connected to controller B.
Each controller has 2 ports. However, the link of only port per controller is up
The wwn of the port that is up on Controller A (A/1) is
20:06:00:A0:B8:32:F4:BD
The wwn of the port that is up on controller B (B/1)is
20:07:00:A0:B8:32:F4:BD

The second storage is 2540 with 1.6 TB of storage with a total of 12 SAS disk each of 136 GB. It consist of single controller.The link of both ports on the controller are active.
The WWN of the first port of the controller (A/1) is
20:24:00:A0:B8:38:92:C4
the WWN of the second port of the controller (A/2) is oth the
20:34:00:A0:B8:38:92:C4
2540 consist of 4 volumes each of 408 GB

Combining info about storage and HBA info on server side.
c1t4 disk and c2t1 disks are from 6140.
c7t1 and c6t0 are disks from 2150. These two disk are from single controller and are identical.
Posted by at No comments:
Labels: , ,

Looking at a FC Storage

  • fcinfo hba-port
Only one HBA is online. The state of the first HBA is offline.
 
HBA Port WWN: 2100001b320f4f21
        OS Device Name: /dev/cfg/c5
        Manufacturer: QLogic Corp.
        Model: 375-3356-02
        Firmware Version: 05.01.02
        FCode/BIOS Version:  BIOS: 1.24; fcode: 1.24; EFI: 1.08;
        Serial Number: 0402G00-0810426346
        Driver Name: qlc
        Driver Version: 20090929-2.32
        Type: unknown
        State: offline
        Supported Speeds: 1Gb 2Gb 4Gb
        Current Speed: not established
        Node WWN: 2000001b320f4f21
HBA Port WWN: 2101001b322f4f21
        OS Device Name: /dev/cfg/c6
        Manufacturer: QLogic Corp.
        Model: 375-3356-02
        Firmware Version: 05.01.02
        FCode/BIOS Version:  BIOS: 1.24; fcode: 1.24; EFI: 1.08;
        Serial Number: 0402G00-0810426346
        Driver Name: qlc
        Driver Version: 20090929-2.32
        Type: N-port
        State: online
        Supported Speeds: 1Gb 2Gb 4Gb
        Current Speed: 2Gb
        Node WWN: 2001001b322f4f21
  • fcinfo remote-port -s -p 2101001b322f4f21

Remote Port WWN: 20030003ba68ec2e
        Active FC4 Types: SCSI
        SCSI Target: yes
        Node WWN: 10000003ba68ec2e
        LUN: 0
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BA68EC2Ed0s2
        LUN: 1
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BA68EC2Ed1s2
        LUN: 2
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BA68EC2Ed2s2
Remote Port WWN: 216000c0ff886cfa
        Active FC4 Types: SCSI
        SCSI Target: yes
        Node WWN: 206000c0ff086cfa
        LUN: 0
          Vendor: SUN
          Product: StorEdge 3510
          OS Device Name: /dev/rdsk/c6t216000C0FF886CFAd0s2
Remote Port WWN: 20030003ba13ec0f
        Active FC4 Types: SCSI
        SCSI Target: yes
        Node WWN: 10000003ba13ec0f
        LUN: 0
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BA13EC0Fd0s2
Remote Port WWN: 20030003baccc8fa
        Active FC4 Types: SCSI
        SCSI Target: yes
        Node WWN: 10000003baccc8fa
        LUN: 0
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BACCC8FAd0s2
        LUN: 1
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BACCC8FAd1s2
Remote Port WWN: 20030003ba13ebd2
Remote Port WWN: 20030003ba13ebd2
        Active FC4 Types: SCSI
        SCSI Target: yes
        Node WWN: 10000003ba13ebd2
        LUN: 0
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BA13EBD2d0s2
        LUN: 1
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BA13EBD2d1s2
Remote Port WWN: 216000c0ff803d33
        Active FC4 Types: SCSI
        SCSI Target: yes
        Node WWN: 206000c0ff003d33
        LUN: 0
          Vendor: SUN
          Product: StorEdge 3510
          OS Device Name: /dev/rdsk/c6t216000C0FF803D33d0s2
        LUN: 1
          Vendor: SUN
          Product: StorEdge 3510
          OS Device Name: /dev/rdsk/c6t216000C0FF803D33d1s2
        LUN: 2
          Vendor: SUN
          Product: StorEdge 3510
          OS Device Name: /dev/rdsk/c6t216000C0FF803D33d2s2
Remote Port WWN: 226000c0ffa03d33
        Active FC4 Types: SCSI
        SCSI Target: yes
        Node WWN: 206000c0ff003d33
        LUN: 0
          Vendor: SUN
          Product: StorEdge 3510
          OS Device Name: /dev/es/ses5
Remote Port WWN: 256000c0ffc86cfa
        Active FC4 Types: SCSI
        SCSI Target: yes
        Node WWN: 206000c0ff086cfa
        LUN: 0
          Vendor: SUN
          Product: StorEdge 3510
          OS Device Name: /dev/es/ses1
Remote Port WWN: 216000c0ff803a7b
Remote Port WWN: 216000c0ff803a7b
        Active FC4 Types: SCSI
        SCSI Target: yes
        Node WWN: 206000c0ff003a7b
        LUN: 0
          Vendor: SUN
          Product: StorEdge 3510
          OS Device Name: /dev/rdsk/c6t216000C0FF803A7Bd0s2
        LUN: 1
          Vendor: SUN
          Product: StorEdge 3510
          OS Device Name: /dev/rdsk/c6t216000C0FF803A7Bd1s2
Remote Port WWN: 20030003baccc902
        Active FC4 Types: SCSI
        SCSI Target: yes
        Node WWN: 10000003baccc902
        LUN: 0
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BACCC902d0s2
        LUN: 1
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BACCC902d1s2
Remote Port WWN: 256000c0ffc03a7b
        Active FC4 Types: SCSI
        SCSI Target: yes
        Node WWN: 206000c0ff003a7b
        LUN: 0
          Vendor: SUN
          Product: StorEdge 3510
          OS Device Name: /dev/es/ses2
Remote Port WWN: 20030003ba13e6a1
        Active FC4 Types: SCSI
        SCSI Target: yes
        Node WWN: 10000003ba13e6a1
        LUN: 0
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BA13E6A1d0s2
        LUN: 1
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BA13E6A1d1s2
Remote Port WWN: 20030003ba4e8829
        Active FC4 Types: SCSI
        SCSI Target: yes
        Node WWN: 10000003ba4e8829
        LUN: 0
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BA4E8829d0s2
        LUN: 1
          Vendor: SUN
          Product: T4
          OS Device Name: /dev/rdsk/c6t20030003BA4E8829d1s2
~
The above output means
Remote Port WWN: 20030003ba68ec2e  : T4:  three LUN from d0-d2
Remote Port WWN: 216000c0ff886cfa : 3510: one LUN d0
Remote Port WWN: 20030003ba13ec0f  : T4: one LUN d0
Remote Port WWN: 20030003baccc8fa: :t4: Two luns d0 and d1
Remote Port WWN: 20030003ba13ebd2:t4: two luns d0-d1
Remote Port WWN: 216000c0ff803d33:3510: thre luns do-d2
Remote Port WWN: 20030003ba4e8829: T4: two luns d0-d2
Remote Port WWN: 20030003ba13e6a1:T4: two luns d0-d1
Remote Port WWN: 216000c0ff803a7b:3510: two luns d0-d1
Remote Port WWN: 20030003baccc902 : T4 two luns d0-d1
Posted by at No comments:
Labels: , ,

Find physical processor, cores and virtual processors in Solaris

  1. To get  number of physical processors
    • psrinfo -p
  2. To get info about all virtual processors  (number of cores * number of threads per core * no of physical processor)
    • psrinfo
  3. To get number of virtual processor per physical processor
    • psrinfo -pv
  4. To get number of cores per physical processor
    • #  kstat cpu_info|grep core_id|uniq  | wc -l
  5. To get the number of threads per physical processor
    • Divide output of 3/output of 4
  • To disable range of CPUS
    • psradm -f 20-63
  • To enable range of CPUS
    • psradm -n 20-63
Posted by at No comments:
Labels: ,

Monday, February 7, 2011

Shared Pool Sizing

This blog entry talks about how the library cache and dictionary cache statistics can be monitored to ensure they are optimally sized.


  • Shared pool: Library cache statistics
When sizing the shared pool, the goal is to ensure that SQL statements that will be executed multiple times are cached in the library cache, without allocating too much memory.

The statistic that shows the amount of reloading (that is, reparsing) of a previously cached SQL statement that was aged out of the cache is the RELOADS column in the  V$LIBRARYCACHE view. In an application that reuses SQL effectively, on a system with an optimal shared pool size, the RELOADS statistic will have a value near zero.

The INVALIDATIONS column in V$LIBRARYCACHE view shows the number of times library cache data was invalidated and had to be reparsed. INVALIDATIONS should be near zero. This means SQL statements that could have been shared were invalidated by some operation (for example, a DDL). This statistic should be near zero on OLTP systems during peak loads.

The following query gives info about reloads and invalidations:

sql> col  namespace format a30
SQL> SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS
  FROM V$LIBRARYCACHE ORDER BY NAMESPACE;

NAMESPACE                            PINS    PINHITS    RELOADS INVALIDATIONS
------------------------------ ---------- ---------- ---------- -------------
APP CONTEXT                           525        524          0             0
BODY                                 7114       6993          0             0
CLUSTER                               529        496          0             0
DBINSTANCE                              0          0          0             0
DBLINK                                  0          0          0             0
EDITION                               482        479          0             0
INDEX                                  44          0          0             0
OBJECT ID                               0          0          0             0
QUEUE                                7316       7300          1             0
RULESET                                 7          6          0             0
SCHEMA                                  0          0          0             0
NAMESPACE                            PINS    PINHITS    RELOADS INVALIDATIONS
------------------------------ ---------- ---------- ---------- -------------
SQL AREA                         55853208   58144246         35           112
SUBSCRIPTION                           12          7          0             0
TABLE/PROCEDURE                     32025      28475         91             0
TRIGGER                               156        145          0             0
USER AGENT                              1          0          0             0
16 rows selected.
 
Another key statistic is the amount of free memory in the shared pool at peak times. The amount of free memory can be queried from V$SGASTAT, looking at the free memory for the shared pool. Optimally, free memory should be as low as possible, without causing any reloads on the system.

You can find out the free memory for the shared pool with the following query:

SELECT * FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';


Lastly, a broad indicator of library cache health is the library cache hit ratio. This value should be considered along with the other statistics discussed in this section and other data, such as the rate of hard parsing and whether there is any shared pool or library cache latch contention.

To calculate the library cache hit ratio, use the following formula:
Library Cache Hit Ratio = sum(pinhits) / sum(pins)

The following query displays the library cache hit ratio:

select sum(pinhits)/ sum(pins) from v$librarycache;

  • Shared pool: Dictionary cache statistics
Typically, if the shared pool is adequately sized for the library cache, it will also be adequate for the dictionary cache data.

Misses on the data dictionary cache are to be expected in some cases. On instance startup, the data dictionary cache contains no data. Therefore, any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses decreases. Eventually, the database reaches a steady state, in which the most frequently used dictionary data is in the cache. At this point, very few cache misses occur.

Each row in the V$ROWCACHE view contains statistics for a single type of data dictionary item. These statistics reflect all data dictionary activity since the most recent instance startup. The columns in the V$ROWCACHE view that reflect the use and effectiveness of the data dictionary cache are listed below.

PARAMETER
 Identifies a particular data dictionary item. For each row, the value in this column is the item prefixed by dc_. For example, in the row that contains statistics for file descriptions, this column has the value dc_files.

GETS
 Shows the total number of requests for information about the corresponding item. For example, in the row that contains statistics for file descriptions, this column has the total number of requests for file description data.

GETMISSES
 Shows the number of data requests which were not satisfied by the cache, requiring an I/O.

MODIFICATIONS
 Shows the number of times data in the dictionary cache was updated.


Use the following query to monitor the statistics in the V$ROWCACHE view over a period while your application is running. The derived column PCT_SUCC_GETS can be considered the item-specific hit ratio:

column parameter format a21
column pct_succ_gets format 999.9
column updates format 999,999,999
SELECT parameter  , sum(gets)  , sum(getmisses)  , 100*sum(gets - getmisses) / sum(gets)  pct_succ_gets
     , sum(modifications)                     updates
  FROM V$ROWCACHE
 WHERE gets 0
 GROUP BY parameter;


It is also possible to calculate an overall dictionary cache hit ratio using the following formula; however, summing up the data over all the caches will lose the finer granularity of data:
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
Posted by at No comments:
Labels: ,

Wednesday, February 2, 2011

Log file sync wait

Time taken to flush the log buffer ie copy from log buffer to log file at disk.

When a user session(foreground process) COMMITs (or rolls back), the session's redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write all redo required from the log buffer to the redo log file. When the LGWR has finished it will post the user session. The user session waits on this wait event while waiting for LGWR to post it back to confirm all redo changes are safely on disk.

This may be described further as the time user session/foreground process spends waiting for redo to be flushed to make the commit durable. Therefore, we may think of these waits as commit latency from the foreground process (or commit client generally).





  • Log file sync flow is as under:

  • Foreground process posts LGWR and goes to sleep
    • the "log file sync" wait starts
    • posting is done via a semaphore operation on unix
  • LGWR wakes up and gets onto CPU
    • issue the IO requests
    • LGWR goes to sleep waiting for "log file parallel write" wait
  • Hardware comples the IO and OS wakes up LGWR
    • LGWR gets into CPU
    • Marks "log file parallel write" event complete and post the foreground process
  • Foreground process is woken up by LGWR posts
    • Foreground process gets into CPU and completes the "log file sync" waits
 If you log file sync is in top 5 events,. find out the time spend in log file parallel write. Usually log file parallel write takes up significant time of log file sync waits. Other components like scheduling latency, IPC etc are small.
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)