ops$oracle.PONP> @?/rdbms/admin/addmrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3392966083 PONP 1 PONP
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 3392966083 1 PONP PONP pcasvs18
Using 3392966083 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Listing the last 3 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
PONP PONP 20941 13 Jun 2010 00:00 1
20942 13 Jun 2010 01:00 1
20943 13 Jun 2010 02:00 1
20944 13 Jun 2010 03:00 1
20945 13 Jun 2010 04:00 1
20946 13 Jun 2010 05:00 1
20947 13 Jun 2010 06:00 1
20948 13 Jun 2010 07:00 1
20949 13 Jun 2010 08:00 1
20950 13 Jun 2010 09:00 1
20951 13 Jun 2010 10:00 1
20952 13 Jun 2010 11:00 1
20953 13 Jun 2010 12:00 1
20954 13 Jun 2010 13:00 1
20955 13 Jun 2010 14:00 1
20956 13 Jun 2010 15:00 1
20957 13 Jun 2010 16:00 1
20958 13 Jun 2010 17:00 1
20959 13 Jun 2010 18:00 1
20960 13 Jun 2010 19:00 1
20961 13 Jun 2010 20:00 1
20962 13 Jun 2010 21:00 1
20963 13 Jun 2010 22:00 1
20964 13 Jun 2010 23:00 1
20965 14 Jun 2010 00:00 1
20966 14 Jun 2010 01:00 1
20967 14 Jun 2010 02:00 1
20968 14 Jun 2010 03:00 1
20969 14 Jun 2010 04:00 1
20970 14 Jun 2010 05:00 1
20971 14 Jun 2010 06:00 1
20972 14 Jun 2010 07:00 1
20973 14 Jun 2010 08:00 1
20974 14 Jun 2010 09:00 1
20975 14 Jun 2010 10:00 1
20976 14 Jun 2010 11:00 1
20977 14 Jun 2010 12:00 1
20978 14 Jun 2010 13:00 1
20979 14 Jun 2010 14:00 1
20980 14 Jun 2010 15:00 1
20981 14 Jun 2010 16:00 1
20982 14 Jun 2010 17:00 1
20983 14 Jun 2010 18:00 1
20984 14 Jun 2010 19:00 1
20985 14 Jun 2010 20:00 1
20986 14 Jun 2010 21:00 1
20987 14 Jun 2010 22:00 1
20988 14 Jun 2010 23:00 1
20989 15 Jun 2010 00:00 1
20990 15 Jun 2010 01:00 1
20991 15 Jun 2010 02:00 1
20992 15 Jun 2010 03:00 1
20993 15 Jun 2010 04:00 1
20994 15 Jun 2010 05:00 1
20995 15 Jun 2010 06:00 1
20996 15 Jun 2010 07:00 1
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
PONP PONP 20997 15 Jun 2010 08:00 1
20998 15 Jun 2010 09:00 1
20999 15 Jun 2010 10:00 1
21000 15 Jun 2010 11:00 1
21001 15 Jun 2010 12:00 1
21002 15 Jun 2010 13:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 21001
Begin Snapshot Id specified: 21001
Enter value for end_snap: 21002
End Snapshot Id specified: 21002
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_1_21001_21002.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name addmrpt_1_21001_21002.txt
Running the ADDM analysis on the specified pair of snapshots ...
Generating the ADDM report for this analysis ...
DETAILED ADDM REPORT FOR TASK 'TASK_29865' WITH ID 29865
--------------------------------------------------------
Analysis Period: 15-JUN-2010 from 12:00:56 to 13:00:08
Database ID/Instance: 3392966083/1
Database/Instance Names: PONP/PONP
Host Name: pcasvs18
Database Version: 10.2.0.4.0
Snapshot Range: from 21001 to 21002
Database Time: 1246 seconds
Average Database Load: .4 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 50% impact (618 seconds)
-----------------------------------
Wait class "User I/O" was consuming significant database time.
NO RECOMMENDATIONS AVAILABLE
ADDITIONAL INFORMATION:
Waits for I/O to temporary tablespaces were not consuming significant
database time.
The throughput of the I/O subsystem was not significantly lower than
expected.
FINDING 2: 36% impact (453 seconds)
-----------------------------------
Time spent on the CPU by the instance was responsible for a substantial part
of database time.
RECOMMENDATION 1: DB Configuration, 21% benefit (260 seconds)
ACTION: Investigate the cause for the "STREAMS Apply" processes
consuming 0% of the host CPU.
RECOMMENDATION 2: SQL Tuning, 15% benefit (193 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "07r10s0yvk29x". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 07r10s0yvk29x
begin :res := STRMADMIN.SRP100_CIS_OMS.TRANSFORM_DML(:1); end;
RATIONALE: SQL statement with SQL_ID "07r10s0yvk29x" was executed 33070
times and had an average elapsed time of 0.0058 seconds.
RATIONALE: Average CPU used per execution was 0.0058 seconds.
FINDING 3: 24% impact (298 seconds)
-----------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 7.4% benefit (92 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"cktyb2w5fxv2y".
RELEVANT OBJECT: SQL statement with SQL_ID cktyb2w5fxv2y and
PLAN_HASH 1162490933
SELECT id FROM po_int_elec_junction WHERE
downstream_line_section_type = :"SYS_B_0" AND faultable = :"SYS_B_1"
AND ROWNUM < :"SYS_B_2"
RATIONALE: SQL statement with SQL_ID "cktyb2w5fxv2y" was executed 6
times and had an average elapsed time of 14 seconds.
RECOMMENDATION 2: SQL Tuning, 6.3% benefit (78 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"9tzfvx7j4v52d".
RELEVANT OBJECT: SQL statement with SQL_ID 9tzfvx7j4v52d and
PLAN_HASH 3043945010
DELETE OMS.PO_MUTEX_ARCHIVE WHERE DELETION_TIME < :B1
ACTION: Investigate the SQL statement with SQL_ID "9tzfvx7j4v52d" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 9tzfvx7j4v52d and
PLAN_HASH 3043945010
DELETE OMS.PO_MUTEX_ARCHIVE WHERE DELETION_TIME < :B1
RATIONALE: SQL statement with SQL_ID "9tzfvx7j4v52d" was executed 1
times and had an average elapsed time of 48 seconds.
RATIONALE: Waiting for event "db file sequential read" in wait class
"User I/O" accounted for 100% of the database time spent in
processing the SQL statement with SQL_ID "9tzfvx7j4v52d".
RECOMMENDATION 3: SQL Tuning, 5.2% benefit (64 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"9bfznbg1ga3pr".
RELEVANT OBJECT: SQL statement with SQL_ID 9bfznbg1ga3pr and
PLAN_HASH 3585497244
select /*+ cardinality(cad 20) */
distinct cad.cd_address as AddressCode,
nvl(cad.nm_admin_area, :"SYS_B_00") as State,
nvl(cad.nm_town, :"SYS_B_01") as Town,
nvl(cad.nm_street_1, :"SYS_B_02") as Street,
nvl(cad.no_bldg_sort, :"SYS_B_03") as Bldg,
nvl(p.no_property, :"SYS_B_04") as PropertyNum,
nvl(cad.formatted_address, :"SYS_B_05") as Address,
nvl(rtp.ds_ref_tab, :"SYS_B_06") as PropertyType
from
tvp046property p,
tadlocality tlst,
tadlocality tltn,
tadlocality tlaa,
CHED_cis_address cad,
tadaddress ta,
tvp358reftab rtp
where
p.cd_company_system = :"SYS_B_07"
and rtp.cd_company_system = :"SYS_B_08"
and ta.cd_company_system = :"SYS_B_09"
and tlst.cd_company_system = :"SYS_B_10"
and tltn.cd_company_system = :"SYS_B_11"
and tlaa.cd_company_system = :"SYS_B_12"
and cad.cd_company_system = :"SYS_B_13"
and ta.cd_locality = tlst.cd_locality
and tlst.cd_loc_type = :"SYS_B_14"
and tltn.cd_loc_type = :"SYS_B_15"
and tlaa.cd_loc_type = :"SYS_B_16"
and tlst.cd_locality_parent = tltn.cd_locality
and tltn.cd_locality_parent = tlaa.cd_locality
and cad.cd_address = ta.cd_address
and ta.cd_address = p.cd_address (+)
and p.tp_property_187 = rtp.cd_ref_tab (+)
and p.ri_ref_tab_187 = rtp.tp_ref_tab (+)
and cad.nm_street_1 like :"SYS_B_17"
and cad.nm_street_type_1 LIKE :"SYS_B_18"
and tltn.nm_loc_c4 = :"SYS_B_19"
and tlaa.nm_loc_c4 = :"SYS_B_20"
and rownum < :"SYS_B_21"
order by State, Town, Street, Bldg
RATIONALE: SQL statement with SQL_ID "9bfznbg1ga3pr" was executed 14
times and had an average elapsed time of 4.3 seconds.
RECOMMENDATION 4: SQL Tuning, 4.4% benefit (55 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"du59m1kxabg03".
RELEVANT OBJECT: SQL statement with SQL_ID du59m1kxabg03 and
PLAN_HASH 3004174626
select /*+ cardinality(cad 20) */
distinct cad.cd_address as AddressCode,
nvl(cad.nm_admin_area, :"SYS_B_00") as State,
nvl(cad.nm_town, :"SYS_B_01") as Town,
nvl(cad.nm_street_1, :"SYS_B_02") as Street,
nvl(cad.no_bldg_sort, :"SYS_B_03") as Bldg,
nvl(p.no_property, :"SYS_B_04") as PropertyNum,
nvl(cad.formatted_address, :"SYS_B_05") as Address,
nvl(rtp.ds_ref_tab, :"SYS_B_06") as PropertyType
from
tvp046property p,
tadlocality tlst,
tadlocality tltn,
tadlocality tlaa,
CHED_cis_address cad,
tadaddress ta,
tvp358reftab rtp
where
p.cd_company_system = :"SYS_B_07"
and rtp.cd_company_system = :"SYS_B_08"
and ta.cd_company_system = :"SYS_B_09"
and tlst.cd_company_system = :"SYS_B_10"
and tltn.cd_company_system = :"SYS_B_11"
and tlaa.cd_company_system = :"SYS_B_12"
and cad.cd_company_system = :"SYS_B_13"
and ta.cd_locality = tlst.cd_locality
and tlst.cd_loc_type = :"SYS_B_14"
and tltn.cd_loc_type = :"SYS_B_15"
and tlaa.cd_loc_type = :"SYS_B_16"
and tlst.cd_locality_parent = tltn.cd_locality
and tltn.cd_locality_parent = tlaa.cd_locality
and cad.cd_address = ta.cd_address
and ta.cd_address = p.cd_address (+)
and p.tp_property_187 = rtp.cd_ref_tab (+)
and p.ri_ref_tab_187 = rtp.tp_ref_tab (+)
and ta.no_bldg = :"SYS_B_17"
and cad.nm_street_1 like :"SYS_B_18"
and tlaa.nm_loc_c4 = :"SYS_B_19"
and rownum < :"SYS_B_20"
order by State, Town, Street, Bldg
RATIONALE: SQL statement with SQL_ID "du59m1kxabg03" was executed 13
times and had an average elapsed time of 3.9 seconds.
RECOMMENDATION 5: SQL Tuning, 3.9% benefit (49 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "f6vzakrsh2bbz". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID f6vzakrsh2bbz
BEGIN
OMS.PO_MUTEX_ARCHIVE_MANAGER.PRUNE_PO_MUTEX_ARCHIVE;
COMMIT;
END;
RATIONALE: SQL statement with SQL_ID "f6vzakrsh2bbz" was executed 1
times and had an average elapsed time of 48 seconds.
FINDING 4: 18% impact (221 seconds)
-----------------------------------
Individual SQL statements responsible for significant user I/O wait were
found.
RECOMMENDATION 1: SQL Tuning, 7.4% benefit (92 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"cktyb2w5fxv2y".
RELEVANT OBJECT: SQL statement with SQL_ID cktyb2w5fxv2y and
PLAN_HASH 1162490933
SELECT id FROM po_int_elec_junction WHERE
downstream_line_section_type = :"SYS_B_0" AND faultable = :"SYS_B_1"
AND ROWNUM < :"SYS_B_2"
RATIONALE: SQL statement with SQL_ID "cktyb2w5fxv2y" was executed 6
times and had an average elapsed time of 14 seconds.
RATIONALE: Average time spent in User I/O wait events per execution was
14 seconds.
RECOMMENDATION 2: SQL Tuning, 6.3% benefit (78 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"9tzfvx7j4v52d".
RELEVANT OBJECT: SQL statement with SQL_ID 9tzfvx7j4v52d and
PLAN_HASH 3043945010
DELETE OMS.PO_MUTEX_ARCHIVE WHERE DELETION_TIME < :B1
ACTION: Investigate the SQL statement with SQL_ID "9tzfvx7j4v52d" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 9tzfvx7j4v52d and
PLAN_HASH 3043945010
DELETE OMS.PO_MUTEX_ARCHIVE WHERE DELETION_TIME < :B1
RATIONALE: SQL statement with SQL_ID "9tzfvx7j4v52d" was executed 1
times and had an average elapsed time of 48 seconds.
RATIONALE: Waiting for event "db file sequential read" in wait class
"User I/O" accounted for 100% of the database time spent in
processing the SQL statement with SQL_ID "9tzfvx7j4v52d".
RATIONALE: Average time spent in User I/O wait events per execution was
47 seconds.
RECOMMENDATION 3: SQL Tuning, 5.2% benefit (64 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"9bfznbg1ga3pr".
RELEVANT OBJECT: SQL statement with SQL_ID 9bfznbg1ga3pr and
PLAN_HASH 3585497244
select /*+ cardinality(cad 20) */
distinct cad.cd_address as AddressCode,
nvl(cad.nm_admin_area, :"SYS_B_00") as State,
nvl(cad.nm_town, :"SYS_B_01") as Town,
nvl(cad.nm_street_1, :"SYS_B_02") as Street,
nvl(cad.no_bldg_sort, :"SYS_B_03") as Bldg,
nvl(p.no_property, :"SYS_B_04") as PropertyNum,
nvl(cad.formatted_address, :"SYS_B_05") as Address,
nvl(rtp.ds_ref_tab, :"SYS_B_06") as PropertyType
from
tvp046property p,
tadlocality tlst,
tadlocality tltn,
tadlocality tlaa,
CHED_cis_address cad,
tadaddress ta,
tvp358reftab rtp
where
p.cd_company_system = :"SYS_B_07"
and rtp.cd_company_system = :"SYS_B_08"
and ta.cd_company_system = :"SYS_B_09"
and tlst.cd_company_system = :"SYS_B_10"
and tltn.cd_company_system = :"SYS_B_11"
and tlaa.cd_company_system = :"SYS_B_12"
and cad.cd_company_system = :"SYS_B_13"
and ta.cd_locality = tlst.cd_locality
and tlst.cd_loc_type = :"SYS_B_14"
and tltn.cd_loc_type = :"SYS_B_15"
and tlaa.cd_loc_type = :"SYS_B_16"
and tlst.cd_locality_parent = tltn.cd_locality
and tltn.cd_locality_parent = tlaa.cd_locality
and cad.cd_address = ta.cd_address
and ta.cd_address = p.cd_address (+)
and p.tp_property_187 = rtp.cd_ref_tab (+)
and p.ri_ref_tab_187 = rtp.tp_ref_tab (+)
and cad.nm_street_1 like :"SYS_B_17"
and cad.nm_street_type_1 LIKE :"SYS_B_18"
and tltn.nm_loc_c4 = :"SYS_B_19"
and tlaa.nm_loc_c4 = :"SYS_B_20"
and rownum < :"SYS_B_21"
order by State, Town, Street, Bldg
RATIONALE: SQL statement with SQL_ID "9bfznbg1ga3pr" was executed 14
times and had an average elapsed time of 4.3 seconds.
RATIONALE: Average time spent in User I/O wait events per execution was
3.4 seconds.
RECOMMENDATION 4: SQL Tuning, 4.4% benefit (55 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"du59m1kxabg03".
RELEVANT OBJECT: SQL statement with SQL_ID du59m1kxabg03 and
PLAN_HASH 3004174626
select /*+ cardinality(cad 20) */
distinct cad.cd_address as AddressCode,
nvl(cad.nm_admin_area, :"SYS_B_00") as State,
nvl(cad.nm_town, :"SYS_B_01") as Town,
nvl(cad.nm_street_1, :"SYS_B_02") as Street,
nvl(cad.no_bldg_sort, :"SYS_B_03") as Bldg,
nvl(p.no_property, :"SYS_B_04") as PropertyNum,
nvl(cad.formatted_address, :"SYS_B_05") as Address,
nvl(rtp.ds_ref_tab, :"SYS_B_06") as PropertyType
from
tvp046property p,
tadlocality tlst,
tadlocality tltn,
tadlocality tlaa,
CHED_cis_address cad,
tadaddress ta,
tvp358reftab rtp
where
p.cd_company_system = :"SYS_B_07"
and rtp.cd_company_system = :"SYS_B_08"
and ta.cd_company_system = :"SYS_B_09"
and tlst.cd_company_system = :"SYS_B_10"
and tltn.cd_company_system = :"SYS_B_11"
and tlaa.cd_company_system = :"SYS_B_12"
and cad.cd_company_system = :"SYS_B_13"
and ta.cd_locality = tlst.cd_locality
and tlst.cd_loc_type = :"SYS_B_14"
and tltn.cd_loc_type = :"SYS_B_15"
and tlaa.cd_loc_type = :"SYS_B_16"
and tlst.cd_locality_parent = tltn.cd_locality
and tltn.cd_locality_parent = tlaa.cd_locality
and cad.cd_address = ta.cd_address
and ta.cd_address = p.cd_address (+)
and p.tp_property_187 = rtp.cd_ref_tab (+)
and p.ri_ref_tab_187 = rtp.tp_ref_tab (+)
and ta.no_bldg = :"SYS_B_17"
and cad.nm_street_1 like :"SYS_B_18"
and tlaa.nm_loc_c4 = :"SYS_B_19"
and rownum < :"SYS_B_20"
order by State, Town, Street, Bldg
RATIONALE: SQL statement with SQL_ID "du59m1kxabg03" was executed 13
times and had an average elapsed time of 3.9 seconds.
RATIONALE: Average time spent in User I/O wait events per execution was
3.1 seconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(50% impact [618 seconds])
INFO: Waits for I/O to temporary tablespaces were not consuming
significant database time.
The throughput of the I/O subsystem was not significantly lower
than expected.
FINDING 5: 14% impact (180 seconds)
-----------------------------------
PL/SQL execution consumed significant database time.
RECOMMENDATION 1: SQL Tuning, 14% benefit (180 seconds)
ACTION: Tune the PL/SQL block with SQL_ID "07r10s0yvk29x". Refer to the
"Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide
and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID 07r10s0yvk29x
begin :res := STRMADMIN.SRP100_CIS_OMS.TRANSFORM_DML(:1); end;
RATIONALE: SQL statement with SQL_ID "07r10s0yvk29x" was executed 33070
times and had an average elapsed time of 0.0058 seconds.
RATIONALE: Average time spent in PL/SQL execution was 0.0054 seconds.
FINDING 6: 14% impact (174 seconds)
-----------------------------------
Individual database segments responsible for significant user I/O wait were
found.
RECOMMENDATION 1: Segment Tuning, 7.8% benefit (97 seconds)
ACTION: Run "Segment Advisor" on TABLE "POWERON.PO_INT_ELEC_JUNCTION"
with object id 34594.
RELEVANT OBJECT: database object with id 34594
ACTION: Investigate application logic involving I/O on TABLE
"POWERON.PO_INT_ELEC_JUNCTION" with object id 34594.
RELEVANT OBJECT: database object with id 34594
RATIONALE: The I/O usage statistics for the object are: 6 full object
scans, 90791 physical reads, 1279 physical writes and 0 direct reads.
RATIONALE: The SQL statement with SQL_ID "cktyb2w5fxv2y" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID cktyb2w5fxv2y
SELECT id FROM po_int_elec_junction WHERE
downstream_line_section_type = :"SYS_B_0" AND faultable = :"SYS_B_1"
AND ROWNUM < :"SYS_B_2"
RECOMMENDATION 2: Segment Tuning, 6.2% benefit (77 seconds)
ACTION: Investigate application logic involving I/O on INDEX
"OMS.PO_MUTEX_ARCHIVE_PX1" with object id 111398.
RELEVANT OBJECT: database object with id 111398
RATIONALE: The I/O usage statistics for the object are: 0 full object
scans, 17120 physical reads, 19731 physical writes and 0 direct
reads.
RATIONALE: The SQL statement with SQL_ID "9tzfvx7j4v52d" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 9tzfvx7j4v52d
DELETE OMS.PO_MUTEX_ARCHIVE WHERE DELETION_TIME < :B1
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(50% impact [618 seconds])
INFO: Waits for I/O to temporary tablespaces were not consuming
significant database time.
The throughput of the I/O subsystem was not significantly lower
than expected.
FINDING 7: 5.7% impact (71 seconds)
-----------------------------------
Wait event "LNS wait on SENDREQ" in wait class "Network" was consuming
significant database time.
RECOMMENDATION 1: Application Analysis, 5.7% benefit (71 seconds)
ACTION: Investigate the cause for high "LNS wait on SENDREQ" waits.
Refer to Oracle's "Database Reference" for the description of this
wait event.
RECOMMENDATION 2: Application Analysis, 5.7% benefit (71 seconds)
ACTION: Investigate the cause for high "LNS wait on SENDREQ" waits in
Service "SYS$BACKGROUND".
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Network" was consuming significant database time.
(5.8% impact [72 seconds])
FINDING 8: 1.2% impact (15 seconds)
-----------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
RECOMMENDATION 1: DB Configuration, 0.72% benefit (9 seconds)
ACTION: Increase the size of the SGA by setting the parameter
"sga_target" to 4640 M.
ADDITIONAL INFORMATION:
The value of parameter "sga_target" was "3712 M" during the analysis
period.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "User I/O" was consuming significant database time.
(50% impact [618 seconds])
INFO: Waits for I/O to temporary tablespaces were not consuming
significant database time.
The throughput of the I/O subsystem was not significantly lower
than expected.
SYMPTOM: Hard parsing of SQL statements was consuming significant
database time. (4% impact [50 seconds])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
The analysis of I/O performance is based on the default assumption that the
average read time for one database block is 10000 micro-seconds.
An explanation of the terminology used in this report is available when you
run the report with the 'ALL' level of detail.
End of Report
Report written to addmrpt_1_21001_21002.txt
@?/rdbms/admin/sqltrpt
ops$oracle.PONP> @?/rdbms/admin/sqltrpt
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
07r10s0yvk29x ##########
begin :res := STRMADMIN.SRP100_CIS_OMS.TRANSFORM_DML(:1
cw8xjn6js143r 33,064.83
BEGIN po_ui_get_list.get(:INPUT_1, :INPUT_2, :INPUT_3,
25f56qfvyjs2a 17,165.59
BEGIN OMS.oms_IVR.IVR_PROCESS_OUTAGES; END;
07pr8cc2vu4d1 13,502.26
BEGIN poweron.po_network_locking_manager.lock_junction(
3faufr074b25t 7,317.00
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
BEGIN poweron.po_locking_manager.lock_names(:n0,:n1,:n2
9kd4fdc5m5vwn 5,438.28
update /*+ streams or_expand(p "CD_COMPANY_SYSTEM" "NO_
8sgfnnxkkws0j 5,390.25
BEGIN :n0 :=poweron.po_coloring_pkg.style_code_for(:n1,
g7r4wbg2wvan8 5,269.38
SELECT DISTINCT T063.NO_UTL_EQUIP FROM TVP056SERVPROV T
dn5jjsc9qu031 5,099.50
update /*+ streams or_expand(p "CD_COMPANY_SYSTEM" "NO_
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
33700mgdq736g 4,778.65
DECLARE job BINARY_INTEGER := :job; next_date DATE := :
bvkj806qpfvn7 4,739.68
DECLARE job BINARY_INTEGER := :job; next_date DATE := :
ff4105cqfvk30 4,726.72
DECLARE job BINARY_INTEGER := :job; next_date DATE := :
ag61w1kdtv0ya 4,667.91
declare -- $Header: /apps/oracle/local/database_secu
g45uwmgq4g5rv 4,635.28
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
DECLARE job BINARY_INTEGER := :job; next_date DATE := :
gwudafahssr4z 4,563.75
DECLARE job BINARY_INTEGER := :job; next_date DATE := :
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
3ax420txtqjbt 67,524.86
select id, business_code, reference_label, csc, fsc, re
46g3dhnp8zptu 64,425.81
select * from ( SELECT * FROM table(ORD_DATA_RPT_PKG.or
ch7a29gdapxgx 63,620.65
SELECT /*+ leading(ost) cardinality( cmos
07r10s0yvk29x 49,282.11
begin :res := STRMADMIN.SRP100_CIS_OMS.TRANSFORM_DML(:1
guss2f7n899cc 49,077.34
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
BEGIN ched_customer_data.incremental_setup; END;
b6usrg82hwsa3 45,372.24
call dbms_stats.gather_database_stats_job_proc ( )
akfdfrvmn6szv 27,248.76
SELECT distinct j.BREAK_ID, b.rwo_id3 FROM po_elec_zone
chmg14nk5c3v4 20,460.02
BEGIN poweron.po_network_integration.delete_network_sec
agssmpjcayffw 16,748.84
SELECT RTRIM(TAF.TP_DELIVERY) || DECODE(RTRIM(TAF.NM_DE
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
25f56qfvyjs2a 15,966.80
BEGIN OMS.oms_IVR.IVR_PROCESS_OUTAGES; END;
cktyb2w5fxv2y 13,986.12
SELECT id FROM po_int_elec_junction WHERE downstream_li
gf7nkcywhgbyj 13,855.38
INSERT INTO CHED_CIS_CUSTOMER_STAGING (CD_COMPANY_SYSTE
2trtpvb5jtr53 12,714.27
SELECT TO_CHAR(current_timestamp AT TIME ZONE :"SYS_B_0
08drnxyr2586m 12,625.71
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
BEGIN ched_customer_data.synchronize_oms_cis_tables; EN
dh0cp47vfddbd 12,572.55
BEGIN ord_data_rpt_pkg.cust_interrupts_arch; END;
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 9bfznbg1ga3pr
Sql Id specified: 9bfznbg1ga3pr
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_29869
Tuning Task Owner : OPS$ORACLE
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 06/15/2010 13:46:40
Completed at : 06/15/2010 13:50:49
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: PO_CONNECT
SQL ID : 9bfznbg1ga3pr
distinct cad.cd_address asinality(cad 20) */
nvl(cad.nm_admin_area, :"SYS_B_00") as State,
nvl(cad.nm_street_1,m_town, :"SYS_B_01") as Town,
nvl(cad.no_bldg_sort, :"SYS_B_03") as
nvl(p.no_property, :"SYS_B_04") as PropertyNum,
nvl(cad.formatted_address, :"SYS_B_05") as Address,
from nvl(rtp.ds_ref_tab, :"SYS_B_06") as PropertyType
tadlocality tltn,property p,
tadaddress ta,ss cad,y tlaa,
andd_company_system = :"SYS_B_07"
and ta.cd_company_system =system = :"SYS_B_08"
and tlst.cd_company_system = :"SYS_B_10"
and tltn.cd_company_system = :"SYS_B_11"
and cad.cd_company_systemy_system = :"SYS_B_12"
and ta.cd_locality = tlst.cd_locality
and tltn.cd_loc_type =_type = :"SYS_B_14"
and tlaa.cd_loc_type = :"SYS_B_16"
and tlst.cd_locality_parent = tltn.cd_locality
and cad.cd_address_locality_parent = tlaa.cd_locality
and ta.cd_address = p.cd_address (+)
and p.ri_ref_tab_187 =ty_187 = rtp.cd_ref_tab (+)
and cad.nm_street_1 like :"SYS_B_17"
and tltn.nm_loc_c4 =reet_type_1 LIKE :"SYS_B_18"
and rownum <loc_c4 = :"SYS_B_20"
order by State, Town, Street, Bldg
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.4%)
-----------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_29869',
replace => TRUE);
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- SQL Profile "SYS_SQLPROF_0149d2a3c3cb0000" exists for this statement and
was ignored during the tuning process.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 314535112
--------------------------------------------------------------------------------
-------------------------
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time |
--------------------------------------------------------------------------------
-------------------------
| 0 | SELECT STATEMENT | | 1 | 247
| 13779 (1)| 00:00:29 |
| 1 | SORT UNIQUE | | 1 | 247
| 13778 (1)| 00:00:29 |
|* 2 | COUNT STOPKEY | | |
| | |
|* 3 | HASH JOIN | | 1 | 247
| 13777 (1)| 00:00:29 |
| 4 | TABLE ACCESS BY INDEX ROWID | TVP046PROPERTY | 1 | 23
| 1 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 219
| 13771 (1)| 00:00:29 |
| 6 | NESTED LOOPS | | 1 | 196
| 13770 (1)| 00:00:29 |
|* 7 | HASH JOIN | | 1 | 178
| 13769 (1)| 00:00:29 |
| 8 | TABLE ACCESS BY INDEX ROWID | TADLOCALITY | 1 | 22
| 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | XADLOC1 | 1 |
| 1 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 3996 | 608K
| 13767 (1)| 00:00:29 |
| 11 | NESTED LOOPS | | 4227 | 573K
| 12919 (1)| 00:00:27 |
|* 12 | TABLE ACCESS BY INDEX ROWID| TADADDRFAST | 3669 | 440K
| 12183 (1)| 00:00:25 |
|* 13 | INDEX RANGE SCAN | TADADDRFAST_PX2 | 73379 |
| 102 (2)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID| TADADDRESS | 1 | 16
| 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | XADDRES0 | 1 |
| 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | TADLOCALITY | 1 | 17
| 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | XADLOC0 | 1 |
| 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | TADLOCALITY_PX1 | 1 | 18
| 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | XVP0462 | 1 |
| 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | TVP358REFTAB_XP1 | 4331 | 118K
| 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<:SYS_B_21)
3 - access("P"."TP_PROPERTY_187"="RTP"."CD_REF_TAB" AND
"P"."RI_REF_TAB_187"="RTP"."TP_REF_TAB")
7 - access("TLST"."CD_LOCALITY_PARENT"="TLTN"."CD_LOCALITY")
9 - access("TLTN"."CD_COMPANY_SYSTEM"=:SYS_B_11 AND "TLTN"."NM_LOC_C4"=:SYS_B
_19 AND
"TLTN"."CD_LOC_TYPE"=:SYS_B_15)
12 - filter(RTRIM("NM_STREET_TYPE_1")||DECODE(RTRIM("NM_STREET_TP_1_SFX"),'','
','
'||RTRIM("NM_STREET_TP_1_SFX")) LIKE :SYS_B_18)
13 - access("NM_STREET_1" LIKE :SYS_B_17 AND "CD_COMPANY_SYSTEM"=:SYS_B_13)
filter("NM_STREET_1" LIKE :SYS_B_17 AND "CD_COMPANY_SYSTEM"=:SYS_B_13)
15 - access("TA"."CD_COMPANY_SYSTEM"=:SYS_B_09 AND "CD_ADDRESS"="TA"."CD_ADDRE
SS")
17 - access("TLST"."CD_COMPANY_SYSTEM"=:SYS_B_10 AND "TA"."CD_LOCALITY"="TLST"
."CD_LOCALITY"
AND "TLST"."CD_LOC_TYPE"=:SYS_B_14)
18 - access("TLAA"."CD_COMPANY_SYSTEM"=:SYS_B_12 AND "TLAA"."CD_LOC_TYPE"=:SYS
_B_16 AND
"TLTN"."CD_LOCALITY_PARENT"="TLAA"."CD_LOCALITY" AND "TLAA"."NM_LO
C_C4"=:SYS_B_20)
19 - access("P"."CD_COMPANY_SYSTEM"=:SYS_B_07 AND "TA"."CD_ADDRESS"="P"."CD_AD
DRESS")
20 - access("RTP"."CD_COMPANY_SYSTEM"=:SYS_B_08)
2- Using SQL Profile
--------------------
Plan hash value: 1135877695
--------------------------------------------------------------------------------
------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
--------------------------------------------------------------------------------
------------------------
| 0 | SELECT STATEMENT | | 6 | 1482 |
82 (4)| 00:00:01 |
| 1 | SORT UNIQUE | | 6 | 1482 |
81 (3)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | |
| |
| 3 | NESTED LOOPS | | 6 | 1482 |
80 (2)| 00:00:01 |
| 4 | NESTED LOOPS | | 6 | 1374 |
79 (2)| 00:00:01 |
| 5 | NESTED LOOPS | | 4 | 804 |
78 (2)| 00:00:01 |
|* 6 | HASH JOIN | | 6 | 1068 |
77 (2)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | TADADDRFAST | 465 | 57195 |
33 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | TADADDRFAST_PX2 | 194 | |
1 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 2892 | 155K|
43 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 204 | 7956 |
2 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| TADLOCALITY | 3 | 66 |
1 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | XADLOC1 | 1 | |
1 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | XADLOC5 | 1 | 17 |
1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | TADADDRESS_PX2 | 14 | 224 |
1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | TVP046PROPERTY | 1 | 23 |
1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | XVP0462 | 1 | |
1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | TVP358REFTAB_XP1 | 1 | 28 |
1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | TADLOCALITY_PX1 | 1 | 18 |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<:SYS_B_21)
6 - access("CD_ADDRESS"="TA"."CD_ADDRESS")
7 - filter(RTRIM("NM_STREET_TYPE_1")||DECODE(RTRIM("NM_STREET_TP_1_SFX"),'','
','
'||RTRIM("NM_STREET_TP_1_SFX")) LIKE :SYS_B_18)
8 - access("NM_STREET_1" LIKE :SYS_B_17 AND "CD_COMPANY_SYSTEM"=:SYS_B_13)
filter("NM_STREET_1" LIKE :SYS_B_17 AND "CD_COMPANY_SYSTEM"=:SYS_B_13)
12 - access("TLTN"."CD_COMPANY_SYSTEM"=:SYS_B_11 AND "TLTN"."NM_LOC_C4"=:SYS_B
_19 AND
"TLTN"."CD_LOC_TYPE"=:SYS_B_15)
13 - access("TLST"."CD_COMPANY_SYSTEM"=:SYS_B_10 AND
"TLST"."CD_LOCALITY_PARENT"="TLTN"."CD_LOCALITY" AND "TLST"."CD_LO
C_TYPE"=:SYS_B_14)
filter("TLST"."CD_LOC_TYPE"=:SYS_B_14)
14 - access("TA"."CD_COMPANY_SYSTEM"=:SYS_B_09 AND "TA"."CD_LOCALITY"="TLST"."
CD_LOCALITY")
16 - access("P"."CD_COMPANY_SYSTEM"=:SYS_B_07 AND "TA"."CD_ADDRESS"="P"."CD_AD
DRESS")
17 - access("RTP"."CD_COMPANY_SYSTEM"=:SYS_B_08 AND "P"."RI_REF_TAB_187"="RTP"
."TP_REF_TAB"
AND "P"."TP_PROPERTY_187"="RTP"."CD_REF_TAB")
18 - access("TLAA"."CD_COMPANY_SYSTEM"=:SYS_B_12 AND "TLAA"."CD_LOC_TYPE"=:SYS
_B_16 AND
"TLTN"."CD_LOCALITY_PARENT"="TLAA"."CD_LOCALITY" AND "TLAA"."NM_LO
C_C4"=:SYS_B_20)
-------------------------------------------------------------------------------
|
Send a message
Printer friendly output
Display this member's tips (202)