{"id":774,"date":"2013-03-31T11:49:25","date_gmt":"2013-03-31T03:49:25","guid":{"rendered":"http:\/\/www.yallonking.com\/?p=774"},"modified":"2013-03-31T11:52:45","modified_gmt":"2013-03-31T03:52:45","slug":"%e6%9c%89%e5%85%b3%e7%b4%a2%e5%bc%95-%e6%9f%a5%e7%9c%8b%e6%9f%90%e7%b4%a2%e5%bc%95%e6%98%af%e5%90%a6%e4%bd%bf%e7%94%a8%e8%bf%87","status":"publish","type":"post","link":"http:\/\/www.yallonking.com\/?p=774","title":{"rendered":"\u6709\u5173\u7d22\u5f15-\u67e5\u770b\u67d0\u7d22\u5f15\u662f\u5426\u4f7f\u7528\u8fc7"},"content":{"rendered":"

\u67e5\u770b\u67d0\u7d22\u5f15\u662f\u5426\u4f7f\u7528\u8fc7<\/strong>
\n\u521b\u5efa\u6d4b\u8bd5\u8868\u53ca\u7d22\u5f15<\/p>\n

\r\nSQL> drop table yallonking;\r\n\r\nTable dropped.\r\n\r\nSQL> create table yallonking as select rownum id,'yallonking' name from dual connect by rownum<1000;\r\n\r\nTable created.\r\n\r\nSQL> create index idx_id on yallonking(id);\r\n\r\nIndex created.\r\n\r\n<\/pre>\n
\uff08\u4e00\uff09\u901a\u8fc7\u5bf9\u7d22\u5f15\u7684\u76d1\u63a7\u6765\u68c0\u67e5<\/strong><\/p>\n
\r\nSQL> alter index idx_id monitoring usage;\r\n\r\nIndex altered.\r\n\r\nSQL> select index_name,monitoring,used from v$object_usage;\r\n\r\nINDEX_NAME                     MON USE\r\n------------------------------ --- ---\r\nIDX_ID                         YES NO\r\n\r\nSQL> set autot traceonly\r\nSQL> select * from yallonking;\r\n\r\n999 rows selected.\r\n\r\n\r\nExecution Plan\r\n----------------------------------------------------------\r\nPlan hash value: 2582482548\r\n\r\n--------------------------------------------------------------------------------\r\n| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |\r\n--------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT  |            |   999 | 24975 |     2   (0)| 00:00:01 |\r\n|   1 |  TABLE ACCESS FULL| YALLONKING |   999 | 24975 |     2   (0)| 00:00:01 |\r\n--------------------------------------------------------------------------------\r\n\r\nNote\r\n-----\r\n   - dynamic sampling used for this statement\r\n\r\n\r\nStatistics\r\n----------------------------------------------------------\r\n        131  recursive calls\r\n          0  db block gets\r\n         96  consistent gets\r\n          0  physical reads\r\n          0  redo size\r\n      28941  bytes sent via SQL*Net to client\r\n       1111  bytes received via SQL*Net from client\r\n         68  SQL*Net roundtrips to\/from client\r\n          4  sorts (memory)\r\n          0  sorts (disk)\r\n        999  rows processed\r\n<\/pre>\n
\u4e0a\u8fb9\u7684sql\u660e\u663e\u6ca1\u6709\u4f7f\u7528\u5230\u7d22\u5f15\uff0c\u6240\u4ee5\u5728\u4e0b\u8fb9\u7684\u67e5\u8be2\u4e2d\u4e5f\u6ca1\u6709\u76d1\u63a7\u5230\u4f7f\u7528\u7684\u8ff9\u8c61\u3002\t<\/p>\n
\r\nSQL> set autot off\r\nSQL> select index_name,monitoring,used from v$object_usage;\r\n\r\nINDEX_NAME                     MON USE\r\n------------------------------ --- ---\r\nIDX_ID                         YES NO\r\n\r\n<\/pre>\n
\u4e0b\u8fb9\u4f7f\u7528\u7528\u5230\u7d22\u5f15\u7684sql\u67e5\u8be2<\/p>\n
\r\nSQL> set autot traceonly\r\nSQL> select * from yallonking where id=2;\r\n\r\n\r\nExecution Plan\r\n----------------------------------------------------------\r\nPlan hash value: 3725498274\r\n\r\n------------------------------------------------------------------------------------------\r\n| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |\r\n------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT            |            |     1 |    25 |     2   (0)| 00:00:01 |\r\n|   1 |  TABLE ACCESS BY INDEX ROWID| YALLONKING |     1 |    25 |     2   (0)| 00:00:01 |\r\n|*  2 |   INDEX RANGE SCAN          | IDX_ID     |     1 |       |     1   (0)| 00:00:01 |\r\n------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   2 - access("ID"=2)\r\n\r\nNote\r\n-----\r\n   - dynamic sampling used for this statement\r\n\r\n\r\nStatistics\r\n----------------------------------------------------------\r\n         12  recursive calls\r\n          3  db block gets\r\n         17  consistent gets\r\n          1  physical reads\r\n        544  redo size\r\n        474  bytes sent via SQL*Net to client\r\n        385  bytes received via SQL*Net from client\r\n          2  SQL*Net roundtrips to\/from client\r\n          0  sorts (memory)\r\n          0  sorts (disk)\r\n          1  rows processed\r\n<\/pre>\n
\u6b64\u523b\u4e0a\u8fb9\u53ef\u4ee5\u770b\u89c1\u5728\u4f7f\u7528\u4e86\u8be5\u7d22\u5f15\u7684sql\u540e\uff0c\u5728v$object_usage\u4e2d\u6709\u4e86\u76f8\u5173\u8bb0\u5f55\t\t  <\/p>\n
\r\nSQL> set autot off\r\nSQL> select index_name,monitoring,used from v$object_usage;\r\n\r\nINDEX_NAME                     MON USE\r\n------------------------------ --- ---\r\nIDX_ID                         YES YES\r\n\r\nSQL> alter index idx_id nomonitoring usage;\r\n\r\nIndex altered.\r\n<\/pre>\n
\uff08\u4e8c\uff09\u67e5\u770b\u5386\u53f2\u89c6\u56fe\u5224\u65ad\u7d22\u5f15\u4f7f\u7528\u7684\u60c5\u51b5<\/strong><\/p>\n
\r\nSQL> select d.object_name,d.operation,d.options,count(1) from dba_hist_sql_plan d,dba_hist_sqlstat h where d.object_owner <> 'SYS' and d.operation like '%INDEX%' and d.sql_id = h.sql_id group by d.object_name,d.operation,d.options order by 1,2,3;\r\n\r\nOBJECT_NAME                     OPERATION                      OPTIONS                          COUNT(1)\r\n------------------------------- ------------------------------ ------------------------------ ----------\r\nAQ$_QUEUES_CHECK                INDEX                          RANGE SCAN                              7\r\nAQ$_QUEUE_TABLES_PRIMARY        INDEX                          UNIQUE SCAN                            17\r\nMGMT_CURRENT_SEVERITY_IDX_03    INDEX                          RANGE SCAN                              1\r\nMGMT_JOB_EXEC_IDX01             INDEX                          RANGE SCAN                             15\r\nMGMT_JOB_EXEC_SUMM_IDX04        INDEX                          RANGE SCAN                              4\r\nMGMT_JOB_PK                     INDEX                          UNIQUE SCAN                            15\r\nMGMT_METRICS_IDX_01             INDEX                          RANGE SCAN                              6\r\nMGMT_METRICS_PK                 INDEX                          RANGE SCAN                              1\r\nMGMT_METRIC_COLLECTIONS_PK      INDEX                          UNIQUE SCAN                            12\r\nMGMT_METRIC_COLLECTIONS_REP_PK  INDEX                          FULL SCAN                              12\r\nMGMT_PARAMETERS_IDX_01          INDEX                          RANGE SCAN                              6\r\n\r\nOBJECT_NAME                     OPERATION                      OPTIONS                          COUNT(1)\r\n------------------------------- ------------------------------ ------------------------------ ----------\r\nMGMT_PERFORMANCE_NAMES_PK       INDEX                          UNIQUE SCAN                             2\r\nMGMT_SYSTEM_ERROR_LOG_01        INDEX                          SKIP SCAN                               1\r\nMGMT_SYSTEM_PERF_LOG_IDX_01     INDEX                          RANGE SCAN                              6\r\nMGMT_TARGETS_IDX_01             INDEX                          UNIQUE SCAN                             5\r\nRLM$JOINQKEY                    INDEX                          FULL SCAN                               1\r\nRLM$SCHACTIONORDER              INDEX                          FULL SCAN                               1\r\nSYS_C003978                     INDEX                          UNIQUE SCAN                             6\r\nSYS_C003981                     INDEX                          UNIQUE SCAN                             6\r\nSYS_IOT_TOP_49769               INDEX                          FULL SCAN                               6\r\n\r\n20 rows selected.\r\n<\/pre>\n
\u53d1\u73b0\u4e0a\u8fb9\u6ca1\u6709\u51fa\u73b0\u76d1\u63a7\u7684\u7d22\u5f15\u7684\u8bb0\u5f55\uff0c\u662f\u56e0\u4e3a\u6570\u636e\u8fd8\u6ca1\u6709\u5199\u5230\u5386\u53f2\u89c6\u56fe\u4e2d\uff0c\u6545\u5728\u6b64\u624b\u52a8\u751f\u6210\u4e00\u6b21\u5feb\u7167\u5373\u53ef<\/p>\n
\r\nSQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> select d.object_name,d.operation,d.options,count(1) from dba_hist_sql_plan d,dba_hist_sqlstat h where d.object_owner <> 'SYS' and d.operation like '%INDEX%' and d.sql_id = h.sql_id group by d.object_name,d.operation,d.options order by 1,2,3;\r\n\r\nOBJECT_NAME                     OPERATION                      OPTIONS                          COUNT(1)\r\n------------------------------- ------------------------------ ------------------------------ ----------\r\nAQ$_QUEUES_CHECK                INDEX                          RANGE SCAN                              7\r\nAQ$_QUEUE_TABLES_PRIMARY        INDEX                          UNIQUE SCAN                            17\r\nIDX_ID                          INDEX                          RANGE SCAN                              2\r\nMGMT_CURRENT_METRICS_PK         INDEX                          FULL SCAN                               1\r\nMGMT_CURRENT_SEVERITY_IDX_03    INDEX                          RANGE SCAN                              1\r\nMGMT_JOB_EXEC_IDX01             INDEX                          RANGE SCAN                             20\r\nMGMT_JOB_EXEC_SUMM_IDX04        INDEX                          RANGE SCAN                              4\r\nMGMT_JOB_PK                     INDEX                          UNIQUE SCAN                            20\r\nMGMT_METRICS_IDX_01             INDEX                          RANGE SCAN                              6\r\nMGMT_METRICS_PK                 INDEX                          RANGE SCAN                              1\r\nMGMT_METRIC_COLLECTIONS_PK      INDEX                          UNIQUE SCAN                            12\r\n\r\nOBJECT_NAME                     OPERATION                      OPTIONS                          COUNT(1)\r\n------------------------------- ------------------------------ ------------------------------ ----------\r\nMGMT_METRIC_COLLECTIONS_REP_PK  INDEX                          FULL SCAN                              12\r\nMGMT_PARAMETERS_IDX_01          INDEX                          RANGE SCAN                              7\r\nMGMT_PERFORMANCE_NAMES_PK       INDEX                          UNIQUE SCAN                             2\r\nMGMT_SYSTEM_ERROR_LOG_01        INDEX                          SKIP SCAN                               1\r\nMGMT_SYSTEM_PERF_LOG_IDX_01     INDEX                          RANGE SCAN                              6\r\nMGMT_TARGETS_IDX_01             INDEX                          UNIQUE SCAN                             6\r\nMGMT_TARGET_PROPERTIES_PK       INDEX                          RANGE SCAN                              1\r\nMGMT_TARGET_ROLLUP_TIMES_PK     INDEX                          SKIP SCAN                               1\r\nRLM$JOINQKEY                    INDEX                          FULL SCAN                               1\r\nRLM$SCHACTIONORDER              INDEX                          FULL SCAN                               1\r\nSYS_C003978                     INDEX                          UNIQUE SCAN                             6\r\n\r\nOBJECT_NAME                     OPERATION                      OPTIONS                          COUNT(1)\r\n------------------------------- ------------------------------ ------------------------------ ----------\r\nSYS_C003981                     INDEX                          UNIQUE SCAN                             6\r\nSYS_IOT_TOP_49769               INDEX                          FULL SCAN                               6\r\n\r\n24 rows selected.\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
\u67e5\u770b\u67d0\u7d22\u5f15\u662f\u5426\u4f7f\u7528\u8fc7 \u521b\u5efa\u6d4b\u8bd5\u8868\u53ca\u7d22\u5f15 SQL> drop table yallonking; Tabl […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[87,9],"tags":[159,158],"_links":{"self":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/774"}],"collection":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=774"}],"version-history":[{"count":4,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/774\/revisions"}],"predecessor-version":[{"id":777,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/774\/revisions\/777"}],"wp:attachment":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=774"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=774"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=774"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}