Oracle Learning Note (iii)----------execution plan

View Oracle several ways to execute a plan

First, through the PL/SQL Dev tool

1. Directly file->new->explain plan window, execute SQL in the window to view the planned results. Where the cost represents the CPU consumption, the unit is n%,cardinality represents the number of rows executed, equivalent to rows.

2, first execute EXPLAIN PLAN for select * from TableA where paraa=1, then select * FROM table (Dbms_xplan. DISPLAY) You can see the Oracle execution plan and see the same results as in 1, so using the tool is recommended when using the 1 method.

Note: the command window of the PL/SQL Dev tool does not support set autotrance on. There are also tools to view the information that the program sees, and sometimes we need sqlplus support.

second, through Sqlplus

1. The simplest way.

Sql> set Autotrace on

Sql> SELECT * from dual;

After the statement is executed, the explain plan and statistics are displayed.

The advantage of this statement is its disadvantage, so that when you use this method to view a long-executing SQL statement, you need to wait for the statement to execute successfully before returning to the execution plan, which greatly increases the period of optimization. If you do not want to execute the statement and just want to get an execution plan can take:

Sql> Set Autotrace traceonly

This will only list the execution plan and not the actual execution of the statement, greatly reducing the tuning time. Although statistics are also listed, this statistic is useless because there is no execution of the statement, and if you encounter an error when executing the statement, the workaround is:

(1) Under the user to be analyzed:

Sqlplus > @?

Dbmsadminutlxplan.sql

(2) Login with SYS user

Sqlplus > @ sqlplusadminplustrce.sql

Sqlplus > Grant plustrace to user_name;

--User_name is the analysis user described above

　2 . With the explain plan command

(1) sqlplus > Explain plan for select * from Testdb.myuser

(2) sqlplus > select * from table (dbms_xplan.display);

The above 2 methods can only generate execution plans for statements that are running in this session, that is, we need to know which statement runs poorly, and we have a purpose to optimize this SQL statement only. In fact, in many cases, we will only listen to a customer complaining that the system is running slowly, and we do not know which SQL is causing it. There are many out-of-the-box statements that can be used to find more expensive statements, such as:

SELECT ADDRESS, substr (sql_text,1,20) TEXT, buffer_gets, executions,

Buffer_gets/executions AVG from V$sqlarea

WHERE executions>0 and buffer_gets > 100000 ORDER by 5;

ADDRESS TEXT buffer_gets Executions AVG

-------- ---------------------------------------- ----------- ---------- ------------------------------------------ ------------------

66D83D64 Select T.name, (sel 421531 60104 7.01336017

66D9E8AC Select T.schema, T.N 1141739 2732 417.913250

66B82BCC Select S.synonym_nam 441261 6 73543.5

The resulting statements are further optimized. Of course we can also generate execution plans for all of the SQL statements running in a running session, which requires tracing the session, producing a trace file, and then formatting the file with the TKPROF program, which is useful in the way it is executed, because it contains additional information, The individual resource conditions (such as CPU, DISK, elapsed, and so on) that are consumed by each phase of SQL statement execution (such as Parse, execute, Fetch).

3 , enable Sql_trace track all background process activity:

Global parameter settings:. specified in Oraclehome/admin/sid/pfile: Sql_trace = True (10g)

Settings in the current session:

Sql> alter session set Sql_trace=true;

Sql> SELECT * from dual;

Sql> alter session set Sql_trace=false;

To track settings for other users:

Sql> Select Sid,serial#,username from v$session where username= ' XXX ';

SID serial# USERNAME

------ ---------- ------------------

127 31923 A

54521 B

Turn on trace:sql> exec dbms_system.set_sql_trace_in_session (127,31923,true);

Close Trace:sql> exec dbms_system.set_sql_trace_in_session (127,31923,false);

The trace file is then formatted using the TKPROF command line tool that comes with Oracle.

4 , use the 10046 event to query:

10046 Event Level:

LV1-Enable the standard Sql_trace function, equivalent to Sql_trace

Lv4-level 1 + bound value (bind values)

Lv8-level 1 + Wait for event tracking

Lv12-level 1 + Level 4 + Level 8

Global Settings:

specified in Oraclehome/admin/sid/pfile: event= "10046 Trace name context forever,level 12"

Current session settings:

Open:sql> alter session set events ' 10046 Trace name Context forever, Level 8 ';

Close:sql> alter session set events ' 10046 Trace name context off ';

To set up other users:

Sql> Select Sid,serial#,username from v$session where username= ' XXX ';

SID serial# USERNAME

------ ---------- ------------------

127 31923 A

sql> exec Dbms_system.set_ev (127,31923,10046,8, ' A ');

5, using TKPROF format tracking file: (according to the following SQL statement to get the file does not exist in the directory, depressed ah, ignorant ah ...)

In general, a single trace can be divided into the following steps:

1. Define the range of targets that need to be tracked and enable the required traces using the appropriate commands.

2, after a period of time, stop tracking. A trace result file should be produced at this time.

3. Locate the trace file and format it, then read or analyze it.

--Use SQL to find the trace file for the current session:

SELECTD.value|| '/' | | Lower (RTrim (i.instance, Chr (0))) | | ' _ora_ ' | | p.spid| | '. TRC ' Trace_file_name
from
(SelectP.spid fromV$mystat m,v$session S, v$process p
wherem.statistic# = 1 andS.sid = M.sid andp.addr = s.paddr) p,
(SelectT.instance fromV$thread T,v$parameter V
whereV.name= ' thread ' and(v.value= 0orT.Thread# = To_number (v.value))) I,
(Select value fromV$parameterwhere name= ' user_dump_dest ') D;
-- other users 'Session
SELECTD.value|| '/' | | Lower (RTrim (i.instance, Chr (0))) | | ' _ora_ ' | | p.spid| | '. TRC ' Trace_file_name
from
(SelectP.spid fromV$session s, v$process p
whereS.sid= ' 27 ' andS. serial#= ' 30 ' andp.addr = s.paddr) p,
(SelectT.instance fromV$thread T,v$parameter V
whereV.name= ' thread ' and(v.value= 0orT.Thread# = To_number (v.value))) I,
(Select value fromV$parameterwhere name= ' user_dump_dest ') D;

--Use the TKPROF command after lookup to format the trace file to the Explain_format.txt file in the D drive

SQL> $tkprof d:/oracle/admin/fzlgfm/udump/fzlgfm_ora_3468.trc d:/explain_format.txt

The contents of the document are as follows (not quite understand ...). Ignorant ah ..... Oh, my God.... Oh, God ..... I'll know when I'm through./////////////)

Tkprof:release 9.2.0.1.0-production on Tuesday April 20 13:59:20 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace FILE:D:/ORACLE/ADMIN/FZLGFM/UDUMP/FZLGFM_ORA_3468.TRC

Sort Options:default

********************************************************************************

Count = number of times OCI procedure was executed

CPU = CPU time in seconds executing

elapsed = elapsed time in seconds executing

Disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

Current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call
********************************************************************************

Alter session SET Events ' 10046 Trace name Context forever, Level 8 '

Call count CPU Elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 0 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse:0

Misses in library cache during execute:1

Optimizer Goal:choose

Parsing user Id:sys

Oracle Learning Note (iii)----------execution plan