Hi Guys,
A word of Caution for reader - Below is a article i have writen on reading Explain plans.If you have good experience ( I mean you can write and debug sql query very very easily ) then only you should read this advance stuff.Its involves a lot of detailed discussion.Not for freshers.I am not discouraging anyone but for less experienced people it wont make much sense.
Some Notes before we start
Two things we need to tune sql (Explain plan and Statistics IO) ......Since Statistics IO is not set by default.Below are steps to enable it
If you are having difficulties understanding terms discussed in this article.Please go through below link
http://cognossimplified.blogspot.in/2013/07/oracle-database-concepts.html)
Below are steps to enable Statistics IO if you dont have it enabled.(Usually DBA would have enabled this )
http://cognossimplified.blogspot.in/2013/07/how-to-enable-statistics-io-in-sql.html
Example of Explain plan and Statistics IO
create table t ( a int, b int, c char(20), d char(20), e int );
Insert into t select 1, 1, 1, 1, rownum from all_objects ;( it will create a dummy table with lot of records)
create index a_indx on t (a) compute statistics;
set autotrace on
select a from t where a =1
Plan hash value: 2016457929
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74275 | 942K| 44 (3)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| A_INDX | 74275 | 942K| 44 (3)| 00:00:01 |
-------------------------------------------------------------------------------
Statistics
-----------------------------------------------------------
13 user calls
0 physical read total bytes
0 physical write total bytes
0 spare statistic 3
0 commit cleanout failures: cannot pin
0 TBS Extension: bytes extended
0 total number of times SMON posted
0 SMON posted for undo segment recovery
0 SMON posted for dropping temp segment
0 segment prealloc tasks
Very Important link gives the various terms used in explain plan in detail.
http://www.akadia.com/services/ora_interpreting_explain_plan.html
What is Index fast full scan ---They are similar to full table scans that is they read the full index and while doing so oracle will fetch the next multiple blocks in anticipation that they will be required.It makes use of flag db_file_multiblock_read . Similar to full table scan.It is used when we dont even need to touch db to get our data .See in this case the the physical read is zero.we got our result just from index scan as the column requested in select statement is a index column .
In index fast full scan index is read as a table. Normally in a index with mutliple leaf nodes we go from one node to other but in fast full scan we read all nodes. Not necessary in order and it will use multiblock i/o that is it will read from multiple blocks
Now lets change the column to one which is not indexed let see the response
select b from t where b =1
Notice that it goes for full table scan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74275 | 942K| 171 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 74275 | 942K| 171 (1)| 00:00:03 |
--------------------------------------------------------------------------
Two links which you might like
http://www.dwbiconcepts.com/database/22-database-oracle/26-oracle-query-plan-a-10-minutes-guide.html
The main idea to improve speed of query is to get the result by going through as little physical reads ( from disk) as possible.
We do indexing , partition all things so that we can easily identify our data from millions of rows.Without having to go through each row.So the idea behind reading explain plan is identifying which part of sql is not using best way to read from db.There might be one table which is doing full scan(reading entire table) to reach at a particular row (say employee id) .Now how can we make it reach it faster , may be by not having to go through entire table and just reading one row.There are number of techniques out there.
For datawarehouse a Bitmap index may speed up {My Actual case study on DW} .OR indexes on a particular column which is forcing a full table read may speed up. So lets look what is a explain plan and What is Statistics IO.
A word of Caution for reader - Below is a article i have writen on reading Explain plans.If you have good experience ( I mean you can write and debug sql query very very easily ) then only you should read this advance stuff.Its involves a lot of detailed discussion.Not for freshers.I am not discouraging anyone but for less experienced people it wont make much sense.
Some Notes before we start
Two things we need to tune sql (Explain plan and Statistics IO) ......Since Statistics IO is not set by default.Below are steps to enable it
If you are having difficulties understanding terms discussed in this article.Please go through below link
http://cognossimplified.blogspot.in/2013/07/oracle-database-concepts.html)
Below are steps to enable Statistics IO if you dont have it enabled.(Usually DBA would have enabled this )
http://cognossimplified.blogspot.in/2013/07/how-to-enable-statistics-io-in-sql.html
Example of Explain plan and Statistics IO
create table t ( a int, b int, c char(20), d char(20), e int );
Insert into t select 1, 1, 1, 1, rownum from all_objects ;( it will create a dummy table with lot of records)
create index a_indx on t (a) compute statistics;
set autotrace on
select a from t where a =1
Plan hash value: 2016457929
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74275 | 942K| 44 (3)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| A_INDX | 74275 | 942K| 44 (3)| 00:00:01 |
-------------------------------------------------------------------------------
Statistics
-----------------------------------------------------------
13 user calls
0 physical read total bytes
0 physical write total bytes
0 spare statistic 3
0 commit cleanout failures: cannot pin
0 TBS Extension: bytes extended
0 total number of times SMON posted
0 SMON posted for undo segment recovery
0 SMON posted for dropping temp segment
0 segment prealloc tasks
Very Important link gives the various terms used in explain plan in detail.
http://www.akadia.com/services/ora_interpreting_explain_plan.html
What is Index fast full scan ---They are similar to full table scans that is they read the full index and while doing so oracle will fetch the next multiple blocks in anticipation that they will be required.It makes use of flag db_file_multiblock_read . Similar to full table scan.It is used when we dont even need to touch db to get our data .See in this case the the physical read is zero.we got our result just from index scan as the column requested in select statement is a index column .
In index fast full scan index is read as a table. Normally in a index with mutliple leaf nodes we go from one node to other but in fast full scan we read all nodes. Not necessary in order and it will use multiblock i/o that is it will read from multiple blocks
select
count(distinct deptno) from t
and either of EMPNO
or DEPTNO is defined as "not null" -- we may very well use the INDEX
via a FAST FULL INDEX SCAN over the table (the index being a "skinny version" of the
table in this case.
via a FAST FULL INDEX SCAN over the table (the index being a "skinny version" of the
table in this case.
Now lets change the column to one which is not indexed let see the response
select b from t where b =1
Notice that it goes for full table scan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74275 | 942K| 171 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 74275 | 942K| 171 (1)| 00:00:03 |
--------------------------------------------------------------------------
Now lets go for condition on A .We have a index on A
select * from t where A >1
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 83 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 83 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | A_INDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
select * from t where A >1
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 83 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 83 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | A_INDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Two links which you might like
http://www.dwbiconcepts.com/database/22-database-oracle/26-oracle-query-plan-a-10-minutes-guide.html
No comments:
Post a Comment