一次访问Oracle数据字典的优化 - 数据库编程

今晚上有位哥们QQ问我有没有什么SQL脚本用来收集统计信息的几乎未加思考我就把上面的脚本原封不动的贴个了那位哥们

后来那位哥们改写了SQL，说下面的SQL要跑200多秒，7千多W的逻辑读

该SQL语句执行计划如下：

SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------- -------------------- Plan hash value: 2028155339 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 22412 | 3852K| 113K (2)| 00:05:55 | | 1 | HASH GROUP BY | | 22412 | 3852K| 113K (2)| 00:05:55 | |* 2 | FILTER | | | | | | | 3 | VIEW | SYS_DBA_SEGS | 2837 | 487K| 110K (2)| 00:05:46 | | 4 | UNION-ALL | | | | | | | 5 | NESTED LOOPS | | 1840 | 296K| 93690 (2)| 00:04:53 | |* 6 | HASH JOIN | | 1779 | 272K| 93690 (2)| 00:04:53 | | 7 | TABLE ACCESS FULL | TS$ | 172 | 1548 | 54 (2)| 00:00:01 | | 8 | NESTED LOOPS | | 1779 | 257K| 93635 (2)| 00:04:53 | |* 9 | HASH JOIN | | 6571 | 757K| 80450 (3)| 00:04:12 | |* 10 | FILTER | | | | | | |* 11 | HASH JOIN RIGHT OUTER | | 7221 | 423K| 10278 (6)| 00:00:33 | | 12 | TABLE ACCESS FULL | USER$ | 3200 | 54400 | 27 (0)| 00:00:01 | | 13 | TABLE ACCESS FULL | OBJ$ | 3854K| 158M| 10133 (5)| 00:00:32 | | 14 | VIEW | SYS_OBJECTS | 3507K| 194M| 70065 (2)| 00:03:40 | | 15 | UNION-ALL | | | | | | |* 16 | TABLE ACCESS FULL | TAB$ | 210K| 5548K| 15995 (2)| 00:00:51 | | 17 | TABLE ACCESS FULL | TABPART$ | 148K| 2895K| 727 (3)| 00:00:03 | | 18 | TABLE ACCESS FULL | CLU$ | 10 | 150 | 14128 (2)| 00:00:45 | |* 19 | TABLE ACCESS FULL | IND$ | 750K| 16M| 16045 (2)| 00:00:51 | | 20 | TABLE ACCESS FULL | INDPART$ | 620K| 11M| 2424 (4)| 00:00:08 | |* 21 | TABLE ACCESS FULL | LOB$ | 2273 | 50006 | 15929 (2)| 00:00:50 | | 22 | TABLE ACCESS FULL | TABSUBPART$ | 269K| 5261K| 932 (4)| 00:00:03 | | 23 | TABLE ACCESS FULL | INDSUBPART$ | 1503K| 28M| 3868 (5)| 00:00:13 | | 24 | TABLE ACCESS FULL | LOBFRAG$ | 2977 | 65494 | 17 (0)| 00:00:01 | |* 25 | TABLE ACCESS CLUSTER | SEG$ | 1 | 30 | 2 (0)| 00:00:01 | |* 26 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 (0)| 00:00:01 | |* 27 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 8 | 0 (0)| 00:00:01 | | 28 | NESTED LOOPS | | 1 | 109 | 2274 (1)| 00:00:08 | | 29 | NESTED LOOPS | | 1 | 101 | 2274 (1)| 00:00:08 | |* 30 | FILTER | | | | | | |* 31 | HASH JOIN OUTER | | 1 | 92 | 2273 (1)| 00:00:08 | | 32 | NESTED LOOPS | | 568 | 42600 | 2245 (1)| 00:00:08 | |* 33 | TABLE ACCESS FULL | UNDO$ | 1116 | 45756 | 5 (0)| 00:00:01 | |* 34 | TABLE ACCESS CLUSTER | SEG$ | 1 | 34 | 2 (0)| 00:00:01 | |* 35 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 (0)| 00:00:01 | | 36 | TABLE ACCESS FULL | USER$ | 3200 | 54400 | 27 (0)| 00:00:01 | | 37 | TABLE ACCESS CLUSTER | TS$ | 1 | 9 | 1 (0)| 00:00:01 | |* 38 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 | |* 39 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 8 | 0 (0)| 00:00:01 | |* 40 | HASH JOIN | | 996 | 77688 | 14672 (1)| 00:00:46 | | 41 | TABLE ACCESS FULL | TS$ | 172 | 1548 | 54 (2)| 00:00:01 | |* 42 | FILTER | | | | | | |* 43 | HASH JOIN RIGHT OUTER | | 996 | 68724 | 14618 (1)| 00:00:46 | | 44 | TABLE ACCESS FULL | USER$ | 3200 | 54400 | 27 (0)| 00:00:01 | | 45 | NESTED LOOPS | | 531K| 26M| 14574 (1)| 00:00:46 | | 46 | TABLE ACCESS FULL | FILE$ | 872 | 10464 | 3 (0)| 00:00:01 | |* 47 | TABLE ACCESS CLUSTER | SEG$ | 610 | 24400 | 23 (0)| 00:00:01 | |* 48 | INDEX RANGE SCAN | I_FILE#_BLOCK# | 1 | | 2 (0)| 00:00:01 | | 49 | VIEW | DBA_TAB_STATISTICS | 42 | 1932 | 2828 (6)| 00:00:09 | | 50 | U</span> </div> <div class="pagination mt-40"> <a href="bencandy.php?fid=57&aid=85379&page=1" title="首页">首页</A> <a href="bencandy.php?fid=57&aid=85379&page=1" title="上一页">上一页</A> <a href='#'><font color=red>1</font></a> <a href="bencandy.php?fid=57&aid=85379&page=2" title="第2页">2</a> <a href="bencandy.php?fid=57&aid=85379&page=2" title="下一页">下一页</A> <a href="bencandy.php?fid=57&aid=85379&page=2" title="尾页">尾页</A> <a href='#'><font color=red>1</font>/2/2</a> </div> <div class="article-footer mt-60 pt-40"> <div class="share-box">  <div class="bdsharebuttonbox"><a href="#" class="bds_more" data-cmd="more"></a><a href="#" class="bds_qzone" data-cmd="qzone" title="分享到QQ空间"></a><a href="#" class="bds_tsina" data-cmd="tsina" title="分享到新浪微博"></a><a href="#" class="bds_tqq" data-cmd="tqq" title="分享到腾讯微博"></a><a href="#" class="bds_renren" data-cmd="renren" title="分享到人人网"></a><a href="#" class="bds_weixin" data-cmd="weixin" title="分享到微信"></a></div> <script>window._bd_share_config={"common":{"bdSnsKey":{},"bdText":"","bdMini":"2","bdMiniList":false,"bdPic":"","bdStyle":"0","bdSize":"24"},"share":{}};with(document)0[(getElementsByTagName('head')[0]||body).appendChild(createElement('script')).src='http://bdimg.share.baidu.com/static/api/js/share.js?v=89860593.js?cdnversion='+~(-new Date()/36e5)];</script> </div> <div class="prev-next mt-20"> <div class="prev"> <span class="label">上一篇</span> <a href="bencandy.php?fid=57&id=85380" onclick="" class="link">CentOS系统MySQL优化详解</a> </div> <div class="next"> <span class="label">下一篇</span> <a href="bencandy.php?fid=57&id=85378" onclick="" class="link">Oracle教程：DBA任务---确保统计..</a> </div> </div> </div> </div> </div> </div> </div> <footer class="container mt-20 site-footer"> <div id="copyright"> Copyright © https://www.cppentry.com all rights reserved <a href="http://www.miibeian.gov.cn" target="_blank">粤ICP备13067022号-3</a><br> </div> </footer> <script> // Logic for double click admin editing </script> <script> if(typeof clickEdit !== 'undefined') clickEdit.init(); </script> </body> </html>

一次访问Oracle数据字典的优化(一)