에너쓰오라클

Oracle 12c에 Exadata를 위한 System Statistics 수집 기능 추가되었습니다. 근데 이건 11gR2(exadata X2)에서도 해줘야 됬던 기능인거 같은뎅..  

참고 : http://kerryosborne.oracle-guy.com/2013/09/system-statistics-exadata-mode/

암튼 system statistic 수집은 (workload statistics mode) 아래의 system 성능 정보를 수집하며 SQL optimizer가 SQL Plan 수립에 이 system 성능정보를 참고하므로 꼭 한번은, 그리고 system hardware 변경시에 수행해야 합니다..

• Single and multiblock read times
• mbrc
• CPU speed (cpuspeed)
• Maximum system throughput
• Average slave throughput

system statistics 수집 방법 

-- 특정 주기동안 수집

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('start') 

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('stop') 

-- 특정 interval(분단위) 동안 수집 

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval=>N)

-- exadata 성능 수집

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('exadata')

-- noworkload statistics 수집

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS()

Enhancements to System Statistics

System statistics allow the optimizer to account for the hardware on which the database system is running. With the introduction of smart storage, such as Exadata storage, the optimizer needs additional system statistics in order to account for all of the smart storage capabilities.

The introduction of the new system statistics gathering method allows the optimizer to more accurately account for the performance characteristics of smart storage, such as Exadata storage.

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에 생성된다는 건지는 확인이 필요할 듯..)

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"