在10gR2中,提供了$ORACLE_HOME/rdbms/admin/sqltrpt.sql腳本,用于抽取占用資源較多的sql,并可以為指定的sql生成執(zhí)行計劃,資源占用較多的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 (此處輸入想要查看執(zhí)行計劃的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-- |
