set lines 190<\/span>
col sql_text format a70 trunc<\/span>
col child format 99999<\/span>
col execs format 9,999<\/span>
col avg_etime format 99,999.99<\/span>
col “IO_SAVED_%” format 999.99<\/span>
col avg_px format 999<\/span>
col offload for a7<\/span>
select sql_id, child_number child, plan_hash_value plan_hash, executions execs,<\/span> <\/span><\/span>
(elapsed_time\/1000000)\/decode(nvl(executions,0),0,1,executions)\/<\/span>
decode(px_servers_executions,0,1,px_servers_executions\/decode(nvl(executions,0),0,1,executions)) avg_etime,<\/span> <\/span><\/span>
px_servers_executions\/decode(nvl(executions,0),0,1,executions) avg_px,<\/span>
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,’No’,’Yes’) Offload,<\/span>
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,0,100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)<\/span>
\/decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,1,IO_CELL_OFFLOAD_ELIGIBLE_BYTES)) “IO_SAVED_%”,<\/span>
sql_text<\/span>
from v$sql s<\/span>
where upper(sql_text) like upper(nvl(q'[&sql_text]’,sql_text))<\/span>
and sql_text not like ‘BEGIN :sql_text := %’<\/span>
and sql_text not like ‘%IO_CELL_OFFLOAD_ELIGIBLE_BYTES%’<\/span>
and sql_text not like ‘\/* SQL Analyze(%’<\/span>
and sql_id like nvl(‘&sql_id’,sql_id)<\/span>
order by 1, 2, 3<\/span>
\/<\/span><\/b><\/p>\n
<\/p>\n
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT<\/p>\n
—— ———- ———- —— ———- —— ——- ———- ————————– —————-<\/p>\n
abgqtq8zt90sb 0 3570092908 1 .79 0 Yes 99.86 select * from test_objects where object_id=’54’<\/p>\n
<\/p>\n
<\/p>\n
select b.name, a.value from v$mystat a, v$statname b<\/span> <\/p>\n <\/p>\n Smart Scan\uc774 \ub3d9\uc791\ud558\uc9c0 \uc54a\ub294 \ub300\ud45c\uc801\uc778 \uc608 \uc785\ub2c8\ub2e4. <\/p>\n (\u203b Full List\ub294 Doc ID 1927934.1\uc758 What are the situations that prevent Smart Scan from happening ? \ubd80\ubd84 \ucc38\uc870)<\/p>\n 1. IOT\uc640 Clustred Table\uc758 Scan<\/b><\/p>\n 2. Index Range Scans<\/b><\/p>\n 3. Access to a compressed index<\/b><\/p>\n 4. Access to a reverse key index<\/b><\/p>\n 5. Cell Storage Server CPU\uc758 \uacfc\ubd80\ud558 \uc0c1\ud0dc\uc77c \ub54c <\/b>(\uad00\ub828 \ud1b5\uacc4\uc815\ubcf4: cell physical IO bytes sent directly to DB node to balance CPU)<\/b><\/p>\n 6. Table\uc5d0 CACHE property\uac00 \uc788\uc744 \ub54c<\/b><\/p>\n 7. Table\uc774 KEEP Buffer \ub85c \uc124\uc815\ub418\uc5b4 \uc788\uc744 \ub54c<\/b><\/p>\n 8. 255\uac1c \uc774\uc0c1\uc758 column\uc774 access\ub420 \ub54c<\/b><\/p>\n 9. Cahined Rows\uc5d0 \ub300\ud574 Smart Scan\uc774 \ubc1c\uc0dd\ub420 \ub54c Computing Node\uc5d0 \uc758\ud55c \ucd94\uac00\uc801\uc778 Single Block <\/b>Access\uac00 \ubc1c\uc0dd\ub420 \uc218 \uc788\uc74c.<\/b><\/p>\n Smart Scan \ud558\uae30 \uc704\ud55c \uc804\uc81c\uac00 \uba87 \uac00\uc9c0 \uc788\uc2b5\ub2c8\ub2e4.<\/b><\/p>\n (\ucd9c\ucc98 : Exadata Smart Scan FAQ (Doc ID 1927934.1) )<\/b><\/p>\n 1. Full Table Sacn \ub610\ub294 Fast Full Index Scan, Full Bitmap Index Scan\uc774 \ubc1c\uc0dd \ub418\uc5b4\uc57c \ud569\ub2c8\ub2e4.<\/b><\/p>\n 2. Buffer Cache\ub85c \ubd80\ud130 \ube14\ub85d\uc744 \uc77d\uc5b4\ub4e4\uc774\uc9c0 \uc54a\uace0 Disk\ub85c \ubd80\ud130 Direct Path Read\ub97c \ud574\uc57c \ud569\ub2c8\ub2e4.<\/b><\/p>\n – Parallel Query\ub294 SMALL TABLE\ubcf4\ub2e4 \ud070 \ud14c\uc774\ube14\uc5d0 \ub300\ud574 Direct Path Read\ub97c \ud569\ub2c8\ub2e4.<\/b><\/p>\n – Serial Query\ub294 VERYLARGE TABLE\ubcf4\ub2e4 \uc791\uc740 \ud14c\uc774\ube14\uc5d0 \ub300\ud574 Buffer Read\ub97c \ud569\ub2c8\ub2e4.,<\/b><\/p>\n _serial_direct_read=TRUE \ub85c direct read\ub97c \uac15\uc81c\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/b><\/p>\n 3. Scan\ub418\ub294 Segment\ub294 Exadata Cell Storage\uc0c1\uc758 Disk Group\uc5d0 \uc874\uc7ac\ud574\uc57c \ud569\ub2c8\ub2e4. \uc989, Exadata \ub098 <\/b>SuperCluster \ub4f1\uc5d0\uc11c\ub9cc \ub3d9\uc791\ud569\ub2c8\ub2e4.<\/b><\/p>\n 4. DB\ub294 11.2 \uc774\uc0c1\uc774\uace0 Parameter CELL_OFFLOAD_PROCESSING=TRUE\ub85c \uc124\uc815\ub418\uc5b4 \uc788\uc5b4\uc57c \ud569\ub2c8\ub2e4.<\/b><\/p>\n 5. ASM\uc740 11.2 \uc774\uc0c1\uc774\uace0 Diskgroup\uc758 Attribute\uc778 cell.smart_scan_capable=TRUE \ub85c \uc124\uc815\ub418\uc5b4 \uc788\uc5b4<\/b>\uc57c \ud569\ub2c8\ub2e4.<\/b><\/p>\n <\/b><\/p>\n 6. No quarantines for the sql_id<\/b><\/p>\n dcli -g cell_group -l root cellcli -e list quarantine <\/b>list quarantine <#> detail — run for each quarantine<\/b><\/p>\n <\/b><\/p>\n ( MOS Doc ID 1534130.1 How To Tell If a Statement is Quarantined by Cellsrv )<\/b><\/p>\n Exadata Smart Scan \ubaa8\ub2c8\ud130\ub9c1 \ud558\ub294 3\uac00\uc9c0 \ucffc\ub9ac \uc785\ub2c8\ub2e4. ” Exadata\uc5d0\uc11c Smart Scan\uc740 Query \uc131\ub2a5 \ud5a5\uc0c1\uc744 \uc704\ud55c \ud575\uc2ec \uae30\uc220\uc785\ub2c8\ub2e4. Smart Scan\uc740 Partitioning,HCC \uc555\ucd95\uacfc \ud568\uaed8 \uc0ac\uc6a9\ub418\uba74 \uadf8 \ud6a8\uc728\uc131\uc774 \ub354\uc6b1 \uc88b\uc544\uc9d1\ub2c8\ub2e4. Smart Scan\uc73c\ub85c \uc218\ud589\ub418\ub358 Quer y\uac00 Smart Scan\uc73c\ub85c \ub3d9\uc791\ud558\uc9c0 \uc54a\uc744 \ub54c, \uc131\ub2a5\uc800\ud558\uc758 \ubb38\uc81c\uac00 \uc57c\uae30\ub420 \uc218 \uc788\uc2b5\ub2c8\ub2e4 “ MOS Doc ID: 1438173.1 \uc5d0\uc11c Smart Scan\uacfc \uad00\ub828\ub41c \ud1b5\uacc4\ub97c \uc870\ud68c\ud560 \uc218 […]<\/p>\n","protected":false},"author":1,"featured_media":698,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[25],"tags":[382,383,384,385,386,387],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/1\/cfile24.uf.233E394F56529790318D78.jpg?fit=612%2C156","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/697"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=697"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/697\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/698"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=697"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=697"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=697"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
where a.STATISTIC# = b.STATISTIC# and<\/span>
(b.name = ‘cell session smart scan efficiency’ or<\/span>
b.name = ‘cell physical IO bytes saved by storage index’ or<\/span>
b.name = ‘cell physical IO bytes eligible for predicate offload’ or<\/span>
b.name = ‘cell physical IO interconnect bytes returned by smart scan’<\/span>
or<\/span>
b.name = ‘cell IO uncompressed bytes’<\/span> <\/span><\/span>
or<\/span>
b.name like ‘%cell blocks processed%’);<\/span><\/b>
select name, value from v$mystat s, v$statname n<\/span>
where n.statistic#=s.statistic#<\/span>
and name like ‘cell scans’;<\/span><\/b><\/p>\n
<\/b><\/p>\n
<\/b><\/p>\n![](\"https:\/\/i0.wp.com\/oracledba.zapto.org\/wordpress\/wp-content\/uploads\/1\/cfile24.uf.233E394F56529790318D78.jpg?resize=612%2C156\")
<\/p>\n
<\/b><\/p>\n
<\/b><\/p>\n","protected":false},"excerpt":{"rendered":"