重生之至尊嫡女:Oracle10g sqltrpt使用

在10gR2中,提供了$ORACLE_HOME/rdbms/admin/sqltrpt.sql脚本,用于抽取占用资源较多的sql,并可以为指定的sql生成执行计划,资源占用较多的sql分为两部分
1) 15 Most expensive SQL in the cursor cache
2) 15 Most expensive SQL in the workload repository

$ sqlplus / as sysdba
SQL> set pages 50
SQL> @?/rdbms/admin/sqltrpt

15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID           ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
0hfq79bujc3zj   1,087.50 BEGIN    LBACSYS.lbac_events.logon(dbms_standard.login_
6gvch1xu9ca3g     988.82 DECLARE job BINARY_INTEGER := :job; next_date DATE := :
cb75rw3w1tt0s     696.16 begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :
8s6khny76f958     329.73 begin :1 := PKG_USER.enterGame(:2, :3, :4, :5, :6, :7,
acuzy2ur5auf9     276.12 begin :1 := PKG_GATEWAY.GWDataReport(:2, :3, :4, :5);en
2b064ybzkwf1y     237.19 BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
81dpw9ux6g91p     215.38 begin :1 := PKG_USER.logout(:2, :3, :4, :5, :6, :7, :8,
7j23tu2qk35zj     136.06 /* OracleOEM */ BEGIN   IF (:1 = 'READ WRITE' AND (:2
abtp0uqvdb1d3     124.44 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mgu
aykvshm7zsabd     109.40 select size_for_estimate,                      size_fac
cydnuss99swtd      83.76 BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END;
0k8522rmdzg4k      75.23 select privilege# from sysauth$ where (grantee#=:1 or g
0h6b2sajwb74n      72.58 select privilege#,level from sysauth$ connect by grante
0hbv80w9ypy0n      68.09 /* OracleOEM */    SELECT end_time, status, session_key
72pwvcwcgp93c      62.36 begin :1 := PKG_USER.login(:2, :3, :4, :5, :6, :7, :8,

15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID           ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- --------------------------------------------------------------------------------------------------------------
6gvch1xu9ca3g     302.50 DECLARE job BINARY_INTEGER := :job; next_date DATE := :
0hfq79bujc3zj     200.45 BEGIN    LBACSYS.lbac_events.logon(dbms_standard.login_
b6usrg82hwsa3     188.72 call dbms_stats.gather_database_stats_job_proc ( )
cb75rw3w1tt0s     158.66 begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :
b2hrmq9xsdw51     125.87 BEGIN EMD_LOADER.STRING_HISTORY_PURGE(:1); END;
6g1p4s9ra6ag8     125.82 SELECT SMH.ROWID FROM (SELECT TARGET_GUID,METRIC_GUID,K
bunssq950snhf     115.90 insert into wrh$_sga_target_advice   (snap_id, dbid, in
2b064ybzkwf1y      53.89 BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
8hk7xvhua40va      40.60 INSERT INTO MGMT_METRICS_RAW(COLLECTION_TIMESTAMP, KEY_
8s6khny76f958      39.28 begin :1 := PKG_USER.enterGame(:2, :3, :4, :5, :6, :7,
acuzy2ur5auf9      37.39 begin :1 := PKG_GATEWAY.GWDataReport(:2, :3, :4, :5);en
7j23tu2qk35zj      29.54 /* OracleOEM */ BEGIN   IF (:1 = 'READ WRITE' AND (:2
abtp0uqvdb1d3      28.67 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mgu
8a1pvy4cy8hgv      26.15 insert into histgrm$(obj#,intcol#,row#,bucket,endpoint,
81dpw9ux6g91p      25.22 begin :1 := PKG_USER.logout(:2, :3, :4, :5, :6, :7, :8,

Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: aykvshm7zsabd    (此处输入想要查看执行计划的sqlid)

Sql Id specified: aykvshm7zsabd

Tune the sql
~~~~~~~~~~~~

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : TASK_2105
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 12/16/2008 18:35:12
Completed at                      : 12/16/2008 18:35:12
Number of SQL Profile Findings    : 1

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : aykvshm7zsabd
SQL Text   : select size_for_estimate,                      size_factor * 100
             f,                    estd_physical_read_time,
             estd_physical_reads              from v$db_cache_advice where id
             = '3'

-------------------------------------------------------------------------------
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<=10%)
---------------------------------------
- Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_2105', replace
            => TRUE);

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 1 of the execution plan.
The optimizer cannot merge a view that contains an "ORDER BY" clause unless
the statement is a "DELETE" or an "UPDATE" and the parent query is the top
most query in the statement.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 2489475782


------------------------------------------------------------------------------------------
| Id | Operation           | Name               | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |    20 | 1340 |     2 (100)| 00:00:01 |
|* 1 | VIEW               | GV$DB_CACHE_ADVICE |    20 | 1340 |     2 (100)| 00:00:01 |
|   2 |   SORT ORDER BY     |                    |    20 | 4400 |     2 (100)| 00:00:01 |
|* 3 |    HASH JOIN        |                    |    20 | 4400 |     1 (100)| 00:00:01 |
|* 4 |     FIXED TABLE FULL| X$KCBSC            |    20 | 3640 |     0   (0)| 00:00:01 |
|* 5 |     FIXED TABLE FULL| X$KCBWBPD          |     1 |    38 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("INST_ID"=USERENV('INSTANCE'))
   3 - access("A"."BPID"="B"."BP_ID" AND "A"."INST_ID"="B"."INST_ID")
   4 - filter("A"."BPID"=3)
   5 - filter("B"."BP_ID"=3)

2- Using SQL Profile
--------------------
Plan hash value: 1829585422

------------------------------------------------------------------------------------------
| Id | Operation           | Name               | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |    20 | 1340 |     2 (100)| 00:00:01 |
|* 1 | VIEW               | GV$DB_CACHE_ADVICE |    20 | 1340 |     2 (100)| 00:00:01 |
|   2 |   SORT ORDER BY     |                    |    20 | 4400 |     2 (100)| 00:00:01 |
|* 3 |    HASH JOIN        |                    |    20 | 4400 |     1 (100)| 00:00:01 |
|* 4 |     FIXED TABLE FULL| X$KCBWBPD          |     1 |    38 |     0   (0)| 00:00:01 |
|* 5 |     FIXED TABLE FULL| X$KCBSC            |    20 | 3640 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("INST_ID"=USERENV('INSTANCE'))
   3 - access("A"."BPID"="B"."BP_ID" AND "A"."INST_ID"="B"."INST_ID")
   4 - filter("B"."BP_ID"=3)
   5 - filter("A"."BPID"=3)

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


SQL>

是不是很方便!

--End--