SQL> set linesize 177 pagesize 120
SQL> @?/rdbms/admin/sqltrpt
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
60uw2vh6q9vn2 2,113.52 insert into col$(obj#,name,intcol#,segcol#,type#,length
d7a9644j72das 1,699.71 INSERT INTO "xxx".tb201309_PriceBill (BillNumber
6v7rvuz53c4bz 1,302.13 MERGE INTO "xxx".TBUNCHECKPAYS UPD USING ( SELECT * FRO
bfu88vc21u6xr 1,295.63 Begin MERGE INTO "xxx".tbUnCheckPays upd USING
gvmgz7t1gqjzf 1,140.42 BEGIN MERGE INTO "xxx".tbUnCheckPays DET
4tz7cwc9a4kzj 685.70 INSERT INTO "xxx".tb201309_ChgBill (NodeCode ,Bi
6hgjc2jzygjud 583.07 MERGE INTO "xxx".TBUNPAYDETAILS UPD USING ( SELECT * FR
78d11ax59srmt 581.66 Begin MERGE INTO "xxx".tbUnPayDetails upd USING
cqwfdp2kgp1y6 508.58 SELECT VALUE FROM SYS.LOADER_SKIP_UNUSABLE_INDEXES
33cmj01uf5zfb 464.55 delete from col$ where obj#=:1
db78fxqxwxt7r 325.58 select /*+ rule */ bucket, endpoint, col#, epvalue from
9wa1jj6mtftx4 287.08 DELETE FROM "xxx".TBDEPTSALEPRICE DELPR WHERE DELPR.ROW
g44t1ww5fgjdf 270.67 MERGE INTO "xxx".TBUNPAYDETAILS DET USING ( SELECT * FR
asnhcg241fr2y 259.86 insert into tab$(obj#,ts#,file#,block#,bobj#,tab#,intco
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- --------------------------------------------------------------------------------------------------------------
dr97hbjraumrm 89,794.66 SELECT 1 FROM V$LOGFILE WHERE(STATUS NOT IN ('STALE',
9wa1jj6mtftx4 29,631.38 DELETE FROM "xxx".TBDEPTSALEPRICE DELPR WHERE DELPR.ROW
6v7rvuz53c4bz 26,554.84 MERGE INTO "xxx".TBUNCHECKPAYS UPD USING ( SELECT * FRO
9gdcsdf04pab3 25,724.07 SELECT DECODE(archived, 'YES', 1, 0), status FRO
g44t1ww5fgjdf 23,180.68 MERGE INTO "xxx".TBUNPAYDETAILS DET USING ( SELECT * FR
11w4hpr2fkdqm 23,036.65 begin insert into TempGo where nodek 21,037.30 select nodecode,nodename from "xxx".tbnode
2p3b49x0hyrj2 19,479.70 SELECT MX.ACCUNITCODE , ACC.ACCOUNTINGUNITNAME ACCUNITN
8s37753p3g1zp 18,659.64 BEGIN UPDATE "xxx".tbUnCheckPays SET B2B
60uw2vh6q9vn2 18,127.33 insert into col$(obj#,name,intcol#,segcol#,type#,length
b6usrg82hwsa3 17,902.35 call dbms_stats.gather_database_stats_job_proc ( )
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 9wa1jj6mtftx4
Sql Id specified: 9wa1jj6mtftx4
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_21643
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 09/26/2013 13:17:13
Completed at : 09/26/2013 13:19:47
-------------------------------------------------------------------------------
Schema Name: SYSDEV
SQL ID : 9wa1jj6mtftx4
SQL Text : DELETE FROM "xxx".TBDEPTSALEPRICE DELPR WHERE DELPR.ROWID IN
(SELECT DELPR.ROWID FROM "xxx".TBGOODS GOODS,
"xxx".TBDEPTSALEPRICE DELPR WHERE DELPR.GOODSCODE =
GOODS.GOODSCODE AND DELPR.SALEPRICE = GOODS.SALEPRICE AND
DELPR.WHOLESALEPRICE = GOODS.WHOLESALEPRICE AND
DELPR.APPSALEPRICE = GOODS.APPSALEPRICE AND DELPR.POINTS =
GOODS.POINTS AND DELPR.PURCHPRICE = GOODS.PURCHPRICE AND
DELPR.DEPTTYPE = 0 AND DELPR.GOODSCODE IN (SELECT DISTINCT
GOODSCODE FROM SALEPRICE))
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SYSDEV"."SALEPRICE" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYSDEV', tabname =>
'SALEPRICE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 3809218839 2013-09-26/11:00:22 5.804 AWR not reproducible
2 688468468 2013-09-24/18:00:36 9.284 AWR not reproducible
3 891999467 2013-09-26/12:00:32 42.547 AWR not reproducible
4 2331989595 2013-09-25/13:00:55 123.258 AWR not reproducible
Information
-----------
- All alternative plans other than the Original Plan could not be
reproduced in the current environment.
- The plan with id 1 could not be reproduced in the current environment.
For this reason, a SQL plan baseline cannot be created to instruct the
Oracle optimizer to pick this plan in the future.
- The plan with id 2 could not be reproduced in the current environment.
For this reason, a SQL plan baseline cannot be created to instruct the
Oracle optimizer to pick this plan in the future.
- The plan with id 3 could not be reproduced in the current environment.
For this reason, a SQL plan baseline cannot be created to instruct the
Oracle optimizer to pick this plan in the future.
- The plan with id 4 could not be reproduced in the current environment.
For this reason, a SQL plan baseline cannot be created to instruct the
Oracle optimizer to pick this plan in the future.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 688468468
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 49 | 59 (2)| 00:00:01 |
| 1 | DELETE | TBDEPTSALEPRICE | | | | |
| 2 | NESTED LOOPS | | 1 | 49 | 59 (2)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 1 | 12 | 57 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 72 | | |
| 5 | NESTED LOOPS | | | | | |
| 6 | NESTED LOOPS | | 1 | 72 | 57 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 47 | 55 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SALEPRICE | 1 | 9 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| TBDEPTSALEPRICE | 56 | 2128 | 53 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PK_TBDEPTSALEPRICE_NEW | 56 | | 3 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_TBGOODS | 1 | | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | TBGOODS | 1 | 25 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY USER ROWID | TBDEPTSALEPRICE | 1 | 37 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("DELPR"."GOODSCODE"="GOODSCODE" AND "DELPR"."DEPTTYPE"=0)
11 - access("DELPR"."GOODSCODE"="GOODS"."GOODSCODE")
12 - filter("DELPR"."SALEPRICE"="GOODS"."SALEPRICE" AND
"DELPR"."WHOLESALEPRICE"="GOODS"."WHOLESALEPRICE" AND "DELPR"."APPSALEPRICE"="GOODS"."APPSALEPRICE"
AND "DELPR"."POINTS"="GOODS"."POINTS" AND "DELPR"."PURCHPRICE"="GOODS"."PURCHPRICE")
-------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
-------------------------------------------------------------------------------
Plan 2
------
Plan Origin :AWR
Plan Hash Value :688468468
Executions :120
Elapsed Time :9.284 sec
CPU Time :1.499 sec
Buffer Gets :254517
Disk Reads :17920
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
2. The plan matches the original plan.
3. The plan with id 2 could not be reproduced in the current environment. For this reason, a SQL plan baseline cannot be created to instruct the Oracle optimizer to pick this
plan in the future.
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 5525 (100)| |
| 1 | DELETE | TBDEPTSALEPRICE | | | | |
| 2 | NESTED LOOPS | | 1 | 49 | 5525 (1)| 00:01:07 |
| 3 | VIEW | VW_NSO_1 | 53 | 636 | 5523 (1)| 00:01:07 |
| 4 | SORT UNIQUE | | 1 | 3816 | | |
| 5 | NESTED LOOPS | | | | | |
| 6 | NESTED LOOPS | | 53 | 3816 | 5523 (1)| 00:01:07 |
| 7 | NESTED LOOPS | | 1895 | 89065 | 1731 (1)| 00:00:21 |
| 8 | TABLE ACCESS FULL | SALEPRICE | 32 | 288 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| TBDEPTSALEPRICE | 59 | 2242 | 54 (0)| 00:00:01 |
| 10 | INDEX RANGE SCAN | PK_TBDEPTSALEPRICE_NEW | 59 | | 3 (0)| 00:00:01 |
| 11 | INDEX UNIQUE SCAN | PK_TBGOODS | 1 | | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | TBGOODS | 1 | 25 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY USER ROWID | TBDEPTSALEPRICE | 1 | 37 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Plan 1
------
Plan Origin :AWR
Plan Hash Value :3809218839
Executions :347
Elapsed Time :5.804 sec
CPU Time :0.890 sec
Buffer Gets :97665
Disk Reads :4158
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
2. The plan with id 1 could not be reproduced in the current environment. For this reason, a SQL plan baseline cannot be created to instruct the Oracle optimizer to pick this
plan in the future.
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 7020 (100)| |
| 1 | DELETE | TBDEPTSALEPRICE | | | | |
| 2 | NESTED LOOPS | | 1 | 49 | 7020 (1)| 00:01:25 |
| 3 | VIEW | VW_NSO_1 | 102 | 1224 | 7018 (1)| 00:01:25 |
| 4 | SORT UNIQUE | | 1 | 7344 | | |
| 5 | HASH JOIN | | 102 | 7344 | 7018 (1)| 00:01:25 |
| 6 | NESTED LOOPS | | | | | |
| 7 | NESTED LOOPS | | 3765 | 172K| 3298 (1)| 00:00:40 |
| 8 | TABLE ACCESS FULL | SALEPRICE | 61 | 549 | 2 (0)| 00:00:01 |
| 9 | INDEX RANGE SCAN | PK_TBDEPTSALEPRICE_NEW | 62 | | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| TBDEPTSALEPRICE | 62 | 2356 | 54 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | TBGOODS | 434K| 10M| 3716 (1)| 00:00:45 |
| 12 | TABLE ACCESS BY USER ROWID | TBDEPTSALEPRICE | 1 | 37 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Plan 3
------
Plan Origin :AWR
Plan Hash Value :891999467
Executions :36
Elapsed Time :42.547 sec
CPU Time :6.509 sec
Buffer Gets :393539
Disk Reads :159834
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
2. The plan with id 3 could not be reproduced in the current environment. For this reason, a SQL plan baseline cannot be created to instruct the Oracle optimizer to pick this
plan in the future.
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | | 56430 (100)| |
| 1 | DELETE | TBDEPTSALEPRICE | | | | | |
| 2 | NESTED LOOPS | | 1 | 49 | | 56430 (2)| 00:11:18 |
| 3 | VIEW | VW_NSO_1 | 8143 | 97716 | | 56428 (2)| 00:11:18 |
| 4 | SORT UNIQUE | | 1 | 572K| | | |
| 5 | HASH JOIN | | 8143 | 572K| 15M| 56428 (2)| 00:11:18 |
| 6 | TABLE ACCESS FULL | TBGOODS | 435K| 10M| | 3781 (1)| 00:00:46 |
| 7 | HASH JOIN | | 293K| 13M| | 51054 (2)| 00:10:13 |
| 8 | TABLE ACCESS FULL | SALEPRICE | 4844 | 43596 | | 10 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | TBDEPTSALEPRICE | 15M| 569M| | 50931 (2)| 00:10:12 |
| 10 | TABLE ACCESS BY USER ROWID| TBDEPTSALEPRICE | 1 | 37 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Plan 4
------
Plan Origin :AWR
Plan Hash Value :2331989595
Executions :200
Elapsed Time :123.258 sec
CPU Time :6.520 sec
Buffer Gets :246880
Disk Reads :171032
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
2. The plan with id 4 could not be reproduced in the current environment. For this reason, a SQL plan baseline cannot be created to instruct the Oracle optimizer to pick this
plan in the future.
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | | 59310 (100)| |
| 1 | DELETE | TBDEPTSALEPRICE | | | | | |
| 2 | NESTED LOOPS | | 1 | 49 | | 59310 (2)| 00:11:52 |
| 3 | VIEW | VW_NSO_1 | 2830 | 33960 | | 59307 (2)| 00:11:52 |
| 4 | SORT UNIQUE | | 1 | 198K| | | |
| 5 | HASH JOIN | | 2830 | 198K| 5896K| 59307 (2)| 00:11:52 |
| 6 | HASH JOIN | | 102K| 4695K| | 54375 (2)| 00:10:53 |
| 7 | TABLE ACCESS FULL | SALEPRICE | 1700 | 15300 | | 4 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | TBDEPTSALEPRICE | 16M| 588M| | 54255 (2)| 00:10:52 |
| 9 | TABLE ACCESS FULL | TBGOODS | 449K| 10M| | 3853 (1)| 00:00:47 |
| 10 | TABLE ACCESS BY USER ROWID| TBDEPTSALEPRICE | 1 | 37 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
SQL>
1301
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
