次のようにオブジェクトを作ります。
UKJA@ukja1106> create table t1(c1 int, c2 varchar2(10));
Table created.
UKJA@ukja1106> insert into t1
2 select 1, level from dual connect by level <= 10000;
10000 rows created.
UKJA@ukja1106>
UKJA@ukja1106> insert into t1
2 select level, level from dual connect by level <= 10000;
10000 rows created.
UKJA@ukja1106>
UKJA@ukja1106> create index t1_n1 on t1(c1);
Index created.
UKJA@ukja1106>
UKJA@ukja1106> exec dbms_stats.gather_table_stats(user, 't1', -
> method_opt=>'for columns c1 size skewonly');
PL/SQL procedure successfully completed.
コラムC1はばらつきがあってヒストグラムが存在します。統計情報はつぎのようです。Height-Balancedヒストグラムが生成されているのが分かります。
UKJA@ukja1106> @tab_stat t1
UKJA@ukja1106> set echo off
01. table stats
old 9: table_name = upper(''&T_NAME'')
new 9: table_name = upper(''t1'')
TABLE_NAME : T1
PARTITION_NAME :
NUM_ROWS : 20000
BLOCKS : 42
SAMPLE_SIZE : 20000
LAST_ANAL : 2009/12/07 13:41:08
-----------------
PL/SQL procedure successfully completed.
02. column stats
old 9: s.table_name = upper(''&T_NAME'')
new 9: s.table_name = upper(''t1'')
TABLE_NAME : T1
COLUMN_NAME : C1
NUM_DISTINCT : 10000
NUM_NULLS : 0
DENSITY : .00005
LOW_VALUE : C102
HIGH_VALUE : C302
HISTOGRAM : HEIGHT BALANCED
-----------------
TABLE_NAME : T1
COLUMN_NAME : C2
NUM_DISTINCT :
NUM_NULLS :
DENSITY :
LOW_VALUE :
HIGH_VALUE :
HISTOGRAM : NONE
-----------------
PL/SQL procedure successfully completed.
03. histogram stats
old 7: table_name = upper('&T_NAME')
new 7: table_name = upper('t1')
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------------
T1 C1 126 1()
T1 C1 127 33()
T1 C1 128 112()
...
T1 C1 254 10000()
129 rows selected.
コラムC1に”１”を代入したらバインドピーキングによって全表走査を選択するようになります。
UKJA@ukja1106> var b1 number;
UKJA@ukja1106> exec :b1 := 1;
UKJA@ukja1106> var b2 varchar2(10);
UKJA@ukja1106> exec :b2 := '1';
UKJA@ukja1106>
UKJA@ukja1106> select /*+ gather_plan_statistics */
2 count(*) from t1
3 where c1 = :b1 and c2 = :b2;
COUNT(*)
----------
2
--------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 99 | 2 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C2"=:B2)
3 - access("C1"=:B1)
今度はSQL*Traceを活性化します。そしてコラムC1に1ではなくて2を代入します。
UKJA@ukja1106> alter session set sql_trace = true;
Session altered.
UKJA@ukja1106> var b1 number;
UKJA@ukja1106> exec :b1 := 2;
PL/SQL procedure successfully completed.
UKJA@ukja1106> var b2 varchar2(10);
UKJA@ukja1106> exec :b2 := '1';
PL/SQL procedure successfully completed.
UKJA@ukja1106>
UKJA@ukja1106> select /*+ gather_plan_statistics */
2 count(*) from t1
3 where c1 = :b1 and c2 = :b2;
COUNT(*)
----------
0
-------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 0 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 1 | 1 | 1 |
-------------------------------------------------------------------------
実行計画が変わりました。何かおかしいではありませんか。最初の実行でバインドピーキングがもうなされたので次の同一なテキストのクエリは同一な実行計画を見せなければなりません。
もっと詳細な分析をしてみましょう。まず現在登録されている息子LCOたちとその実行計画を見ます。
UKJA@ukja1106> select * from table(dbms_xplan.display_cursor('&sql_id', null, 'typical'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id', null, 'typical'))
new 1: select * from table(dbms_xplan.display_cursor('98721ruagfx5c', null, 'typical'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 98721ruagfx5c, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where c1 = :b1
and c2 = :b2
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| T1 | 99 | 792 | 18 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("C2"=:B2 AND "C1"=:B1))
SQL_ID 98721ruagfx5c, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where c1 = :b1
and c2 = :b2
Plan hash value: 359681750
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 8 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_N1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C2"=:B2)
3 - access("C1"=:B1)
確かに二つの実行計画が存在します。これが意味するのはSQLトレースが活性化されるとオラクルは同一なSQL文章だといっても他のSQL文で認識するということを意味します。その理由をV$SQL_SHARED_CURSORビューを通じて見つけられます。私が作成した簡単なスクリプトでV$SQL_SHARED_CURSORビューを検索します。
UKJA@ukja1106> @shared_cursor2 &sql_id
UKJA@ukja1106> set echo off
old 14: and s.sql_id = ''&1''',
new 14: and s.sql_id = ''98721ruagfx5c''',
SQL_TEXT = select /*+ gather_plan_statistics */ count(*) from t1 where c1 = :b1 and c2 = :b2
SQL_ID = 98721ruagfx5c
ADDRESS = 29D81B30
CHILD_ADDRESS = 2879E2BC
CHILD_NUMBER = 0
--------------------------------------------------
SQL_TEXT = select /*+ gather_plan_statistics */ count(*) from t1 where c1 = :b1 and c2 = :b2
SQL_ID = 98721ruagfx5c
ADDRESS = 29D81B30
CHILD_ADDRESS = 2F5E79DC
CHILD_NUMBER = 1
STATS_ROW_MISMATCH = Y
--------------------------------------------------
すなわち、SQLトレースが活性化されるとSTATS_ROW_MISMATCHによってSQL文章を共有できずに新しいチャイルドカーソルを作るようになります。その過程で再びバインドピーキングがなされます。
SQLトレースの結果だけを見てせっかちにチューニングをするのはあぶないという恐れが生じませんか。
Oracle 11gの基準で同一なSQL文章が共有されないにはなんと60余種類の理由があります。
UKJA@ukja1106> desc v$sql_shared_cursor
Name
---------------------------------------------------
1 SQL_ID
2 ADDRESS
3 CHILD_ADDRESS
4 CHILD_NUMBER
5 UNBOUND_CURSOR
6 SQL_TYPE_MISMATCH
7 OPTIMIZER_MISMATCH
8 OUTLINE_MISMATCH
9 STATS_ROW_MISMATCH
10 LITERAL_MISMATCH
11 FORCE_HARD_PARSE
12 EXPLAIN_PLAN_CURSOR
13 BUFFERED_DML_MISMATCH
14 PDML_ENV_MISMATCH
15 INST_DRTLD_MISMATCH
16 SLAVE_QC_MISMATCH
17 TYPECHECK_MISMATCH
18 AUTH_CHECK_MISMATCH
19 BIND_MISMATCH
20 DESCRIBE_MISMATCH
21 LANGUAGE_MISMATCH
22 TRANSLATION_MISMATCH
23 ROW_LEVEL_SEC_MISMATCH
24 INSUFF_PRIVS
25 INSUFF_PRIVS_REM
26 REMOTE_TRANS_MISMATCH
27 LOGMINER_SESSION_MISMATCH
28 INCOMP_LTRL_MISMATCH
29 OVERLAP_TIME_MISMATCH
30 EDITION_MISMATCH
31 MV_QUERY_GEN_MISMATCH
32 USER_BIND_PEEK_MISMATCH
33 TYPCHK_DEP_MISMATCH
34 NO_TRIGGER_MISMATCH
35 FLASHBACK_CURSOR
36 ANYDATA_TRANSFORMATION
37 INCOMPLETE_CURSOR
38 TOP_LEVEL_RPI_CURSOR
39 DIFFERENT_LONG_LENGTH
40 LOGICAL_STANDBY_APPLY
41 DIFF_CALL_DURN
42 BIND_UACS_DIFF
43 PLSQL_CMP_SWITCHS_DIFF
44 CURSOR_PARTS_MISMATCH
45 STB_OBJECT_MISMATCH
46 CROSSEDITION_TRIGGER_MISMATCH
47 PQ_SLAVE_MISMATCH
48 TOP_LEVEL_DDL_MISMATCH
49 MULTI_PX_MISMATCH
50 BIND_PEEKED_PQ_MISMATCH
51 MV_REWRITE_MISMATCH
52 ROLL_INVALID_MISMATCH
53 OPTIMIZER_MODE_MISMATCH
54 PX_MISMATCH
55 MV_STALEOBJ_MISMATCH
56 FLASHBACK_TABLE_MISMATCH
57 LITREP_COMP_MISMATCH
58 PLSQL_DEBUG
59 LOAD_OPTIMIZER_STATS
60 ACL_MISMATCH
61 FLASHBACK_ARCHIVE_MISMATCH
62 LOCK_USER_SCHEMA_FAILED
63 REMOTE_MAPPING_MISMATCH
64 LOAD_RUNTIME_HEAP_FAILED
こんなに多い理由が存在するので同一なSQL文章が複数の実行計画を見せる時必ずこのビューを検索しなければならないんでしょうね。
Posts
No comments:
Post a Comment