꿈꾸는 개발자, DBA 커뮤니티 구루비

XPLAN

Download : http://www.adellera.it/scripts_etcetera/xplan/index.html
XPLAN 유틸리티는 DBMS_XPLAN 패키지의 확장판이라고 보시면 된다.
SQL 문장의 실행 계획과 관련된 모든 정보를 일목 요연하게 리포팅 해 줌

예제 )

1. T1테이블, T1N1인덱스 생성


create table t1(c1 number, c2 char(100));
insert into t1
select level, level from dual connect by level < = 100000;
create index t1_n1 on t1(c1);
exec dbms_stats.gather_table_stats(user, 't1');

2. 바인드변수 B1을 선언


var b1 number;
exec :b1 :=1;

3. OPTIMIZER_INDEX_COST_ADJ 파라미터값을 50으로 변경

XPLAN 유틸리티에서 옵티마이저와 관련된 정보가 어떻게 리포팅되는지 확인


alter sesson set optimizer_index_cost_adj = 50;

4. 아래의 쿼리를 여러번에 걸쳐서 수행


alter system flush buffer_cache;

select /*+ gather_plan_statistics index(t1) opt_parm('_hash_join_enabled', 'false') */ count (c2)
from t1
where c1>= :b1 ;

/
/
/
/
...

5. 위에서 수행한 SQL 문장의 SQL_ID를 알아낸다.

V$SESSION뷰의 PREV_SQL_ID 값을 읽으면 바로 직전에 수행한 SQL문장의 SQL_ID를 알아낼 수 있다.


select
prev_sql_id
from v$session
where sid = userenv('sid');

6. 위에서 얻은 SQL_ID를 이용해 아래와 같이 XPLAN 유틸리티를 수행한다.


@xplan "" "sql_id=&prev_sql_id"

결과



--기본요약 정보가 출력
xplan version 2.4.2 01-May-2011 (C) Copyright 2008-2009 Alberto Dell'Era, www.adellera.it
db_name=BWD instance_name=BWD version=10.2.0.4.0 (compatible = 10.2.0)


--시스템 레벨의 옵티마이저 파라미터값( V$SYS_OPTIMIZER_ENV 뷰 )
optimizer parameters instance(sys) settings:
------------------------------------------ ------------------------------------------ ------------------------------------------
|optimizer param name         |value     | |optimizer param name          |value    | |optimizer param name        |value      |
------------------------------------------ ------------------------------------------ ------------------------------------------
|_B_TREE_BITMAP_PLANS         |    false | |optimizer_features_enable     |10.2.0.4 | |pga_aggregate_target        |1481038 KB |
|_OPTIM_PEEK_USER_BINDS       |    false | |optimizer_index_caching       |       0 | |QUERY_REWRITE_ENABLED       |     false |
|_PGA_MAX_SIZE                |296200 KB | |optimizer_index_cost_adj      |     100 | |query_rewrite_integrity     |  enforced |
|_SORT_ELIMINATION_COST_RATIO |       10 | |OPTIMIZER_MODE                |  choose | |skip_unusable_indexes       |      true |
|active_instance_count        |        1 | |optimizer_secure_view_merging |    true | |sort_area_retained_size     |         0 |
|BITMAP_MERGE_AREA_SIZE       | 33554432 | |parallel_ddl_mode             | enabled | |SORT_AREA_SIZE              |  45485760 |
|cpu_count                    |        6 | |parallel_dml_mode             |disabled | |STAR_TRANSFORMATION_ENABLED |      true |
|cursor_sharing               |    exact | |parallel_execution_enabled    |    true | |statistics_level            |   typical |
|HASH_AREA_SIZE               | 20971520 | |parallel_query_mode           | enabled | |workarea_size_policy        |      auto |
|optimizer_dynamic_sampling   |        2 | |PARALLEL_THREADS_PER_CPU      |       1 | ------------------------------------------


--시스템 통계정보를 보여준다. ( SYS.AUX_STATS$ 테이블 )
optimizer system statistics:
---------------------------------------- -------------------------- --------------------------
|system statistic |value               | |system statistic |value | |system statistic |value |
---------------------------------------- -------------------------- --------------------------
|status           |          completed | |cpuspeednw       |  827 | |ioseektim        |   10 |
|gathering start  |2011-01-08/13:25:00 | |sreadtim         | null | |iotfrspeed       | 4096 |
|gathering stop   |2011-01-08/13:25:00 | |mreadtim         | null | |maxthr           | null |
|cpuspeed         |               null | |mbrc             | null | |slavethr         | null |
---------------------------------------- -------------------------- --------------------------


--SQL레벨의 기본 실행 정보들, 즉 실행 횟수, Logical Reads, Physical Reads, 수행시간등의 정도( V$SQL 뷰 )
sql_id=dwm98gygzfk9b hash=2683783467 child_number=0 plan_hash=3892869488 module=sqlplus@r3dev (TNS V1-V3)
first_load: 2012/05/11 17:10:38 last_load: 2012/05/11 17:10:38 last_active: 2012/05/11 17:10:44
parsed_by=SYS inst_id=1
------------------------------------ -------------------------------- -----------------------------------------
|gv$sql statname |total  |/exec    | |gv$sql statname |total |/exec | |gv$sql statname         |total |/exec  |
------------------------------------ -------------------------------- -----------------------------------------
|executions      |     6 |         | |sorts           |    0 |   .0 | |users_executing         |    0 |    .0 |
|rows_processed  |     6 |     1.0 | |fetches         |    6 |  1.0 | |application wait (usec) |    0 |    .0 |
|buffer_gets     | 10560 |  1760.0 | |end_of_fetch_c  |    6 |  1.0 | |concurrency wait (usec) |    0 |    .0 |
|disk_reads      |  1760 |   293.3 | |parse_calls     |    6 |  1.0 | |cluster     wait (usec) |    0 |    .0 |
|direct_writes   |     0 |      .0 | |sharable_mem    |18769 |      | |user io     wait (usec) |15969 |2661.5 |
|elapsed (usec)  |702820 |117136.7 | |persistent_mem  | 4736 |      | |plsql exec  wait (usec) |    0 |    .0 |
|cpu_time (usec) |695806 |115967.7 | |runtime_mem     | 3552 |      | |java  exec  wait (usec) |    0 |    .0 |
------------------------------------ -------------------------------- -----------------------------------------


--SQL 실행 계획 정보( V$SQL_PLAN_STATISTICS 뷰)
select /*+ gather_plan_statistics index(t1) opt_parm('_hash_join_enabled', 'false') */ count (c2)
from t1
where c1>=:b1
-------------------------------------------------------------------------
|Id|Operation                    |Name |TabName|Erows|Arows |Cost|IoCost|
------------------------------------------------------last---------------
| 0|SELECT STATEMENT             |     |       |     |      |  17|      |
| 1| SORT AGGREGATE              |     |       |    1|     1|    |      |
| 2|  TABLE ACCESS BY INDEX ROWID|T1   |T1     | 4999|100000|  17|    17|
| 3|   INDEX RANGE SCAN          |T1_N1|T1     |  900|100000|   3|     3|
-------------------------------------------------------------------------
.   3 - access[ "C1">=:B1 ]


--각 실행 단계별 Consistent Read, Current Read, Direct Read, Direct Write, Elapsed Time 의 정보
--------------------------------------
|Id|Starts|CR  |CU  |DR  |DW  |Ela   |
----last---last-last-last-last-last---
| 0|      |    |    |    |    |      |
| 1|     1|1760|   0|   0|   0|110395|
| 2|     1|1760|   0|   0|   0|100057|
| 3|     1| 223|   0|   0|   0|    43|
-------------------------------usec---
note: stats Aram, A01M, 0/1/M, ActTim do not seem to be always accurate.
all params in gv$sql_optimizer_env are the same as instance ones.


--대기 이벤트 정보(V$ACTIVE_SESSION_HISTORY)
--액티브 세션 히스토리 기능을 이용하면 SQL 트레이스를 수행하지 않아도 대기 이벤트 정보를 어느 정도 분석할 수 있다.(앞에서 설명)
-------------------
|ash event|cnt|%  |
-------------------
|cpu      |  1|100|
-------------------


--현재 SQL 문장에서 변경된 옵티마이저 파라미터가 있는 경우, 해당 파라미터의 값을 보여준다.(V$SQL_OPTIMIZER_ENV 뷰)


--쿼리에서 사용되는 테이블과 인덱스의 정의와 통계 정보( DBA_TAB_STATISTICS뷰, DBA_TAB_COL_STATISTICS 뷰, DBA_IND_STATISTICS 뷰 )
############################################# table SYS.T1 ###
-----------------------------------------
|Id|IId|V|ColName|Type           |Null|1|
-----------------------------------------
| 1|  1|N|C1     |NUMBER         |yes |1|
| 2|  2|N|C2     |CHAR (100 byte)|yes | |
-----------------------------------------

-------------------------------------------------------------------------------------
|num_rows|blocks|empty_blocks|avg_row_len|sample_size|last_analyzed      |parallel  |
-------------------------------------------------------------------------------------
|   99984|  1541|           0|        105|      24996|2012/05/11 15:34:39|         1|
-------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
|ColName|ndv  |dens*#rows|num_nulls|#bkts|avg_col_len|sample_size|last_analyzed      |
--------------------------------------------------------------------------------------
|C1     |99984|         1|        0|    1|          5|      24996|2012/05/11 15:34:39|
|C2     |99984|         1|        0|    1|        101|      24996|2012/05/11 15:34:39|
--------------------------------------------------------------------------------------

### index #1: SYS.T1_N1
on SYS.T1 ( C1 )
NONUNIQUE B+TREE
-----------------------------------------------------------------------------------------
|distinct_keys|num_rows|blevel|leaf_blocks|cluf|sample_size|last_analyzed      |parallel|
-----------------------------------------------------------------------------------------
|       100000|  100000|     1|        222|1537|     100000|2012/05/11 15:34:40|1       |
-----------------------------------------------------------------------------------------
OPTIONS: inst_id=1 plan_stats=last access_predicates=Y lines=200 ash_profile_mins=15 module= action= hash= sql_id=dwm98gygzfk9b parsed_by= child_number= dbms_xplan=N dbms_metadata=N plan_details=N
plan_env=Y tabinfos=Y objinfos=Y partinfos=Y order_by= spool_name=xplan_dwm98gygzfk9b_i1.lst spool_files=single
SQL_LIKE=""

-- Warning: since ash_profile_mins > 0, you are using ASH/AWR; make sure you are licensed to use it.

위에서 제공된 정보를 보면 SQL 문장의 실행 계획 분석에 있어서 필수적인 데이터들을 매우 일목요연하게 보여주는 것을 알 수 있다.
위의 정보들을 미리 입수한 후에 SQL 문장의 최적화 작업을 수행한다면 보다 체계적인 작업이 가능할 것이다.