OraFAQ Forum: Performance Tuning » Partition Pruning Not happening!

Home » RDBMS Server » Performance Tuning » Partition Pruning Not happening! (Oracle 10.2.0.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Partition Pruning Not happening! [message #550742]

Thu, 12 April 2012 01:43

orapratap
Messages: 134
Registered: November 2011
Location: Canada

Senior Member

Hello

Why partition pruning is not happening implicitly when I am joining 2 partitioned tables which have same partitioning key?

Also the number of rows in explain plan are far away from reality

Is it because I am gathering stats with wrong method?

create table dbo_part 
(
ID		   NUMBER,
OWNER              VARCHAR2(30),
OBJECT_NAME        VARCHAR2(30),
SUBOBJECT_NAME     VARCHAR2(30),
OBJECT_ID          NUMBER,
DATA_OBJECT_ID     NUMBER,
OBJECT_TYPE        VARCHAR2(19),
CREATED            DATE,
LAST_DDL_TIME      DATE,
TIMESTAMP          VARCHAR2(19),
STATUS             VARCHAR2(7),
TEMPORARY          VARCHAR2(1),
GENERATED          VARCHAR2(1),
SECONDARY          VARCHAR2(1)
)
 partition by list(object_type)
 ( partition part_1 values (  'INDEX', 'CONTEXT', 'TYPE BODY', 'INDEXTYPE', 'PROCEDURE','RULE','JAVA RESOURCE') tablespace drsys,
 partition part_2 values ( 'JAVA CLASS', 'SCHEDULE', 'TABLE PARTITION','WINDOW','WINDOW GROUP' ) tablespace drsys,
partition part_3 values ( 'TABLE', 'TYPE', 'VIEW','SYNONYM' ) tablespace drsys,
partition part_4 values ( 'PACKAGE BODY', 'SEQUENCE', 'INDEX PARTITION','JOB' ) tablespace drsys
 ) ;

create table dbo_part_1
(
ID		   NUMBER,
OWNER              VARCHAR2(30),
OBJECT_NAME        VARCHAR2(30),
SUBOBJECT_NAME     VARCHAR2(30),
OBJECT_ID          NUMBER,
DATA_OBJECT_ID     NUMBER,
OBJECT_TYPE        VARCHAR2(19),
CREATED            DATE,
LAST_DDL_TIME      DATE,
TIMESTAMP          VARCHAR2(19),
STATUS             VARCHAR2(7),
TEMPORARY          VARCHAR2(1),
GENERATED          VARCHAR2(1),
SECONDARY          VARCHAR2(1)
)
 partition by list(object_type)
 ( partition part_1 values (  'INDEX', 'CONTEXT', 'TYPE BODY', 'INDEXTYPE', 'PROCEDURE','RULE','JAVA RESOURCE') tablespace drsys,
 partition part_2 values ( 'JAVA CLASS', 'SCHEDULE', 'TABLE PARTITION','WINDOW','WINDOW GROUP' ) tablespace drsys,
partition part_3 values ( 'TABLE', 'TYPE', 'VIEW','SYNONYM' ) tablespace drsys,
partition part_4 values ( 'PACKAGE BODY', 'SEQUENCE', 'INDEX PARTITION','JOB' ) tablespace drsys
 ) ;

SQL > exec dbms_stats.gather_table_stats(user,'DBO_PART_1',CASCADE=>TRUE,GRANULARITY=>'ALL');

SQL > select * from dbo_part_1 where object_type='VIEW';
Execution Plan
----------------------------------------------------------
Plan hash value: 2983184589

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            | 14013 |  1300K|   182   (2)| 00:00:03 |       |       |
|   1 |  PARTITION LIST SINGLE|            | 14013 |  1300K|   182   (2)| 00:00:03 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | DBO_PART_1 | 14013 |  1300K|   182   (2)| 00:00:03 |     3 |     3 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='VIEW')

SQL > select dp.object_name,dp1.id from dbo_part dp, dbo_part_1 dp1 where  dp.object_type=dp1.object_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 3673923416

----------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |  6263M|   280G|       | 46105  (87)| 00:09:14 |       |    |
|   1 |  PARTITION LIST ALL |            |  6263M|   280G|       | 46105  (87)| 00:09:14 |     1 |     4 |
|*  2 |   HASH JOIN         |            |  6263M|   280G|   616K| 46105  (87)| 00:09:14 |       |    |
|   3 |    TABLE ACCESS FULL| DBO_PART_1 | 96373 |  1317K|       |   320   (1)| 00:00:04 |     1 |     4 |
|   4 |    TABLE ACCESS FULL| DBO_PART   |   974K|    31M|       |  3132   (1)| 00:00:38 |     1 |     4 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DP"."OBJECT_TYPE"="DP1"."OBJECT_TYPE")

SQL > 
SQL > 
SQL > select * from dbo_part_1 where object_type='VIEW';

Execution Plan
----------------------------------------------------------
Plan hash value: 2983184589

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            | 14013 |  1300K|   182   (2)| 00:00:03 |       |       |
|   1 |  PARTITION LIST SINGLE|            | 14013 |  1300K|   182   (2)| 00:00:03 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | DBO_PART_1 | 14013 |  1300K|   182   (2)| 00:00:03 |     3 |     3 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='VIEW')

SQL > select * from dbo_part where object_type='VIEW';

Execution Plan
----------------------------------------------------------
Plan hash value: 97391151

--------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          | 74638 |  6997K|  1738   (1)| 00:00:21 |       |       |
|   1 |  PARTITION LIST SINGLE|          | 74638 |  6997K|  1738   (1)| 00:00:21 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | DBO_PART | 74638 |  6997K|  1738   (1)| 00:00:21 |     3 |     3 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='VIEW')

SQL > select dp.object_name,dp1.id from dbo_part dp, dbo_part_1 dp1 where  dp.object_type=dp1.object_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 3673923416

----------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |  6263M|   280G|       | 46105  (87)| 00:09:14 |       |    |
|   1 |  PARTITION LIST ALL |            |  6263M|   280G|       | 46105  (87)| 00:09:14 |     1 |     4 |
|*  2 |   HASH JOIN         |            |  6263M|   280G|   616K| 46105  (87)| 00:09:14 |       |    |
|   3 |    TABLE ACCESS FULL| DBO_PART_1 | 96373 |  1317K|       |   320   (1)| 00:00:04 |     1 |     4 |
|   4 |    TABLE ACCESS FULL| DBO_PART   |   974K|    31M|       |  3132   (1)| 00:00:38 |     1 |     4 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DP"."OBJECT_TYPE"="DP1"."OBJECT_TYPE")

SQL > select dp.object_name,dp1.id from dbo_part partition(part_3) dp, dbo_part_1 partition(part_3) dp1 where
dp.object_type=dp1.object_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 2444580069

-------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |  7702M|   322G|       | 52583  (94)| 00:10:31 |       |       |
|*  1 |  HASH JOIN             |            |  7702M|   322G|  1376K| 52583  (94)| 00:10:31 |       |       |
|   2 |   PARTITION LIST SINGLE|            | 56053 |   711K|       |   181   (1)| 00:00:03 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | DBO_PART_1 | 56053 |   711K|       |   181   (1)| 00:00:03 |     3 |     3 |
|   4 |   PARTITION LIST SINGLE|            |   549K|    16M|       |  1735   (1)| 00:00:21 |   KEY |   KEY |
|   5 |    TABLE ACCESS FULL   | DBO_PART   |   549K|    16M|       |  1735   (1)| 00:00:21 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DP"."OBJECT_TYPE"="DP1"."OBJECT_TYPE")

SQL > exec dbms_stats.gather_table_stats(user,'DBO_PART_1',CASCADE=>TRUE,GRANULARITY=>'ALL');

PL/SQL procedure successfully completed.

SQL > exec dbms_stats.gather_table_stats(user,'DBO_PART',CASCADE=>TRUE,GRANULARITY=>'ALL');

PL/SQL procedure successfully completed.

SQL > 
SQL > select dp.object_name,dp1.id from dbo_part partition(part_3) dp, dbo_part_1 partition(part_3) dp1 where
dp.object_type=dp1.object_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 2444580069

-------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |    17G|   741G|       |   116K (98)| 00:23:19 |       |       |
|*  1 |  HASH JOIN             |            |    17G|   741G|  1376K|   116K (98)| 00:23:19 |       |       |
|   2 |   PARTITION LIST SINGLE|            | 56053 |   711K|       |   181   (1)| 00:00:03 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | DBO_PART_1 | 56053 |   711K|       |   181   (1)| 00:00:03 |     3 |     3 |
|   4 |   PARTITION LIST SINGLE|            |   553K|    16M|       |  1735   (1)| 00:00:21 |   KEY |   KEY |
|   5 |    TABLE ACCESS FULL   | DBO_PART   |   553K|    16M|       |  1735   (1)| 00:00:21 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DP"."OBJECT_TYPE"="DP1"."OBJECT_TYPE")

SQL > select dp.object_name,dp1.id from dbo_part dp, dbo_part_1 dp1 where  dp.object_type=dp1.object_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 3673923416

----------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |    27G|  1248G|       |   184K (97)| 00:36:57 |       |    |
|   1 |  PARTITION LIST ALL |            |    27G|  1248G|       |   184K (97)| 00:36:57 |     1 |     4 |
|*  2 |   HASH JOIN         |            |    27G|  1248G|   616K|   184K (97)| 00:36:57 |       |    |
|   3 |    TABLE ACCESS FULL| DBO_PART_1 | 96373 |  1317K|       |   320   (1)| 00:00:04 |     1 |     4 |
|   4 |    TABLE ACCESS FULL| DBO_PART   |   974K|    31M|       |  3132   (1)| 00:00:38 |     1 |     4 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DP"."OBJECT_TYPE"="DP1"."OBJECT_TYPE")

SQL > set autotrace off
SQL > 

SQL > select count(*),object_type from dbo_part group by object_type;

  COUNT(*) OBJECT_TYPE
---------- -------------------
     34325 INDEX
        75 CONTEXT
      2200 TYPE BODY
       100 INDEXTYPE
       100 RULE
      7475 PROCEDURE
     15550 JAVA RESOURCE
    329825 JAVA CLASS
        25 SCHEDULE
      5500 TABLE PARTITION
        50 WINDOW
        25 WINDOW GROUP
     33700 TABLE
     25850 TYPE
     74451 VIEW
    416775 SYNONYM
     17650 PACKAGE BODY
      5274 SEQUENCE
       125 JOB
      5800 INDEX PARTITION

20 rows selected.

SQL >
SQL > select count(*),object_type from dbo_part_1 group by object_type;

  COUNT(*) OBJECT_TYPE
---------- -------------------
      4065 INDEX
         7 CONTEXT
       257 TYPE BODY
         8 INDEXTYPE
        12 RULE
       894 PROCEDURE
      1244 JAVA RESOURCE
     29896 JAVA CLASS
         3 SCHEDULE
       647 TABLE PARTITION
         6 WINDOW
         3 WINDOW GROUP
      3988 TABLE
      2995 TYPE
      8842 VIEW
     40228 SYNONYM
      2052 PACKAGE BODY
       529 SEQUENCE
        15 JOB
       682 INDEX PARTITION

20 rows selected.

SQL >

Thanks and Regards
Orapratap

Report message to a moderator

Re: Partition Pruning Not happening! [message #550749 is a reply to message #550742]

Thu, 12 April 2012 02:05

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Quote:

Why partition pruning is not happening

It is happening. You are also getting a partition-wise join.
Quote:

the number of rows in explain plan are far away from reality

your test case doesn't insert any rows, so conformance with "reality" is hard to assess.

Report message to a moderator

Re: Partition Pruning Not happening! [message #550750 is a reply to message #550742]

Thu, 12 April 2012 02:06

orapratap
Messages: 134
Registered: November 2011
Location: Canada

Senior Member

Apologies

My mistake writing the SQL

Please ignore the post

Regards
Orapratap

Report message to a moderator

Re: Partition Pruning Not happening! [message #550751 is a reply to message #550750]

Thu, 12 April 2012 02:08

orapratap
Messages: 134
Registered: November 2011
Location: Canada

Senior Member

Hello John

Our posts crossed

For question on stats :
I have not provided insert but I have provided "select with group by"

Regarding Pruning :
Thanks I got it

Regards
Orapratap

Report message to a moderator

Previous Topic:	sql_id and sql_child_id not are null for SQL statement
Next Topic:	Different explain plan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 04 02:54:46 CST 2025