This is continuation of my previous post : http://vkoracle.blogspot.com/2012/08/query-not-using-index-1-of-4.html
Parallel Option:
With Parallel option optimizer thinks full table scans are inexpensive than index scans. Caution should be taken setting Parallelism at table level as it can have profound impact on your system as it forces optimizer to use full table scan then to use index scan wherever possible..
Parallelism should be set at session level or as a query hint.
Example:
SQL>Drop table test
SQL>Create table test( id number, name varchar(10));
SQL>Insert into test
Select round(DBMS_RANDOM.VALUE(1,5)), upper(DBMS_RANDOM.STRING('A',10)) NAME from all_objects;
SQL>commit;
-- Lets create index on a table
SQL>Create index idx_test_id on test(id);
SQL>Create index idx_test_name on test(name);
--Gather statistics on tables
SQL>EXEC
dbms_stats.gather_table_stats(user, 'test', method_opt=>'for all columns size auto' );
SQL> Select * from test where id=5;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8653 | 118K| 33 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8653 | 118K| 33 (0)|
| 2 | INDEX RANGE SCAN | IDX_TEST_ID | 8653 | | 4 (0)|
--------------------------------------------------------------------------------
Query using index scan and in next step enable parallelism at table level.
SQL>alter table test parallel 4;
Table altered.
SQL> select * from test where id=5;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |I
N-OUT| PQ Distrib |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 8653 | 118K| 14 (0)| |
| |
| 1 | PX COORDINATOR | | | | | |
| |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 8653 | 118K| 14 (0)| Q1,00 |
P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 8653 | 118K| 14 (0)| Q1,00 |
PCWC | |
| 4 | TABLE ACCESS FULL| TEST | 8653 | 118K| 14 (0)| Q1,00 |
PCWP | |
--------------------------------------------------------------------------------
-------------------
Same query from previous example doing full table scan instead of index scan. Lets try same query with parallel hint.
SQL> select /*+ Parallel(test,4)*/ * from test where id=1;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |I
N-OUT| PQ Distrib |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 8482 | 115K| 14 (0)| |
| |
| 1 | PX COORDINATOR | | | | | |
| |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 8482 | 115K| 14 (0)| Q1,00 |
P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 8482 | 115K| 14 (0)| Q1,00 |
PCWC | |
| 4 | TABLE ACCESS FULL| TEST | 8482 | 115K| 14 (0)| Q1,00 |
PCWP | |
--------------------------------------------------------------------------------
-------------------
SQL> select /*+ Parallel(test,4)*/ * from test where id=2;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |I
N-OUT| PQ Distrib |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 17810 | 243K| 14 (0)| |
| |
| 1 | PX COORDINATOR | | | | | |
| |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 17810 | 243K| 14 (0)| Q1,00 |
P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 17810 | 243K| 14 (0)| Q1,00 |
PCWC | |
| 4 | TABLE ACCESS FULL| TEST | 17810 | 243K| 14 (0)| Q1,00 |
PCWP | |
--------------------------------------------------------------------------------
-------------------
SQL> select /*+ Parallel(test,4)*/ * from test where id=5;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |I
N-OUT| PQ Distrib |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 8653 | 118K| 14 (0)| |
| |
| 1 | PX COORDINATOR | | | | | |
| |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 8653 | 118K| 14 (0)| Q1,00 |
P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 8653 | 118K| 14 (0)| Q1,00 |
PCWC | |
| 4 | TABLE ACCESS FULL| TEST | 8653 | 118K| 14 (0)| Q1,00 |
PCWP | |
--------------------------------------------------------------------------------
-------------------
As you can see the parallel hint doing full table scan.
No comments:
Post a Comment