oracle10怎么使用,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--