/*
|| Oracle 11g ADDM Enhancements Reports
*/
/*
|| Report 1:
|| ADDM Database Analysis for Cluster Database RACDB
*/
ADDM Report for Task 'ADDM_100'
-------------------------------
Analysis Period
---------------
AWR snapshot range from 147 to 150.
Time period starts at 21-OCT-08 08.09.52 PM
Time period ends at 21-OCT-08 08.55.20 PM
Analysis Target
---------------
Database 'RACDB' with DB ID 646658104.
Database version 11.1.0.6.0.
ADDM performed an analysis of all instances.
Activity During the Analysis Period
-----------------------------------
Total database time was 9285 seconds.
The average number of active sessions was 3.4.
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
-------------------------------- ------------------- ---------------
1 Top SQL by DB Time 2.07 | 60.74 5
2 Top SQL by "Cluster" Wait 1.62 | 47.48 5
3 Session Connect and Disconnect 1.26 | 36.93 1
4 Buffer Busy .93 | 27.4 3
5 Unusual "Concurrency" Wait Event .81 | 23.67 5
6 Buffer Busy .35 | 10.18 2
7 Shared Pool Latches .27 | 8.08 0
8 Hard Parse .19 | 5.5 0
9 PL/SQL Execution .07 | 2.2 1
10 I/O Throughput .07 | 2.11 3
11 Interconnect Latency .06 | 1.82 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Top SQL by DB Time
Impact is 2.07 active sessions, 60.74% of total activity.
---------------------------------------------------------
SQL statements consuming significant database time were found.
Recommendation 1: SQL Tuning
Estimated benefit is .58 active sessions, 17.01% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "1dmv3vasxtjtu".
Related Object
SQL statement with SQL_ID 1dmv3vasxtjtu.
INSERT INTO AP.INVOICE_ITEMS ( INVOICE_ID ,LINE_ITEM_NBR ,ACTIVE_IND
,PRODUCT_ID ,QTY ,EXTENDED_AMT ,TAXABLE_IND ) VALUES( :B6 ,:B5 ,'Y'
,:B4 ,:B3 ,:B2 ,:B1 )
Rationale
SQL statement with SQL_ID "1dmv3vasxtjtu" was executed 104400 times and
had an average elapsed time of 0.018 seconds.
Recommendation 2: SQL Tuning
Estimated benefit is .39 active sessions, 11.34% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "8wzc2r9wadx8u".
Related Object
SQL statement with SQL_ID 8wzc2r9wadx8u and PLAN_HASH 897482116.
UPDATE ADMIN.NEXT_IDS SET VALUE = VALUE + 1 WHERE OWNER = UPPER(:B3 )
AND TABLE_NAME = UPPER(:B2 ) AND KEY_ID = UPPER(:B1 ) RETURNING VALUE
INTO :O0
Rationale
SQL statement with SQL_ID "8wzc2r9wadx8u" was executed 108000 times and
had an average elapsed time of 0.01 seconds.
Recommendation 3: SQL Tuning
Estimated benefit is .23 active sessions, 6.67% of total activity.
------------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "0wvttswphs2ky". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 0wvttswphs2ky.
BEGIN
AP.PKG_LOAD_GENERATOR.RandomDML(250);
COMMIT;
END;
Rationale
SQL statement with SQL_ID "0wvttswphs2ky" was executed 3 times and had
an average elapsed time of 225 seconds.
Finding 2: Top SQL by "Cluster" Wait
Impact is 1.62 active sessions, 47.48% of total activity.
---------------------------------------------------------
SQL statements responsible for significant inter-instance messaging were
found.
Recommendation 1: SQL Tuning
Estimated benefit is .58 active sessions, 17.01% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "1dmv3vasxtjtu".
Related Object
SQL statement with SQL_ID 1dmv3vasxtjtu.
INSERT INTO AP.INVOICE_ITEMS ( INVOICE_ID ,LINE_ITEM_NBR ,ACTIVE_IND
,PRODUCT_ID ,QTY ,EXTENDED_AMT ,TAXABLE_IND ) VALUES( :B6 ,:B5 ,'Y'
,:B4 ,:B3 ,:B2 ,:B1 )
Rationale
SQL statement with SQL_ID "1dmv3vasxtjtu" was executed 104400 times and
had an average elapsed time of 0.018 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.014
seconds.
Recommendation 2: SQL Tuning
Estimated benefit is .39 active sessions, 11.34% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "8wzc2r9wadx8u".
Related Object
SQL statement with SQL_ID 8wzc2r9wadx8u and PLAN_HASH 897482116.
UPDATE ADMIN.NEXT_IDS SET VALUE = VALUE + 1 WHERE OWNER = UPPER(:B3 )
AND TABLE_NAME = UPPER(:B2 ) AND KEY_ID = UPPER(:B1 ) RETURNING VALUE
INTO :O0
Rationale
SQL statement with SQL_ID "8wzc2r9wadx8u" was executed 108000 times and
had an average elapsed time of 0.01 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.0093
seconds.
Recommendation 3: SQL Tuning
Estimated benefit is .17 active sessions, 4.89% of total activity.
------------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "0wvttswphs2ky". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 0wvttswphs2ky.
BEGIN
AP.PKG_LOAD_GENERATOR.RandomDML(250);
COMMIT;
END;
Rationale
SQL statement with SQL_ID "0wvttswphs2ky" was executed 3 times and had
an average elapsed time of 225 seconds.
Rationale
Average time spent in Cluster wait events per execution was 151 seconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Cluster" was consuming significant database time.
Impact is .97 active sessions, 28.49% of total activity.
Finding 3: Session Connect and Disconnect
Impact is 1.26 active sessions, 36.93% of total activity.
---------------------------------------------------------
Session connect and disconnect calls were consuming significant database time.
Instances that were significantly affected by this finding:
Number Name Percent Impact ADDM Task Name
------ ------ -------------- --------------
2 racdb2 61.37 TASK_802$2
1 racdb1 38.63 TASK_802$1
Recommendation 1: Application Analysis
Estimated benefit is 1.26 active sessions, 36.93% of total activity.
--------------------------------------------------------------------
Action
Investigate application logic for possible reduction of connect and
disconnect calls. For example, you might use a connection pool scheme in
the middle tier.
Finding 4: Buffer Busy
Impact is .93 active sessions, 27.4% of total activity.
-------------------------------------------------------
Read and write contention on database blocks was consuming significant
database time.
Recommendation 1: Schema Changes
Estimated benefit is .46 active sessions, 13.41% of total activity.
-------------------------------------------------------------------
Action
Consider hash partitioning the INDEX "AP.INVOICE_ITEMS_PK_IDX" with
object ID 73052 in a manner that will evenly distribute concurrent DML
across multiple partitions.
Related Object
Database object with ID 73052.
Recommendation 2: Schema Changes
Estimated benefit is .35 active sessions, 10.18% of total activity.
-------------------------------------------------------------------
Action
Consider rebuilding the TABLE "ADMIN.NEXT_IDS" with object ID 72348
using a higher value for PCTFREE.
Related Object
Database object with ID 72348.
Recommendation 3: Schema Changes
Estimated benefit is .35 active sessions, 10.18% of total activity.
-------------------------------------------------------------------
Action
Consider partitioning the TABLE "ADMIN.NEXT_IDS" with object ID 72348 in
a manner that will evenly distribute concurrent DML across multiple
partitions.
Related Object
Database object with ID 72348.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Concurrency" was consuming significant database time.
Impact is 1.02 active sessions, 30.02% of total activity.
Inter-instance messaging was consuming significant database time.
Impact is .77 active sessions, 22.64% of total activity.
Wait class "Cluster" was consuming significant database time.
Impact is .97 active sessions, 28.49% of total activity.
Finding 5: Unusual "Concurrency" Wait Event
Impact is .81 active sessions, 23.67% of total activity.
--------------------------------------------------------
Wait event "row cache lock" in wait class "Concurrency" was consuming
significant database time.
Recommendation 1: Application Analysis
Estimated benefit is .81 active sessions, 23.67% of total activity.
-------------------------------------------------------------------
Action
Investigate the cause for high "row cache lock" waits. Refer to Oracle's
"Database Reference" for the description of this wait event.
Recommendation 2: Application Analysis
Estimated benefit is .77 active sessions, 22.7% of total activity.
------------------------------------------------------------------
Action
Investigate the cause for high "row cache lock" waits in Service
"SYS$USERS".
Recommendation 3: Application Analysis
Estimated benefit is .49 active sessions, 14.52% of total activity.
-------------------------------------------------------------------
Action
Investigate the cause for high "row cache lock" waits with P1,P2,P3
("cache id, mode, request") values "7", "0" and "3" respectively.
Recommendation 4: Application Analysis
Estimated benefit is .37 active sessions, 10.76% of total activity.
-------------------------------------------------------------------
Action
Investigate the cause for high "row cache lock" waits in Module
"emagent@racnode1 (TNS V1-V3)".
Recommendation 5: Application Analysis
Estimated benefit is .3 active sessions, 8.82% of total activity.
-----------------------------------------------------------------
Action
Investigate the cause for high "row cache lock" waits in Module
"emagent@racnode2 (TNS V1-V3)".
Symptoms That Led to the Finding:
---------------------------------
Wait class "Concurrency" was consuming significant database time.
Impact is 1.02 active sessions, 30.02% of total activity.
Finding 6: Buffer Busy
Impact is .35 active sessions, 10.18% of total activity.
--------------------------------------------------------
A hot data block with concurrent read and write activity was found. The block
belongs to segment "ADMIN.NEXT_IDS" and is block 468 in file 5.
Recommendation 1: Application Analysis
Estimated benefit is .35 active sessions, 10.18% of total activity.
-------------------------------------------------------------------
Action
Investigate application logic to find the cause of high concurrent read
and write activity to the data present in this block.
Related Object
Database block with object number 72348, file number 5 and block
number 468.
Recommendation 2: Schema Changes
Estimated benefit is .35 active sessions, 10.18% of total activity.
-------------------------------------------------------------------
Action
Consider rebuilding the TABLE "ADMIN.NEXT_IDS" with object ID 72348
using a higher value for PCTFREE.
Related Object
Database object with ID 72348.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Concurrency" was consuming significant database time.
Impact is 1.02 active sessions, 30.02% of total activity.
Inter-instance messaging was consuming significant database time.
Impact is .77 active sessions, 22.64% of total activity.
Wait class "Cluster" was consuming significant database time.
Impact is .97 active sessions, 28.49% of total activity.
Finding 7: Shared Pool Latches
Impact is .27 active sessions, 8.08% of total activity.
-------------------------------------------------------
Contention for latches related to the shared pool was consuming significant
database time in some instances.
Instances that were significantly affected by this finding:
Number Name Percent Impact ADDM Task Name
------ ------ -------------- --------------
1 racdb1 85.92 TASK_802$1
2 racdb2 14.08 TASK_802$2
Check the ADDM analysis of affected instances for recommendations.
Finding 8: Hard Parse
Impact is .19 active sessions, 5.5% of total activity.
------------------------------------------------------
Hard parsing of SQL statements was consuming significant database time in some
instances.
Instances that were significantly affected by this finding:
Number Name Percent Impact ADDM Task Name
------ ------ -------------- --------------
1 racdb1 55.54 TASK_802$1
2 racdb2 44.46 TASK_802$2
Check the ADDM analysis of affected instances for recommendations.
Finding 9: PL/SQL Execution
Impact is .07 active sessions, 2.2% of total activity.
------------------------------------------------------
PL/SQL execution consumed significant database time.
Instances that were significantly affected by this finding:
Number Name Percent Impact ADDM Task Name
------ ------ -------------- --------------
2 racdb2 67.07 TASK_802$2
Recommendation 1: SQL Tuning
Estimated benefit is .07 active sessions, 2.2% of total activity.
-----------------------------------------------------------------
Action
Tune the entry point PL/SQL "AP.PKG_LOAD_GENERATOR.RANDOMDML" of type
"PACKAGE" and ID 73055. Refer to the PL/SQL documentation for addition
information.
Rationale
167 seconds spent in executing PL/SQL "SYS.DBMS_RANDOM.SEED#2" of type
"PACKAGE" and ID 8106.
Finding 10: I/O Throughput
Impact is .07 active sessions, 2.11% of total activity.
-------------------------------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
Instances that were significantly affected by this finding:
Number Name Percent Impact ADDM Task Name
------ ------ -------------- --------------
2 racdb2 76.93 TASK_802$2
Recommendation 1: Host Configuration
Estimated benefit is .07 active sessions, 2.11% of total activity.
------------------------------------------------------------------
Action
Consider increasing the throughput of the I/O subsystem. Oracle's
recommended solution is to stripe all data file using the SAME
methodology. You might also need to increase the number of disks for
better performance. Alternatively, consider using Oracle's Automatic
Storage Management solution.
Rationale
During the analysis period, the average data files' I/O throughput was
149 K per second for reads and 58 K per second for writes. The average
response time for single block reads was 52 milliseconds.
Recommendation 2: Host Configuration
Estimated benefit is .04 active sessions, 1.21% of total activity.
------------------------------------------------------------------
Action
The performance of file +DATA/racdb/datafile/sysaux.257.649041137 was
significantly worse than other files. If striping all files using the
SAME methodology is not possible, consider striping this file over
multiple disks.
Related Object
Database file
"+DATA/racdb/datafile/sysaux.257.649041137"
Rationale
The average response time for single block reads for this file was 229
milliseconds.
Recommendation 3: Host Configuration
Estimated benefit is .03 active sessions, .78% of total activity.
-----------------------------------------------------------------
Action
The performance of file +DATA/racdb/datafile/system.256.649041135 was
significantly worse than other files. If striping all files using the
SAME methodology is not possible, consider striping this file over
multiple disks.
Related Object
Database file
"+DATA/racdb/datafile/system.256.649041135"
Rationale
The average response time for single block reads for this file was 50
milliseconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is .12 active sessions, 3.53% of total activity.
Finding 11: Interconnect Latency
Impact is .06 active sessions, 1.82% of total activity.
-------------------------------------------------------
Higher than expected latency of the cluster interconnect was responsible for
significant database time.
The database was consuming 1383 kilo bits per second of interconnect
bandwidth.
81% of this interconnect bandwidth was used for global cache messaging, 1% for
parallel query messaging and 10% for database lock management.
The average latency for 8K interconnect messages was 1972 microseconds.
Recommendation 1: Host Configuration
Estimated benefit is .06 active sessions, 1.82% of total activity.
------------------------------------------------------------------
Action
Investigate cause of high network interconnect latency between database
instances. Oracle's recommended solution is to use a high speed
dedicated network.
Action
Check the configuration of the cluster interconnect. Check OS setup like
adapter setting, firmware and driver release. Check that the OS's socket
receive buffers are large enough to store an entire multiblock read. The
value of parameter "db_file_multiblock_read_count" may be decreased as a
workaround.
Action
Look at the instance level ADDM tasks for a list of interconnect devices
used by a specific instance.
Symptoms That Led to the Finding:
---------------------------------
Inter-instance messaging was consuming significant database time.
Impact is .77 active sessions, 22.64% of total activity.
Wait class "Cluster" was consuming significant database time.
Impact is .97 active sessions, 28.49% of total activity.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Instances that were analyzed:
-----------------------------
Number Name Host Name Active Sessions Percent of Activity
------ ------ --------- --------------- -------------------
2 racdb2 racnode2 1.82 53.56
1 racdb1 racnode1 1.58 46.44
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Wait class "Network" was not consuming significant database time.
/*
|| Report 2:
|| ADDM Instance Analysis for Cluster Database RACDB, Instance RACDB1
*/
Thu Oct 23 page 1
ADDM Analysis for Cluster Database RACDB - Instance RACDB1 - POST
ADDM Report for Task 'ADDM_110'
-------------------------------
Analysis Period
---------------
AWR snapshot range from 149 to 150.
Time period starts at 21-OCT-08 08.40.13 PM
Time period ends at 21-OCT-08 08.55.20 PM
Analysis Target
---------------
Database 'RACDB' with DB ID 646658104.
Database version 11.1.0.6.0.
ADDM performed an analysis of instance racdb1, numbered 1 and hosted at
racnode1.
Activity During the Analysis Period
-----------------------------------
Total database time was 1980 seconds.
The average number of active sessions was 2.18.
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
-------------------------- ------------------- ---------------
1 Top SQL by "Cluster" Wait 2.18 | 100 5
2 Top SQL by DB Time 2.18 | 100 5
3 Buffer Busy 1.33 | 61.04 1
4 Buffer Busy .51 | 23.34 2
5 CPU Usage .15 | 6.64 3
6 PL/SQL Execution .08 | 3.55 1
7 Interconnect Latency .08 | 3.48 1
8 Index Block Split .06 | 2.76 0
9 Unusual "Other" Wait Event .06 | 2.55 3
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Top SQL by "Cluster" Wait
Impact is 2.35 active sessions, 107.27% of total activity.
----------------------------------------------------------
SQL statements responsible for significant inter-instance messaging were
found.
Recommendation 1: SQL Tuning
Estimated benefit is .89 active sessions, 40.74% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "1dmv3vasxtjtu".
Related Object
SQL statement with SQL_ID 1dmv3vasxtjtu.
INSERT INTO AP.INVOICE_ITEMS ( INVOICE_ID ,LINE_ITEM_NBR ,ACTIVE_IND
,PRODUCT_ID ,QTY ,EXTENDED_AMT ,TAXABLE_IND ) VALUES( :B6 ,:B5 ,'Y'
,:B4 ,:B3 ,:B2 ,:B1 )
Rationale
SQL statement with SQL_ID "1dmv3vasxtjtu" was executed 43650 times and
had an average elapsed time of 0.021 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.017
seconds.
Recommendation 2: SQL Tuning
Estimated benefit is .52 active sessions, 23.66% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "8wzc2r9wadx8u".
Related Object
SQL statement with SQL_ID 8wzc2r9wadx8u and PLAN_HASH 897482116.
UPDATE ADMIN.NEXT_IDS SET VALUE = VALUE + 1 WHERE OWNER = UPPER(:B3 )
AND TABLE_NAME = UPPER(:B2 ) AND KEY_ID = UPPER(:B1 ) RETURNING VALUE
INTO :O0
Rationale
SQL statement with SQL_ID "8wzc2r9wadx8u" was executed 45100 times and
had an average elapsed time of 0.011 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.0099
seconds.
Recommendation 3: SQL Tuning
Estimated benefit is .39 active sessions, 18.03% of total activity.
-------------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "cxubjzfd0hmyd". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID cxubjzfd0hmyd.
BEGIN
AP.PKG_LOAD_GENERATOR.RandomDML(100);
COMMIT;
END;
Rationale
SQL statement with SQL_ID "cxubjzfd0hmyd" was executed 2 times and had
an average elapsed time of 246 seconds.
Rationale
Average time spent in Cluster wait events per execution was 178 seconds.
Recommendation 4: SQL Tuning
Estimated benefit is .33 active sessions, 15% of total activity.
----------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "6z4mq42v75q8s". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 6z4mq42v75q8s.
BEGIN
AP.PKG_LOAD_GENERATOR.RandomDML(200);
COMMIT;
END;
Rationale
SQL statement with SQL_ID "6z4mq42v75q8s" was executed 3 times and had
an average elapsed time of 143 seconds.
Rationale
Average time spent in Cluster wait events per execution was 98 seconds.
Recommendation 5: SQL Tuning
Estimated benefit is .29 active sessions, 13.09% of total activity.
-------------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "9qqv0czupcx22". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 9qqv0czupcx22.
BEGIN
AP.PKG_LOAD_GENERATOR.RandomDML(50);
COMMIT;
END;
Rationale
SQL statement with SQL_ID "9qqv0czupcx22" was executed 2 times and had
an average elapsed time of 183 seconds.
Rationale
Average time spent in Cluster wait events per execution was 129 seconds.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Cluster" was consuming significant database time.
Impact is 1.38 active sessions, 62.99% of total activity.
Finding 2: Top SQL by DB Time
Impact is 2.2 active sessions, 100% of total activity.
------------------------------------------------------
SQL statements consuming significant database time were found.
Recommendation 1: SQL Tuning
Estimated benefit is .89 active sessions, 40.74% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "1dmv3vasxtjtu".
Related Object
SQL statement with SQL_ID 1dmv3vasxtjtu.
INSERT INTO AP.INVOICE_ITEMS ( INVOICE_ID ,LINE_ITEM_NBR ,ACTIVE_IND
,PRODUCT_ID ,QTY ,EXTENDED_AMT ,TAXABLE_IND ) VALUES( :B6 ,:B5 ,'Y'
,:B4 ,:B3 ,:B2 ,:B1 )
Rationale
SQL statement with SQL_ID "1dmv3vasxtjtu" was executed 43650 times and
had an average elapsed time of 0.021 seconds.
Recommendation 2: SQL Tuning
Estimated benefit is .52 active sessions, 23.66% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "8wzc2r9wadx8u".
Related Object
SQL statement with SQL_ID 8wzc2r9wadx8u and PLAN_HASH 897482116.
UPDATE ADMIN.NEXT_IDS SET VALUE = VALUE + 1 WHERE OWNER = UPPER(:B3 )
AND TABLE_NAME = UPPER(:B2 ) AND KEY_ID = UPPER(:B1 ) RETURNING VALUE
INTO :O0
Rationale
SQL statement with SQL_ID "8wzc2r9wadx8u" was executed 45100 times and
had an average elapsed time of 0.011 seconds.
Recommendation 3: SQL Tuning
Estimated benefit is .51 active sessions, 23.29% of total activity.
-------------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "cxubjzfd0hmyd". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID cxubjzfd0hmyd.
BEGIN
AP.PKG_LOAD_GENERATOR.RandomDML(100);
COMMIT;
END;
Rationale
SQL statement with SQL_ID "cxubjzfd0hmyd" was executed 2 times and had
an average elapsed time of 246 seconds.
Recommendation 4: SQL Tuning
Estimated benefit is .43 active sessions, 19.87% of total activity.
-------------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "6z4mq42v75q8s". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 6z4mq42v75q8s.
BEGIN
AP.PKG_LOAD_GENERATOR.RandomDML(200);
COMMIT;
END;
Rationale
SQL statement with SQL_ID "6z4mq42v75q8s" was executed 3 times and had
an average elapsed time of 143 seconds.
Recommendation 5: SQL Tuning
Estimated benefit is .38 active sessions, 17.19% of total activity.
-------------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "9qqv0czupcx22". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 9qqv0czupcx22.
BEGIN
AP.PKG_LOAD_GENERATOR.RandomDML(50);
COMMIT;
END;
Rationale
SQL statement with SQL_ID "9qqv0czupcx22" was executed 2 times and had
an average elapsed time of 183 seconds.
Finding 3: Buffer Busy
Impact is 1.34 active sessions, 61.04% of total activity.
---------------------------------------------------------
Read and write contention on database blocks was consuming significant
database time.
Recommendation 1: Schema Changes
Estimated benefit is .61 active sessions, 27.83% of total activity.
-------------------------------------------------------------------
Action
Consider hash partitioning the INDEX "AP.INVOICE_ITEMS_PK_IDX" with
object ID 73052 in a manner that will evenly distribute concurrent DML
across multiple partitions.
Related Object
Database object with ID 73052.
Symptoms That Led to the Finding:
---------------------------------
Inter-instance messaging was consuming significant database time on this
instance.
Impact is 1.06 active sessions, 48.69% of total activity.
Wait class "Cluster" was consuming significant database time.
Impact is 1.38 active sessions, 62.99% of total activity.
Wait class "Concurrency" was consuming significant database time.
Impact is .12 active sessions, 5.5% of total activity.
Finding 4: Buffer Busy
Impact is .51 active sessions, 23.34% of total activity.
--------------------------------------------------------
A hot data block with concurrent read and write activity was found. The block
belongs to segment "ADMIN.NEXT_IDS" and is block 468 in file 5.
Recommendation 1: Application Analysis
Estimated benefit is .51 active sessions, 23.34% of total activity.
-------------------------------------------------------------------
Action
Investigate application logic to find the cause of high concurrent read
and write activity to the data present in this block.
Related Object
Database block with object number 72348, file number 5 and block
number 468.
Recommendation 2: Schema Changes
Estimated benefit is .51 active sessions, 23.34% of total activity.
-------------------------------------------------------------------
Action
Consider rebuilding the TABLE "ADMIN.NEXT_IDS" with object ID 72348
using a higher value for PCTFREE.
Related Object
Database object with ID 72348.
Symptoms That Led to the Finding:
---------------------------------
Inter-instance messaging was consuming significant database time on this
instance.
Impact is 1.06 active sessions, 48.69% of total activity.
Wait class "Cluster" was consuming significant database time.
Impact is 1.38 active sessions, 62.99% of total activity.
Wait class "Concurrency" was consuming significant database time.
Impact is .12 active sessions, 5.5% of total activity.
Finding 5: CPU Usage
Impact is .15 active sessions, 6.64% of total activity.
-------------------------------------------------------
Time spent on the CPU by the instance was responsible for a substantial part
of database time.
Recommendation 1: SQL Tuning
Estimated benefit is .89 active sessions, 40.74% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "1dmv3vasxtjtu".
Related Object
SQL statement with SQL_ID 1dmv3vasxtjtu.
INSERT INTO AP.INVOICE_ITEMS ( INVOICE_ID ,LINE_ITEM_NBR ,ACTIVE_IND
,PRODUCT_ID ,QTY ,EXTENDED_AMT ,TAXABLE_IND ) VALUES( :B6 ,:B5 ,'Y'
,:B4 ,:B3 ,:B2 ,:B1 )
Rationale
SQL statement with SQL_ID "1dmv3vasxtjtu" was executed 43650 times and
had an average elapsed time of 0.021 seconds.
Rationale
Average CPU used per execution was 0.00097 seconds.
Recommendation 2: SQL Tuning
Estimated benefit is .05 active sessions, 2.49% of total activity.
------------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "6z4mq42v75q8s". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 6z4mq42v75q8s.
BEGIN
AP.PKG_LOAD_GENERATOR.RandomDML(200);
COMMIT;
END;
Rationale
SQL statement with SQL_ID "6z4mq42v75q8s" was executed 3 times and had
an average elapsed time of 143 seconds.
Rationale
Average CPU used per execution was 16 seconds.
Recommendation 3: SQL Tuning
Estimated benefit is .04 active sessions, 2.01% of total activity.
------------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "cxubjzfd0hmyd". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID cxubjzfd0hmyd.
BEGIN
AP.PKG_LOAD_GENERATOR.RandomDML(100);
COMMIT;
END;
Rationale
SQL statement with SQL_ID "cxubjzfd0hmyd" was executed 2 times and had
an average elapsed time of 246 seconds.
Rationale
Average CPU used per execution was 19 seconds.
Finding 6: PL/SQL Execution
Impact is .08 active sessions, 3.55% of total activity.
-------------------------------------------------------
PL/SQL execution consumed significant database time.
Recommendation 1: SQL Tuning
Estimated benefit is .08 active sessions, 3.55% of total activity.
------------------------------------------------------------------
Action
Tune the entry point PL/SQL "AP.PKG_LOAD_GENERATOR.RANDOMDML" of type
"PACKAGE" and ID 73055. Refer to the PL/SQL documentation for addition
information.
Rationale
52 seconds spent in executing PL/SQL "SYS.DBMS_RANDOM.SEED#2" of type
"PACKAGE" and ID 8106.
Finding 7: Interconnect Latency
Impact is .08 active sessions, 3.48% of total activity.
-------------------------------------------------------
Higher than expected latency of the cluster interconnect was responsible for
significant database time on this instance.
The instance was consuming 1547 kilo bits per second of interconnect
bandwidth.
86% of this interconnect bandwidth was used for global cache messaging, 0% for
parallel query messaging and 10% for database lock management.
The average latency for 8K interconnect messages was 2002 microseconds.
The instance is using the private interconnect device "eth2" with IP address
10.10.10.11 and source "Oracle Cluster Repository".
The device "eth2" was used for 100% of interconnect traffic and experienced 0
send or receive errors during the analysis period.
Recommendation 1: Host Configuration
Estimated benefit is .08 active sessions, 3.48% of total activity.
------------------------------------------------------------------
Action
Investigate cause of high network interconnect latency between database
instances. Oracle's recommended solution is to use a high speed
dedicated network.
Action
Check the configuration of the cluster interconnect. Check OS setup like
adapter setting, firmware and driver release. Check that the OS's socket
receive buffers are large enough to store an entire multiblock read. The
value of parameter "db_file_multiblock_read_count" may be decreased as a
workaround.
Symptoms That Led to the Finding:
---------------------------------
Inter-instance messaging was consuming significant database time on this
instance.
Impact is 1.06 active sessions, 48.69% of total activity.
Wait class "Cluster" was consuming significant database time.
Impact is 1.38 active sessions, 62.99% of total activity.
Finding 8: Index Block Split
Impact is .06 active sessions, 2.76% of total activity.
-------------------------------------------------------
Contention on index block splits was consuming significant database time.
However, no single index was the predominant cause for this contention.
No recommendations are available.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Concurrency" was consuming significant database time.
Impact is .12 active sessions, 5.5% of total activity.
Finding 9: Unusual "Other" Wait Event
Impact is .06 active sessions, 2.55% of total activity.
-------------------------------------------------------
Wait event "buffer deadlock" in wait class "Other" was consuming significant
database time.
Recommendation 1: Application Analysis
Estimated benefit is .06 active sessions, 2.55% of total activity.
------------------------------------------------------------------
Action
Investigate the cause for high "buffer deadlock" waits. Refer to
Oracle's "Database Reference" for the description of this wait event.
Use given SQL for further investigation.
Rationale
The SQL statement with SQL_ID "1dmv3vasxtjtu" was found waiting for
"buffer deadlock" wait event.
Related Object
SQL statement with SQL_ID 1dmv3vasxtjtu.
INSERT INTO AP.INVOICE_ITEMS ( INVOICE_ID ,LINE_ITEM_NBR ,ACTIVE_IND
,PRODUCT_ID ,QTY ,EXTENDED_AMT ,TAXABLE_IND ) VALUES( :B6 ,:B5 ,'Y'
,:B4 ,:B3 ,:B2 ,:B1 )
Recommendation 2: Application Analysis
Estimated benefit is .06 active sessions, 2.55% of total activity.
------------------------------------------------------------------
Action
Investigate the cause for high "buffer deadlock" waits in Module
"DMLGenerator".
Recommendation 3: Application Analysis
Estimated benefit is .06 active sessions, 2.55% of total activity.
------------------------------------------------------------------
Action
Investigate the cause for high "buffer deadlock" waits in Service
"TESTLBA".
Symptoms That Led to the Finding:
---------------------------------
Wait class "Other" was consuming significant database time.
Impact is .08 active sessions, 3.8% of total activity.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Wait class "Network" was not consuming significant database time.
Wait class "User I/O" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.
/*
|| Report 3:
|| Results of applying ADDM Finding Filters
*/
ADDM Report for Task 'ADDM_110'
-------------------------------
Analysis Period
---------------
AWR snapshot range from 149 to 150.
Time period starts at 21-OCT-08 08.40.13 PM
Time period ends at 21-OCT-08 08.55.20 PM
Analysis Target
---------------
Database 'RACDB' with DB ID 646658104.
Database version 11.1.0.6.0.
ADDM performed an analysis of instance racdb1, numbered 1 and hosted at
racnode1.
Activity During the Analysis Period
-----------------------------------
Total database time was 1980 seconds.
The average number of active sessions was 2.18.
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
-------------------------- ------------------- ---------------
1 Top SQL by "Cluster" Wait 2.18 | 100 5
2 Top SQL by DB Time 2.18 | 100 5
3 Buffer Busy 1.33 | 61.04 1
4 Buffer Busy .51 | 23.34 2
5 CPU Usage .15 | 6.64 3
6 PL/SQL Execution .08 | 3.55 1
7 Interconnect Latency .08 | 3.48 1
8 Index Block Split .06 | 2.76 0
9 Unusual "Other" Wait Event .06 | 2.55 3
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Top SQL by "Cluster" Wait
Impact is 2.35 active sessions, 107.27% of total activity.
----------------------------------------------------------
SQL statements responsible for significant inter-instance messaging were
found.
Recommendation 1: SQL Tuning
Estimated benefit is .89 active sessions, 40.74% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "1dmv3vasxtjtu".
Related Object
SQL statement with SQL_ID 1dmv3vasxtjtu.
INSERT INTO AP.INVOICE_ITEMS ( INVOICE_ID ,LINE_ITEM_NBR ,ACTIVE_IND
,PRODUCT_ID ,QTY ,EXTENDED_AMT ,TAXABLE_IND ) VALUES( :B6 ,:B5 ,'Y'
,:B4 ,:B3 ,:B2 ,:B1 )
Rationale
SQL statement with SQL_ID "1dmv3vasxtjtu" was executed 43650 times and
had an average elapsed time of 0.021 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.017
seconds.
Recommendation 2: SQL Tuning
Estimated benefit is .52 active sessions, 23.66% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "8wzc2r9wadx8u".
Related Object
SQL statement with SQL_ID 8wzc2r9wadx8u and PLAN_HASH 897482116.
UPDATE ADMIN.NEXT_IDS SET VALUE = VALUE + 1 WHERE OWNER = UPPER(:B3 )
AND TABLE_NAME = UPPER(:B2 ) AND KEY_ID = UPPER(:B1 ) RETURNING VALUE
INTO :O0
Rationale
SQL statement with SQL_ID "8wzc2r9wadx8u" was executed 45100 times and
had an average elapsed time of 0.011 seconds.
Rationale
Average time spent in Cluster wait events per execution was 0.0099
seconds.
Recommendation 3: SQL Tuning
Estimated benefit is .39 active sessions, 18.03% of total activity.
-------------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "cxubjzfd0hmyd". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID cxubjzfd0hmyd.
BEGIN
AP.PKG_LOAD_GENERATOR.RandomDML(100);
COMMIT;
END;
Rationale
SQL statement with SQL_ID "cxubjzfd0hmyd" was executed 2 times and had
an average elapsed time of 246 seconds.
Rationale
Average time spent in Cluster wait events per execution was 178 seconds.
<< several pages removed for sake of brevity >>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Wait class "Network" was not consuming significant database time.
Wait class "User I/O" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.
Directives that may affect this report:
---------------------------------------
Filter findings of type "Interconnect Buffer Busy" if the impact is less than
1 active sessions or the impact is less than 10 percent of database time for
the analysis period.
Filter recommendations to change the value of parameter "cursor_sharing".
Filter recommendations regarding segments that belong to owner "ap", named
"invoices" with sub-objects named "%".
Filter recommendations regarding segments that belong to owner "ap", named
"invoice_items" with sub-objects named "%".
Filter recommendations regarding SQL statements with SQL_ID "cxubjzfd0hmyd" if
the impact of the SQL is less than 0 active sessions or its response time is
less than 5000000 microseconds.
Filter recommendations regarding SQL statements with SQL_ID "6z4mq42v75q8s" if
the impact of the SQL is less than 0 active sessions or its response time is
less than 5000000 microseconds.
Filter recommendations regarding SQL statements with SQL_ID "9qqv0czupcx22" if
the impact of the SQL is less than 0 active sessions or its response time is
less than 5000000 microseconds.
Filter findings of type "Interconnect Buffer Busy" if the impact is less than
1 active sessions or the impact is less than 10 percent of database time for
the analysis period.