在10gR2中,提供了$ORACLE_HOME/rdbms/admin/sqltrpt.sql脚本,用于抽取占用资源较多的sql,并可以为指定的sql生成执行计划,资源占用较多的sql分为两部分
1) 15 Most expensive SQL in the cursor cache
1) 15 Most expensive SQL in the cursor cache
2) 15 Most expensive SQL in the workload repository
sys@ORCL> conn /as sysdba
已连接。
sys@ORCL> set linesize 200;
sys@ORCL> @?/rdbms/admin/sqltrpt.sql
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
b6usrg82hwsa3 52.54 call dbms_stats.gather_database_stats_job_proc ( )
572fbaj0fdw2b 21.16 select output from table(dbms_workload_repository.awr_r
59v4zh1ac3v2a 14.61 DECLARE job BINARY_INTEGER := :job; next_date TIMESTAM
2tr12b1b8uj71 8.69 MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_c
6mcpb06rctk0x 7.58 call dbms_space.auto_space_advisor_job_proc ( )
63kf3an2j0pkc 5.31 insert into sys.wri$_optstat_histgrm_history (obj#,intc
c0j6cx9kzjf7g 4.89 SELECT EXTRACTVALUE(VALUE(T), '/select_list_item/pos')
62yyzw3309d6a 3.89 SELECT VALUE FROM V$SESSION_FIX_CONTROL WHERE BUGNO = :
cvn54b7yz0s8u 3.01 select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length
4y1y43113gv8f 2.72 delete from histgrm$ where obj#=:1 and intcol#=:2 and r
dayq182sk41ks 2.55 insert into wrh$_memory_target_advice (snap_id, dbid,
bm2pwrpcr8ru6 2.53 select sga_size s, sga_size_factor * 100 f,
advr729cu0627 2.51 select * from ( select stat.sql_id as sql_id, sum(elap
bunssq950snhf 2.49 insert into wrh$_sga_target_advice (snap_id, dbid, in
43ruajmwv9fus 2.41 insert into histgrm$(obj#,intcol#,row#,bucket,endpoint,
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- --------------------------------------------------------------------------------------------------------------
b6usrg82hwsa3 238.62 call dbms_stats.gather_database_stats_job_proc ( )
59v4zh1ac3v2a 121.83 DECLARE job BINARY_INTEGER := :job; next_date TIMESTAM
db78fxqxwxt7r 81.03 select /*+ rule */ bucket, endpoint, col#, epvalue from
f6cz4n8y72xdc 70.19 SELECT space_usage_kbytes FROM v$sysaux_occupants WH
6mcpb06rctk0x 67.76 call dbms_space.auto_space_advisor_job_proc ( )
5jvf84zg4c49n 67.39 select s.synonym_name as object_name, o.object_type
cvn54b7yz0s8u 59.87 select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length
gqbsq1gpca1gh 52.65 declare logoff_sid pls_integer; logoff_time date :
3ktacv9r56b51 48.01 select owner#,name,namespace,remoteowner,linkname,p_tim
8szmwam7fysa3 43.19 insert into wri$_adv_objspace_trend_data select timepoi
12a2xbmwn5v6z 41.41 select owner, segment_name, blocks from dba_segments wh
c2p32r5mzv8hb 41.28 BEGIN prvt_advisor.delete_expired_tasks; END;
2tr12b1b8uj71 36.90 MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_c
ga6ja2d04ycbm 33.30 DECLARE job BINARY_INTEGER := :job; next_date TIMESTAM
62yyzw3309d6a 33.25 SELECT VALUE FROM V$SESSION_FIX_CONTROL WHERE BUGNO = :
Specify the Sql id
~~~~~~~~~~~~~~~~~~
输入 sqlid 的值: 8szmwam7fysa3
Sql Id specified: 8szmwam7fysa3
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 任务_832
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 11/19/2013 22:17:00
Completed at : 11/19/2013 22:17:00
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 8szmwam7fysa3
SQL Text : insert into wri$_adv_objspace_trend_data select timepoint,
space_usage, space_alloc, quality from
table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,
NULL, 'FALSE', :5, 'FALSE'))
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
尚未分析表 "SYS"."WRI$_ADV_OBJSPACE_TREND_DATA"。
Recommendation
--------------
- 考虑收集此表的优化程序统计信息。
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'WRI$_ADV_OBJSPACE_TREND_DATA', estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
AUTO');
Rationale
---------
为了选择好的执行计划, 优化程序需要此表的最新统计信息。
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2976124318
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 8168 | 32672 | 19 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | WRI$_ADV_OBJSPACE_TREND_DATA | | | | |
| 2 | COLLECTION ITERATOR PICKLER FETCH| OBJECT_GROWTH_TREND | 8168 | 32672 | 19 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
sys@ORCL>