Interpreting Execution Plans1. :

1. ;2. ;3. ;

2. :1. SQL ;2. , ;

3. :1. PLAN_TABLE: EXPLAIN PLAN SQL/PLUS autotrace , ;2. v$sql_plan: Shared Pool Library Cache ;3. v$sql_plan_monitor:11g ;4. dba_hist_sql_plan: AWR ;5. stats$sql_plan: Statspack ;6. SQL Management Base: SQL Plan Management Baselines ;7. SQL tuning set;8. DBMS_MONITOR trace : 10046 ;9. 10053 trace ;

10. 10gR2 dump ;4. :

1. , , SQL , DBMS_XPLAN;

2. DBMS_XPLAN.DISPLAY(): plan_table ;3. DBMS_XPLAN.DISPLAY_CURSOR(): v$sql_plan ;4. DBMS_XPLAN.DISPLAY_AWR(): AWR ;5. DBMS_XPLAN.DISPLAY_SQLSET(): SQL tuning set ;6. DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(): SQL Plan Management Baselines

;5. EXPLAIN TABLE :

1. , PLAN_TABLE , SQL ;2. :EXPLAIN PLAN [SET STATEMENT_ID = 'text'] [INTO plan_table] FOR statement;

PLAN_TABLE ;3. PLAN_TABLE:

1. EXPLAN_PLAN PLAN_TABLE, , sys.plan_table$;SELECT * FROM dba_synonyms WHERE synonym_name = 'PLAN_TABLE';SELECT

table_name, TEMPORARY, duration FROM dba_tables WHERE table_name = 'PLAN_TABLE$';

2. $ORACLE_HOME/rdbms/admin/utlxplan.sql , ,session , ;

3. SQL ; , ,;

4. , ID PAREANT_ID ;4. DBMS_XPLAN.DISPLAY :DBMS_XPLAN.DISPLAY(table_name, statement_id, format,

filter_preds):1. table_name: PLAN_TABLE ;2. statement_id: , ;3. format: TYPICAL , , ;4. ;

5. statement_id ;

6. ;

6. AUTOTRACE:1. AUTOTRACE sql*plus , oracle7.3 , PLAN_TABLE ;2. PLUSTRACE v$ , $ORACLE_HOME/sqlplus/admin/plustrce.sql

;3. , ;4. EXPLAIN PLAN , ;5. :SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]];

1. ON: trace ;2. TRACEONLY: ;

6. :show autotrace;7. :

1. recursive calls: , , , . , ;, , ; shared_pool :alter system flush

shared_pool;2. db block gets: block . DML db block gets ,

,SELECT , REDO CR ;3. consistent gets: ( BLOCK), , arraysize ;

1. arraysize: , show arraysize ;2. :consistent gets=rows processed/arraysize, ;3. arraysize , ;

4. physical reads: , BLOCK ,BUFFER CACHE , BUFFER CACHE :alter system flush buffer_cache;

5. redo size: , DML ;6. bytes sent via SQL*Net to client: ;7. bytes received via SQL*Net from client: ;8. SQL*Net roundtrips to/from client:SQL , arraysize ;9. sorts (memory): , PGA;

10. sorts (disk): , ;11. rows processed: ;

8. v$sql_plan:1. v$sql_plan: library cahce ;PLAN_TABLE ;2. sql_id v$sql , address hash_value ;3. :

1. HASH_VALUE: library cache ;2. ADDRESS: SQL , ;3. CHILD_NUMBER: CURSOR ;4. POSITION: PARENT_ID ;5. PARENT_ID: ID, , , ;6. ID: ;7. PLAN_HASH_VALUE: ;

4. :SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('sql_id'));5. v$sql_plan_statistics:

1. STATISTICS_LEVEL ALL ;2. GATHER_PLAN_STATISTICS hint;3. v$sql_plan_statistics_all: ;

6. v$sql_workarea: SQL CURSOR ;

9. AWR:1. AWR , , ;2. :

1. ;2. ;3. session ;4. ASH(Active Session History) ;

3. ;4. AWR :

1. V$ACTIVE_SESSION_HISTORY;2. V$metric views;3. DBA_HIST views:

1. DBA_HIST_ACTIVE_SESS_HISTORY;2. DBA_HIST_BASELINE;3. DBA_HIST_DATABASE_INSTANCE;4. DBA_HIST_SNAPSHOT;5. DBA_HIST_SQL_PLAN;6. DBA_HIST_WR_CONTROL;

5. sql_id AWR sql : SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('g22czkqq3pxmb'));

6. AWR SQL :@$ORACLE_HOME/rdbms/admin/awrsqrpt;10. SQL Monitoring:11g;11. :

1. :1. , ;2. ;3. ;

2. :1. : , , , ;2. : , , , ;3. : , , , ;

3. :1. :356421;

2. :43652871;

3. :325410;

4. :1. , ;2. ;3. , , ;4. ;

12. , SQL Tuning Advisor ;