1 2 3 Previous Next 30 Replies Latest reply: Apr 9, 2014 4:48 PM by 63b5b83c-15d7-44c5-b8d4-08f856de5236 Go to original post RSS
      • 15. Re: Partition Pruning not working properly or not as expected
        63b5b83c-15d7-44c5-b8d4-08f856de5236

        racle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

         

        Sometimes is awful.  Its easy to falsely conclude thats it been fixed.  Its also tricky to test if the problem doesnt happen.  I have tried to isolate as many factors as possible (doing it off hours to minimize load, tried both RAC nodes, etc...)  Clearly something has to be instructing the optimizer to behave differently, but just not sure how to diagnose what might be effecting it.  Using an equality instead of the IN subquery always works without exception as far as I have seen.

        • 16. Re: Partition Pruning not working properly or not as expected
          jgarry

          OK, that version of Oracle has many possibilities.  I suggest reading the Maria Colgan optimizer and Jonathan Lewis blogs, and there are many people here who can help you if you follow the HOW TO: Post a SQL statement tuning request - template posting

          • 17. Re: Partition Pruning not working properly or not as expected
            rp0428

            That plan appears to be the one that works; not the problem one.

            Doing a join actually behaves the same way.

            You have to TRY to be specific when you provide information. What does 'behaves the same way' mean? Does it work: yes/no?

             

            If it works why not join the tables - you haven't posted an example of doing that.

             

            Similarly you could try using a table expression

            WITH q as (select block_id from rkp_css_dictionary3 pp where table_name ='ZAP_SOLE1_DS_TXN_VW' and rownum = 1)

            select count(*),max(block_id) from ZAP_SOLE1_DS_TXN_VW_C01 ct, q where ct.block_id = q.block_id;

            • 18. Re: Partition Pruning not working properly or not as expected
              63b5b83c-15d7-44c5-b8d4-08f856de5236

              Sorry to be more clear doing a join does not work.  If it did it would be a great solution as well.  I actually tried your suggestion along with materialized hints in the past and never had any success with this query.  I am running it again now so I can provide the a-rows and e-rows as well.

               

               

               

              EVERCLEAR  07-APR-2014 18:09:26>

              WITH q as (select block_id from rkp_css_dictionary3 pp where table_name ='ZAP_SOLE1_DS_TXN_VW' and rownum = 1) select /*+ gather_plan_statistics */ count(*),max(q.block_id) from ZAP_SOLE1_DS_TXN_VW_C01 ct, q where ct.block_id = q.block_id;

              select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

               

               

              Elapsed: 00:06:20.60

               

              -----------------------------------------------------------------------------------------------------------------------------------------------------

              | Id  | Operation                   | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

              -----------------------------------------------------------------------------------------------------------------------------------------------------

              |   0 | SELECT STATEMENT            |                         |      1 |        |      1 |00:06:20.29 |     907K|    347K|       |       |          |

              |   1 |  SORT AGGREGATE             |                         |      1 |      1 |      1 |00:06:20.29 |     907K|    347K|       |       |          |

              |   2 |   PX COORDINATOR            |                         |      1 |        |      8 |00:06:20.27 |     907K|    347K|       |       |          |

              |   3 |    PX SEND QC (RANDOM)      | :TQ10001                |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |

              |   4 |     SORT AGGREGATE          |                         |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |

              |   5 |      NESTED LOOPS           |                         |      0 |    215K|      0 |00:00:00.01 |       0 |      0 |       |       |          |

              |   6 |       BUFFER SORT           |                         |      0 |        |      0 |00:00:00.01 |       0 |      0 | 73728 | 73728 |          |

              |   7 |        PX RECEIVE           |                         |      0 |        |      0 |00:00:00.01 |       0 |      0 |       |       |          |

              |   8 |         PX SEND BROADCAST   | :TQ10000                |      0 |        |      0 |00:00:00.01 |       0 |      0 |       |       |          |

              |   9 |          VIEW               |                         |      1 |      1 |      1 |00:00:00.01 |       2 |      2 |       |       |          |

              |* 10 |           COUNT STOPKEY     |                         |      1 |        |      1 |00:00:00.01 |       2 |      2 |       |       |          |

              |* 11 |            TABLE ACCESS FULL| RKP_CSS_DICTIONARY3     |      1 |      1 |      1 |00:00:00.01 |       2 |      2 |       |       |          |

              |  12 |       PX BLOCK ITERATOR     |                         |      0 |    215K|      0 |00:00:00.01 |       0 |      0 |       |       |          |

              |* 13 |        TABLE ACCESS FULL    | ZAP_SOLE1_DS_TXN_VW_C01 |      0 |    215K|      0 |00:00:00.01 |       0 |      0 |       |       |          |

              -----------------------------------------------------------------------------------------------------------------------------------------------------

               

               

              Predicate Information (identified by operation id):

              ---------------------------------------------------

               

               

                10 - filter(ROWNUM=1)

                11 - filter("TABLE_NAME"='ZAP_SOLE1_DS_TXN_VW')

                13 - access(:Z>=:Z AND :Z<=:Z)

                     filter("CT"."BLOCK_ID"="Q"."BLOCK_ID")

               

              Is there anything else I can provide to help diagnose this?    Seeing the E-Rows with 215K vs A-Rows as 0 is most puzzling to me. 

              • 19. Re: Partition Pruning not working properly or not as expected
                rp0428
                Seeing the E-Rows with 215K vs A-Rows as 0 is most puzzling to me. 

                That is puzzling to me too since it is MUCH LOWER than anything you posted before.

                 

                Before the bad one had 3981M and the good one had  24M. So why is this so much lower?

                 

                Just shooting in the dark now - try specifying a single partition in the query and see how the plans and times compare

                SELECT * FROM myTable partition for (xxx)

                SELECT * FROM myTable partition myPartitionName

                That will at least ensure that only ONE partition is being used.

                • 20. Re: Partition Pruning not working properly or not as expected
                  63b5b83c-15d7-44c5-b8d4-08f856de5236

                  That was a good idea to try I hadnt thought of.  The results come back very quickly.


                  WITH q as (select block_id from rkp_css_dictionary3 pp where table_name ='ZAP_SOLE1_DS_TXN_VW' and rownum = 1) select /*+ gather_plan_statistics */ count(*),max(q.block_id) from ZAP_SOLE1_DS_TXN_VW_C01 partition for (262578) ct, q where ct.block_id = q.block_id;

                   

                   

                    COUNT(*) MAX(Q.BLOCK_ID)

                  ---------- ---------------

                       69038          262578

                   

                   

                  Elapsed: 00:00:00.39

                  EVERCLEAR@edwprd  07-APR-2014 18:56:17> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

                   

                   

                  ----------------------------------------------------------------------------------------------------------------------------------------------

                  | Id  | Operation                     | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

                  ----------------------------------------------------------------------------------------------------------------------------------------------

                  |   0 | SELECT STATEMENT              |                         |      1 |        |      1 |00:00:00.29 |      65 |       |       |          |

                  |   1 |  SORT AGGREGATE               |                         |      1 |      1 |      1 |00:00:00.29 |      65 |       |       |          |

                  |   2 |   PX COORDINATOR              |                         |      1 |        |      8 |00:00:00.29 |      65 |       |       |          |

                  |   3 |    PX SEND QC (RANDOM)        | :TQ10002                |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |

                  |   4 |     SORT AGGREGATE            |                         |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |

                  |*  5 |      HASH JOIN                |                         |      0 |    215K|      0 |00:00:00.01 |       0 |  1198K|  1198K|  697K (0)|

                  |   6 |       BUFFER SORT             |                         |      0 |        |      0 |00:00:00.01 |       0 |  2048 |  2048 |          |

                  |   7 |        PART JOIN FILTER CREATE| :BF0000                 |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |

                  |   8 |         PX RECEIVE            |                         |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |

                  |   9 |          PX SEND HASH         | :TQ10000                |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |

                  |  10 |           VIEW                |                         |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |

                  |* 11 |            COUNT STOPKEY      |                         |      1 |        |      1 |00:00:00.01 |       2 |       |       |          |

                  |* 12 |             TABLE ACCESS FULL | RKP_CSS_DICTIONARY3     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |

                  |  13 |       PX RECEIVE              |                         |      0 |    215K|      0 |00:00:00.01 |       0 |       |       |          |

                  |  14 |        PX SEND HASH           | :TQ10001                |      0 |    215K|      0 |00:00:00.01 |       0 |       |       |          |

                  |  15 |         PX BLOCK ITERATOR     |                         |      0 |    215K|      0 |00:00:00.01 |       0 |       |       |          |

                  |* 16 |          TABLE ACCESS FULL    | ZAP_SOLE1_DS_TXN_VW_C01 |      0 |    215K|      0 |00:00:00.01 |       0 |       |       |          |

                  ----------------------------------------------------------------------------------------------------------------------------------------------

                   

                   

                  Predicate Information (identified by operation id):

                  ---------------------------------------------------

                   

                   

                     5 - access("CT"."BLOCK_ID"="Q"."BLOCK_ID")

                    11 - filter(ROWNUM=1)

                    12 - filter("TABLE_NAME"='ZAP_SOLE1_DS_TXN_VW')

                    16 - access(:Z>=:Z AND :Z<=:Z)

                  • 21. Re: Partition Pruning not working properly or not as expected
                    rp0428

                    Umm - are you changing things on us again?

                     

                    You have added a 'gather_plan_statistics' hint to those last two queries. That hint did NOT appear in your previous queries.

                     

                    You have to compare 'apples' to 'apples' - if you change the query by adding hints you can no longer compare the results to anything you had before.

                     

                    Debugging is ALL about comparison - you HAVE to know what you are comparing or the results you get don't mean anything.

                     

                    Either retest your original queries (good and bad) with that hint or remove it from that last test.


                    • 22. Re: Partition Pruning not working properly or not as expected
                      63b5b83c-15d7-44c5-b8d4-08f856de5236

                      Sorry, I have trying a number different things, but I had run it both ways.  Here is the last one again without that hint...


                      WITH q as (select block_id from rkp_css_dictionary3 pp where table_name ='ZAP_SOLE1_DS_TXN_VW' and rownum = 1) select count(*),max(q.block_id) from ZAP_SOLE1_DS_TXN_VW_C01 partition for (262578) ct, q where ct.block_id = q.block_id;

                       

                       

                        COUNT(*) MAX(Q.BLOCK_ID)

                      ---------- ---------------

                           69038          262578

                       

                       

                      -----------------------------------------------------------------------------------------------------

                      | Id  | Operation                     | Name                    | E-Rows |  OMem |  1Mem | Used-Mem |

                      -----------------------------------------------------------------------------------------------------

                      |   0 | SELECT STATEMENT              |                         |        |       |       |          |

                      |   1 |  SORT AGGREGATE               |                         |      1 |       |       |          |

                      |   2 |   PX COORDINATOR              |                         |        |       |       |          |

                      |   3 |    PX SEND QC (RANDOM)        | :TQ10002                |      1 |       |       |          |

                      |   4 |     SORT AGGREGATE            |                         |      1 |       |       |          |

                      |*  5 |      HASH JOIN                |                         |    215K|   888K|   888K|          |

                      |   6 |       BUFFER SORT             |                         |        | 73728 | 73728 |          |

                      |   7 |        PART JOIN FILTER CREATE| :BF0000                 |      1 |       |       |          |

                      |   8 |         PX RECEIVE            |                         |      1 |       |       |          |

                      |   9 |          PX SEND HASH         | :TQ10000                |      1 |       |       |          |

                      |  10 |           VIEW                |                         |      1 |       |       |          |

                      |* 11 |            COUNT STOPKEY      |                         |        |       |       |          |

                      |* 12 |             TABLE ACCESS FULL | RKP_CSS_DICTIONARY3     |      1 |       |       |          |

                      |  13 |       PX RECEIVE              |                         |    215K|       |       |          |

                      |  14 |        PX SEND HASH           | :TQ10001                |    215K|       |       |          |

                      |  15 |         PX BLOCK ITERATOR     |                         |    215K|       |       |          |

                      |* 16 |          TABLE ACCESS FULL    | ZAP_SOLE1_DS_TXN_VW_C01 |    215K|       |       |          |

                      -----------------------------------------------------------------------------------------------------

                       

                       

                      Predicate Information (identified by operation id):

                      ---------------------------------------------------

                       

                       

                         5 - access("CT"."BLOCK_ID"="Q"."BLOCK_ID")

                        11 - filter(ROWNUM=1)

                        12 - filter("TABLE_NAME"='ZAP_SOLE1_DS_TXN_VW')

                        16 - access(:Z>=:Z AND :Z<=:Z)

                      • 23. Re: Partition Pruning not working properly or not as expected
                        Bono

                        Hi,

                         

                        Try this

                        select /*+ gather_plan_statistics no_parallel  */

                        count(*),max(block_id)

                        from ZAP_SOLE1_DS_TXN_VW_C01 ct

                        where block_id in (select block_id from rkp_css_dictionary3 pp where table_name ='ZAP_SOLE1_DS_TXN_VW' and rownum = 1);

                         

                        Regards Bono

                        • 24. Re: Partition Pruning not working properly or not as expected
                          63b5b83c-15d7-44c5-b8d4-08f856de5236

                          That doesnt actually help at all.  Same results as before.

                          • 25. Re: Partition Pruning not working properly or not as expected
                            Bono

                            Hi,

                            What do you mean "same results"?

                            Did you stop parallel processing?

                             

                            Try below test,

                            You can confirm that your problem is due to parallel processing.

                             

                            You can compare in Buffers.

                             

                                              parallel    no_parallel

                            'in'  query        310                 7

                            '='  query            7                 7

                             

                            ※Test Version '11.2.0.1'

                             

                            drop table zap purge;

                            drop table rkp purge;

                             

                            create table zap

                            ( block_id     number,

                            cacheset_id  number,

                            chunk_id varchar2(5)

                            )

                            partition by range (block_id) interval (1)

                            (partition p1  values less than (1))

                            parallel 8

                            ;


                            create table rkp

                            ( cacheset_id   number,

                            block_id      number,

                            table_name    varchar2(26)

                            );

                             

                            insert into zap

                            select level, level, 'a'

                            from dual

                            connect by level<=100;

                             

                            insert into rkp

                            select level, level, 'zap'

                            from dual

                            connect by level<=1

                            union all

                            select level, level, 'a'

                            from dual

                            connect by level<=9;


                            commit;

                             

                            exec dbms_stats.gather_table_stats(user, 'zap', no_invalidate=>false);

                            exec dbms_stats.gather_table_stats(user, 'rkp', no_invalidate=>false);

                             

                            case1 'in' query

                             

                            ※Parallel processing

                             

                            alter session set statistics_level=all;

                             

                            select

                            count(*),max(block_id)

                            from ZAP ct

                            where block_id in (select block_id

                                                     from rkp pp

                                                        where table_name ='ZAP'

                                                         and   rownum = 1);

                             

                            set linesize 200

                            set pagesize 9999

                             

                            select * from table (dbms_xplan.display_cursor(null,null,'allstats cost last +outline +alias'));

                            ----------------------------------------------------------------------------------

                            | Id  | Operation                    | Name     || A-Rows |   A-Time   | Buffers |

                            ----------------------------------------------------------------------------------

                            |   0 | SELECT STATEMENT             |          ||      1 |00:00:00.05 |     310 |

                            |   1 |  SORT AGGREGATE              |          ||      1 |00:00:00.05 |     310 |

                            |   2 |   PX COORDINATOR             |          ||      8 |00:00:00.05 |     310 |

                            |   3 |    PX SEND QC (RANDOM)       | :TQ10001 ||      0 |00:00:00.01 |       0 |

                            |   4 |     SORT AGGREGATE           |          ||      0 |00:00:00.01 |       0 |

                            |   5 |      NESTED LOOPS            |          ||      0 |00:00:00.01 |       0 |

                            |   6 |       BUFFER SORT            |          ||      0 |00:00:00.01 |       0 |

                            |   7 |        PX RECEIVE            |          ||      0 |00:00:00.01 |       0 |

                            |   8 |         PX SEND BROADCAST    | :TQ10000 ||      0 |00:00:00.01 |       0 |

                            |   9 |          VIEW                | VW_NSO_1 ||      0 |00:00:00.01 |       7 |

                            |  10 |           HASH UNIQUE        |          ||      0 |00:00:00.01 |       7 |

                            |* 11 |            COUNT STOPKEY     |          ||      0 |00:00:00.01 |       7 |

                            |* 12 |             TABLE ACCESS FULL| RKP      ||      0 |00:00:00.01 |       7 |

                            |  13 |       PX BLOCK ITERATOR      |          ||      0 |00:00:00.01 |       0 |

                            |* 14 |        TABLE ACCESS FULL     | ZAP      ||      0 |00:00:00.01 |       0 |

                            ----------------------------------------------------------------------------------


                            Query Block Name / Object Alias (identified by operation id):

                            -------------------------------------------------------------

                               1 - SEL$5DA710D3

                               9 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3

                              10 - SEL$683B0107

                              12 - SEL$683B0107 / PP@SEL$2

                              14 - SEL$5DA710D3 / CT@SEL$1

                             

                            Outline Data

                            -------------


                            /*+

                                  BEGIN_OUTLINE_DATA

                                  IGNORE_OPTIM_EMBEDDED_HINTS

                                  OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

                                  DB_VERSION('11.2.0.1')

                                  ALL_ROWS

                                  OUTLINE_LEAF(@"SEL$683B0107")

                                  OUTLINE_LEAF(@"SEL$5DA710D3")

                                  UNNEST(@"SEL$2")

                                  OUTLINE(@"SEL$2")

                                  OUTLINE(@"SEL$1")

                                  NO_ACCESS(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")

                                  FULL(@"SEL$5DA710D3" "CT"@"SEL$1")

                                  LEADING(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3" "CT"@"SEL$1")

                                  USE_NL(@"SEL$5DA710D3" "CT"@"SEL$1")

                                 PQ_DISTRIBUTE(@"SEL$5DA710D3" "CT"@"SEL$1" BROADCAST NONE)

                                  FULL(@"SEL$683B0107" "PP"@"SEL$2")

                                  USE_HASH_AGGREGATION(@"SEL$683B0107")

                                  END_OUTLINE_DATA

                              */

                             

                            Predicate Information (identified by operation id):

                            ---------------------------------------------------

                              11 - filter(ROWNUM=1)

                              12 - filter("TABLE_NAME"='ZAP')

                              14 - access(:Z>=:Z AND :Z<=:Z)

                                   filter("BLOCK_ID"="BLOCK_ID")

                             

                            ※No parallel processing

                             

                            select /*+ no_parallel */

                            count(*),max(block_id)

                            from ZAP ct

                            where block_id in (select block_id

                                                        from  rkp pp

                                                      where table_name ='ZAP'

                                                        and   rownum = 1);  

                              

                            --------------------------------------------------------------------------------

                            | Id  | Operation                  | Name     || A-Rows |   A-Time   | Buffers |

                            --------------------------------------------------------------------------------

                            |   0 | SELECT STATEMENT           |          ||      1 |00:00:00.01 |       7 |

                            |   1 |  SORT AGGREGATE            |          ||      1 |00:00:00.01 |       7 |

                            |   2 |   NESTED LOOPS             |          ||      0 |00:00:00.01 |       7 |

                            |   3 |    VIEW                    | VW_NSO_1 ||      0 |00:00:00.01 |       7 |

                            |   4 |     HASH UNIQUE            |          ||      0 |00:00:00.01 |       7 |

                            |*  5 |      COUNT STOPKEY         |          ||      0 |00:00:00.01 |       7 |

                            |*  6 |       TABLE ACCESS FULL    | RKP      ||      0 |00:00:00.01 |       7 |

                            |   7 |    PARTITION RANGE ITERATOR|          ||      0 |00:00:00.01 |       0 |

                            |*  8 |     TABLE ACCESS FULL      | ZAP      ||      0 |00:00:00.01 |       0 |

                            --------------------------------------------------------------------------------

                             

                            Query Block Name / Object Alias (identified by operation id):

                            -------------------------------------------------------------

                               1 - SEL$5DA710D3

                               3 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3

                               4 - SEL$683B0107

                               6 - SEL$683B0107 / PP@SEL$2

                               8 - SEL$5DA710D3 / CT@SEL$1

                             

                            Outline Data

                            -------------

                              /*+

                                  BEGIN_OUTLINE_DATA

                                  IGNORE_OPTIM_EMBEDDED_HINTS

                                  OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

                                  DB_VERSION('11.2.0.1')

                                  ALL_ROWS

                                  OUTLINE_LEAF(@"SEL$683B0107")

                                  OUTLINE_LEAF(@"SEL$5DA710D3")

                                  UNNEST(@"SEL$2")

                                  OUTLINE(@"SEL$2")

                                  OUTLINE(@"SEL$1")

                                  NO_ACCESS(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")

                                  FULL(@"SEL$5DA710D3" "CT"@"SEL$1")

                                  LEADING(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3" "CT"@"SEL$1")

                                  USE_NL(@"SEL$5DA710D3" "CT"@"SEL$1")

                                  FULL(@"SEL$683B0107" "PP"@"SEL$2")

                                  USE_HASH_AGGREGATION(@"SEL$683B0107")

                                  END_OUTLINE_DATA

                              */


                            Predicate Information (identified by operation id):

                            ---------------------------------------------------

                               5 - filter(ROWNUM=1)

                               6 - filter("TABLE_NAME"='ZAP')

                               8 - filter("BLOCK_ID"="BLOCK_ID") 

                              

                            case2 '=' query  

                              

                            ※Parallel processing

                             

                            alter session set statistics_level=all;

                             

                            select 

                            count(*),max(block_id)

                            from ZAP ct

                            where block_id = (select block_id

                                                        from rkp pp

                                                      where table_name ='ZAP'

                                                         and rownum = 1);                   

                             

                            set linesize 200

                            set pagesize 9999

                             

                            select * from table (dbms_xplan.display_cursor(null,null,'allstats cost last +outline +alias'));

                              ------------------------------------------------------------------------------

                            | Id  | Operation                | Name     || A-Rows |   A-Time   | Buffers |

                            ------------------------------------------------------------------------------

                            |   0 | SELECT STATEMENT         |          ||      1 |00:00:00.01 |       7 |

                            |   1 |  SORT AGGREGATE          |          ||      1 |00:00:00.01 |       7 |

                            |   2 |   PX COORDINATOR         |          ||      1 |00:00:00.01 |       7 |

                            |   3 |    PX SEND QC (RANDOM)   | :TQ10000 ||      1 |00:00:00.01 |       0 |

                            |   4 |     SORT AGGREGATE       |          ||      1 |00:00:00.01 |       0 |

                            |   5 |      PX BLOCK ITERATOR   |          ||      0 |00:00:00.01 |       0 |

                            |*  6 |       TABLE ACCESS FULL  | ZAP      ||      0 |00:00:00.01 |       0 |

                            |*  7 |        COUNT STOPKEY     |          ||      0 |00:00:00.01 |       7 |

                            |*  8 |         TABLE ACCESS FULL| RKP      ||      0 |00:00:00.01 |       7 |

                            ------------------------------------------------------------------------------

                             

                            Query Block Name / Object Alias (identified by operation id):

                            -------------------------------------------------------------

                               1 - SEL$1

                               6 - SEL$1 / CT@SEL$1

                               7 - SEL$2

                               8 - SEL$2 / PP@SEL$2

                             

                             

                            Outline Data

                            -------------

                              /*+

                                  BEGIN_OUTLINE_DATA

                                  IGNORE_OPTIM_EMBEDDED_HINTS

                                  OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

                                  DB_VERSION('11.2.0.1')

                                  ALL_ROWS

                                  OUTLINE_LEAF(@"SEL$2")

                                  OUTLINE_LEAF(@"SEL$1")

                                  FULL(@"SEL$1" "CT"@"SEL$1")

                                  PUSH_SUBQ(@"SEL$2")

                                  FULL(@"SEL$2" "PP"@"SEL$2")

                                  END_OUTLINE_DATA

                              */  

                             

                            Predicate Information (identified by operation id):

                            ---------------------------------------------------

                               6 - access(:Z>=:Z AND :Z<=:Z)

                                   filter("BLOCK_ID"=)

                               7 - filter(ROWNUM=1)

                               8 - filter("TABLE_NAME"='ZAP')  


                            ※No Parallel processing


                            alter session set statistics_level=all;

                             

                            select /*+ no_parallel */

                            count(*),max(block_id)

                            from ZAP ct

                            where block_id = (select block_id

                                                         from rkp pp

                                                      where table_name ='ZAP'

                                                          and rownum = 1);                   

                             

                            set linesize 200

                            set pagesize 9999

                             

                            select * from table (dbms_xplan.display_cursor(null,null,'allstats cost last +outline +alias'));

                            -------------------------------------------------------------------------

                            | Id  | Operation               | Name || A-Rows |   A-Time   | Buffers |

                            -------------------------------------------------------------------------

                            |   0 | SELECT STATEMENT        |      ||      1 |00:00:00.01 |       7 |

                            |   1 |  SORT AGGREGATE         |      ||      1 |00:00:00.01 |       7 |

                            |   2 |   PARTITION RANGE SINGLE|      ||      0 |00:00:00.01 |       7 |

                            |*  3 |    TABLE ACCESS FULL    | ZAP  ||      0 |00:00:00.01 |       0 |

                            |*  4 |     COUNT STOPKEY       |      ||      0 |00:00:00.01 |       7 |

                            |*  5 |      TABLE ACCESS FULL  | RKP  ||      0 |00:00:00.01 |       7 |

                            -------------------------------------------------------------------------

                             

                            Query Block Name / Object Alias (identified by operation id):

                            -------------------------------------------------------------

                               1 - SEL$1

                               3 - SEL$1 / CT@SEL$1

                               4 - SEL$2

                               5 - SEL$2 / PP@SEL$2

                             

                            Outline Data

                            -------------

                              /*+

                                  BEGIN_OUTLINE_DATA

                                  IGNORE_OPTIM_EMBEDDED_HINTS

                                  OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

                                  DB_VERSION('11.2.0.1')

                                  ALL_ROWS

                                  OUTLINE_LEAF(@"SEL$2")

                                  OUTLINE_LEAF(@"SEL$1")

                                  FULL(@"SEL$1" "CT"@"SEL$1")

                                  PUSH_SUBQ(@"SEL$2")

                                  FULL(@"SEL$2" "PP"@"SEL$2")

                                  END_OUTLINE_DATA

                              */

                             

                            Predicate Information (identified by operation id):

                            ---------------------------------------------------

                               3 - filter("BLOCK_ID"=)

                               4 - filter(ROWNUM=1)

                               5 - filter("TABLE_NAME"='ZAP')

                             

                            Regards Bono

                            • 26. Re: Partition Pruning not working properly or not as expected
                              Bono

                              You can use for stopping parallel processing below alter clause instead of "no_parallel" hint.

                               

                              alter session disable parallel query;

                               

                              Regards Bono

                              • 27. Re: Partition Pruning not working properly or not as expected
                                63b5b83c-15d7-44c5-b8d4-08f856de5236

                                Thanks so much for that info, that seems to work.  I was going to look into things a bit more, but can i use parallel still if i increase the size of that list?

                                • 28. Re: Partition Pruning not working properly or not as expected
                                  63b5b83c-15d7-44c5-b8d4-08f856de5236

                                  So thats really great info and looking at it closer:

                                   

                                  This works fast (parallelism off)

                                  select /*+ parallel (ct,1,1) */ count(*),max(block_id) from ZAP_SOLE1_DS_TXN_VW_C01 ct where block_id in (select 262578 from dual);

                                   

                                   

                                  This doesnt (parallelism set to two)

                                  select /*+ parallel (ct,2,1) */ count(*),max(block_id) from ZAP_SOLE1_DS_TXN_VW_C01 ct where block_id in (select 262578 from dual);

                                   

                                  I am happy to post explain plans but any suggestion on why that behavior would be different?  Any factors we could research?

                                  • 29. Re: Partition Pruning not working properly or not as expected
                                    ddf_dba_ifox

                                    Nice plans but they still do not provide the PSTART and PEND values.  Those values report where the partition scans begin and end and will indicate if the query is pruning partitions or running a full table scan on all available partitions.  Without that  information no one can say how the optimizer is behaving (or 'misbehaving').

                                     

                                    Please post  the parallel plans for both queries.

                                     

                                     

                                    David Fitzjarrell