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

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

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;

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.

Logwriter basics

• Find out if database is in archive mode
• Increase the size of log files dynamically
• Are my log files multiplexed
• Get size, filename and groups information about the log file
• Under what conditions does LGWR get trigged

 

• Find out if database is in archive mode

The LGWR writes to both the files in circular manner. When one file is filled, it writes to second one. When second is filled, it writes to first one after the changes recorded in it have been written to datafiles if database is not in archived mode. If the database is in archived mode, then LGWR writes to first file after the changes recorded in it have been written to data files and the files have been archived.

To check if database is in archive mode:

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oracle11gR2/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     451
Current log sequence           452

• Increase the size of log files

Create two additional groups with desired size.
alter database add logfile group 3 ('+LOG1/log_1_8') size 10000M 
alter database add logfile group 4 ('+LOG2/log_1_9') size 10000M

Find out the status of all groups and drop the old inactive group

 select group#,thread#,status from v$log ;
alter database drop logfile group 2

Switch the logfile so you can drop the old active group as well

alter system switch logfile ;
alter database drop logfile group 1 ;

• Get size, filename and groups information about the log file

 Find out the size of the log file
SQL> select group#,thread#,bytes/(1024*1024) , blocksize ,members from v$log ;

Find out the filenames and the group to which they belong:

  sql>select group#,member,status from v$logfile;

• Are my log files multiplexed

Multiplexing of log files (having multiple identical copies) is implemented by creating groups of redo log file. A group consist of a redo log file and its multiplexed copies. Each identical copy is the member of the group.


IIssue the following commnd to find out how many groups you have. The output shows I have two groups and each group has one member ...Hence multiplexing is enabled.
SQL> select group#,member from v$logfile ;

    GROUP#                                           MEMBER
--------------------------------------------------------------------------------
         1                                                      +TPCC1/log_1_1

         2                                                         +TPCC1/log_1_2

• Under what conditions does LGWR gets triggered

When a transaction is committed, info in the redo log buffer is written to a Redo Log File. In addition to this, the following conditions will trigger LGWR to write the contents of the log buffer to disk:

• Whenever the log buffer is MIN(1/3 full, 1 MB) full; or
• Every 3 seconds; or
• When a DBWn process writes modified buffers to disk (checkpoint).

Queries to get info about SGA

•  Find the size of SGA and its components:
• select name, value/(1024*1024*1024) as sizeGB from v$sga;
• select sum(value)/(1024*1024*1024) as totalGB from v$sga;
• Detailed look into SGA:
• select name, bytes/(1024*1024*1024)) as sizeGB ,resizeable from v$sgainfo;
• Find the size and contents of buffer pool:
• select sum(current_size)/1024  as sizeGB from v$buffer_pool;
• select name, block_size, current_size/1024 as sizeGB  from v$buffer_pool ;
• Find  the size and contents of variable SGA size
• the sum of following two queries should add up to variable sga as reported by show sga:
• select name, bytes/(1024*1024*1024) as sizeGB from v$sgainfo where name='Free SGA Memory Available';
•  select sum(bytes)/(1024*1024*1024) as sizeGB from v$sgastat where pool in ('shared pool' , 'java pool' ,'large pool') ;
• The amount of SGA memory available for future dynamic SGA operations can be found by either one of the following query:
• select current_size/(1024*1024*1024)  as freeGB from v$sga_dynamic_free_memory;
• select name, bytes/(1024*1024*1024) as sizeGB from v$sgainfo where name='Free SGA Memory Available';
• Query to find out the current, min and max size of each dynamic component in SGA. This query also returns the number of type an grow or shrink operation was performed on each component.
• column component format a25
• select component, current_size/(1024*1024) as currentsizeMB, min_size/(1024*1024) as minMB,max_size/(1024*1024) as maxMB,user_specified_size/(1024*1024) as userspecifiedMB  ,oper_count from  v$sga_dynamic_components;

Oracle Memory Essentials

• There are five ways of managing memory in Oracle

• Enable automatic memory management where Oracle manages both SGA and PGA
• http://www.oracle-base.com/articles/11g/AutomaticMemoryManagement_11gR1.php
• http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/AMM1/Default.aspx
• Enable automatic SGA managment where Oracle manages SGA
• http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/memory004.htm#i1014121
• http://www.scribd.com/doc/2674966/Oracle-Automatic-Shared-Memory-Management-Asmm
• Enable automatic PGA management where Oracle manages PGA
• Manual SGA management
• Manual PGA management

• You can manage memory through EM as under:
• Under Home page -> Server-> Memory Advisor
• The following views provide information about dynamic resize operations:
  
  • V$MEMORY_CURRENT_RESIZE_OPS displays information about memory resize operations (both automatic and manual) which are currently in progress.
  • V$MEMORY_DYNAMIC_COMPONENTS displays information about the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.
  • V$MEMORY_RESIZE_OPS displays information about the last 800 completed memory resize operations (both automatic and manual). This does not include in-progress operations.
  • V$MEMORY_TARGET_ADVICE displays tuning advice for the MEMORY_TARGET initialization parameter.
  • V$SGA_CURRENT_RESIZE_OPS displays information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component.
  • V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations. This does not include any operations currently in progress.
  • V$SGA_DYNAMIC_COMPONENTS displays information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations that occurred after startup.
  • V$SGA_DYNAMIC_FREE_MEMORY displays information about the amount of SGA memory available for future dynamic SGA resize operations.
• SGA Internals
• Relevant views: v$sga,v$sgainfo, v$sgastat
• You can display SGA related info by issuing show sga command
• SQL> show sga
• Total System Global Area 3.1277E+10 bytes
  Fixed Size                  2149312 bytes
  Variable Size            7247762496 bytes
  Database Buffers         2.3891E+10 bytes
  Redo Buffers              136527872 bytes
• The same info can be obtained as under:
• SQL> select name, value/(1024*1024*1024) as sizeGB from v$sga;
• NAME                     SIZEGB
  -------------------- ----------
  Fixed Size           .002001703
  Variable Size        6.75000483
  Database Buffers          22.25
  Redo Buffers         .127151489
  
  
  SQL> select sum(value)/(1024*1024*1024) as totalMB from v$sga;
  
     TOTALMB
  ----------
   29.129158
• Fixed Size
  Contains general information about the state of the database and the instance, which the background processes need to access. No user data is stored here. The size of the fixed portion is constant for a release and a plattform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters. For 11gR2, fixed SGA size is around 200MB
• Variable size
• The variable portion of SGA is called variable because its size (measured in bytes) can be changed.
  
  The variable portion consists of:
      Large Pool (large_pool_size)
      Shared Pool (shared_pool_size)
      Java Pool (java_pool_size)
  
  The size for the variable portion is roughly equal to the result of the following statement till Oracle 9i:
  
  SQL> select sum(bytes)/(1024*1024*1024) as sizeGB from v$sgastat where pool in ('shared pool' , 'java pool' ,'large pool') ;
  
      SIZEGB
  ----------
  6.75000446
• Post Oracle 9i, the variable component is calculated by adding free memory as shown by v$sgainfo to the above query

Variable Component(Show SGA) = Shared Pool + Large Pool + Java Pool + Overhead + free memory(9i onwards)

>  select name, bytes/(1024*1024*1024) as sizeGB from v$sgainfo;
Adding free sga memory available to the above query from v$sgastat should get you the variable SGA size as reported by show sga command.

• Database buffers consist of default buffer (db_cache_size), keep buffer (db_keep_cache_size) , recycle buffer (db_recycle_cache_size) and all non standard buffer (db_n_cache_size). Oracle has the following non standard buffer cache:
• DB_2k_cache_size
• DB_4K_cache_size
• DB_8k_cache_size
• db_16k_cache_size
• db_32k_cache_size.

 The default block size is determined by db_block_size parameter.Multiple buffer pools are only available for the standard block size. Non-standard block size caches have a single DEFAULT pool.

• Query v$buffer_pool to get the sizes of all database buffers configured for the instance.
  SQL> select name, block_size, current_size from v$buffer_pool ;
  NAME                 BLOCK_SIZE CURRENT_SIZE
  -------------------- ---------- ------------
  KEEP                       2048        10240
  RECYCLE                    2048         7168
  DEFAULT                    2048         2048
  DEFAULT                    8192          256
  DEFAULT                   16384         3072
  The above output indicates that the size of keep buffer is 10 GB, size of recycle buffer is 7GB and the size of default buffer is 2 GB. Also , an 8K buffer cache of size 256MB and a 16k buffer cache of size 3 gb is configured.
  The following query indicates that total buffer cache is 22GB as shown in the output of show sga command:
  SQL> select sum(current_size)/1024  as sizeGB from v$buffer_pool;
      SIZEGB
  ----------
       22.25