sql执行计划解析案例(二)
来源:互联网 发布:知乎 我爱男保姆 编辑:程序博客网 时间:2024/06/06 22:07
sql执行计划解析案例(二)
SQL> select addr,ts#,file#,dbarfil,dbablk,tch from x$bh where rownum<20 order by tch desc 2 ;ADDR TS# FILE# DBARFIL DBABLK TCH---------------- ---------- ---------- ---------- ---------- ----------00007F64CC0825A0 0 1 1 8210 1800007F64CC0825A0 0 1 1 233 1000007F64CC0825A0 0 1 1 95203 400007F64CC0825A0 0 1 1 4571 300007F64CC0825A0 0 1 1 95436 200007F64CC0825A0 0 1 1 77851 200007F64CC0825A0 0 1 1 52289 100007F64CC0825A0 0 1 1 65536 100007F64CC0825A0 1 2 2 42914 100007F64CC0825A0 0 1 1 96368 100007F64CC0825A0 0 1 1 57093 1ADDR TS# FILE# DBARFIL DBABLK TCH---------------- ---------- ---------- ---------- ---------- ----------00007F64CC0825A0 0 1 1 22156 100007F64CC0825A0 0 1 1 34704 100007F64CC0825A0 0 1 1 17119 100007F64CC0825A0 0 1 1 30133 100007F64CC0825A0 0 1 1 38809 100007F64CC0825A0 0 1 1 21224 100007F64CC0825A0 0 1 1 17818 100007F64CC0825A0 0 1 1 55928 119 rows selected.SQL> set autotrace trace explainSQL> r 1 select addr,ts#,file#,dbarfil,dbablk,tch from x$bh where rownum<20 order by tch desc 2*Execution Plan----------------------------------------------------------Plan hash value: 2913638504---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 19 | 1349 | 1 (100)| 00:00:01 || 1 | SORT ORDER BY | | 19 | 1349 | 1 (100)| 00:00:01 ||* 2 | COUNT STOPKEY | | | | | || 3 | FIXED TABLE FULL| X$BH | 19 | 1349 | 0 (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter(ROWNUM<20)SQL>
eg:
SQL> select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum<20 2 ;
Execution Plan----------------------------------------------------------Plan hash value: 2453498899
--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 19 | 1349 | 1 (100)| 00:00:01 ||* 1 | COUNT STOPKEY | | | | | || 2 | VIEW | | 100 | 7100 | 1 (100)| 00:00:01 ||* 3 | SORT ORDER BY STOPKEY| | 100 | 7100 | 1 (100)| 00:00:01 || 4 | FIXED TABLE FULL | X$BH | 100 | 7100 | 0 (0)| 00:00:01 |--------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
1 - filter(ROWNUM<20) 3 - filter(ROWNUM<20)
SQL> set autotrace offSQL> r 1 select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum<20 2*
ADDR TS# FILE# DBARFIL DBABLK TCH---------------- ---------- ---------- ---------- ---------- ----------00007F64CBFCD840 0 1 1 2017 16200007F64CBFCD840 0 1 1 2016 16100007F64CBFCD840 0 1 1 3025 5400007F64CBFCD840 0 1 1 3073 5000007F64CBFCD840 0 1 1 385 5000007F64CBFCD840 0 1 1 169 5000007F64CBFCD840 0 1 1 345 4900007F64CBFCD840 0 1 1 3057 4900007F64CBFCD840 0 1 1 337 4900007F64CBFCD840 0 1 1 481 4900007F64CBFCD840 0 1 1 46461 48
ADDR TS# FILE# DBARFIL DBABLK TCH---------------- ---------- ---------- ---------- ---------- ----------00007F64CBFCD840 0 1 1 2945 4600007F64CBFCD840 0 1 1 489 4300007F64CBFCD840 0 1 1 170 4200007F64CBFCD840 0 1 1 577 4200007F64CBFCD840 0 1 1 1625 4100007F64CBFCD840 0 1 1 490 4100007F64CBFCD840 0 1 1 2946 4100007F64CBFCD840 0 1 1 386 41
19 rows selected.
SQL>
"the run-time engine simply scanned the table, keeping a cache of the top
10 values. It didn’t really sort 1,000,000 rows, it merely checked each row to see if it was larger
than the smallest item in the current cache and should replace it. At the end of the scan, it only
had 10 rows to sort."
这就是这两个sql语句执行计划的区别。
- sql执行计划解析案例(二)
- SQL Server执行计划 解析
- Oracle sql执行计划解析
- 《高性能SQL调优精要与案例解析》一书谈主流关系库SQL调优(优化&TUNING)技术精髓之——执行计划获取及理解
- oracle如何产生sql 执行计划(二)
- sql server execution plan - 执行计划的诡异 ( 二 )
- ORACLE里SQL语句的执行计划(二)
- hive sql执行计划树解析
- Hive SQL执行计划深度解析
- Hive SQL执行计划深度解析
- Hive SQL执行计划深度解析
- Hive SQL执行计划深度解析
- Hive SQL执行计划深度解析
- Hive SQL执行计划深度解析
- Hive 之 SQL执行计划深度解析
- HIVE SQL执行计划深度解析
- Oracle之深入浅出(二)--SQL原理、解释计划与执行计划
- ORACLE执行计划(二)
- org.xml.sax.SAXParseException: Content is not allowed in prolog
- 列表产品添加链接页代码
- jQuery练习实例(四)
- 去掉搜狗拼音烦人的x+;进入搜狗搜索
- 苹果10月22日发布iPad5 使Surface2发售遇冷
- sql执行计划解析案例(二)
- linker command failed with exit code 1 (use -v to see invocation)
- 变更管理组织机构与作用
- 收集的一些程序员的漫画
- MMC不能打开文件SQL Server企业管理器问题解决方法
- float,double和decimal类型
- 变更控制委员会
- 《Linux内核设计与实现》学习笔记之“Linux进程管理机制”
- 修改 MyEclipse 编辑区、控制台背景颜色以及隐藏烦人的breadcrumb(面包屑)