1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Table Partationing Issue

Discussion in 'SQL PL/SQL' started by ravi_joshi235, Aug 31, 2009.

  1. ravi_joshi235

    ravi_joshi235 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I am new in this partationing concept.
    I am using Oracle 10g database.I am creating table partationing in four tables ,but after partationing System giving me even worst effects.I think i am doing somthing wrong.

    I am using four Tables.
    1.perf_plan_kra : 5 Lacs Record
    2.perf_plan_kpi : 11 Lacs Record
    3.plan_kpi_target : 46 Lacs Record
    4.plan_kpi_stretch : 40 Lacs Record

    In Each Table have create Partation in Foreign Key.I used here Range Partationing.

    select ppkr.plan_kra_type,ppki.plan_kpi_measure,pkt.plan_target_type,pks.PLAN_STRETCH_RATING from perf_plan_kra ppkr,perf_plan_kpi ppki,plan_kpi_target pkt,plan_kpi_stretch pks where ppkr.PLAN_KRA_DTL_ID=ppki.PLAN_KPI_KRADTL_ID and ppki.PLAN_KPI_DTL_ID=pkt.PLAN_TARGET_KPIDTL_ID and ppki.PLAN_KPI_DTL_ID=pks.PLAN_STRETCH_KPIDTL_ID and ppkr.PLAN_KRA_PLAN_ID=2767;

    After Executing This Query it takes 10 seconds (Before Partation)
    After Partationing it takes 35 seconds. (After Partation)

    Can Anyone Sugess me is it i am doing wrong.Thanks in Advance.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Show us the scripts you're using to create these partitioned tables and provide sample data so we can possibly create them and test this query. Query plans would also help us determine what Oracle is doing and how any plan steps could be improved.
     
  3. ravi_joshi235

    ravi_joshi235 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Thanks A Lot zargon for your reply.

    I am sending you a script as an attachment which i am using to create these partitioned tables .I am trying to send some sample table structure but i am unable to do that.I think my file size is big (100 kb).If you will provide me your mail id then i'll sended these details onto it.
    Please Review it.If you need any clearification then let me know.
     

    Attached Files:

  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why do you create bigfile tablespaces for your partitions when your initial file size is no more than 10 MB? Bigfile tablespaces can have but one datafile which can extend to a maximum size of 32 GB. Is it such a problem to add space to a tablespace, either by adding datafiles or by extending the existing datafiles? Unrestricted autoextend (as you've done) is a disaster waiting to occur, as you now have 20 datafiles which can grow to 32 GB each before they stop extending due to reaching the maximum allowed size. You could easily run out of space before you reach the maximum allowed size, and risk corrupting these files and the data stored in them.

    You've provided no query plans to prove this performance issue; you've also not posted any information regarding the state of the table statistics. I see no indexes on any of these tables; did you really believe you could not improve the performance by adding indexes on the detail id and plan id columns?

    Please send me the testing data at oratune@yahoo.com. I can do no more until I can populate these tables and execute your query.
     
  5. ravi_joshi235

    ravi_joshi235 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi zargon,
    I have sended table statistic related information in your mail id (oratune@yahoo.com.).what do you mean by "query plans to prove this performance issue ?".

    Thanks.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is the query plan for the non-partitioned table:

    Code (SQL):
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 2616039518

    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name             | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |                  |   811 | 28385 |    11  (19)| 00:00:01 |
    |*  1 |  HASH JOIN                     |                  |   811 | 28385 |    11  (19)| 00:00:01 |
    |*  2 |   HASH JOIN                    |                  |    81 |  2349 |    10  (20)| 00:00:01 |
    |   3 |    MERGE JOIN                  |                  |    16 |   352 |     6  (17)| 00:00:01 |
    |*  4 |     TABLE ACCESS BY INDEX ROWID| PERF_PLAN_KRA    |    11 |   110 |     2   (0)| 00:00:01 |
    |   5 |      INDEX FULL SCAN           | SYS_C0014627     |    11 |       |     1   (0)| 00:00:01 |
    |*  6 |     SORT JOIN                  |                  |    17 |   204 |     4  (25)| 00:00:01 |
    |   7 |      TABLE ACCESS FULL         | PERF_PLAN_KPI    |    17 |   204 |     3   (0)| 00:00:01 |
    |   8 |    TABLE ACCESS FULL           | PLAN_KPI_STRETCH |    85 |   595 |     3   (0)| 00:00:01 |
    |   9 |   INDEX FULL SCAN              | KPITGT3          |   170 |  1020 |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------
     

    This is the plan for the partitioned version:

    Code (SQL):
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 3102901790

    ----------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name             | ROWS  | Bytes | Cost (%CPU)| TIME     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                  |   850 |   105K|    10  (10)| 00:00:01 |       |       |
    |*  1 |  HASH JOIN                  |                  |   850 |   105K|    10  (10)| 00:00:01 |       |       |
    |   2 |   PARTITION RANGE ALL       |                  |    85 |  2210 |     3   (0)| 00:00:01 |     1 |    12 |
    |   3 |    TABLE ACCESS FULL        | PLAN_KPI_STRETCH |    85 |  2210 |     3   (0)| 00:00:01 |     1 |    12 |
    |   4 |   NESTED LOOPS              |                  |   170 | 17170 |     6   (0)| 00:00:01 |       |       |
    |   5 |    NESTED LOOPS             |                  |    17 |  1462 |     4   (0)| 00:00:01 |       |       |
    |   6 |     PARTITION RANGE SINGLE  |                  |    11 |   308 |     3   (0)| 00:00:01 |     1 |     1 |
    |*  7 |      TABLE ACCESS FULL      | PERF_PLAN_KRA    |    11 |   308 |     3   (0)| 00:00:01 |     1 |     1 |
    |   8 |     PARTITION RANGE ITERATOR|                  |     2 |   116 |     0   (0)| 00:00:01 |   KEY |   KEY |
    |*  9 |      TABLE ACCESS FULL      | PERF_PLAN_KPI    |     2 |   116 |     0   (0)| 00:00:01 |   KEY |   KEY |
    |  10 |    PARTITION RANGE ITERATOR |                  |    10 |   150 |     0   (0)| 00:00:01 |   KEY |   KEY |
    |* 11 |     TABLE ACCESS FULL       | PLAN_KPI_TARGET  |    10 |   150 |     0   (0)| 00:00:01 |   KEY |   KEY |
    ----------------------------------------------------------------------------------------------------------------
     
    Both complete in less than one second on my system. I can't duplicate your 'problem'. Do you have such plans from your system for this same query run against a partitioned and non-partitioned table?
     
  7. ravi_joshi235

    ravi_joshi235 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi zargon,

    How can i view Query Plan details for my Sql Query.Please let me know then i'll send you my machine query plan details.

    Thank you for helping me.
     
  8. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    You didn't ask me but...

    The general syntax of EXPLAIN PLAN is:

    Code (SQL):
    EXPLAIN plan FOR your-precious-sql-statement;
    To display the Explain Plan output

    Code (SQL):

    SELECT PLAN_TABLE_OUTPUT
      FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));
     
    For more details, refer:

    http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm