Parallel Processing

Parallel Processing should be used under this two conditions

1) When plenty of free resource is available . The aim of PX is to reduce the respnose time by distributing the work done by single process

2) It can used for SQL statements that take more than a dozen seconds to execute serially

If PX is commonly used for many SQL statements,the degree of parallelism should be set at the table or index level.

If it is used only for specific batches or reports . It is better to enable it at the session level or through hints

Insert statements with values clause cannot be parallelized

DML --> Insert,update,delete & merge can be executed in parallel when

1) Table has a trigger
2)  A table has either a foreign key constraint refering itself
3) an object column is modified
4) a clustered or temporary table is modified

An actual distribution for a SQL statement , you can use the dynamic performance view v$pq_tqstat.

Information provided for current session and last SQL statements

Direct path insert gives better performance as it generates minimum undo . In fact undo is generated for only space management operation . For Ex :-- To increase the high watermark and to add new extent to the segment and not for the rows contained in the blocks that are inserted by direct-path .

The purpose of minimal logging is t
\'o minimize the redo generation

You can set minimual logging by setting parameter nologging at the table or partition level

Mimimial loggging is supported for direct path loads and some DDL Statements

Fetching numerous row at a time is called row prefetching

Row prefetching is enabled by JDBC drive by default

Cluster startup error

Service gpnpd failed to come up

Resolution as follows

[root@rac1 bin]# ./crsctl start cluster
CRS-2672: Attempting to start 'ora.evmd' on 'rac1'
CRS-2676: Start of 'ora.evmd' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rac1'
CRS-2674: Start of 'ora.gpnpd' on 'rac1' failed
CRS-2673: Attempting to stop 'ora.evmd' on 'rac1'
CRS-2677: Stop of 'ora.evmd' on 'rac1' succeeded
CRS-4000: Command Start failed, or completed with errors.

[root@rac1 bin]# pwd

/u01/app/12.1.0/grid/bin

[root@rac1 bin]# ./crsctl stat res -t -init

--------------------------------------------------------------------------------

Name           Target  State        Server                   State details

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.asm

      1        ONLINE  OFFLINE                               Instance Shutdown,ST

                                                             ABLE

ora.cluster_interconnect.haip

      1        ONLINE  OFFLINE                               STABLE

ora.crf

      1        ONLINE  ONLINE       rac1                     STABLE

ora.crsd

      1        ONLINE  OFFLINE                               STABLE

ora.cssd

      1        ONLINE  OFFLINE                               STABLE

ora.cssdmonitor

      1        ONLINE  OFFLINE                               STABLE

ora.ctssd

      1        ONLINE  OFFLINE                               STABLE

ora.diskmon

      1        ONLINE  OFFLINE                               STABLE

ora.evmd

      1        ONLINE  OFFLINE                               STABLE

ora.gipcd

      1        ONLINE  ONLINE       rac1                     STABLE

ora.gpnpd

      1        ONLINE  OFFLINE                               STABLE

ora.mdnsd

      1        ONLINE  ONLINE       rac1                     STABLE

ora.storage

      1        ONLINE  OFFLINE                               STABLE

--------------------------------------------------------------------------------

[root@rac1 bin]# ./u01/app/11.2.0/grid/bin/ocrcheck

-bash: ./u01/app/11.2.0/grid/bin/ocrcheck: No such file or directory

[root@rac1 bin]# cd /u01/app/11.2.0/grid/bin/

-bash: cd: /u01/app/11.2.0/grid/bin/: No such file or directory

[root@rac1 bin]# pwd

/u01/app/12.1.0/grid/bin

[root@rac1 bin]# ./ocrcheck

Status of Oracle Cluster Registry is as follows :

         Version                  :          4

         Total space (kbytes)     :     409568

         Used space (kbytes)      :       3380

         Available space (kbytes) :     406188

         ID                       : 1384592632

         Device/File Name         :      +DATA

                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@rac1 bin]# pwd

/u01/app/12.1.0/grid/bin

You have new mail in /var/spool/mail/root

[root@rac1 bin]# ./crsctl start crs -excl

CRS-2672: Attempting to start 'ora.evmd' on 'rac1'

CRS-2676: Start of 'ora.evmd' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.gpnpd' on 'rac1'

CRS-2676: Start of 'ora.gpnpd' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac1'

CRS-2676: Start of 'ora.cssdmonitor' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.cssd' on 'rac1'

CRS-2672: Attempting to start 'ora.diskmon' on 'rac1'

CRS-2676: Start of 'ora.diskmon' on 'rac1' succeeded

CRS-2676: Start of 'ora.cssd' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac1'

CRS-2672: Attempting to start 'ora.ctssd' on 'rac1'

CRS-2676: Start of 'ora.ctssd' on 'rac1' succeeded

CRS-5017: The resource action "ora.cluster_interconnect.haip start" encountered the following error:

Start action for HAIP aborted. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac1/crs/trace/ohasd_orarootagent_root.trc".

CRS-2674: Start of 'ora.cluster_interconnect.haip' on 'rac1' failed

CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'rac1'

CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac1'

CRS-5017: The resource action "ora.cluster_interconnect.haip start" encountered the following error:

Start action for HAIP aborted. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac1/crs/trace/ohasd_orarootagent_root.trc".

CRS-2674: Start of 'ora.cluster_interconnect.haip' on 'rac1' failed

CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'rac1'

CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'rac1' succeeded

CRS-4000: Command Start failed, or completed with errors.

[root@rac1 bin]# ./crsctl stop resource ora.crsd -init

[root@rac1 bin]# ./crsctl stop resource ora.crsd -init;

CRS-2500: Cannot stop resource 'ora.crsd' as it is not running

CRS-4000: Command Stop failed, or completed with errors.

[root@rac1 bin]# ./crsctl start crs -excl -nocrs

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac1'

CRS-5017: The resource action "ora.cluster_interconnect.haip start" encountered the following error:

Start action for HAIP aborted. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac1/crs/trace/ohasd_orarootagent_root.trc".

CRS-2674: Start of 'ora.cluster_interconnect.haip' on 'rac1' failed

CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'rac1'

CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'rac1' succeeded

CRS-4000: Command Start failed, or completed with errors.

[root@rac1 bin]# pwd

/u01/app/12.1.0/grid/bin

You have new mail in /var/spool/mail/root

[root@rac1 bin]# ./crsctl stat res -t -init

--------------------------------------------------------------------------------

Name           Target  State        Server                   State details

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.asm

      1        ONLINE  OFFLINE                               Instance Shutdown,ST

                                                             ABLE

ora.cluster_interconnect.haip

      1        ONLINE  OFFLINE                               STABLE

ora.crf

      1        ONLINE  ONLINE       rac1                     STABLE

ora.crsd

      1        OFFLINE OFFLINE                               STABLE

ora.cssd

      1        ONLINE  ONLINE       rac1                     STABLE

ora.cssdmonitor

      1        ONLINE  ONLINE       rac1                     STABLE

ora.ctssd

      1        ONLINE  ONLINE       rac1                     OBSERVER,STABLE

ora.diskmon

      1        OFFLINE OFFLINE                               STABLE

ora.evmd

      1        ONLINE  INTERMEDIATE rac1                     STABLE

ora.gipcd

      1        ONLINE  ONLINE       rac1                     STABLE

ora.gpnpd

      1        ONLINE  ONLINE       rac1                     STABLE

ora.mdnsd

      1        ONLINE  ONLINE       rac1                     STABLE

ora.storage

      1        ONLINE  OFFLINE                               STABLE

--------------------------------------------------------------------------------

[root@rac1 bin]# gpnptool get

-bash: gpnptool: command not found

Check the gpnpd profile

[root@rac1 bin]# ./gpnptool get

Warning: some command line parameters were defaulted. Resulting command line:

         ./gpnptool.bin get -o-

<?xml version="1.0" encoding="UTF-8"?><gpnp:GPnP-Profile Version="1.0" xmlns="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:gpnp="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:orcl="http://www.oracle.com/gpnp/2005/11/gpnp-profile" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.grid-pnp.org/2005/11/gpnp-profile gpnp-profile.xsd" ProfileSequence="6" ClusterUId="7e7ad9a8f8245f05ffad6b38a93508ac" ClusterName="rac-scan" PALocation=""><gpnp:Network-Profile><gpnp:HostNetwork id="gen" HostName="*"><gpnp:Network id="net1" IP="192.168.10.0" Adapter="eth0" Use="cluster_interconnect"/><gpnp:Network id="net2" IP="192.168.56.0" Adapter="eth1" Use="public"/></gpnp:HostNetwork></gpnp:Network-Profile><orcl:CSS-Profile id="css" DiscoveryString="+asm" LeaseDuration="400"/><orcl:ASM-Profile id="asm" DiscoveryString="" SPFile="+DATA/rac-scan/ASMPARAMETERFILE/registry.253.858426557" Mode="legacy"/><ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#"><ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/><ds:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"/><ds:Reference URI=""><ds:Transforms><ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/><ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"> <InclusiveNamespaces xmlns="http://www.w3.org/2001/10/xml-exc-c14n#" PrefixList="gpnp orcl xsi"/></ds:Transform></ds:Transforms><ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"/><ds:DigestValue>qD+D2x79xr16FoMfaWXKc1AcYBE=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>Yb2cGmHj/flpEvMi4ZzkqUfr/EsPX3yXtqXTraPLG3pffP5IEfwpzF+Q5WQCoYFq5byEmcslYZKl+eNe4Wxog0c183dk5NbAKzdT4S818XD8eh8+XrJP3XtXt2qA2O8FvcLK++1m27Eus7TW+MG/DTUU58V1z2CFyHh9rCF8QaY=</ds:SignatureValue></ds:Signature></gpnp:GPnP-Profile>

Success.

Change the ip address of eth1 and eth0

[root@rac1 bin]# ifconfig eth1 inet 192.168.56.2

[root@rac1 bin]# ifconfig eth0

[root@rac1 bin]# cat /etc/hosts

127.0.0.1                localhost.localdomain localhost

192.168.56.2  rac1.localdomain       rac1

192.168.10.3  rac1-priv.localdomain  rac1-priv

192.168.56.4  rac1-vip.localdomain   rac1-vip

192.168.56.5   rac-scan.localdomain   rac-scan

192.168.56.6  rac2.localdomain       rac2

192.168.10.7  rac2-priv.localdomain  rac2-priv

192.168.56.8  rac2-vip.localdomain   rac2-vip

[root@rac1 bin]# ifconfig eth0 inet 192.168.10.3

[root@rac1 bin]#  ./crsctl start res ora.cluster_interconnect.haip -init

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac1'

CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.asm' on 'rac1'

CRS-2676: Start of 'ora.asm' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.storage' on 'rac1'

CRS-2676: Start of 'ora.storage' on 'rac1' succeeded

[root@rac1 bin]# ./gpnptool get

Warning: some command line parameters were defaulted. Resulting command line:

         ./gpnptool.bin get -o-

<?xml version="1.0" encoding="UTF-8"?><gpnp:GPnP-Profile Version="1.0" xmlns="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:gpnp="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:orcl="http://www.oracle.com/gpnp/2005/11/gpnp-profile" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.grid-pnp.org/2005/11/gpnp-profile gpnp-profile.xsd" ProfileSequence="6" ClusterUId="7e7ad9a8f8245f05ffad6b38a93508ac" ClusterName="rac-scan" PALocation=""><gpnp:Network-Profile><gpnp:HostNetwork id="gen" HostName="*"><gpnp:Network id="net1" IP="192.168.10.0" Adapter="eth0" Use="cluster_interconnect"/><gpnp:Network id="net2" IP="192.168.56.0" Adapter="eth1" Use="public"/></gpnp:HostNetwork></gpnp:Network-Profile><orcl:CSS-Profile id="css" DiscoveryString="+asm" LeaseDuration="400"/><orcl:ASM-Profile id="asm" DiscoveryString="" SPFile="+DATA/rac-scan/ASMPARAMETERFILE/registry.253.858426557" Mode="legacy"/><ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#"><ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/><ds:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"/><ds:Reference URI=""><ds:Transforms><ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/><ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"> <InclusiveNamespaces xmlns="http://www.w3.org/2001/10/xml-exc-c14n#" PrefixList="gpnp orcl xsi"/></ds:Transform></ds:Transforms><ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"/><ds:DigestValue>qD+D2x79xr16FoMfaWXKc1AcYBE=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>Yb2cGmHj/flpEvMi4ZzkqUfr/EsPX3yXtqXTraPLG3pffP5IEfwpzF+Q5WQCoYFq5byEmcslYZKl+eNe4Wxog0c183dk5NbAKzdT4S818XD8eh8+XrJP3XtXt2qA2O8FvcLK++1m27Eus7TW+MG/DTUU58V1z2CFyHh9rCF8QaY=</ds:SignatureValue></ds:Signature></gpnp:GPnP-Profile>

Success.

[root@rac1 bin]# ifconfig -a

eth0      Link encap:Ethernet  HWaddr 08:00:27:BE:F9:01

          inet addr:192.168.10.3  Bcast:192.168.10.255  Mask:255.255.255.0

          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

          RX packets:41660 errors:0 dropped:0 overruns:0 frame:0

          TX packets:18776 errors:0 dropped:0 overruns:0 carrier:0

          collisions:0 txqueuelen:1000

          RX bytes:3665536 (3.4 MiB)  TX bytes:4289493 (4.0 MiB)

eth0:1    Link encap:Ethernet  HWaddr 08:00:27:BE:F9:01

          inet addr:169.254.229.41  Bcast:169.254.255.255  Mask:255.255.0.0

          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

eth1      Link encap:Ethernet  HWaddr 08:00:27:3A:51:18

          inet addr:192.168.56.2  Bcast:192.168.56.255  Mask:255.255.255.0

          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

          RX packets:24668 errors:0 dropped:0 overruns:0 frame:0

          TX packets:253 errors:0 dropped:0 overruns:0 carrier:0

          collisions:0 txqueuelen:1000

          RX bytes:1825506 (1.7 MiB)  TX bytes:28575 (27.9 KiB)

eth2      Link encap:Ethernet  HWaddr 08:00:27:11:31:D5

          inet addr:10.0.0.19  Bcast:10.0.0.255  Mask:255.255.255.0

          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

          RX packets:10052 errors:0 dropped:0 overruns:0 frame:0

          TX packets:123 errors:0 dropped:0 overruns:0 carrier:0

          collisions:0 txqueuelen:1000

          RX bytes:1424473 (1.3 MiB)  TX bytes:20276 (19.8 KiB)

lo        Link encap:Local Loopback

          inet addr:127.0.0.1  Mask:255.0.0.0

          UP LOOPBACK RUNNING  MTU:16436  Metric:1

          RX packets:3126 errors:0 dropped:0 overruns:0 frame:0

          TX packets:3126 errors:0 dropped:0 overruns:0 carrier:0

          collisions:0 txqueuelen:0

          RX bytes:3800660 (3.6 MiB)  TX bytes:3800660 (3.6 MiB)

[root@rac1 bin]# ifconfig eth1

eth1      Link encap:Ethernet  HWaddr 08:00:27:3A:51:18

          inet addr:192.168.56.2  Bcast:192.168.56.255  Mask:255.255.255.0

          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

          RX packets:24709 errors:0 dropped:0 overruns:0 frame:0

          TX packets:259 errors:0 dropped:0 overruns:0 carrier:0

          collisions:0 txqueuelen:1000

          RX bytes:1828108 (1.7 MiB)  TX bytes:31067 (30.3 KiB)

Start the services

[root@rac1 bin]# ./crsctl start res ora.crsd -init

CRS-2672: Attempting to start 'ora.crsd' on 'rac1'

CRS-2676: Start of 'ora.crsd' on 'rac1' succeeded

[root@rac1 bin]# ./crsctl start res ora.cssd -init

CRS-5702: Resource 'ora.cssd' is already running on 'rac1'

CRS-4000: Command Start failed, or completed with errors.

[root@rac1 bin]#

Output of OCRCHECK

OCR :-- Oracle Cluster Registry

Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     409568
         Used space (kbytes)      :       3380
         Available space (kbytes) :     406188
         ID                       : 1384592632
         Device/File Name         :      +DATA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

Clusterverification Utility Sample Output

-bash-3.2$ ./runcluvfy.sh stage -pre crsinst -fixup -n rac1,rac2 -verbose


Performing pre-checks for cluster services setup

Checking node reachability...

Check: Node reachability from node "rac1"
  Destination Node                      Reachable?
  ------------------------------------  ------------------------
  rac2                                  yes
  rac1                                  yes
Result: Node reachability check passed from node "rac1"


Checking user equivalence...

Check: User equivalence for user "oracle"
  Node Name                             Comment
  ------------------------------------  ------------------------
  rac2                                  passed
  rac1                                  passed
Result: User equivalence check passed for user "oracle"

Checking node connectivity...

Checking hosts config file...
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  rac2          passed
  rac1          passed

Verification of the hosts config file successful


Interface information for node "rac2"
 Name   IP Address      Subnet          Gateway         Def. Gateway    HW Address        MTU
 ------ --------------- --------------- --------------- --------------- ----------------- ------
 eth0   192.168.56.6    192.168.56.0    0.0.0.0         UNKNOWN         08:00:27:F0:6C:E6 1500
 eth1   192.168.10.7    192.168.10.0    0.0.0.0         UNKNOWN         08:00:27:37:97:F2 1500


Interface information for node "rac1"
 Name   IP Address      Subnet          Gateway         Def. Gateway    HW Address        MTU
 ------ --------------- --------------- --------------- --------------- ----------------- ------
 eth0   192.168.56.2    192.168.56.0    0.0.0.0         10.0.0.1        08:00:27:BE:F9:01 1500
 eth1   192.168.10.3    192.168.10.0    0.0.0.0         10.0.0.1        08:00:27:3A:51:18 1500
 eth2   10.0.0.19       10.0.0.0        0.0.0.0         10.0.0.1        08:00:27:11:31:D5 1500


Check: Node connectivity of subnet "192.168.56.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  rac2:eth0                       rac1:eth0                       yes
Result: Node connectivity passed for subnet "192.168.56.0" with node(s) rac2,rac1


Check: TCP connectivity of subnet "192.168.56.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  rac1:192.168.56.2               rac2:192.168.56.6               passed
Result: TCP connectivity check passed for subnet "192.168.56.0"


Check: Node connectivity of subnet "192.168.10.0"

WARNING:
Make sure IP address "192.168.10.3" is up and is a valid IP address on node "rac1"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  rac2:eth1                       rac1:eth1                       no
Result: Node connectivity failed for subnet "192.168.10.0"


Check: TCP connectivity of subnet "192.168.10.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  rac1:192.168.10.3               rac2:192.168.10.7               failed
Result: TCP connectivity check failed for subnet "192.168.10.0"


Check: Node connectivity of subnet "10.0.0.0"
Result: Node connectivity passed for subnet "10.0.0.0" with node(s) rac1


Check: TCP connectivity of subnet "10.0.0.0"
Result: TCP connectivity check passed for subnet "10.0.0.0"


Interfaces found on subnet "192.168.56.0" that are likely candidates for a private interconnect are:
rac2 eth0:192.168.56.6
rac1 eth0:192.168.56.2

WARNING:
Could not find a suitable set of interfaces for VIPs

Result: Node connectivity check failed


Check: Total memory
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          7.81GB (8188052.0KB)      1.5GB (1572864.0KB)       passed
  rac1          7.81GB (8188052.0KB)      1.5GB (1572864.0KB)       passed
Result: Total memory check passed

Check: Available memory
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          7.28GB (7636388.0KB)      50MB (51200.0KB)          passed
  rac1          7.17GB (7518068.0KB)      50MB (51200.0KB)          passed
Result: Available memory check passed

Check: Swap space
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          9.76GB (1.0233364E7KB)    7.81GB (8188052.0KB)      passed
  rac1          9.76GB (1.0233364E7KB)    7.81GB (8188052.0KB)      passed
Result: Swap space check passed

Check: Free disk space for "rac2:/tmp"
  Path              Node Name     Mount point   Available     Required      Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  /tmp              rac2          /tmp          9.47GB        1GB           passed
Result: Free disk space check passed for "rac2:/tmp"

Check: Free disk space for "rac1:/tmp"
  Path              Node Name     Mount point   Available     Required      Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  /tmp              rac1          /tmp          9.14GB        1GB           passed
Result: Free disk space check passed for "rac1:/tmp"

Check: User existence for "oracle"
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  rac2          exists                    passed
  rac1          exists                    passed
Result: User existence check passed for "oracle"

Check: Group existence for "oinstall"
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  rac2          exists                    passed
  rac1          exists                    passed
Result: Group existence check passed for "oinstall"

Check: Group existence for "dba"
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  rac2          exists                    passed
  rac1          exists                    passed
Result: Group existence check passed for "dba"

Check: Membership of user "oracle" in group "oinstall" [as Primary]
  Node Name         User Exists   Group Exists  User in Group  Primary       Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rac2              yes           yes           yes           yes           passed
  rac1              yes           yes           yes           yes           passed
Result: Membership check for user "oracle" in group "oinstall" [as Primary] passed

Check: Membership of user "oracle" in group "dba"
  Node Name         User Exists   Group Exists  User in Group  Comment
  ----------------  ------------  ------------  ------------  ----------------
  rac2              yes           yes           yes           passed
  rac1              yes           yes           yes           passed
Result: Membership check for user "oracle" in group "dba" passed

Check: Run level
  Node Name     run level                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          5                         3,5                       passed
  rac1          5                         3,5                       passed
Result: Run level check passed

Check: Hard limits for "maximum open file descriptors"
  Node Name         Type          Available     Required      Comment
  ----------------  ------------  ------------  ------------  ----------------
  rac2              hard          131072        65536         passed
  rac1              hard          131072        65536         passed
Result: Hard limits check passed for "maximum open file descriptors"

Check: Soft limits for "maximum open file descriptors"
  Node Name         Type          Available     Required      Comment
  ----------------  ------------  ------------  ------------  ----------------
  rac2              soft          131072        1024          passed
  rac1              soft          131072        1024          passed
Result: Soft limits check passed for "maximum open file descriptors"

Check: Hard limits for "maximum user processes"
  Node Name         Type          Available     Required      Comment
  ----------------  ------------  ------------  ------------  ----------------
  rac2              hard          131072        16384         passed
  rac1              hard          131072        16384         passed
Result: Hard limits check passed for "maximum user processes"

Check: Soft limits for "maximum user processes"
  Node Name         Type          Available     Required      Comment
  ----------------  ------------  ------------  ------------  ----------------
  rac2              soft          131072        2047          passed
  rac1              soft          131072        2047          passed
Result: Soft limits check passed for "maximum user processes"

Check: System architecture
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          x86_64                    x86_64                    passed
  rac1          x86_64                    x86_64                    passed
Result: System architecture check passed

Check: Kernel version
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          2.6.32-100.26.2.el5       2.6.18                    passed
  rac1          2.6.32-100.26.2.el5       2.6.18                    passed
Result: Kernel version check passed

Check: Kernel parameter for "semmsl"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          250                       250                       passed
  rac1          250                       250                       passed
Result: Kernel parameter check passed for "semmsl"

Check: Kernel parameter for "semmns"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          32000                     32000                     passed
  rac1          32000                     32000                     passed
Result: Kernel parameter check passed for "semmns"

Check: Kernel parameter for "semopm"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          100                       100                       passed
  rac1          100                       100                       passed
Result: Kernel parameter check passed for "semopm"

Check: Kernel parameter for "semmni"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          142                       128                       passed
  rac1          142                       128                       passed
Result: Kernel parameter check passed for "semmni"

Check: Kernel parameter for "shmmax"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          4398046511104             536870912                 passed
  rac1          4398046511104             536870912                 passed
Result: Kernel parameter check passed for "shmmax"

Check: Kernel parameter for "shmmni"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          4096                      4096                      passed
  rac1          4096                      4096                      passed
Result: Kernel parameter check passed for "shmmni"

Check: Kernel parameter for "shmall"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          1073741824                2097152                   passed
  rac1          1073741824                2097152                   passed
Result: Kernel parameter check passed for "shmall"

Check: Kernel parameter for "file-max"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          6815744                   6815744                   passed
  rac1          6815744                   6815744                   passed
Result: Kernel parameter check passed for "file-max"

Check: Kernel parameter for "ip_local_port_range"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          between 9000 & 65500      between 9000 & 65500      passed
  rac1          between 9000 & 65500      between 9000 & 65500      passed
Result: Kernel parameter check passed for "ip_local_port_range"

Check: Kernel parameter for "rmem_default"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          262144                    262144                    passed
  rac1          262144                    262144                    passed
Result: Kernel parameter check passed for "rmem_default"

Check: Kernel parameter for "rmem_max"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          4194304                   4194304                   passed
  rac1          4194304                   4194304                   passed
Result: Kernel parameter check passed for "rmem_max"

Check: Kernel parameter for "wmem_default"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          262144                    262144                    passed
  rac1          262144                    262144                    passed
Result: Kernel parameter check passed for "wmem_default"

Check: Kernel parameter for "wmem_max"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          1048576                   1048576                   passed
  rac1          1048576                   1048576                   passed
Result: Kernel parameter check passed for "wmem_max"

Check: Kernel parameter for "aio-max-nr"
  Node Name     Configured                Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          3145728                   1048576                   passed
  rac1          3145728                   1048576                   passed
Result: Kernel parameter check passed for "aio-max-nr"

Check: Package existence for "make-3.81"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          make-3.81-3.el5           make-3.81                 passed
  rac1          make-3.81-3.el5           make-3.81                 passed
Result: Package existence check passed for "make-3.81"

Check: Package existence for "binutils-2.17.50.0.6"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          binutils-2.17.50.0.6-14.el5  binutils-2.17.50.0.6      passed
  rac1          binutils-2.17.50.0.6-14.el5  binutils-2.17.50.0.6      passed
Result: Package existence check passed for "binutils-2.17.50.0.6"

Check: Package existence for "gcc-4.1.2"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          gcc-4.1.2-50.el5          gcc-4.1.2                 passed
  rac1          gcc-4.1.2-50.el5          gcc-4.1.2                 passed
Result: Package existence check passed for "gcc-4.1.2"

Check: Package existence for "libaio-0.3.106 (i386)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          libaio-0.3.106-5 (i386)   libaio-0.3.106 (i386)     passed
  rac1          libaio-0.3.106-5 (i386)   libaio-0.3.106 (i386)     passed
Result: Package existence check passed for "libaio-0.3.106 (i386)"

Check: Package existence for "libaio-0.3.106 (x86_64)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          libaio-0.3.106-5 (x86_64)  libaio-0.3.106 (x86_64)   passed
  rac1          libaio-0.3.106-5 (x86_64)  libaio-0.3.106 (x86_64)   passed
Result: Package existence check passed for "libaio-0.3.106 (x86_64)"

Check: Package existence for "glibc-2.5-24 (i686)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          glibc-2.5-58 (i686)       glibc-2.5-24 (i686)       passed
  rac1          glibc-2.5-58 (i686)       glibc-2.5-24 (i686)       passed
Result: Package existence check passed for "glibc-2.5-24 (i686)"

Check: Package existence for "glibc-2.5-24 (x86_64)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          glibc-2.5-58 (x86_64)     glibc-2.5-24 (x86_64)     passed
  rac1          glibc-2.5-58 (x86_64)     glibc-2.5-24 (x86_64)     passed
Result: Package existence check passed for "glibc-2.5-24 (x86_64)"

Check: Package existence for "compat-libstdc++-33-3.2.3 (i386)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          compat-libstdc++-33-3.2.3-61 (i386)  compat-libstdc++-33-3.2.3 (i386)  passed
  rac1          compat-libstdc++-33-3.2.3-61 (i386)  compat-libstdc++-33-3.2.3 (i386)  passed
Result: Package existence check passed for "compat-libstdc++-33-3.2.3 (i386)"

Check: Package existence for "compat-libstdc++-33-3.2.3 (x86_64)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          compat-libstdc++-33-3.2.3-61 (x86_64)  compat-libstdc++-33-3.2.3 (x86_64)  passed
  rac1          compat-libstdc++-33-3.2.3-61 (x86_64)  compat-libstdc++-33-3.2.3 (x86_64)  passed
Result: Package existence check passed for "compat-libstdc++-33-3.2.3 (x86_64)"

Check: Package existence for "elfutils-libelf-0.125 (x86_64)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          elfutils-libelf-0.137-3.el5 (x86_64)  elfutils-libelf-0.125 (x86_64)  passed
  rac1          elfutils-libelf-0.137-3.el5 (x86_64)  elfutils-libelf-0.125 (x86_64)  passed
Result: Package existence check passed for "elfutils-libelf-0.125 (x86_64)"

Check: Package existence for "elfutils-libelf-devel-0.125"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          elfutils-libelf-devel-0.137-3.el5  elfutils-libelf-devel-0.125  passed
  rac1          elfutils-libelf-devel-0.137-3.el5  elfutils-libelf-devel-0.125  passed
Result: Package existence check passed for "elfutils-libelf-devel-0.125"

Check: Package existence for "glibc-common-2.5"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          glibc-common-2.5-58       glibc-common-2.5          passed
  rac1          glibc-common-2.5-58       glibc-common-2.5          passed
Result: Package existence check passed for "glibc-common-2.5"

Check: Package existence for "glibc-devel-2.5 (i386)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          glibc-devel-2.5-58 (i386)  glibc-devel-2.5 (i386)    passed
  rac1          glibc-devel-2.5-58 (i386)  glibc-devel-2.5 (i386)    passed
Result: Package existence check passed for "glibc-devel-2.5 (i386)"

Check: Package existence for "glibc-devel-2.5 (x86_64)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          glibc-devel-2.5-58 (x86_64)  glibc-devel-2.5 (x86_64)  passed
  rac1          glibc-devel-2.5-58 (x86_64)  glibc-devel-2.5 (x86_64)  passed
Result: Package existence check passed for "glibc-devel-2.5 (x86_64)"

Check: Package existence for "glibc-headers-2.5"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          glibc-headers-2.5-58      glibc-headers-2.5         passed
  rac1          glibc-headers-2.5-58      glibc-headers-2.5         passed
Result: Package existence check passed for "glibc-headers-2.5"

Check: Package existence for "gcc-c++-4.1.2"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          gcc-c++-4.1.2-50.el5      gcc-c++-4.1.2             passed
  rac1          gcc-c++-4.1.2-50.el5      gcc-c++-4.1.2             passed
Result: Package existence check passed for "gcc-c++-4.1.2"

Check: Package existence for "libaio-devel-0.3.106 (i386)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          libaio-devel-0.3.106-5 (i386)  libaio-devel-0.3.106 (i386)  passed
  rac1          libaio-devel-0.3.106-5 (i386)  libaio-devel-0.3.106 (i386)  passed
Result: Package existence check passed for "libaio-devel-0.3.106 (i386)"

Check: Package existence for "libaio-devel-0.3.106 (x86_64)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          libaio-devel-0.3.106-5 (x86_64)  libaio-devel-0.3.106 (x86_64)  passed
  rac1          libaio-devel-0.3.106-5 (x86_64)  libaio-devel-0.3.106 (x86_64)  passed
Result: Package existence check passed for "libaio-devel-0.3.106 (x86_64)"

Check: Package existence for "libgcc-4.1.2 (i386)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          libgcc-4.1.2-50.el5 (i386)  libgcc-4.1.2 (i386)       passed
  rac1          libgcc-4.1.2-50.el5 (i386)  libgcc-4.1.2 (i386)       passed
Result: Package existence check passed for "libgcc-4.1.2 (i386)"

Check: Package existence for "libgcc-4.1.2 (x86_64)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          libgcc-4.1.2-50.el5 (x86_64)  libgcc-4.1.2 (x86_64)     passed
  rac1          libgcc-4.1.2-50.el5 (x86_64)  libgcc-4.1.2 (x86_64)     passed
Result: Package existence check passed for "libgcc-4.1.2 (x86_64)"

Check: Package existence for "libstdc++-4.1.2 (i386)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          libstdc++-4.1.2-50.el5 (i386)  libstdc++-4.1.2 (i386)    passed
  rac1          libstdc++-4.1.2-50.el5 (i386)  libstdc++-4.1.2 (i386)    passed
Result: Package existence check passed for "libstdc++-4.1.2 (i386)"

Check: Package existence for "libstdc++-4.1.2 (x86_64)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          libstdc++-4.1.2-50.el5 (x86_64)  libstdc++-4.1.2 (x86_64)  passed
  rac1          libstdc++-4.1.2-50.el5 (x86_64)  libstdc++-4.1.2 (x86_64)  passed
Result: Package existence check passed for "libstdc++-4.1.2 (x86_64)"

Check: Package existence for "libstdc++-devel-4.1.2 (x86_64)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          libstdc++-devel-4.1.2-50.el5 (x86_64)  libstdc++-devel-4.1.2 (x86_64)  passed
  rac1          libstdc++-devel-4.1.2-50.el5 (x86_64)  libstdc++-devel-4.1.2 (x86_64)  passed
Result: Package existence check passed for "libstdc++-devel-4.1.2 (x86_64)"

Check: Package existence for "sysstat-7.0.2"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          sysstat-7.0.2-3.el5_5.1   sysstat-7.0.2             passed
  rac1          sysstat-7.0.2-3.el5_5.1   sysstat-7.0.2             passed
Result: Package existence check passed for "sysstat-7.0.2"

Check: Package existence for "unixODBC-2.2.11 (i386)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          unixODBC-2.2.11-7.1 (i386)  unixODBC-2.2.11 (i386)    passed
  rac1          unixODBC-2.2.11-7.1 (i386)  unixODBC-2.2.11 (i386)    passed
Result: Package existence check passed for "unixODBC-2.2.11 (i386)"

Check: Package existence for "unixODBC-2.2.11 (x86_64)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          unixODBC-2.2.11-7.1 (x86_64)  unixODBC-2.2.11 (x86_64)  passed
  rac1          unixODBC-2.2.11-7.1 (x86_64)  unixODBC-2.2.11 (x86_64)  passed
Result: Package existence check passed for "unixODBC-2.2.11 (x86_64)"

Check: Package existence for "unixODBC-devel-2.2.11 (i386)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          unixODBC-devel-2.2.11-7.1 (i386)  unixODBC-devel-2.2.11 (i386)  passed
  rac1          unixODBC-devel-2.2.11-7.1 (i386)  unixODBC-devel-2.2.11 (i386)  passed
Result: Package existence check passed for "unixODBC-devel-2.2.11 (i386)"

Check: Package existence for "unixODBC-devel-2.2.11 (x86_64)"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          unixODBC-devel-2.2.11-7.1 (x86_64)  unixODBC-devel-2.2.11 (x86_64)  passed
  rac1          unixODBC-devel-2.2.11-7.1 (x86_64)  unixODBC-devel-2.2.11 (x86_64)  passed
Result: Package existence check passed for "unixODBC-devel-2.2.11 (x86_64)"

Check: Package existence for "ksh-20060214"
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          ksh-20100202-1.el5_5.1    ksh-20060214              passed
  rac1          ksh-20100202-1.el5_5.1    ksh-20060214              passed
Result: Package existence check passed for "ksh-20060214"

Checking for multiple users with UID value 0
Result: Check for multiple users with UID value 0 passed

Check: Current group ID
Result: Current group ID check passed
Checking Core file name pattern consistency...
Core file name pattern consistency check passed.

Checking to make sure user "oracle" is not in "root" group
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  rac2          does not exist            passed
  rac1          does not exist            passed
Result: User "oracle" is not part of "root" group. Check passed

Check default user file creation mask
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rac2          0022                      0022                      passed
  rac1          0022                      0022                      passed
Result: Default user file creation mask check passed

Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
The NTP configuration file "/etc/ntp.conf" is available on all nodes
NTP Configuration file check passed

Checking daemon liveness...

Check: Liveness for "ntpd"
  Node Name                             Running?
  ------------------------------------  ------------------------
  rac2                                  no
  rac1                                  no
Result: Liveness check failed for "ntpd"
PRVF-5415 : Check to see if NTP daemon is running failed
Result: Clock synchronization check using Network Time Protocol(NTP) failed


Pre-check for cluster services setup was unsuccessful on all the nodes.

STATSPACK REPORT - P2

Load Profile Section

1. This is used for comparing two reports to see if the Load characteristics from the two report periods are similar

Sample Load Profile Section Report

Definition

a) Redo Size :-- Amount of Redo generated . If increase in the value more DML happening (INSERT,UPDATE & DELETE) than before

b) Logical Reads :-- Reading from memory and diskc
c) Block changes :-- The number of blocks modified during sample interval . If it is more ,more DML statements are taking place (INSERT,UPDATE & DELETE)
d) Physical Reads :-- The number of call that caused physical I/O
e) Physical Writes :-- The number of physical writes issued
f) User calls :-- The number of queries generated
g) Parses :-- Total of both parse hard and soft
h) Hard Parse :-- This is condition when sql statements is parsed .If it is 100% .This is bad .A hard parse is usually accompained by latch contention on the shared pool and library cache latches.
I) soft parse :-- This is obtained by subtracting hard parses from parse
j) Executes :-- How many statements we are executing per second/transaction
k) Transaction :-- How many transactions per second we process

Buffer Nowait Ration :-- This is the percentage of time that the instance made a call to get a buffer . Buffer was made available immediately meaning it did not had to wait for buffer.

Buffer Hit Ratio :-- Also know as buffer-cache hit ratio . It shows % of time a particular block was found in buffer cache instead of performing a physical I/O

Library Hit Ratio :-- give the percentage of pin requests that result in pin hits.A pin hit occurs when the sql or pl/sql code to be executed is already in the library cache and is valid to execute .If “Library Hit ratio” is low . It could mean low shared pool

Execute to Parse.:-- If value is negative, it means that the number of parses is larger than the number of executions. Another cause for a negative execute to parse ratio is if the shared pool is too small and queries are aging out of the shared pool and need to be reparsed.

Parse CPU to Parse Elapsd % :-- Generally this is measure of how available CPU cycles were for SQL parsing. If this is low you may see “latch free” as one of your top wait events.

Redo Nowait Ratio :-- This indicates the amount of redo entries generated for which there was space available in the redo log.

In Memory Sort Ratio :-- This give percentage sorts that were performed in memory.

Soft Parse Ratio :-- This ratio gives the % of parses that were soft,as opposed to hard.

Latch Hit Ratio :-- This is the ratio of total number of latch misses to the number of latch gets for all latches.

Reading Statspack Reoport – P1

1) Wait Events :-- Look for excessive waits and wait times and check for specific problems

2) SQL Ordered by Buffer Gets,Physical Reads and Rows Processed :-- Figure out which SQL statements to tune

3) Instance Activity statistics :-- Compare with baseline report,compute additional statistics

4) Tablespace and File I/O :-- Investigate I/O bottlenecks,identify files and tablespaces with heavy I/O

5) Buffer Pool :-- Identify buffer pool with high I/O

6) Buffer wait statistics :-- Identify buffer type with large waits

7) Enqueue Activity :- Identify the locks which are causing most waits

8) Rollback Segment statistics and Storage :-- Investigate waits for rollback segments

9) Latch Activity,Latch Sleep Breakdown ,Latch Miss Sources :-- Identify latch bottlenacks

10) Non-default init.ora -- Look for init.ora parameter definitions

Look for statspack report in this order

1) Top 5 wait events

2) Load profile

3) Instance Efficiency Hit Ratios

4) Wait Events / Wait Event Histograms

5) Latch Waits

6) Top SQL

7) Instance Activity/Time Model Stats/ O/S Stats

8) File I/O / File Read Histogram / Undo Stats

9) Memory Allocations

10) Undo

SAMPLE STATSPACK REPORT

STATSPACK report for

Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
3494669306 DBPR11G 1 25-Sep-14 00:08 11.2.0.1.0 NO

Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
PRDG11G.localdom Linux x86 64-bit 1 0 0 7.8

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 1 25-Sep-14 10:40:08 31 1.0
End Snap: 2 25-Sep-14 10:40:59 31 1.0
Elapsed: 0.85 (mins) Av Act Sess: 0.0
DB time: 0.02 (mins) DB CPU: 0.02 (mins)

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 1,376M Std Block Size: 8K
Shared Pool: 416M Log Buffer: 15,376K

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.0 1.0 0.00 0.02
DB CPU(s): 0.0 0.9 0.00 0.02
Redo size: 30,890.4 1,575,412.0
Logical reads: 180.6 9,212.0
Block changes: 96.2 4,907.0
Physical reads: 0.1 3.0
Physical writes: 0.0 0.0
User calls: 1.1 58.0
Parses: 16.6 844.0
Hard parses: 2.2 114.0
W/A MB processed: 0.5 27.0
Logons: 0.1 3.0
Executes: 27.2 1,387.0
Rollbacks: 0.0 0.0
Transactions: 0.0

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.97 Optimal W/A Exec %: 100.00
Library Hit %: 90.79 Soft Parse %: 86.49
Execute to Parse %: 39.15 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 16.67 % Non-Parse CPU: 98.86

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 48.89 49.57
% SQL with executions>1: 51.91 53.90
% Memory for SQL w/exec>1: 58.95 62.19

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 1 60.2
control file parallel write 70 0 4 19.7
log file parallel write 124 0 2 15.6
os thread startup 1 0 39 2.7
log file sync 3 0 7 1.5
-------------------------------------------------------------

Host CPU (CPUs: 1 Cores: 0 Sockets: 0)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.15 0.06 1.29 0.90 97.59 0.29

Instance CPU
~~~~~~~~~~~~ % Time (seconds)
-------- --------------
Host: Total time (s): 49.0
Host: Busy CPU time (s): 1.2
% of time Host is Busy: 2.4
Instance: Total CPU time (s): 1.2
% of Busy CPU used for Instance: 103.2
Instance: Total Database time (s): 10.8
%DB time waiting for CPU (Resource Mgr): 0.0

Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 7,986.1 7,986.1
SGA use (MB): 3,185.7 3,185.7
PGA use (MB): 178.3 176.4
% Host Mem used for SGA+PGA: 42.1 42.1
-------------------------------------------------------------

Time Model System Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Ordered by % of DB time desc, Statistic name

Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
DB CPU 0.9 92.8
sql execute elapsed time 0.9 92.2
parse time elapsed 0.2 16.4
hard parse elapsed time 0.2 15.4
PL/SQL compilation elapsed time 0.0 2.8
connection management call elapsed 0.0 2.3
PL/SQL execution elapsed time 0.0 .5
repeated bind elapsed time 0.0 .0
DB time 1.0
background elapsed time 9.8
background cpu time 0.3
-------------------------------------------------------------

Foreground Wait Events DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
log file sync 3 0 0 7 3.0 1.5
SQL*Net message from client 38 0 41 1086 38.0
-------------------------------------------------------------

Background Wait Events DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
control file parallel write 70 0 0 4 70.0 19.7
log file parallel write 124 0 0 2 124.0 15.6
os thread startup 1 0 0 39 1.0 2.7
control file sequential read 238 0 0 0 238.0 .3
rdbms ipc message 366 79 2,675 7308 366.0
DIAG idle wait 101 100 101 1000 101.0
dispatcher timer 1 100 60 60007 1.0
Streams AQ: qmn slave idle w 2 0 56 28002 2.0
Streams AQ: qmn coordinator 4 50 56 14001 4.0
pmon timer 18 94 51 2836 18.0
Space Manager: slave idle wa 10 100 50 5000 10.0
shared server idle wait 1 100 30 30012 1.0
SQL*Net message from client 9 0 0 0 9.0
-------------------------------------------------------------

Wait Events (fg and bg) DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
control file parallel write 70 0 0 4 70.0 19.7
log file parallel write 124 0 0 2 124.0 15.6
os thread startup 1 0 0 39 1.0 2.7
log file sync 3 0 0 7 3.0 1.5
control file sequential read 518 0 0 0 518.0 .3
rdbms ipc message 366 79 2,675 7308 366.0
DIAG idle wait 101 100 101 1000 101.0
dispatcher timer 1 100 60 60007 1.0
Streams AQ: qmn slave idle w 2 0 56 28002 2.0
Streams AQ: qmn coordinator 4 50 56 14001 4.0
pmon timer 18 94 51 2836 18.0
Space Manager: slave idle wa 10 100 50 5000 10.0
SQL*Net message from client 47 0 41 878 47.0
shared server idle wait 1 100 30 30012 1.0
-------------------------------------------------------------

Wait Event Histogram DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)

Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
Disk file operations I/O 31 100.0
LGWR wait for redo copy 1 100.0
Parameter File I/O 16 100.0
SQL*Net break/reset to cli 2 100.0
asynch descriptor resize 120 100.0
control file parallel writ 70 7.1 42.9 48.6 1.4
control file sequential re 518 100.0
db file sequential read 3 100.0
log file parallel write 124 51.6 38.7 6.5 1.6 1.6
log file sync 3 66.7 33.3
os thread startup 1 100.0
DIAG idle wait 101 100.0
SQL*Net message from clien 47 70.2 10.6 6.4 2.1 2.1 8.5
SQL*Net message to client 45 100.0
Space Manager: slave idle 10 100.0
Streams AQ: qmn coordinato 4 50.0 50.0
Streams AQ: qmn slave idle 2 100.0
class slave wait 1 100.0
dispatcher timer 1 100.0
pmon timer 18 5.6 94.4
rdbms ipc message 366 3.3 .3 .3 .3 .3 47.5 48.1
shared server idle wait 1 100.0
-------------------------------------------------------------

SQL ordered by CPU DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Total DB CPU (s): 1
-> Captured SQL accounts for 96.5% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU

CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
0.82 1 0.82 89.6 0.88 13,193 2522684317
Module: sqlplus@PRDG11G.localdomain (TNS V1-V3)
BEGIN statspack.snap; END;

0.03 11 0.00 3.2 0.03 0 3547921727
SELECT value,type FROM v$parameter WHERE name = :1

0.02 9 0.00 1.8 0.02 0 2223195045
SELECT value FROM v$parameter WHERE name = 'spfile'

-------------------------------------------------------------

SQL ordered by Elapsed time for DB: DBPR11G Instance: DBPR11G Snaps: 1 -2
-> Total DB Time (s): 1
-> Captured SQL accounts for 95.4% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time

Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
0.88 1 0.88 88.6 0.82 7 2522684317
Module: sqlplus@PRDG11G.localdomain (TNS V1-V3)
BEGIN statspack.snap; END;

0.03 11 0.00 3.2 0.03 0 3547921727
SELECT value,type FROM v$parameter WHERE name = :1

0.02 9 0.00 2.0 0.02 0 2223195045
SELECT value FROM v$parameter WHERE name = 'spfile'

-------------------------------------------------------------

SQL ordered by Gets DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 9,212
-> Captured SQL accounts for 2.1% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets

CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
13,193 1 13,193.0 143.2 0.82 0.88 2522684317
Module: sqlplus@PRDG11G.localdomain (TNS V1-V3)
BEGIN statspack.snap; END;

-------------------------------------------------------------

SQL ordered by Reads DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> End Disk Reads Threshold: 1000 Total Disk Reads: 3
-> Captured SQL accounts for 0.0% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads

CPU Elapsd
Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
7 1 7.0 233.3 0.82 0.88 2522684317
Module: sqlplus@PRDG11G.localdomain (TNS V1-V3)
BEGIN statspack.snap; END;

-------------------------------------------------------------

SQL ordered by Executions DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> End Executions Threshold: 100 Total Executions: 1,387
-> Captured SQL accounts for 10.0% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions

CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
19 0 0.0 0.00 0.00 4274598960
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#
=:1 and intcol#=:2

-------------------------------------------------------------

SQL ordered by Parse Calls DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> End Parse Calls Threshold: 1000 Total Parse Calls: 844
-> Captured SQL accounts for 12.8% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls

% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
11 11 1.30 3547921727
SELECT value,type FROM v$parameter WHERE name = :1

10 10 1.18 1254950678
select file# from file$ where ts#=:1

9 9 1.07 2223195045
SELECT value FROM v$parameter WHERE name = 'spfile'

-------------------------------------------------------------
truncate table STATS$TEMP_SQLSTATS
*
ERROR at line 1:
ORA-00942: table or view does not exist

Instance Activity Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
Batched IO (bound) vector count 0 0.0 0.0
Batched IO (full) vector count 0 0.0 0.0
Batched IO block miss count 0 0.0 0.0
Batched IO buffer defrag count 0 0.0 0.0
Batched IO double miss count 0 0.0 0.0
Batched IO same unit count 0 0.0 0.0
Batched IO vector block count 0 0.0 0.0
Batched IO vector read count 0 0.0 0.0
Block Cleanout Optim referenced 0 0.0 0.0
CCursor + sql area evicted 8 0.2 8.0
CPU used by this session 88 1.7 88.0
CPU used when call started 91 1.8 91.0
CR blocks created 2 0.0 2.0
Cached Commit SCN referenced 0 0.0 0.0
Commit SCN cached 0 0.0 0.0
DB time 100 2.0 100.0
DBWR checkpoint buffers written 0 0.0 0.0
DBWR checkpoints 0 0.0 0.0
DBWR transaction table writes 0 0.0 0.0
DBWR undo block writes 0 0.0 0.0
HSC Heap Segment Block Changes 321 6.3 321.0
Heap Segment Array Inserts 97 1.9 97.0
Heap Segment Array Updates 58 1.1 58.0
IMU Flushes 1 0.0 1.0
IMU Redo allocation size 356 7.0 356.0
IMU commits 0 0.0 0.0
IMU ktichg flush 0 0.0 0.0
IMU undo allocation size 1,224 24.0 1,224.0
LOB table id lookup cache misses 0 0.0 0.0
RowCR attempts 1 0.0 1.0
RowCR hits 1 0.0 1.0
SMON posted for undo segment shri 0 0.0 0.0
SQL*Net roundtrips to/from client 38 0.8 38.0
active txn count during cleanout 59 1.2 59.0
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 289 5.7 289.0
buffer is not pinned count 1,612 31.6 1,612.0
buffer is pinned count 292 5.7 292.0
bytes received via SQL*Net from c 15,486 303.7 15,486.0
bytes sent via SQL*Net to client 14,148 277.4 14,148.0
calls to get snapshot scn: kcmgss 1,643 32.2 1,643.0
calls to kcmgas 382 7.5 382.0
calls to kcmgcs 715 14.0 715.0
cell physical IO interconnect byt 12,401,152 243,159.8 ############
change write time 1 0.0 1.0
cleanout - number of ktugct calls 63 1.2 63.0
cleanouts and rollbacks - consist 0 0.0 0.0
cleanouts only - consistent read 1 0.0 1.0
cluster key scan block gets 87 1.7 87.0
cluster key scans 85 1.7 85.0
commit batch/immediate performed 2 0.0 2.0
commit batch/immediate requested 2 0.0 2.0
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: callbac 6 0.1 6.0
commit cleanouts 513 10.1 513.0

Instance Activity Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
commit cleanouts successfully com 507 9.9 507.0
commit immediate performed 2 0.0 2.0
commit immediate requested 2 0.0 2.0
commit txn count during cleanout 28 0.6 28.0
concurrency wait time 4 0.1 4.0
consistent changes 2 0.0 2.0
consistent gets 3,497 68.6 3,497.0
consistent gets - examination 1,602 31.4 1,602.0
consistent gets from cache 3,497 68.6 3,497.0
consistent gets from cache (fastp 1,739 34.1 1,739.0
cursor authentications 18 0.4 18.0
data blocks consistent reads - un 0 0.0 0.0
db block changes 4,907 96.2 4,907.0
db block gets 5,715 112.1 5,715.0
db block gets direct 0 0.0 0.0
db block gets from cache 5,715 112.1 5,715.0
db block gets from cache (fastpat 1,900 37.3 1,900.0
deferred (CURRENT) block cleanout 260 5.1 260.0
enqueue conversions 15 0.3 15.0
enqueue releases 1,762 34.6 1,762.0
enqueue requests 1,762 34.6 1,762.0
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 1,387 27.2 1,387.0
file io service time 0 0.0 0.0
file io wait time 20 0.4 20.0
free buffer requested 1,085 21.3 1,085.0
heap block compress 10 0.2 10.0
immediate (CR) block cleanout app 1 0.0 1.0
immediate (CURRENT) block cleanou 151 3.0 151.0
index crx upgrade (positioned) 0 0.0 0.0
index fast full scans (full) 2 0.0 2.0
index fetch by key 513 10.1 513.0
index scans kdiixs1 638 12.5 638.0
leaf node 90-10 splits 5 0.1 5.0
leaf node splits 25 0.5 25.0
lob reads 0 0.0 0.0
lob writes 0 0.0 0.0
lob writes unaligned 0 0.0 0.0
logons cumulative 3 0.1 3.0
max cf enq hold time 0 0.0 0.0
messages received 70 1.4 70.0
messages sent 70 1.4 70.0
min active SCN optimization appli 0 0.0 0.0
no work - consistent read gets 1,283 25.2 1,283.0
non-idle wait count 928 18.2 928.0
non-idle wait time 59 1.2 59.0
opened cursors cumulative 1,355 26.6 1,355.0
parse count (describe) 0 0.0 0.0
parse count (failures) 0 0.0 0.0
parse count (hard) 114 2.2 114.0
parse count (total) 844 16.6 844.0
parse time cpu 1 0.0 1.0
parse time elapsed 6 0.1 6.0
physical read IO requests 3 0.1 3.0
physical read bytes 24,576 481.9 24,576.0

Instance Activity Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
physical read total IO requests 537 10.5 537.0
physical read total bytes 8,531,968 167,293.5 8,531,968.0
physical read total multi block r 0 0.0 0.0
physical reads 3 0.1 3.0
physical reads cache 3 0.1 3.0
physical reads cache prefetch 0 0.0 0.0
physical reads prefetch warmup 0 0.0 0.0
physical write IO requests 0 0.0 0.0
physical write bytes 0 0.0 0.0
physical write total IO requests 202 4.0 202.0
physical write total bytes 3,869,184 75,866.4 3,869,184.0
physical write total multi block 5 0.1 5.0
physical writes 0 0.0 0.0
physical writes direct 0 0.0 0.0
physical writes direct (lob) 0 0.0 0.0
physical writes from cache 0 0.0 0.0
physical writes non checkpoint 0 0.0 0.0
pinned cursors current 1 0.0 1.0
recovery blocks read 0 0.0 0.0
recursive calls 18,850 369.6 18,850.0
recursive cpu usage 78 1.5 78.0
redo blocks checksummed by FG (ex 1,599 31.4 1,599.0
redo blocks read for recovery 0 0.0 0.0
redo blocks written 3,077 60.3 3,077.0
redo buffer allocation retries 0 0.0 0.0
redo entries 3,341 65.5 3,341.0
redo k-bytes read for recovery 0 0.0 0.0
redo k-bytes read total 0 0.0 0.0
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo size 1,575,412 30,890.4 1,575,412.0
redo size for direct writes 0 0.0 0.0
redo synch time 3 0.1 3.0
redo synch writes 3 0.1 3.0
redo wastage 23,104 453.0 23,104.0
redo write time 22 0.4 22.0
redo writes 62 1.2 62.0
rollback changes - undo records a 4 0.1 4.0
rollbacks only - consistent read 0 0.0 0.0
rows fetched via callback 89 1.8 89.0
scheduler wait time 0 0.0 0.0
session cursor cache hits 1,228 24.1 1,228.0
session logical reads 9,212 180.6 9,212.0
session uga memory max 10,034,256 196,750.1 ############
shared hash latch upgrades - no w 71 1.4 71.0
sorts (memory) 132 2.6 132.0
sorts (rows) 6,865 134.6 6,865.0
sql area evicted 8 0.2 8.0
sql area purged 0 0.0 0.0
switch current to new buffer 85 1.7 85.0
table fetch by rowid 231 4.5 231.0
table fetch continued row 0 0.0 0.0
table scan blocks gotten 393 7.7 393.0
table scan rows gotten 1,209 23.7 1,209.0
table scans (short tables) 89 1.8 89.0
total cf enq hold time 60 1.2 60.0

Instance Activity Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
total number of cf enq holders 9 0.2 9.0
total number of times SMON posted 0 0.0 0.0
transaction rollbacks 2 0.0 2.0
undo change vector size 491,948 9,646.0 491,948.0
user I/O wait time 0 0.0 0.0
user calls 58 1.1 58.0
user commits 1 0.0 1.0
user rollbacks 0 0.0 0.0
workarea executions - optimal 91 1.8 91.0
write clones created in backgroun 0 0.0 0.0
-------------------------------------------------------------

Instance Activity Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Statistics with absolute values (should not be diffed)

Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 31 31
opened cursors current 30 30
session cursor cache count 2,952 2,987
-------------------------------------------------------------

Instance Activity Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Statistics identified by '(derived)' come from sources other than SYSSTAT

Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 0 .00
-------------------------------------------------------------

OS Statistics DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name

Statistic Total
------------------------- ----------------------
BUSY_TIME 118
IDLE_TIME 4,780
IOWAIT_TIME 14
NICE_TIME 0
SYS_TIME 44
USER_TIME 63
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 8,374,050,816
NUM_CPUS 1
GLOBAL_RECEIVE_SIZE_MAX 4,194,304
GLOBAL_SEND_SIZE_MAX 1,048,576
TCP_RECEIVE_SIZE_DEFAULT 87,380
TCP_RECEIVE_SIZE_MAX 6,291,456
TCP_RECEIVE_SIZE_MIN 4,096
TCP_SEND_SIZE_DEFAULT 16,384
TCP_SEND_SIZE_MAX 4,194,304
TCP_SEND_SIZE_MIN 4,096
-------------------------------------------------------------

OS Statistics - detail DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

Snap Snapshot
Id Day Time Load %Busy %User %System %WIO %WCPU
------ --------------- ------ ------ ------ ------- ------ ------
1 Thu 25 10:40:08 .1
2 Thu 25 10:40:59 .1 2.4 1.3 .9 0.3
-------------------------------------------------------------

IO Stat by Function - summary DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->Data Volume values suffixed with M,G,T,P are in multiples of 1024,
other values suffixed with K,M,G,T,P are in multiples of 1000
->ordered by Data Volume (Read+Write) desc

---------- Read --------- --------- Write -------- --- Wait ----
Data Requests Data Data Requests Data Avg
Function Volume /sec Vol/sec Volume /sec Vol/sec Count Tm(ms)
--------------- ------ -------- -------- ------ -------- -------- ------ ------
Others 9M 11.3 .2M 2M 2.7 .0M 718 0.0
Buffer Cache Re .1 4 0.0
-------------------------------------------------------------

IO Stat by Function - detail DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->ordered by Data Volume (Read+Write) desc

----------- Read ---------- ----------- Write ---------
Small Large Small Large Small Large Small Large
Read Read Data Data Write Write Data Data
Function Reqs Reqs Read Read Reqs Reqs Writn Writn
------------------ ------ ------ ------ ------ ------ ------ ------ ------
Others 578 9M 140 2M
Buffer Cache Reads 4
-------------------------------------------------------------

Tablespace IO Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SYSTEM
4 0 0.0 1.0 0 0 0 0.0
-------------------------------------------------------------

File IO Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File

Tablespace Filename
------------------------ ----------------------------------------------------
Av Mx Av
Av Rd Rd Av Av Buffer BufWt
Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
SYSTEM /u01/app/oracle/oradata/DBPR11G/system01.dbf
4 0 0.0 1 1.0 0 0 0

-------------------------------------------------------------

File Read Histogram Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->Number of single block reads in each time range
->Tempfiles are not included
->ordered by Tablespace, File

Tablespace Filename
------------------------ ----------------------------------------------------
0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
SYSTEM /u01/app/oracle/oradata/DBPR11G/system01.dbf
4 0 0 0 0 0

-------------------------------------------------------------

Instance Recovery Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> B: Begin snapshot, E: End snapshot

Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- --------- --------- ---------- --------- ------------
B 0 13 1311 2613 3815 165888 3815
E 0 14 1814 3905 5562 165888 5562
-------------------------------------------------------------

Memory Target Advice DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Advice Reset: if this is null, the data shown has been diffed between
the Begin and End snapshots. If this is 'Y', the advisor has been
reset during this interval due to memory resize operations, and
the data shown is since the reset operation.

Memory Size Est. Advice
Memory Size (M) Factor DB time (s) Reset
--------------- ----------- ------------ ------
800 .3 1
1,600 .5 1
2,400 .8 1
3,200 1.0 1
4,000 1.3 1
4,800 1.5 1
5,600 1.8 1
6,400 2.0 1
-------------------------------------------------------------

Memory Dynamic Components DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Op - memory resize Operation
-> Cache: D: Default, K: Keep, R: Recycle
-> Mode: DEF: DEFerred mode, IMM: IMMediate mode

Begin Snap End Snap Op Last Op
Cache Size (M) Size (M) Count Type/Mode Last Op Time
---------------------- ---------- -------- ------- ---------- ---------------
D:buffer cache 1,376 0 INITIA/
PGA Target 1,344 0 STATIC
SGA Target 1,856 0 STATIC
java pool 16 0 STATIC
large pool 16 0 STATIC
shared pool 416 0 STATIC
-------------------------------------------------------------

Buffer Pool Advisory DB/Inst: DBPR11G/DBPR11G End Snap: 2
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate

Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D 128 .1 16 1.0 12 17 27.0
D 256 .2 32 1.0 12 17 27.0
D 384 .3 47 1.0 12 17 27.0
D 512 .4 63 1.0 12 17 27.0
D 640 .5 79 1.0 12 17 27.0
D 768 .6 95 1.0 12 17 27.0
D 896 .7 110 1.0 12 17 27.0
D 1,024 .7 126 1.0 12 17 27.0
D 1,152 .8 142 1.0 12 17 27.0
D 1,280 .9 158 1.0 12 17 27.0
D 1,376 1.0 169 1.0 12 17 27.0
D 1,408 1.0 173 1.0 12 17 27.0
D 1,536 1.1 189 1.0 12 17 27.0
D 1,664 1.2 205 1.0 12 17 27.0
D 1,792 1.3 221 1.0 12 17 27.0
D 1,920 1.4 236 1.0 12 17 27.0
D 2,048 1.5 252 1.0 12 17 27.0
D 2,176 1.6 268 1.0 12 17 27.0
D 2,304 1.7 284 1.0 12 17 27.0
D 2,432 1.8 299 1.0 12 17 27.0
D 2,560 1.9 315 1.0 12 17 27.0
-------------------------------------------------------------

Buffer Pool Statistics DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers. Units of K, M, G are divided by 1000

Free Writ Buffer
Pool Buffer Physical Physical Buffer Comp Busy
P Buffers Hit% Gets Reads Writes Waits Wait Waits
--- ------- ---- -------------- ------------ ----------- ------- ---- ----------
D 169K 100 10,889 4 0 0 0 1
-------------------------------------------------------------

PGA Aggr Target Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> B: Begin snap E: End snap (rows identified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all WorkAreas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to WorkAreas
-> %Auto W/A Mem - percentage of WorkArea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of WorkArea memory under Manual control

PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 27 0

PGA Aggr Target Histogram DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Optimal Executions are purely in-memory operations

Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 42 42 0 0
64K 128K 2 2 0 0
128K 256K 5 5 0 0
512K 1024K 23 23 0 0
4M 8M 2 2 0 0
-------------------------------------------------------------

PGA Memory Advisory DB/Inst: DBPR11G/DBPR11G End Snap: 2
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0

Estd Extra Estd
PGA Aggr W/A MB Estd Time PGA Estd PGA
Target Size W/A MB Read/Written to Process Cache Overalloc
Est (MB) Factr Processed to Disk Bytes (s) Hit % Count
---------- ------ -------------- -------------- ---------- ------ ----------
168 0.1 186 0 0.0 100.0 0
336 0.3 186 0 0.0 100.0 0
672 0.5 186 0 0.0 100.0 0
1,008 0.8 186 0 0.0 100.0 0
1,344 1.0 186 0 0.0 100.0 0
1,613 1.2 186 0 0.0 100.0 0
1,882 1.4 186 0 0.0 100.0 0
2,150 1.6 186 0 0.0 100.0 0
2,419 1.8 186 0 0.0 100.0 0
2,688 2.0 186 0 0.0 100.0 0
4,032 3.0 186 0 0.0 100.0 0
5,376 4.0 186 0 0.0 100.0 0
8,064 6.0 186 0 0.0 100.0 0
10,752 8.0 186 0 0.0 100.0 0
-------------------------------------------------------------

Process Memory Summary Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> B: Begin snap E: End snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of
processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc

Hist Num
Avg Std Dev Max Max Procs
Alloc Used Freeabl Alloc Alloc Alloc Alloc or
Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs
- -------- --------- --------- -------- -------- ------- ------- ------ ------
B -------- 178.3 131.5 34.6 5.4 8.4 29 29 33
Other 143.0 4.3 7.1 28 28 33
Freeable 34.6 .0 3.1 5.0 17 11
PL/SQL .6 .5 .0 .1 0 0 31
SQL .1 .1 .0 .0 0 7 7
E -------- 176.4 130.9 35.1 5.3 8.3 29 29 33
Other 140.5 4.3 7.1 28 28 33
Freeable 35.1 .0 3.2 4.9 17 11
PL/SQL .6 .5 .0 .1 0 0 31
SQL .2 .1 .0 .0 0 6 7
-------------------------------------------------------------

Top Process Memory (by component) DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> ordered by Begin/End snapshot, Alloc (MB) desc

Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 25 ARC1 -------- 29.5 27.6 1.2 29.5 29.5
Other 28.3 28.3 28.3
Freeable 1.2 .0 1.2
PL/SQL .0 .0 .0 .0
27 ARC3 -------- 29.5 27.6 1.2 29.5 29.5
Other 28.3 28.3 28.3
Freeable 1.2 .0 1.2
PL/SQL .0 .0 .0 .0
24 ARC0 -------- 28.4 10.8 17.1 28.4 28.4
Freeable 17.1 .0 17.1
Other 11.4 11.4 11.4
PL/SQL .0 .0 .0 .0
26 ARC2 -------- 11.7 10.9 .0 11.7 11.7
Other 11.7 11.7 11.7
PL/SQL .0 .0 .0 .0
28 NSA2 -------- 11.6 10.8 .1 11.6 11.6
Other 11.4 11.4 11.4
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
11 LGWR -------- 11.5 10.9 .0 11.5 11.5
Other 11.5 11.5 11.5
PL/SQL .0 .0 .0 .0
20 TNS V1-V3 --- 10.2 2.0 7.1 10.2 10.2
Freeable 7.1 .0 7.1
Other 2.8 2.8 2.8
PL/SQL .2 .2 .2 .2
SQL .1 .1 .1 6.9
10 DBW0 -------- 6.6 6.3 .0 6.6 6.6
Other 6.6 6.6 6.6
PL/SQL .0 .0 .0 .0
21 NSV1 -------- 4.9 4.8 .0 4.9 4.9
Other 4.9 4.9 4.9
PL/SQL .0 .0 .0 .0
13 SMON -------- 4.7 1.1 2.5 4.7 4.7
Freeable 2.5 .0 2.5
Other 2.2 2.2 2.2
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 2.1
15 MMON -------- 4.6 1.9 1.1 4.6 4.6
Other 3.4 3.4 3.4
Freeable 1.1 .0 1.1
PL/SQL .1 .1 .1 .1
SQL .0 .0 .0 1.1
34 CJQ0 -------- 4.3 1.2 2.9 4.3 4.3
Freeable 2.9 .0 2.9
Other 1.3 1.3 1.3
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 1.4
23 RSM0 -------- 2.2 .8 1.1 2.2 2.2
Freeable 1.1 .0 1.1
Other 1.1 1.1 1.1
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .3

Top Process Memory (by component) DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> ordered by Begin/End snapshot, Alloc (MB) desc

Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 19 DMON -------- 1.4 .8 .0 1.4 1.4
Other 1.4 1.4 1.4
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .2
14 RECO -------- 1.4 .8 .3 1.4 1.4
Other 1.2 1.2 1.2
Freeable .3 .0 .3
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .5
41 Q000 -------- 1.4 1.1 .0 1.4 1.4
Other 1.2 1.2 1.2
PL/SQL .2 .2 .2 .2
SQL .0 .0 .0 .2
8 DIA0 -------- 1.4 1.1 .1 1.4 1.4
Other 1.3 1.3 1.3
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
17 D000 -------- 1.3 1.0 .0 1.3 1.3
Other 1.3 1.3 1.3
12 CKPT -------- 1.0 .7 .0 1.0 1.0
Other 1.0 1.0 1.0
PL/SQL .0 .0 .0 .0
E 25 ARC1 -------- 29.5 27.6 1.2 29.5 29.5
Other 28.3 28.3 28.3
Freeable 1.2 .0 1.2
PL/SQL .0 .0 .0 .0
27 ARC3 -------- 29.5 27.6 1.2 29.5 29.5
Other 28.3 28.3 28.3
Freeable 1.2 .0 1.2
PL/SQL .0 .0 .0 .0
24 ARC0 -------- 28.4 10.8 17.1 28.4 28.4
Freeable 17.1 .0 17.1
Other 11.4 11.4 11.4
PL/SQL .0 .0 .0 .0
26 ARC2 -------- 11.7 10.9 .0 11.7 11.7
Other 11.7 11.7 11.7
PL/SQL .0 .0 .0 .0
28 NSA2 -------- 11.6 10.8 .1 11.6 11.6
Other 11.4 11.4 11.4
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
11 LGWR -------- 11.5 10.9 .0 11.5 11.5
Other 11.5 11.5 11.5
PL/SQL .0 .0 .0 .0
20 TNS V1-V3 --- 8.3 1.4 6.5 8.3 8.3
Freeable 6.5 .0 6.5
Other 1.6 1.6 1.6
PL/SQL .2 .2 .2 .2
SQL .1 .1 .1 6.2
10 DBW0 -------- 6.6 6.3 .0 6.6 6.6
Other 6.6 6.6 6.6
PL/SQL .0 .0 .0 .0
21 NSV1 -------- 4.9 4.8 .0 4.9 4.9

Top Process Memory (by component) DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> ordered by Begin/End snapshot, Alloc (MB) desc

Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB)

(MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 21 Other 4.9 4.9 4.9
PL/SQL .0 .0 .0 .0
13 SMON -------- 4.7 1.1 2.5 4.7 4.7
Freeable 2.5 .0 2.5
Other 2.2 2.2 2.2
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 2.1
15 MMON -------- 4.6 1.9 2.3 4.6 4.6
Freeable 2.3 .0 2.3
Other 2.2 2.2 2.2
PL/SQL .1 .1 .1 .1
SQL .0 .0 .0 1.1
34 CJQ0 -------- 4.3 1.2 2.9 4.3 4.3
Freeable 2.9 .0 2.9
Other 1.3 1.3 1.3
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 1.4
23 RSM0 -------- 2.2 .8 1.1 2.2 2.2
Freeable 1.1 .0 1.1
Other 1.1 1.1 1.1
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .3
19 DMON -------- 1.4 .8 .0 1.4 1.4
Other 1.4 1.4 1.4
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .2
14 RECO -------- 1.4 .8 .3 1.4 1.4
Other 1.2 1.2 1.2
Freeable .3 .0 .3
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .5
41 Q000 -------- 1.4 1.1 .0 1.4 1.4
Other 1.2 1.2 1.2
PL/SQL .2 .2 .2 .2
SQL .0 .0 .0 .2
8 DIA0 -------- 1.4 1.1 .1 1.4 1.4
Other 1.3 1.3 1.3
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
17 D000 -------- 1.3 1.0 .0 1.3 1.3
Other 1.3 1.3 1.3
12 CKPT -------- 1.0 .7 .0 1.0 1.0
Other 1.0 1.0 1.0
PL/SQL .0 .0 .0 .0
-------------------------------------------------------------

Latch Activity DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
AQ deq hash table latch 1 0.0 0 0
ASM db client latch 100 0.0 0 0
ASM map operation hash t 1 0.0 0 0
Change Notification Hash 17 0.0 0 0
Consistent RBA 62 0.0 0 0
DML lock allocation 590 0.0 0 0
DMON Network Error List 1 0.0 0 0
DMON Work Queues Latch 4 0.0 0 0
Event Group Locks 4 0.0 0 0
FAL Queue 2 0.0 0 0
FIB s.o chain latch 16 0.0 0 0
FOB s.o list latch 46 0.0 0 0
File State Object Pool P 1 0.0 0 0
IPC stats buffer allocat 1 0.0 0 0
In memory undo latch 106 0.0 0 17 0.0
JS Sh mem access 1 0.0 0 0
JS queue access latch 1 0.0 0 0
JS queue state obj latch 396 0.0 0 0
JS slv state obj latch 1 0.0 0 0
KFC FX Hash Latch 1 0.0 0 0
KFC Hash Latch 1 0.0 0 0
KFCL LE Freelist 1 0.0 0 0
KGNFS-NFS:SHM structure 1 0.0 0 0
KGNFS-NFS:SVR LIST 1 0.0 0 0
KJC message pool free li 1 0.0 0 0
KJCT flow control latch 1 0.0 0 0
KMG MMAN ready and start 17 0.0 0 0
KTF sga latch 1 0.0 0 17 0.0
KWQP Prop Status 1 0.0 0 0
LGWR NS Write 1 0.0 0 0
Locator state objects po 1 0.0 0 0
Lsod array latch 1 0.0 0 0
MQL Tracking Latch 0 0 1 0.0
Managed Standby Recovery 1 0.0 0 0
Memory Management Latch 1 0.0 0 17 0.0
Memory Queue 1 0.0 0 0
Memory Queue Message Sub 1 0.0 0 0
Memory Queue Message Sub 1 0.0 0 0
Memory Queue Message Sub 1 0.0 0 0
Memory Queue Message Sub 1 0.0 0 0
Memory Queue Subscriber 1 0.0 0 0
Mutex 1 0.0 0 0
Mutex Stats 1 0.0 0 0
NSV command ID generatio 1 0.0 0 0
NSV creation/termination 1 0.0 0 0
OS process 16 0.0 0 0
OS process allocation 27 0.0 0 0
OS process: request allo 6 0.0 0 0
PL/SQL warning settings 18 0.0 0 0
PX hash array latch 1 0.0 0 0

Latch Activity DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
QMT 1 0.0 0 0
RSM process latch 6 0.0 0 0
Request id generation la 1 0.0 0 0
SGA blob parent 1 0.0 0 0
SGA bucket locks 1 0.0 0 0
SGA heap locks 1 0.0 0 0
SGA pool locks 1 0.0 0 0
SQL memory manager latch 3 0.0 0 50 0.0
SQL memory manager worka 3,514 0.0 0 0
Shared B-Tree 6 0.0 0 0
Streams Generic 1 0.0 0 0
Testing 1 0.0 0 0
Token Manager 1 0.0 0 0
WCR: sync 1 0.0 0 0
Write State Object Pool 1 0.0 0 0
XDB NFS Security Latch 1 0.0 0 0
XDB unused session pool 1 0.0 0 0
XDB used session pool 1 0.0 0 0
active checkpoint queue 17 0.0 0 0
active service list 122 0.0 0 562 0.0
archive control 448 0.0 0 0
archive process latch 3 0.0 0 0
buffer pool 1 0.0 0 0
business card 1 0.0 0 0
cache buffer handles 818 0.0 0 0
cache buffers chains 24,362 0.0 0 926 0.0
cache buffers lru chain 1,093 0.0 0 43 0.0
call allocation 112 0.0 0 0
cas latch 1 0.0 0 0
change notification clie 1 0.0 0 0
channel handle pool latc 7 0.0 0 0
channel operations paren 591 0.0 0 0
checkpoint queue latch 945 0.0 0 938 0.0
client/application info 20 0.0 0 0
compile environment latc 3 0.0 0 0
corrupted undo seg latch 14 0.0 0 0
cp cmon/server latch 1 0.0 0 0
cp pool latch 1 0.0 0 0
cp server hash latch 1 0.0 0 0
cvmap freelist lock 1 0.0 0 0
dml lock allocation 96 0.0 0 0
done queue latch 1 0.0 0 0
dummy allocation 7 0.0 0 0
enqueue hash chains 3,349 0.0 0 0
enqueues 2,186 0.0 0 0
fifth spare latch 1 0.0 0 0
file cache latch 78 0.0 0 0
flashback copy 1 0.0 0 0
gc element 1 0.0 0 0
gcs commit scn state 1 0.0 0 0

Latch Activity DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
gcs partitioned table ha 1 0.0 0 0
gcs pcm hashed value buc 1 0.0 0 0
gcs resource freelist 1 0.0 0 0
gcs resource hash 1 0.0 0 0
gcs resource scan list 1 0.0 0 0
gcs shadows freelist 1 0.0 0 0
ges domain table 1 0.0 0 0
ges enqueue table freeli 1 0.0 0 0
ges group table 1 0.0 0 0
ges process hash list 1 0.0 0 0
ges process parent latch 1 0.0 0 0
ges resource hash list 1 0.0 0 0
ges resource scan list 1 0.0 0 0
ges resource table freel 1 0.0 0 0
ges value block free lis 1 0.0 0 0
global KZLD latch for me 1 0.0 0 0
global tx hash mapping 1 0.0 0 0
granule operation 1 0.0 0 0
hash table column usage 0 0 7,210 0.0
hash table modification 25 0.0 0 0
heartbeat check 1 0.0 0 0
intra txn parallel recov 1 0.0 0 0
io pool granule metadata 1 0.0 0 0
job workq parent latch 1 0.0 0 0
job_queue_processes para 12 0.0 0 0
k2q lock allocation 1 0.0 0 0
kdlx hb parent latch 1 0.0 0 0
kgb parent 1 0.0 0 0
kks stats 166 0.0 0 0
ksfv messages 1 0.0 0 0
kss move lock 3 0.0 0 0
ksuosstats global area 8 0.0 0 0
ksv allocation latch 4 0.0 0 0
ksv class latch 5 0.0 0 0
ksv msg queue latch 1 0.0 0 0
ksz_so allocation latch 6 0.0 0 0
kwqbsn:qsga 5 0.0 0 0
lgwr LWN SCN 62 0.0 0 0
list of block allocation 17 0.0 0 0
loader state object free 48 0.0 0 0
lob segment dispenser la 1 0.0 0 0
lob segment hash table l 1 0.0 0 0
lob segment query latch 1 0.0 0 0
lock DBA buffer during m 1 0.0 0 0
logical standby cache 1 0.0 0 0
logminer context allocat 1 0.0 0 0
logminer work area 1 0.0 0 0
longop free list parent 1 0.0 0 0
managed standby latch 1 0.0 0 0
mapped buffers lru chain 1 0.0 0 0

Latch Activity DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
message pool operations 16 0.0 0 0
messages 810 0.0 0 0
mostly latch-free SCN 62 0.0 0 0
msg queue latch 1 0.0 0 0
name-service namespace b 1 0.0 0 0
object queue header heap 66 0.0 0 0
object queue header oper 1,971 0.0 0 0
object stats modificatio 175 0.0 0 0
parallel query alloc buf 9 0.0 0 0
parallel query stats 1 0.0 0 0
parameter list 4 0.0 0 0
parameter table manageme 6 0.0 0 0
peshm 1 0.0 0 0
pesom_free_list 1 0.0 0 0
pesom_hash_node 1 0.0 0 0
post/wait queue 6 0.0 0 3 0.0
process allocation 7 0.0 0 3 0.0
process group creation 6 0.0 0 0
process queue 1 0.0 0 0
process queue reference 1 0.0 0 0
qmn task queue latch 8 0.0 0 0
query server freelists 1 0.0 0 0
queuing load statistics 1 0.0 0 0
recovery domain hash lis 1 0.0 0 0
redo allocation 159 0.0 0 3,136 0.0
redo copy 1 0.0 0 3,136 0.1
redo writing 277 0.0 0 0
resmgr group change latc 4 0.0 0 0
resmgr:active threads 7 0.0 0 0
resmgr:actses change gro 4 0.0 0 0
resmgr:actses change sta 1 0.0 0 0
resmgr:free threads list 6 0.0 0 0
resmgr:plan CPU method 1 0.0 0 0
resmgr:resource group CP 1 0.0 0 0
resmgr:schema config 1 0.0 0 0
resmgr:session queuing 1 0.0 0 0
rm cas latch 1 0.0 0 0
row cache objects 28,610 0.0 0 0
rules engine rule set st 101 0.0 0 0
second spare latch 1 0.0 0 0
sequence cache 6 0.0 0 0
session allocation 107 0.0 0 0
session idle bit 141 0.0 0 0
session queue latch 1 0.0 0 0
session state list latch 8 0.0 0 0
session switching 16 0.0 0 0
session timer 18 0.0 0 0
shared pool 8,480 0.0 0 0
shared pool sim alloc 4 0.0 0 0
shared pool simulator 563 0.0 0 0

Latch Activity DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
sim partition latch 1 0.0 0 0
simulator hash latch 650 0.0 0 0
simulator lru latch 1 0.0 0 547 0.0
sort extent pool 3 0.0 0 0
space background task la 37 0.0 0 34 0.0
tablespace key chain 1 0.0 0 0
test excl. parent l0 1 0.0 0 0
test excl. parent2 l0 1 0.0 0 0
third spare latch 1 0.0 0 0
threshold alerts latch 11 0.0 0 0
transaction allocation 242 0.0 0 0
undo global data 1,041 0.0 0 0
virtual circuit buffers 1 0.0 0 0
virtual circuit holder 1 0.0 0 0
virtual circuit queues 1 0.0 0 0
-------------------------------------------------------------

Dictionary Cache Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->"Pct Misses" should be very low (< 2% in most cases)
->"Final Usage" is the number of cache entries being used in End Snapshot

Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control 1 0.0 0 0 1
dc_global_oids 26 0.0 0 0 43
dc_histogram_defs 2,551 18.1 0 0 4,884
dc_object_grants 20 0.0 0 0 24
dc_objects 1,137 0.0 0 0 2,392
dc_profiles 1 0.0 0 0 1
dc_rollback_segments 24 0.0 0 0 22
dc_segments 815 8.1 0 169 780
dc_tablespace_quotas 404 0.0 0 0 2
dc_tablespaces 2,553 0.0 0 0 8
dc_users 3,217 0.0 0 0 270
global database name 37 0.0 0 0 1
outstanding_alerts 4 0.0 0 0 5
-------------------------------------------------------------


Library Cache Activity DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->"Pct Misses" should be very low

Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
SQL AREA 184 52.2 2,497 11.9 46 38
TABLE/PROCEDURE 1,201 0.0 2,082 4.6 43 0
BODY 9 0.0 16 0.0 0 0
TRIGGER 1 0.0 1 0.0 0 0
INDEX 36 0.0 36 100.0 0 0
EDITION 3 0.0 4 0.0 0 0
DBLINK 3 0.0 0 0 0
SCHEMA 2 0.0 0 0 0
-------------------------------------------------------------

Rule Sets DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> * indicates Rule Set activity (re)started between Begin/End snaps
-> Top 25 ordered by Evaluations desc

No-SQL SQL
Rule * Eval/sec Reloads/sec Eval % Eval %
----------------------------------- - ------------ ----------- ------ ------
SYS.ALERT_QUE_R 0 0 0 0
-------------------------------------------------------------

Shared Pool Advisory DB/Inst: DBPR11G/DBPR11G End Snap: 2
-> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid

Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem
Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
128 .3 0 28 845 1.0 23 1.9 601
176 .4 49 3,548 845 1.0 23 1.9 76,407
224 .5 90 6,034 856 1.0 12 1.0 77,610
272 .7 90 6,034 856 1.0 12 1.0 77,610
320 .8 90 6,034 856 1.0 12 1.0 77,610
368 .9 90 6,034 856 1.0 12 1.0 77,610
416 1.0 90 6,034 856 1.0 12 1.0 77,610
464 1.1 90 6,034 856 1.0 12 1.0 77,610
512 1.2 90 6,034 856 1.0 12 1.0 77,610
560 1.3 90 6,034 856 1.0 12 1.0 77,610
608 1.5 90 6,034 856 1.0 12 1.0 77,610
656 1.6 90 6,034 856 1.0 12 1.0 77,610
704 1.7 90 6,034 856 1.0 12 1.0 77,610
752 1.8 90 6,034 856 1.0 12 1.0 77,610
800 1.9 90 6,034 856 1.0 12 1.0 77,610
848 2.0 90 6,034 856 1.0 12 1.0 77,610
-------------------------------------------------------------

SGA Target Advisory DB/Inst: DBPR11G/DBPR11G End Snap: 2

SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- -------- -------- ----------- --------------
464 .3 63 1.0 11,955
928 .5 63 1.0 11,955
1,392 .8 63 1.0 11,955
1,856 1.0 63 1.0 11,955
2,320 1.3 63 1.0 11,955
2,784 1.5 63 1.0 11,955
3,248 1.8 63 1.0 11,955
3,712 2.0 63 1.0 11,955
-------------------------------------------------------------

SGA Memory Summary DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ -------------------- --------------------
Database Buffers 1,442,840,576
Fixed Size 2,217,952
Redo Buffers 16,343,040
Variable Size 1,879,050,272
-------------------- --------------------
sum 3,340,451,840
-------------------------------------------------------------


SGA breakdown difference DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Top 35 rows by size, ordered by Pool, Name (note rows with null values for
Pool column, or Names showing free memory are always shown)
-> Null value for Begin MB or End MB indicates the size of that Pool/Name was
insignificant, or zero in that snapshot

Pool Name Begin MB End MB % Diff
------ ------------------------------ -------------- -------------- --------
java p free memory 16.0 16.0 0.00
large PX msg pool .5 .5 0.00
large free memory 15.5 15.5 0.00
shared CCUR 9.6 9.8 2.74
shared FileOpenBlock 9.8 9.8 0.00
shared KGLH0 7.1 7.1 0.00
shared KGLHD 5.5 5.6 1.06
shared KGLS 14.1 14.3 1.12
shared KGLSG 5.0 5.0 0.00
shared PCUR 7.6 7.8 2.95
shared PLDIA 7.0 7.0 0.00
shared PLMCD 5.9 5.9 0.00
shared SQLA 43.3 45.0 3.94
shared db_block_hash_buckets 11.1 11.1 0.00
shared free memory 212.6 209.8 -1.33
shared row cache 7.2 7.2 0.00
buffer_cache 1,376.0 1,376.0 0.00
fixed_sga 2.1 2.1 0.00
log_buffer 15.6 15.6 0.00
-------------------------------------------------------------

SQL Memory Statistics DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

Begin End % Diff
-------------- -------------- --------------
Avg Cursor Size (KB): 27.19 27.17 -.08
Cursor to Parent ratio: 1.10 1.10 -.11
Total Cursors: 1,733 1,763 1.70
Total Parents: 1,574 1,603 1.81
-------------------------------------------------------------

init.ora Parameters DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
archive_lag_target 0
audit_file_dest /u01/app/oracle/admin/DBPR11G/adu
mp
audit_trail DB
compatible 11.2.0.0.0
control_files /u01/app/oracle/oradata/DBPR11G/c
ontrol01.ctl, /u01/app/oracle/fla
sh_recovery_area/DBPR11G/control0
2.ctl
cursor_sharing EXACT
db_block_size 8192
db_domain
db_name DBPR11G
db_recovery_file_dest /u01/app/oracle/flash_recovery_ar
ea
db_recovery_file_dest_size 4070572032
dg_broker_start TRUE
diagnostic_dest /u01/app/oracle
dispatchers (PROTOCOL=TCP) (SERVICE=DBPR11GXD
B)
fal_client DBPR11G
fal_server DBSTP11G
log_archive_config dg_config=(DBPR11G,dbstp11g)
log_archive_dest_1 LOCATION=/u01/app/oracle/oradata/
arch/DBPR11G VALID_FOR=(ALL_LOGFI
LES,ALL_ROLES) DB_UNIQUE_NAME=DBP
R11G
log_archive_dest_2 service="DBSTP11G", LGWR ASYNC NO
AFFIRM delay=0 optional compressi
on=disable max_failure=0 max_conn
ections=1 reopen=300 db_unique_na
me="dbstp11g" net_timeout=30, val
id_for=(all_logfiles,primary_role
)
log_archive_dest_state_2 ENABLE
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
log_archive_min_succeed_dest 1
log_archive_trace 0
memory_target 3355443200
open_cursors 300
processes 150
remote_login_passwordfile EXCLUSIVE
standby_file_management AUTO
undo_tablespace UNDOTBS1
-------------------------------------------------------------

End of Report ( sp_1_2.lst )