■イベント－－10046
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
LEVEL 1 SQL_TRACE機能と同等
LEVEL 4 LEVEL 1の情報に追加して、バインド変数情報が出力される
LEVEL 8 LEVEL 1の情報に追加して、待機イベント情報が出力される
LEVEL 12 LEVEL 1の情報に追加して、バインド変数情報、待機イベント情報が出力される
パラメータ：SID、SERIAL#、トレース開始/停止
１．SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(10,12,TRUE);
２．SQL> EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(10,12,FALSE);
別セッションに対して、EVENT 10046のSQLトレースを設定する場合には、
手順の3.と4.で以下のプロシージャを実行します。
イベント開始
３．SQL> EXECUTE DBMS_SYSTEM.SET_EV(10,12,10046,12,'');
イベント停止
４．SQL> EXECUTE DBMS_SYSTEM.SET_EV(10,12,10046,0,'');
■イベント－－10053
SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
LEVTL : 1 OR 2 (1 is more detailly)
【FOR TABLES:】
--------------------------------------------------------------------------------------------
Trace label dba_tables column
--------------------------------------------------------------------------------------------
CDN NUM_ROWS The cardinality = number of rows of the table
NBLKS BLOCKS The number of blocks below the high water mark
TABLE_SCAN_CST The estimated cost in I/O to full-table-scan the table
AVG_ROW_LEN AVG_ROW_LEN The average length of a row
【FOR INDEXES:】
-------------------------------------------------------------------------------------------
Trace label dba_indexes column
--------------------------------------------------------------------------------------------
Index#, col# The object# of the index and the column_id of the columns.
Oracle 9 brings an improvement by using the index name
rather than index#
LVLS BLEVEL The height of the index b-tree
#LB LEAF_BLOCKS The number of leaf blocks
#DK DISTINCT_KEYS The number of distinct keys of the index
LB/K AVG_LEAF_BLOCKS_PER_KEY The average number of leaf blocks per key
DB/K AVG_DATA_BLOCKS_PER_KEY The average number of data blocks per key
CLUF CLUSTERING_FACTOR The clustering factor of the index
【BASE ACCESS PLANS】
--------------------------------------------------------------------------------------------
This is a list of the basic plans to access a single table or, more accurately,
a row source. I have practically only seen plans 2, 3, 4, 5, and 23
--------------------------------------------------------------------------------------------
0 parallel hint | 11 rowid range scan | 22 noparallel_index hint
1 no access path spec | 12 driving_site hint | 23 index fast full scan
2 table scan | 13 | 24 swap inputs to join
3 index unique | 14 cache hint | 25 fact table
4 index range | 15 nocache hint | 26 not a fact table
5 index and-equal | 16 partitions hint | 27 merge of this view
6 order by using an index | 17 nopartitions hint | 28 don't push join predicate into this view
7 open cluster | 18 anti-join | 29 push join predicate into this view
8 hash cluster | 19 index rowid range scan | 30 no_merge of this view
9 rowid lookup | 20 bitmap index | 31 semi-join
10 range scan backwards | 21 parallel_index hint |
【SINGLE TABLE ACCESS PATH】
--------------------------------------------------------------------------------------------
Trace label dba_tables column
--------------------------------------------------------------------------------------------
NDV NUM_DISTINCT Number of distinct values for the column
NULLS NUM_NULLS Number of rows with a null “value” for the column
DENS DENSITY “Density” of the column. Without histogram this is = 1/NDV
LO LOW_VALUE The lowest value for the column (only for numeric columns)
HI HIGH_VALUE The highest value for the column (only for numeric columns)
CMPTD CDN = ORIG CDN * FF (FF:Filter Factor)
D_F_M_R_C = DB_FILE_MULTI_BLOCK_READ_COUNT
TSC = TABLE_SCAN_COST
( TSC != NBLKS / D_F_M_R_C )
( TSC = NBLKS / k ) k = = 1.6765x0.65810
FF = 1/NDV = Density
Density : 密度, 濃度, 比重
Predicate : 【文法】述語（の）, 述部（の）;
Ramification : 分枝, 分岐; （普通pl.） 支脈, 分派;
【WITHOUT BIND VARIABLES:】
--------------------------------------------------------------------------------------------
Predicate Filter Factor
--------------------------------------------------------------------------------------------
c1 = value c1.density
c1 like value c1.density
c1 > value (Hi - value) / (Hi - Lo)
c1 >= value (Hi - value) / (Hi - Lo) + 1/c1.num_distinct
c1 < value (value - Lo) / (Hi - Lo)
c1 <= value (value - Lo) / (Hi - Lo) + 1/c1.num_distinct
c1 between val1 and val2 (val2 - val1) / (Hi - Lo) + 2 * 1/c1.num_distinct
WHEN C1 IS NULL :
c1.density * ( 1 - num_nulls/num_rows)
【WHEN USING BIND VARIABLES:】
--------------------------------------------------------------------------------------------
predicate Filter factor
--------------------------------------------------------------------------------------------
c1 = :b1 c1.density
c1 {like | > | >= | < | <=} :b1 {5.0000e-02 | c1.density }5
c1 between :b1 and :b2 5.0000e-02 * 5.0000e-02
【COMBINING PREDICATES / FILTER FACTORS:】
--------------------------------------------------------------------------------------------
predicate Filter factor
--------------------------------------------------------------------------------------------
predicate 1 and predicate 2 FF1 * FF2
predicate 1 or predicate 2 FF1 + FF2 - FF1 * FF2
For the calculation of the filter factors for ranges of string literals,
the value of the literal is the weighted sum of the ASCII values of its
characters. Strings of different lengths are “right padded” with zeros:
ADAMS = 65*256@4 + 68*256@3 + 65*256@2 + 77*256@1 + 83*256@0 = 2.8032e+11
ward = 119*256@4 + 97*256@3 + 114*256@2 + 100*256@1 + 0*256@0 = 5.1274e+11
James = 74*256@4 + 97*256@3 + 109*256@2 + 101*256@1 + 115*256@0 = 3.1946e+11
【INDEX ACCESS COSTS】
COST ：blevel + FF*leaf_blocks + FF*clustering_factor
0 + 1/362 * 1 + 1/362 * 76 = 2?
The formula essentially equates to the count of blocks that Oracle has to traverse in order to get all qualifying rows:
From the root page follow the tree down to the leaf page blevel blocks
Access all qualifying leaf blocks leaf blocks * filter factor
Access all qualifying data blocks clustering factor * filter factor
The cost calculations for other index access methods are:
Unique scan blevel+1
Fast full scan leaf_blocks / k ( k = 1.6765x0.6581 )
Index-only blevel + FF*leaf_blocks
■イベント－－？？？
SQL>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME BUFFERS LEVEL6';
-- レベル6だと全バッファ、レベル4だとなに？
DUMPによるデータBLOCKチェック方法（BLOCK内データを確認可能）
SELECT EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = '&EXTENT_NAME'
/
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
----------- --------- --------- ------
0 9 209 87
最初のBLOCK_IDはBLOCK HEADERであるため、次のBLOCK_IDから取得する。
SQL> alter system dump datafile 9 block 210;
■イベント－－6502
event="6502 trace name errorstack level 12"
0 件のコメント:
コメントを投稿