Exadata의 Smart scan 기능 중 Storage Index 사용 확인 방법

Exadata의 Smart Scan 중 주요 기능인 Storage index는 

disk내 데이터를 1M 단위로 쪼개서 해당 block 내의 데이터의 min, max, NULL 값 존재 여부를 

메모리에 기억하여 해당 block을 읽기 전 읽을 필요가 있는지를 판단하게 됩니다. 

또한 Block 내의 데이터 중 column 8개에 대해서만 이러한 summary data를 갖게 됩니다. 

이러한 operation은 내부적으로 진행되고 어떤 column에 대해 summary를 갖을지 

사용자는 control 할 필요가 없습니다. 

그러나 성능 문제가 발생하게 된다면 사용자가 예상하는 column의 정보를 갖는지 확인이 필요한데 

SQL이나 cellcli에서 해당 내용을 판단할 수 있는 방법은 아직 없습니다. 

따라서 이에 대한 확인을 하려면 아래와 같이 trace를 생성해서 확인해야 합니다.

Storage index에 대한 trace 수행 방법과 trace file에 대해 간단히 내용입니다.  

1. Storage index trace 걸기 

SQL>alter system set "_kcfis_storageidx_diag_mode"=2 scope=memory;

SQL> select ...

2. Storage index trace 제거 

SQL> alter system set "_kcfis_storageidx_diag_mode"=0 scope=memory;

3. Storage index trace 

..

2014-11-17 17:18:32.419680*: RIDX (0x7c40f0) for SQLID 6hv5zxwck5dd4 filter 1

--> Storage index(0x7c40f0)를 사용한 SQL ID

2014-11-17 17:18:32.419680*: RIDX (0x7c40f0) : st 2 validBitMap 0 tabn 0 id {75884 7 2415342635}  

--> object number : tablespace number, DB id 

2014-11-17 17:18:32.419680*: RIDX: strt 32 end 2048 offset 115359744 size 1032192 rgnIdx 110 RgnOffset 16384 scn: 0x0000.000ddc2e hist: 0x1

2014-11-17 17:18:32.419680*: RIDX validation history: 0:PartialRead 1:Undef 2:Undef 3:Undef 4:Undef 5:Undef 6:Undef 7:Undef 8:Undef 9:Undef

2014-11-17 17:18:32.419780*: Col id [7] numFilt 4 flg 2:

2014-11-17 17:18:32.419780*: lo: 42 3a 20 33 30 30 30 30

2014-11-17 17:18:32.419780*: hi: 42 3a 20 33 30 30 30 30

--> storage index 대상 column 

2014-11-17 17:18:32.419811*: RIDX (0x7c4168) for SQLID 6hv5zxwck5dd4 filter 1

2014-11-17 17:18:32.419811*: RIDX (0x7c4168) : st 2 validBitMap 0 tabn 0 id {75884 7 2415342635}

2014-11-17 17:18:32.419811*: RIDX: strt 32 end 2048 offset 116408320 size 1032192 rgnIdx 111 RgnOffset 16384 scn: 0x0000.000ddc30 hist: 0x1

2014-11-17 17:18:32.419811*: RIDX validation history: 0:PartialRead 1:Undef 2:Undef 3:Undef 4:Undef 5:Undef 6:Undef 7:Undef 8:Undef 9:Undef

2014-11-17 17:18:32.419874*: Col id [7] numFilt 4 flg 2:

2014-11-17 17:18:32.419874*: lo: 42 3a 20 33 30 30 30 30

2014-11-17 17:18:32.419874*: hi: 42 3a 20 33 30 30 30 30

...

4. 참고 

그동안 궁금해했던 부분 하나. 

Storage index가 한 테이블에 최대 8개의 column에 대해서 min, max, null 사용정보를 구성한다는데..

조건에 9개 column을 쓰는 query가 있다면? 

아님.. 엄한 query가 먼저 돌아 8개를 다 채워버린다면? 

아래 John Clarke's Blog에 관련 언급이 있네요..

workload에 따라 cellsrv가 지속적으로 storage index를 update 함 !!

(같은 object에 대해 workload에 따라 storage index가 재생성되는 것인지 아니면 여러 storage index가 한 object에 생성된다는 건지는 확인이 필요할 듯..)

Oracle documents that Exadata maintains storage indexes for up to 8 columns in a table, which means that if you have an application that accesses a table using more than 8 columns in your predicates (across the entire workload), each with Smart Scan, not all of your columns/queries will benefit from Storage Indexes.  cellsrv dynamically updates these region indexes based on your workload, and one of the interesting things to note is that each region index on the same object can contain different combinations of columns.   Like the high and low values tracked, it's very data-dependent in design.  

참고 : http://jccoracle.blogspot.kr/2013/01/tracing-storage-indexes-with.html