在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-- |
联系客服