This discussion is archived
9 Replies Latest reply: Jan 17, 2013 9:41 PM by Nikolay Savvinov RSS

selecting from partition is taking too much time

user12236189 Newbie
Currently Being Moderated
The SEARCH_MEMBE_PROVIDER_ROLLUP is partitioned with range-list partition
ROLLUP_ID --range (about 30 partitions ) and other column with list subpartitioned (about 48)

*indexes on columns
b-tree global index on ROLLUP_ID
bitmap local index on ROLLUP_ID, MEMBERONE_ID_1010
bitmap local index on MEMBERONE_ID_1010*


I could not figured it out as some partition's data is coming fast and some partition's data is not coming at all(taking 18 minutes)

any help would be greatly appreciated and please let me know if need more information

Please see the following information
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE     10.2.0.4.0     Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> explain plan for SELECT COUNT (1)
  2  FROM (SELECT LY.MEMBERONE_ID_1010
  3          FROM (SELECT *
  4                  FROM (SELECT dr.*
  5                          FROM MEMBERONE_last1year dr)) ly
  6         WHERE 1 = 1
  7               AND LY.MEMBERONE_ID_1010 IN (SELECT RP.MEMBERONE_ID_1010
  8                                              FROM SEARCH_MEMBE_PROVIDER_ROLLUP RP
  9                                             WHERE RP.rollup_id = 7));

Explained.

SQL> @utlxplp

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 677376773

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                           | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                                |     1 |    18 |    84 |       |       |
|   1 |  SORT AGGREGATE                 |                                |     1 |    18 |       |       |       |
|   2 |   NESTED LOOPS SEMI             |                                |   154K|  2707K|    84 |       |       |
|   3 |    INDEX FULL SCAN              | IX1_MEMBERONE_LAST1YEAR        |  2701K|    20M|    75 |       |       |
|   4 |    PARTITION RANGE SINGLE       |                                | 64749 |   632K|    84 |     7 |     7 |
|   5 |     PARTITION LIST ALL          |                                | 64749 |   632K|    84 |     1 |    48 |
|   6 |      BITMAP CONVERSION TO ROWIDS|                                | 64749 |   632K|    84 |       |       |
|*  7 |       BITMAP INDEX SINGLE VALUE | IX3_SEARCH_MEMBE_PROVIDER_ROLL |       |       |       |   289 |   336 |
------------------------------------------------------------------------------------------------------------------

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

   7 - access("RP"."ROLLUP_ID"=7 AND "DR"."MEMBERONE_ID_1010"="RP"."MEMBERONE_ID_1010")

Note
-----
   - cpu costing is off (consider enabling it)

23 rows selected.


*THE ABOVE QUERY is taking more than 18 minutes*




========================
*THE BELOW QUERY is taking not even two seconds*


SQL> SELECT COUNT (1)
  2  FROM (SELECT LY.MEMBERONE_ID_1010
  3          FROM (SELECT *
  4                  FROM (SELECT dr.*
  5                          FROM MEMBERONE_last1year dr)) ly
  6         WHERE 1 = 1
  7               AND LY.MEMBERONE_ID_1010 IN (SELECT RP.MEMBERONE_ID_1010
  8                                              FROM SEARCH_MEMBE_PROVIDER_ROLLUP RP
  9                                             WHERE RP.rollup_id = 11));

  COUNT(1)
----------
    131575

Elapsed: 00:00:01.53
SQL> explain plan for SELECT COUNT (1)
  2  FROM (SELECT LY.MEMBERONE_ID_1010
  3          FROM (SELECT *
  4                  FROM (SELECT dr.*
  5                          FROM MEMBERONE_last1year dr)) ly
  6         WHERE 1 = 1
  7               AND LY.MEMBERONE_ID_1010 IN (SELECT RP.MEMBERONE_ID_1010
  8                                              FROM SEARCH_MEMBE_PROVIDER_ROLLUP RP
  9                                             WHERE RP.rollup_id = 11));

Explained.

Elapsed: 00:00:00.03
SQL> @utlxplp

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3092271485

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                         | Rows  | Bytes | Cost  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                              |     1 |    18 |    80 |       |       |        |      |            |
|   1 |  SORT AGGREGATE           |                              |     1 |    18 |       |       |       |        |      |            |
|   2 |   PX COORDINATOR          |                              |       |       |       |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10002                     |     1 |    18 |       |       |       |  Q1,02 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |                              |     1 |    18 |       |       |       |  Q1,02 | PCWP |            |
|*  5 |      HASH JOIN RIGHT SEMI |                              | 70780 |  1244K|    80 |       |       |  Q1,02 | PCWP |            |
|   6 |       PX RECEIVE          |                              |   208K|  2034K|     6 |       |       |  Q1,02 | PCWP |            |
|   7 |        PX SEND HASH       | :TQ10001                     |   208K|  2034K|     6 |       |       |  Q1,01 | P->P | HASH       |
|   8 |         PX BLOCK ITERATOR |                              |   208K|  2034K|     6 |     1 |    48 |  Q1,01 | PCWC |            |
|*  9 |          TABLE ACCESS FULL| SEARCH_MEMBE_PROVIDER_ROLLUP |   208K|  2034K|     6 |   481 |   528 |  Q1,01 | PCWP |            |
|  10 |       BUFFER SORT         |                              |       |       |       |       |       |  Q1,02 | PCWC |            |
|  11 |        PX RECEIVE         |                              |  2701K|    20M|    75 |       |       |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH      | :TQ10000                     |  2701K|    20M|    75 |       |       |        | S->P | HASH       |
|  13 |          INDEX FULL SCAN  | IX1_MEMBERONE_LAST1YEAR      |  2701K|    20M|    75 |       |       |        |      |            |
---------------------------------------------------------------------------------------------------------------------------------------

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

   5 - access("DR"."MEMBERONE_ID_1010"="RP"."MEMBERONE_ID_1010")
   9 - filter("RP"."ROLLUP_ID"=11)

Note
-----
   - cpu costing is off (consider enabling it)

30 rows selected.

Elapsed: 00:00:00.54
SQL> spool off
thanks again
  • 1. Re: selecting from partition is taking too much time
    damorgan Oracle ACE Director
    Currently Being Moderated
    Two thoughts in no particular order:

    1. 10.2.0.4? Why? ... and this is a serious question ... not only is all of 10gR# in desupport mode but this isn't even a fully patched antique.

    2. What is this?
          SELECT dr.*
          FROM MEMBERONE_last1year dr)) ly
          WHERE 1 = 1
    WHERE 1=1?

    and when I look at four nested SQL statements I can not find a single justification for not doing this in one step ... simply put you are returning "1" if something is found ... nothing more. Why not just SELECT 1 in the first place and use EXISTS so the first time it finds a match it stops processing rather than having so much overhead?
  • 2. Re: selecting from partition is taking too much time
    APC Oracle ACE
    Currently Being Moderated
    The second query is running a parallel query. This much is clear from the explain plan.

    So the question becomes, why didn't the first query run in parallel? That will be something in your environment - either the way you configured the session or perhaps transient load on the server meant parallelization was not feasible. Have you been able to reproduce these plans (and the concommitant performance profiles) on more than one occasion?

    Of course this also means that your partitions are not optimises for performance, on at least for the performance of the query you're running. This is not surprising: partritioning is mainly a management and availability feature. If it happens to improve the performance of some queries that's a bonus. Because it is just as likely to diminish the performance of some queries, specifically any query which doesn't include the partition key in its criteria.

    Cheers, APC
  • 3. Re: selecting from partition is taking too much time
    APC Oracle ACE
    Currently Being Moderated
    damorgan wrote:
    1. 10.2.0.4? Why? ... and this is a serious question ... not only is all of 10gR# in desupport mode but this isn't even a fully patched antique.
    Dan, such things are simple for ACE Directors working in academe but I'm afriad things are a lot more complicated out in the real world.

    Database upgrades are expensive undertakings, especially if you have to co-ordinate them across multiple systems. Even when 10g goes into extended support organisations may consider the additional support charges to be worth the money, compared to the cost of an upgrade. I'm not saying those organisations are right, I'm saying they are a fact on the ground.

    Anyway, the chances are, people are still running 10.2.0.4 because that was the version they downloaded from OTN. It's not patched because they don't have a support contract and so don't care whether 10g is about to go into desupport mode. (Not saying that's the case with the OP here, it's just a general observation).

    Cheers, APC
  • 4. Re: selecting from partition is taking too much time
    damorgan Oracle ACE Director
    Currently Being Moderated
    I haven't taught at the university in years ... my current consulting clients, and I never stopped consulting in the "real world" was one of the largest retailers on the planet.

    I am very aware of real-world issues. Patching from 10.2.0.4 to 10.2.0.5 isn't one of them. There is, quite literally, no excuse.

    PS: To the best of my knowledge 10.2.0.4 was never available as a download.
  • 5. Re: selecting from partition is taking too much time
    rp0428 Guru
    Currently Being Moderated
    1. Are the table and index stats up to date? How did you gather the stats? Did you create any histograms?

    2. How many total records in each of the tables involved in the query?

    3. How many records have RP.rollup_id = 11? RP.rollup_id = 7?
  • 6. Re: selecting from partition is taking too much time
    APC Oracle ACE
    Currently Being Moderated
    damorgan wrote:
    my current consulting clients, ... one of the largest retailers on the planet.
    Apologies for doubting your real world credentials but your profile still reads as though you're doing training. Perhaps you need to patch it ;)

    However, what pertains in the "one of the largest retailers on the planet" is pretty much an edge case compared to IT policy in most organisations.
    I am very aware of real-world issues. Patching from 10.2.0.4 to 10.2.0.5 isn't one of them. There is, quite literally, no excuse.
    I think the thrust of your remark as asking why the OP was on 10g rather than 11g. That's a rather diiferent scale of issue than patching to a point release.
    PS: To the best of my knowledge 10.2.0.4 was never available as a download.
    Hmmm, I think you're right. I must have been got confused with 9.2.0.4.

    Cheers, APC
  • 7. Re: selecting from partition is taking too much time
    user12236189 Newbie
    Currently Being Moderated
    APC,

    You do not need to go all the way up or down, simply from the above plan as for those queries (both are same just the rollup_id is different)
    and
    BOTH are giving different plan

    do you know the answer for that?
    all indexes as well as the table analyzed
    each partition may be the range from 30000 to 10000000
    the problem with particular partition (7) is having about 600K, all others partitions even 7000000 comes so fast and clean

    thanks
  • 8. Re: selecting from partition is taking too much time
    542159 Newbie
    Currently Being Moderated
    Hi,

    Based on your search criteria, are the partitions pruned properly ?

    Can you provide the following details ?

    1. table structure
    2. table size
    3. Index keys
  • 9. Re: selecting from partition is taking too much time
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi

    1) there are two major differences between the plans: serial vs parallel and NESTED LOOPS SEMI vs HASH JOIN SEMI
    2) NESTED LOOPS look like a bad choice here, and it also looks like the optimizer badly underestimates it's real cost. Which is a bit odd, because it knows that the outer rowsource has 2701K, i.e. the inner operation would have to be executed 2701K times. Looks like an optimizer glitch, which brings us to the point that Dan has already raised here: you shouldn't be running on an old unsupported version
    3) regarding serial vs parallel: it could be due to difference in session settings (ALTER SESSION ENABLE/DISABLE PARALLEL QUERY)
    4) the two plans have different values of the filter against rollup_id (7 vs 11), so one possible reason for different behavior is the histogram on SEARCH_MEMBE_PROVIDER_ROLLUP.rollup_id. If you can reproduce the issue on a test database, re-gather stats on this table without histograms (method_opt=>'for all columns size 1', no_invalidate=>false) and see if the problem goes away

    Best regards,
    Nikolay

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points