Shulou(Shulou.com)06/02 Report--

1.SQL*PLUS AUTOTRACE:

To make SQLPLUS AUTOTRACE available to all users, you need to do the following.

SQL > @? / rdbms/admin/utlxplan.sql

Table created.

SQL > create or replace public synonym plan_table for plan_table

Synonym created.

SQL > grant all on plan_table to public

Grant succeeded.

SQL > @? / sqlplus/admin/plustrce.sql

SQL >

SQL > drop role plustrace

Drop role plustrace

*

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

SQL > create role plustrace

Role created.

SQL >

SQL > grant select on v_$sesstat to plustrace

Grant succeeded.

SQL > grant select on v_$statname to plustrace

Grant succeeded.

SQL > grant select on v_$mystat to plustrace

Grant succeeded.

SQL > grant plustrace to dba with admin option

Grant succeeded.

SQL >

SQL > set echo off

SQL > grant plustrace to public

Grant succeeded.

SQL > connect test/test

Connected.

SQL > set autotrace on

SQL >

AUTOTRACE options:

Usage: SET AUTOT [RACE] {OFF | ON | TRACE [ONLY]} [explain] [stats]]

SET AUTOTRACE ON

SET AUTOTRACE TRACE EXPLAIN

SET AUTOTRACE TRACE EXPLAIN STATISTICS

Enable the AUTOTRACE function, which corresponds to two sessions in a server process, one for querying data, and one for recording the execution plan and final result. SQLPLUS AUTOTRACE is a method to query the execution plan based on the PLAN_TABLE table, and the internal implementation is actually the same as the method to be discussed below:

EXPLAIN PLAN FOR

SELECT * FROM TABLE_NAME

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY (format= > 'BASIC'))

two。 In fact, the method I'm going to talk about below is already talking about the combination of EXPLAIN PLAN FOR and DBMS_XPLAN when talking about SQLPLUS AUTOTRACE.

EXPLAIN PLAN [SET statement_id='xxx'] FOR

SELECT * FROM TABLE_NAME

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY)

Joining SET statement_id='xxx' allows plan_table to host multiple execution plans. In addition to using dbms_xplan.display, you can also get the execution plan by executing the

Utlxpls.sql / / displays the planned results of a serial query

Utlxplp.sql / / displays the planned results of parallel queries

Using this method is also based on the PLAN_ table. It's actually the same as SQLPLUS AUTOTRACE.

3. The following method is to directly query the V$SQL_PLAN table, directly query V$SQL_PLAN is not well formatted, it seems inconvenient, ORACLE 10g began to provide a new package to well format the results of V$SQL_PLAN. It is also the fourth query method.

4.DBMS_XPLAN.DISPLAY_CURSOR

SELECT * FROM TABLE_NAME

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR)

Ordinary users need the permissions of the following views if they want to use DBMS_XPLAN.DISPLAY_CURSOR:

Grant select on v_$session to scott

Grant select on v_$sql_plan to scott

Grant select on v_$sql to scott

This implementation is the same as querying V$SQL_PLAN directly.

OK, there is a problem to mention at this point. We observe that the first and second methods are based on the PLAN_TABLE table to generate the execution plan, and the third and fourth methods are based on the V$SQL_PLAN view to generate the execution plan. Through practice and some forum experience, it is found that for a SQL, the results of the two query execution plans may be different. Of course, the execution plans for the third and fourth queries are the real execution plans in the real LIBRARY CACHE. The execution plans generated by the first and second methods can be considered to be pre-judged. So we sometimes encounter in the actual production environment, the execution speed in SQLPLUS is very fast, and once used in stored procedures or programs, it will become extremely slow, probably because of different execution plans. Through monitoring, we will find that the execution plan generated by the two is completely different, and the wrong execution plan is generated during the specific implementation. This situation may be due to execution plan errors caused by parameters such as CURSOR_SHARING=FORCE or indexes.

In addition, the execution plan obtained through the DBMS_XPLAN.DISPLAY_AWR function comes from the DBA_HIST_SQL_PLAN view, through historical data records, and even some aging SQL execution plans can still be found.

You can also add some parameter values:

SQL > desc dbms_xplan

FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

TABLE_NAME VARCHAR2 IN DEFAULT

STATEMENT_ID VARCHAR2 IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FILTER_PREDS VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE NUMBER (38) IN DEFAULT

DB_ID NUMBER (38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

SQL_ID VARCHAR2 IN DEFAULT

CURSOR_CHILD_NO NUMBER (38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

SELECT plan_table_output FROM TABLE (DBMS_XPLAN.DISPLAY ('PLAN_TABLE','NO','ALL'))

SELECT plan_table_output FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (', 2 ALL all')); / ALL corresponds to FORMAT, with several values of BASIC,TYPICAL,SERIAL,ALL. Each value corresponds to a different display, and ALL shows the most detailed content. The default is that the TYPICAL,SERIAL and TYPICAL displays are the same, except that SERIAL removes the parallel display.

SELECT plan_table_output FROM TABLE (DBMS_XPLAN.AWR ('))

Ordinary users need to be granted the SELECT_CATALOG role if they are to be able to use dbms_xplan.display_cursor and dbms_xplan.display_awr.

5.SQL TRACE

Enable:

Alter session set sql_trace=on

Disable:

Alter session set sql_trace=off

Track other users:

Exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (SID,SERIAL#,SQL_TRACE)

6. Diagnostic events (10046)

Enable:

Alter session set events' 10046 trace name context forever,level 12'

Disable:

Alter session set events' 10046 trace name context off'

Enable other user 10046 diagnostics:

Exec DBMS_SYSTEM.SET_EV (SI,SE,EV,LE,NM)

On: exec DBMS_SYSTEM.SET_EV (1056, 232, 10046, 12, and 12)

Off: exec DBMS_SYSTEM.SET_EV (1056 pencils 232 pas 10046 pas 0pr')

Also how to see if the 10046 event is enabled:

SQL > alter session set events' 10046 trace name context forever, level 12'

Session altered.

SQL > oradebug setmypid

Statement processed.

SQL > oradebug tracefile_name

/ u01/app/oracle/db_1/rdbms/log/test_ora_5529.trc

SQL > oradebug eventdump session

10046 trace name CONTEXT level 12, forever

7. Use oracle third-party tools:

Plsql developer (F5)

Toad (Ctrl+E)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.