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




Exadata의 cellserver는 기존의 disk server와 개념과 구조가 많이 달라

기본적으로 사용하고 있는 system 모니터링 툴의 기능 만으로 전반적인 내용을 판단하기 어렵습니다. 


Oracle Enterprise Manager를 사용해보신 분은 알겠지만..

오라클은 matric에 대한 임계치를 설정해 그에 따른 경고를 보내 주는 기능을 조아합니다. 

가장 많이 사용한 EM의 matric은 active session 등이 특정 개수 이상일 경우 경고해주는 등이죠..


Exadata의 cellserver도 그 와 비슷하게 여러 matric을 만들어 놓고 이에 따른 임계치를 설정할 수 있습니다. 

이 임계치 설정에 따라 미리 설정된 sms나 메일로 경고를 받아 볼 수 있습니다. 

아래 리스트는 Exadata cellserver에서 설정할 수 있는 전체 matric 입니다. 

대부분 성능에 대한 matric이나 file system 사용량이나 CPU 사용량, IO error 에 대한 임계치를 미리 설정할 만 하네요.


CellCLI> list METRICDEFINITION attributes name, description
         CD_IO_BY_R_LG                   "Number of megabytes read in large blocks from a cell disk"
         CD_IO_BY_R_LG_SEC               "Number of megabytes read in large blocks per second from a cell disk"
         CD_IO_BY_R_SM                   "Number of megabytes read in small blocks from a cell disk"
         CD_IO_BY_R_SM_SEC               "Number of megabytes read in small blocks per second from a cell disk"
         CD_IO_BY_W_LG                   "Number of megabytes written in large blocks to a cell disk"
         CD_IO_BY_W_LG_SEC               "Number of megabytes written in large blocks per second to a cell disk"
         CD_IO_BY_W_SM                   "Number of megabytes written in small blocks to a cell disk"
         CD_IO_BY_W_SM_SEC               "Number of megabytes written in small blocks per second to a cell disk"
         CD_IO_ERRS                      "Number of IO errors on a cell disk"
         CD_IO_ERRS_MIN                  "Number of IO errors on a cell disk per minute"
         CD_IO_RQ_R_LG                   "Number of requests to read large blocks from a cell disk"
         CD_IO_RQ_R_LG_SEC               "Number of requests to read large blocks per second from a cell disk"
         CD_IO_RQ_R_SM                   "Number of requests to read small blocks from a cell disk"
         CD_IO_RQ_R_SM_SEC               "Number of requests to read small blocks per second from a cell disk"
         CD_IO_RQ_W_LG                   "Number of requests to write large blocks to a cell disk"
         CD_IO_RQ_W_LG_SEC               "Number of requests to write large blocks per second to a cell disk"
         CD_IO_RQ_W_SM                   "Number of requests to write small blocks to a cell disk"
         CD_IO_RQ_W_SM_SEC               "Number of requests to write small blocks per second to a cell disk"
         CD_IO_ST_RQ                     "Average service time per request for small IO requests to a cell disk"
         CD_IO_TM_R_LG                   "Cumulative latency of reading large blocks from a cell disk"
         CD_IO_TM_R_LG_RQ                "Average latency of writing large blocks per request to a cell disk"
         CD_IO_TM_R_SM                   "Cumulative latency of reading small blocks from a cell disk"
         CD_IO_TM_R_SM_RQ                "Average latency of reading small blocks per request from a cell disk"
         CD_IO_TM_W_LG                   "Cumulative latency of writing large blocks to a cell disk"
         CD_IO_TM_W_LG_RQ                "Average latency of writing large blocks per request to a cell disk"
         CD_IO_TM_W_SM                   "Cumulative latency of writing small blocks to a cell disk"
         CD_IO_TM_W_SM_RQ                "Average latency of writing small blocks per request to a cell disk"
         CG_FC_IO_BY_SEC                 "Number of megabytes of I/O per second for this consumer group to flash cache"
         CG_FC_IO_RQ                     "Number of IO requests issued by a consumer group to flash cache"
         CG_FC_IO_RQ_SEC                 "Number of IO requests issued by a consumer group to flash cache per second"
         CG_FD_IO_BY_SEC                 "Number of megabytes of I/O per second for this consumer group to flash disks"
         CG_FD_IO_LOAD                   "Average I/O load from this consumer group for flash disks"
         CG_FD_IO_RQ_LG                  "Number of large IO requests issued by a consumer group to flash disks"
         CG_FD_IO_RQ_LG_SEC              "Number of large IO requests issued by a consumer group to flash disks per second"
         CG_FD_IO_RQ_SM                  "Number of small IO requests issued by a consumer group to flash disks"
         CG_FD_IO_RQ_SM_SEC              "Number of small IO requests issued by a consumer group to flash disks per second"
         CG_IO_BY_SEC                    "Number of megabytes of I/O per second for this consumer group to hard disks"
         CG_IO_LOAD                      "Average I/O load from this consumer group for hard disks"
         CG_IO_RQ_LG                     "Number of large IO requests issued by a consumer group to hard disks"
         CG_IO_RQ_LG_SEC                 "Number of large IO requests issued by a consumer group to hard disks per second"
         CG_IO_RQ_SM                     "Number of small IO requests issued by a consumer group to hard disks"
         CG_IO_RQ_SM_SEC                 "Number of small IO requests issued by a consumer group to hard disks per second"
         CG_IO_UTIL_LG                   "Percentage of disk resources utilized by large requests from this consumer group"
         CG_IO_UTIL_SM                   "Percentage of disk resources utilized by small requests from this consumer group"
         CG_IO_WT_LG                     "IORM wait time for large IO requests issued by a consumer group"
         CG_IO_WT_LG_RQ                  "Average IORM wait time per request for large IO requests issued by a consumer group"
         CG_IO_WT_SM                     "IORM wait time for small IO requests issued by a consumer group"
         CG_IO_WT_SM_RQ                  "Average IORM wait time per request for small IO requests issued by a consumer group"
         CL_CPUT                         "Cell CPU Utilization is the percentage of time over the previous minute that the system CPUs were not idle (from /proc/stat)."
         CL_FANS                         "Number of working fans on the cell"
         CL_FSUT                         "Percentage of total space on this file system that is currently used"
         CL_MEMUT                        "Percentage of total physical memory on the cell that is currently used"
         CL_RUNQ                         "Average number (over the preceding minute) of processes in the Linux run queue marked running or uninterruptible (from /proc/loadavg)."
         CL_TEMP                         "Temperature (Celsius) of the server, provided by the BMC"
         CT_FC_IO_BY_SEC                 "Number of megabytes of I/O per second for this category to flash cache"
         CT_FC_IO_RQ                     "Number of IO requests issued by an IORM category to flash cache"
         CT_FC_IO_RQ_SEC                 "Number of IO requests issued by an IORM category to flash cache per second"
         CT_FD_IO_BY_SEC                 "Number of megabytes of I/O per second for this category to flash disks"
         CT_FD_IO_LOAD                   "Average I/O load from this category for flash disks"
         CT_FD_IO_RQ_LG                  "Number of large IO requests issued by an IORM category to flash disks"
         CT_FD_IO_RQ_LG_SEC              "Number of large IO requests issued by an IORM category to flash disks per second"
         CT_FD_IO_RQ_SM                  "Number of small IO requests issued by an IORM category to flash disks"
         CT_FD_IO_RQ_SM_SEC              "Number of small IO requests issued by an IORM category to flash disks per second"
         CT_IO_BY_SEC                    "Number of megabytes of I/O per second for this category to hard disks"
         CT_IO_LOAD                      "Average I/O load from this category for hard disks"
         CT_IO_RQ_LG                     "Number of large IO requests issued by an IORM category to hard disks"
         CT_IO_RQ_LG_SEC                 "Number of large IO requests issued by an IORM category to hard disks per second"
         CT_IO_RQ_SM                     "Number of small IO requests issued by an IORM category to hard disks"
         CT_IO_RQ_SM_SEC                 "Number of small IO requests issued by an IORM category to hard disks per second"
         CT_IO_UTIL_LG                   "Percentage of disk resources utilized by large requests from this category"
         CT_IO_UTIL_SM                   "Percentage of disk resources utilized by small requests from this category"
         CT_IO_WT_LG                     "IORM wait time for large IO requests issued by an IORM category"
         CT_IO_WT_LG_RQ                  "Average IORM wait time per request for large IO requests issued by an IORM category"
         CT_IO_WT_SM                     "IORM wait time for small IO requests issued by an IORM category"
         CT_IO_WT_SM_RQ                  "Average IORM wait time per request for small IO requests issued by an IORM category"
         DB_FC_IO_BY_SEC                 "Number of megabytes of I/O per second for this database to flash cache"
         DB_FC_IO_RQ                     "Number of IO requests issued by a database to flash cache"
         DB_FC_IO_RQ_SEC                 "Number of IO requests issued by a database to flash cache per second"
         DB_FD_IO_BY_SEC                 "Number of megabytes of I/O per second for this database to flash disks"
         DB_FD_IO_LOAD                   "Average I/O load from this database for flash disks"
         DB_FD_IO_RQ_LG                  "Number of large IO requests issued by a database to flash disks"
         DB_FD_IO_RQ_LG_SEC              "Number of large IO requests issued by a database to flash disks per second"
         DB_FD_IO_RQ_SM                  "Number of small IO requests issued by a database to flash disks"
         DB_FD_IO_RQ_SM_SEC              "Number of small IO requests issued by a database to flash disks per second"
         DB_IO_BY_SEC                    "Number of megabytes of I/O per second for this database to hard disks"
         DB_IO_LOAD                      "Average I/O load from this database for hard disks"
         DB_IO_RQ_LG                     "Number of large IO requests issued by a database to hard disks"
         DB_IO_RQ_LG_SEC                 "Number of large IO requests issued by a database to hard disks per second"
         DB_IO_RQ_SM                     "Number of small IO requests issued by a database to hard disks"
         DB_IO_RQ_SM_SEC                 "Number of small IO requests issued by a database to hard disks per second"
         DB_IO_UTIL_LG                   "Percentage of disk resources utilized by large requests from this database"
         DB_IO_UTIL_SM                   "Percentage of disk resources utilized by small requests from this database"
         DB_IO_WT_LG                     "IORM wait time for large IO requests issued by a database"
         DB_IO_WT_LG_RQ                  "Average IORM wait time per request for large IO requests issued by a database"
         DB_IO_WT_SM                     "IORM wait time for small IO requests issued by a database"
         DB_IO_WT_SM_RQ                  "Average IORM wait time per request for small IO requests issued by a database"
         FC_BYKEEP_OVERWR                "Number of megabytes pushed out of the FlashCache because of space limit for 'keep' objects"
         FC_BYKEEP_OVERWR_SEC            "Number of megabytes per second pushed out of the FlashCache because of space limit for 'keep' objects"
         FC_BYKEEP_USED                  "Number of megabytes used for 'keep' objects on FlashCache"
         FC_BY_USED                      "Number of megabytes used on FlashCache"
         FC_IO_BYKEEP_R                  "Number of megabytes read from FlashCache for 'keep' objects"
         FC_IO_BYKEEP_R_SEC              "Number of megabytes read per second from FlashCache for 'keep' objects"
         FC_IO_BYKEEP_W                  "Number of megabytes written to FlashCache for 'keep' objects"
         FC_IO_BYKEEP_W_SEC              "Number of megabytes per second written to FlashCache for 'keep' objects"
         FC_IO_BY_R                      "Number of megabytes read from FlashCache"
         FC_IO_BY_R_MISS                 "Number of megabytes read from disks because not all requested data was in FlashCache"
         FC_IO_BY_R_MISS_SEC             "Number of megabytes read from disks per second because not all requested data was in FlashCache"
         FC_IO_BY_R_SEC                  "Number of megabytes read per second from FlashCache"
         FC_IO_BY_R_SKIP                 "Number of megabytes read from disks for IO requests with a hint to bypass FlashCache"
         FC_IO_BY_R_SKIP_SEC             "Number of megabytes read from disks per second for IO requests with a hint to bypass FlashCache"
         FC_IO_BY_W                      "Number of megabytes written to FlashCache"
         FC_IO_BY_W_SEC                  "Number of megabytes per second written to FlashCache"
         FC_IO_ERRS                      "Number of IO errors on FlashCache"
         FC_IO_RQKEEP_R                  "Number of read IO requests for 'keep' objects satisfied from FlashCache"
         FC_IO_RQKEEP_R_MISS             "Number of read IO requests for 'keep' objects which did not find all data in FlashCache"
         FC_IO_RQKEEP_R_MISS_SEC         "Number of read IO requests per second for 'keep' objects which did not find all data in FlashCache"
         FC_IO_RQKEEP_R_SEC              "Number of read IO requests for 'keep' objects per second satisfied from FlashCache"
         FC_IO_RQKEEP_R_SKIP             "Number of read IO requests for 'keep' objects with a hint to bypass FlashCache"
         FC_IO_RQKEEP_R_SKIP_SEC         "Number of read IO requests per second for 'keep' objects with a hint to bypass FlashCache"
         FC_IO_RQKEEP_W                  "Number of IO requests for 'keep' objects which resulted in FlashCache being populated with data"
         FC_IO_RQKEEP_W_SEC              "Number of IO requests per second for 'keep' objects which resulted in FlashCache being populated with data"
         FC_IO_RQ_R                      "Number of read IO requests satisfied from FlashCache"
         FC_IO_RQ_R_MISS                 "Number of read IO requests which did not find all data in FlashCache"
         FC_IO_RQ_R_MISS_SEC             "Number of read IO requests per second which did not find all data in FlashCache"
         FC_IO_RQ_R_SEC                  "Number of read IO requests satisfied per second from FlashCache"
         FC_IO_RQ_R_SKIP                 "Number of read IO requests with a hint to bypass FlashCache"
         FC_IO_RQ_R_SKIP_SEC             "Number of read IO requests per second with a hint to bypass FlashCache"
         FC_IO_RQ_W                      "Number of IO requests which resulted in FlashCache being populated with data"
         FC_IO_RQ_W_SEC                  "Number of IO requests per second which resulted in FlashCache being populated with data"
         FD_IO_LOAD                      "Average I/O load for flash disks"
         GD_IO_BY_R_LG                   "Number of megabytes read in large blocks from a grid disk"
         GD_IO_BY_R_LG_SEC               "Number of megabytes read in large blocks per second from a grid disk"
         GD_IO_BY_R_SM                   "Number of megabytes read in small blocks from a grid disk"
         GD_IO_BY_R_SM_SEC               "Number of megabytes read in small blocks per second from a grid disk"
         GD_IO_BY_W_LG                   "Number of megabytes written in large blocks to a grid disk"
         GD_IO_BY_W_LG_SEC               "Number of megabytes written in large blocks per second to a grid disk"
         GD_IO_BY_W_SM                   "Number of megabytes written in small blocks to a grid disk"
         GD_IO_BY_W_SM_SEC               "Number of megabytes written in small blocks per second to a grid disk"
         GD_IO_ERRS                      "Number of IO errors on a grid disk"
         GD_IO_ERRS_MIN                  "Number of IO errors on a grid disk per minute"
         GD_IO_RQ_R_LG                   "Number of requests to read large blocks from a grid disk"
         GD_IO_RQ_R_LG_SEC               "Number of requests to read large blocks per second from a grid disk"
         GD_IO_RQ_R_SM                   "Number of requests to read small blocks from a grid disk"
         GD_IO_RQ_R_SM_SEC               "Number of requests to read small blocks per second from a grid disk"
         GD_IO_RQ_W_LG                   "Number of requests to write large blocks to a grid disk"
         GD_IO_RQ_W_LG_SEC               "Number of requests to write large blocks per second to a grid disk"
         GD_IO_RQ_W_SM                   "Number of requests to write small blocks to a grid disk"
         GD_IO_RQ_W_SM_SEC               "Number of requests to write small blocks per second to a grid disk"
         IORM_MODE                       "I/O Resource Manager objective for the cell"
         IO_LOAD                         "Average I/O load for hard disks"
         N_MB_DROP                       "Number of megabytes droped during  transmission  to  a particular host"
         N_MB_DROP_SEC                   "Number of megabytes droped during transmission  per second  to  a particular host"
         N_MB_RDMA_DROP                  "Number of megabytes dropped during RDMA transmission to  a particular host"
         N_MB_RDMA_DROP_SEC              "Number of megabytes dropped during RDMA transmission per second  to  a particular host"
         N_MB_RECEIVED                   "Number of megabytes received from a particular host"
         N_MB_RECEIVED_SEC               "Number of megabytes per second received from a particular host"
         N_MB_RESENT                     "Number of megabytes resent  to  a particular host"
         N_MB_RESENT_SEC                 "Number of megabytes resent per second to  a particular host"
         N_MB_SENT                       "Number of megabytes transmitted to  a particular host"
         N_MB_SENT_SEC                   "Number of megabytes transmitted per second to  a particular host"
         N_NIC_NW                        "Number of non-working interconnects"
         N_NIC_RCV_SEC                   "Total number of IO packets received by interconnects per second"
         N_NIC_TRANS_SEC                 "Total number of IO packets transmitted by interconnects per second"
         N_RDMA_RETRY_TM                 "Latency of the retry actions during RDMA transmission to a particular host"


  •  DBFS로 구성된 파일 시스템은 NFS을 지원하지 않습니다.
  •  파일명을 한글로 사용하는 것을 원칙적으로 지원하지 않습니다.
  •  파일이 내부적으로는 DB내에 Lob 테이블로 저장되기 때문에, 일반 파일시스템과 다르게 mv명령와 같은 경우 Row별로 데이터를 삭제 후 다시 저장되기 때문에 성능적인 차이가 존재합니다.
  •  DBFS의 경우 실행파일은 수행되지 않습니다..

Exadata에서 DBFS를 구성해 보니 DBFS용으로 추가 구성한 DB 내 LOB file을 filesystem 형식으로 보여주는 .. 형태로 구성되는 군요. 위의 주의사항 처럼 DBFS를 구성해서 filesystem으로 보인다 해도 여기에 어떠한 실행파일을 구성할 수 없습니다. 또한 성능도 이러한 구성이라면 그닥 기대할 만하지 못할 듯하네요...


어디다가 쓰지? DBFS? 

External table을 위한 sam file 정도 ? 




+ Recent posts