- 浏览: 70548 次
- 性别:
- 来自: 杭州
文章分类
最新评论
Understanding Oracle QUERY PLAN
What is Query Execution Plan?
When you fire an SQL query to Oracle, Oracle first comes up with a query execution plan in order to fetch the desired data from the physical tables. This query execution plan is crucial as different execution plan take different time for the query to execute.
Oracle Query Execution Plan actually depends on the choice of Oracle optimizer – Rule based (RBO) Or Cost based (CBO) Optimizer. For Oracle 10g, CBO is the default optimizer. Cost Based optimizer enforces Oracle to generate the optimization plan by taking all the related table statistics into consideration. On the other hand, RBO uses a fixed set of pre-defined rules to generate the query plan. Obviously such fixed set of rules might not always be accurate to come up with most efficient plan, as actual plan depends a lot on the nature and volume of tables’ data.
Understanding Oracle Query Execution Plan
But this article is not for comparing RBO and CBO (In fact, there is not much point in comparing these two). This article will briefly help you understand,
- How can we see Query Execution Plan
- How do we understand (or rather interpret) the execution plan.
So let’s begin. I will be using Oracle 10g server and SQL *Plus client to demonstrate all the details.
Oracle Full Table Scan (FTS)
Let’s start by creating a simple product table with the following structure,
ID number(10) NAME varchar2(100) DESCRIPTION varchar2(255) SERVICE varchar2(30) PART_NUM varchar2(50) LOAD_DATE date
Next I will insert 15,000 records into this newly created table (data taken from one of my existing product table from one of my client’s production environment).
Remember, currently there is no index on the table.
So we start our journey by writing a simple select statement on this table as below,
SQL> explain plan for select * from product; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------- Plan hash value: 3917577207 ------------------------------------- | Id | Operation | Name | ------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL | PRODUCT| ------------------------------------- Note ----- - rule based optimizer used (consider using cbo)
Notice that optimizer has decided to use RBO instead of CBO as Oracle does not have any statistics for this table. Let’s now build some statistics for this table by issuing the following command,
SQL> Analyze table product compute statistics;
Now let’s do the same experiment once again,
SQL> explain plan for select * from product; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ----------------------------------------------------- Plan hash value: 3917577207 ----------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ----------------------------------------------------- | 0 | SELECT STATEMENT | | 15856 | 1254K| | 1 | TABLE ACCESS FULL | PRODUCT | 15856 | 1254K| -----------------------------------------------------
You can easily see that this time optimizer has used Cost Based Optimizer (CBO) and has also detailed some additional information (e.g. Rows etc.)
The point to note here is, Oracle is reading the whole table (denoted by TABLE ACCESS FULL) which is very obvious because the select * statement that is being fired is trying to read everything. So, there’s nothing interesting up to this point.
Index Unique Scan
Now let’s add a WHERE clause in the query and also create some additional indexes on the table.
SQL> create unique index idx_prod_id on product (id) compute statistics; Index created. SQL> explain plan for select id from product where id = 100; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------- Plan hash value: 2424962071 --------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | --------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | |* 1 | INDEX UNIQUE SCAN | IDX_PROD_ID | 1 | 4 | ---------------------------------------------------------
So the above statement indicates that CBO is performing Index Unique Scan. This means, in order to fetch the id value as requested, Oracle is actually reading the index only and not the whole table. Of course this will be faster than FULL TABLE ACCESS operation shown earlier.
Table Access by Index RowID
Searching the index is a fast and an efficient operation for Oracle and when Oracle finds the desired value it is looking for (in this case id=100), it can also find out the rowid of the record in product table that has id=100. Oracle can then use this rowid to fetch further information if requested in query. See below,
SQL> explain plan for select * from product where id = 100; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------- Plan hash value: 3995597785 ---------------------------------------------------------- | Id | Operation | Name |Rows | Bytes| ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 81 | | 1 | TABLE ACCESS BY INDEX ROWID| PRODUCT| 1 | 81 | |* 2 | INDEX UNIQUE SCAN | IDX_PROD_ID | 1 | | ----------------------------------------------------------
TABLE ACCESS BY INDEX ROWID is the interesting part to check here. Since now we have specified select * for id=100, so Oracle first use the index to obtain the rowid of the record. And then it selects all the columns by the rowid.
Index Range Scan
But what if we specify a >, or between criteria in the WERE clause instead of equality condition? Like below,
SQL> explain plan for select id from product where id <10 Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------- Plan hash value: 1288034875 ------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 28 | |* 1 | INDEX RANGE SCAN| IDX_PROD_ID | 7 | 28 | -------------------------------------------------------
So this time CBO goes for an Index Range Scan instead of INDEX UNIQUE SCAN. The same thing will normally happen if we use a between clause also.
Index Fast Full Scan
Now, let’s see another interesting aspect of INDEX scan here by just altering the “ 10”. Before we see the outcome, just remind yourself that there are 15000 over products with their ids starting from 1 to 15000+. So if we write “10” we are likely to get almost 14990+ records in return. So does Oracle go for an INDEX RANGE SCAN in this case? Let’s see,
SQL> explain plan for select id from product where id>10; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------ Plan hash value: 2179322443 -------------------------------------------------------- | Id | Operation | Name | Rows |Bytes | -------------------------------------------------------- | 0 | SELECT STATEMENT | | 15849|63396 | |* 1 | INDEX FAST FULL SCAN| IDX_PROD_ID| 15849|63396 | ---------------------------------------------------------
So, Oracle is actually using a INDEX FAST FULL SCAN to “quickly” scan through the index and return the records from table.
Note
FTS
- Whole table is read upto high water mark
- Uses multiblock input/output
- Buffer from FTS operation is stored in LRU end of buffer cache
Index Unique Scan
- Single block input/output
Index Fast Full Scan
- Multi block i/o possible
- Returned rows might not be in sorted order
Index Full Scan
- Single block i/o
- Returned rows can be in sorted order
So I think we covered the basics of simple SELECT queries running on a single table. We will move forward to understand how the query plan changes when we join more than one table. This I will cover up in the next article. Happy reading!
发表评论
-
关于Oracle 版本
2015-10-10 10:23 0第一部分是“Version Number",也就是产 ... -
了解Oracle数据库的版本号
2015-10-10 10:20 0Major Database Release ... -
PDF 资料
2013-03-13 15:45 0Java design pattern --Bob ... -
Oracle sys和system用户、sysdba 和sysoper系统权限、sysdba和dba角色的区别 [转]
2013-03-12 14:17 1022sys和system用户区别 1)最重要的区别,存储的数 ... -
Oracle 用户、对象权限、系统权限 [转]
2013-03-12 14:12 0--============================ ... -
表分区分割脚本
2013-03-12 13:10 726表分区分割脚本 -
Oracle Session 视图[转]
2013-03-06 10:17 975v$session v$session_wait v$ ... -
10G中查看历史执行计划信息[转]
2013-03-01 11:02 3760现在总结下10G的,使用的是AWR报告中的信息,主要是查询 ... -
Oracle 表连接 [转]
2013-02-26 15:20 647Oracle 表之间的连接分为三种: 1. 内连接(自然 ... -
oracle的number类型精度、刻度范围 [转]
2013-02-26 15:06 5250一、 oracle 的 number 类型精度、刻度范围 ... -
Oracle Tablespace
2012-11-29 16:53 01. 几个重要的TableSpace SYSTE ... -
[转]Optimizing SPLIT PARTITION and SPLIT SUBPARTITION Operations
2012-11-27 15:11 910Optimizing SPLIT PARTITION and ... -
Oracle splitting partitions简单小结[转]
2012-11-27 15:12 994http://www.oracleonlinux.cn/201 ... -
When the explanation doesn't sound quite right
2012-10-30 13:05 0When the explanatio ... -
oracle中join的用法 .
2012-10-10 11:43 0oracle中join的用法8i: create ... -
[转]Oracle中Left Outer Join和外关联(+)的区别
2012-11-27 15:15 837外关联是Oracle数据库的专有语句 Left Outer ... -
[转]关于ORACLE的锁表与解锁总结
2012-09-29 11:11 0总结1:Oracle的锁表与解锁 selects.userna ... -
not in/not exists 的 null 陷阱
2012-09-27 11:07 0[转]not in/not exists 的 nul ... -
Oracle Database Link Tutorials,Examples to create, use, manage and drop them[转]
2012-09-21 10:54 0Oracle Database Link TutorialsE ... -
Oracle AUTOTRACE Explained
2012-01-06 11:04 998Oracle AUTOTRACE Explained ...
相关推荐
标题《Understanding Oracle RAC Internals - The Cache Fusion Edition》涉及的主题是Oracle RAC内部机制,特别是Cache Fusion技术。Oracle RAC(Real Application Clusters)是Oracle数据库的一个重要特性,它允许...
This new edition of Understanding Oracle APEX 5 Application Development shows APEX developers how to build practical, non-trivial web applications. The book introduces the world of APEX properties, ...
Understanding Oracle Locking InternalsArup Nanda Longtime Oracle DBA@ArupNandaCopyrightIMPORTANT Please feel free to use the material presented here foryour benefit. All I ask is please attribute the...
Oracle® Fusion Middleware Understanding Oracle Application Development Framework 12c (12.2.1.3.0) E80020-01 August 2017
### 知识点一:查询意图的理解及其重要性 在搜索引擎技术中,理解用户的查询意图是提高搜索结果质量的关键步骤之一。用户输入的查询词往往包含丰富的语义信息,但这些信息并不总是显而易见的。...
总结来说,"Temporal Query Networks for Fine-Grained Video Understanding"这篇论文提出了一种新颖的时间查询网络模型,该模型通过时间注意力机制和随机特征银行更新策略,有效地解决了视频中精细动作识别的问题。...
This preface describes the document accessibility features and conventions used in this guide—Understanding Domain Configuration for Oracle WebLogic Server.
Understanding Storage 参数是 Oracle 数据库管理员和开发者的一项基本技能,本文将通过图表和实例的阐述来解释 Oracle 创建表时 Storage 参数的具体含义。 Storage 参数的基本概念 在 Oracle 中,每个表空间都有...
problems at the right moment and in a methodological way, but also why understanding business needs and problems is essential. It also describes the most common database-related design problems that ...
Read this short, 150-page book that is adapted from Expert Oracle Database Architecture to gain a solid and accurate understanding of how locking and concurrency are dealt with by Oracle Database....
Numerous books have been written about C. They usually offer a broad ...That higher level of understanding for C can only be achieved with a solid understanding of pointers and the management of memory.
根据提供的文件信息,可以得知“Understanding Understanding”是由Heinz von Foerster所著的一本书籍,该书以“Essays on Cybernetics and Cognition”为副标题,涉及的领域是认知理论与人工智能。书名中的...
Having a basic understanding of databases and the use of Business Intelligence is expected, but no knowledge of Oracle BI is required. What You Will Learn Install OBIEE in Windows, including how to ...
"Oracle数据块结构分析说明" Oracle 数据块结构是 Oracle 数据库...Understanding the structure of Oracle data blocks is essential for database administrators to manage and optimize database performance.