Re: sane number of the table partitions in DWH

From: Milen Kulev <makulev_at_gmx.net>
Date: Wed, 01 Apr 2009 14:52:11 +0200
Message-ID: <20090401125211.294720_at_gmx.net>

Hello Riyaj,
yep, the the limit of partitions per table is 1024k -1 on 10gR2 . My cusormer plans to use RANGE->List partionining scheme. Range on time (period_id),
list on product type (domain_product_id).

period_id -> 280 Partitions (slowly changing over time ) domain_product_id -> 300 distinct values (fix).

This we should get 280x300 = 84000 partitions. I *suppose* we should get reasonable parse times. It must be tested anyway.

Partioning pruning is obviously not for free. Just see what a 10046 event trace file is showing me (trace file processed with orasrp profiler. I have added line numbering to the interesting lines ):

Statement Plan

met 1 time
Rows Row Source Operation [Object Id]

----------  --------------------------------
      632,821  SORT GROUP BY (cr=318,371 pr=260,367 pw=19,710 time=791.3366s)
   10,095,369    HASH JOIN  (cr=318,371 pr=260,367 pw=19,710 time=793.4486s)
   10,095,369      HASH JOIN  (cr=318,265 pr=240,653 pw=0 time=1,090.5466s)
          183        MAT_VIEW ACCESS BY INDEX ROWID ADM_CC_REP_BASE_CHANNEL_MV (cr=162 pr=0 pw=0 time=0.0037s) [381542]
          183          INDEX RANGE SCAN ADM_CC_REP_BASE_CHANNEL_MV_PK (cr=2 pr=0 pw=0 time=0.0006s) [381543]
1) 10,095,369        PARTITION RANGE INLIST PARTITION: KEY(INLIST) KEY(INLIST) (cr=318,103 pr=240,653 pw=0 time=959.3032s)
2) 10,095,369          PARTITION HASH INLIST PARTITION: KEY(INLIST) KEY(INLIST) (cr=318,103 pr=240,653 pw=0 time=888.6356s)
3) 10,095,369            TABLE ACCESS BY LOCAL INDEX ROWID RB_FACT_PD_OUT_ITM PARTITION: KEY(INLIST) KEY(INLIST) (cr=318,103 pr=240,653 pw=0 time=731.4175s) [81746]
4) 10,455,843              BITMAP CONVERSION TO ROWIDS (cr=34,233 pr=14,284 pw=0 time=105.9193s)
5)        87                BITMAP AND  (cr=34,233 pr=14,284 pw=0 time=99.0991s)
         131                  BITMAP OR  (cr=1,073 pr=723 pw=0 time=4.2803s)
       5,843                    BITMAP INDEX SINGLE VALUE RB_FACT_PD_OUT_ITM_DPG_IDX PARTITION: KEY(INLIST) KEY(INLIST) (cr=861 pr=618 pw=0 time=15.4104s) [102014]
         639                    BITMAP INDEX SINGLE VALUE RB_FACT_PD_OUT_ITM_DPG_IDX PARTITION: KEY(INLIST) KEY(INLIST) (cr=212 pr=105 pw=0 time=2.3254s) [102014]
          87                  BITMAP OR  (cr=7,197 pr=651 pw=0 time=5.1466s)
         102                    BITMAP INDEX SINGLE VALUE RB_FACT_PD_OUT_ITM_PRJ_IDX PARTITION: KEY(INLIST) KEY(INLIST) (cr=119 pr=65 pw=0 time=0.3890s) [82658]
           4                    BITMAP INDEX SINGLE VALUE RB_FACT_PD_OUT_ITM_PRJ_IDX PARTITION: KEY(INLIST) KEY(INLIST) (cr=95 pr=2 pw=0 time=0.0118s) [82658]
           9                    BITMAP INDEX SINGLE VALUE RB_FACT_PD_OUT_ITM_PRJ_IDX PARTITION: KEY(INLIST) KEY(INLIST) (cr=102 pr=5 pw=0 time=0.0110s) [82658]
           1                    BITMAP INDEX SINGLE VALUE RB_FACT_PD_OUT_ITM_PRJ_IDX PARTITION: KEY(INLIST) KEY(INLIST) (cr=99 pr=2 pw=0 time=0.0128s) [82658]
         215                    BITMAP INDEX SINGLE VALUE RB_FACT_PD_OUT_ITM_PRJ_IDX PARTITION: KEY(INLIST) KEY(INLIST) (cr=105 pr=5 pw=0 time=0.6705s) [82658]
           1                    BITMAP INDEX SINGLE VALUE RB_FACT_PD_OUT_ITM_PRJ_IDX PARTITION: KEY(INLIST) KEY(INLIST) (cr=101 pr=2 pw=0 time=0.0171s) [82658]
           5                    BITMAP INDEX SINGLE VALUE RB_FACT_PD_OUT_ITM_PRJ_IDX PARTITION: KEY(INLIST) KEY(INLIST) (cr=106 pr=3 pw=0 time=0.0428s) [82658]
         1                      BITMAP INDEX SINGLE VALUE RB_FACT_PD_OUT_ITM_PRJ_IDX PARTITION: KEY(INLIST) KEY(INLIST) (cr=104 pr=1 pw=0 time=0.0092s) [82658]
								....																											
								....

Lines 4) - 5) : Converting 87 a bitmap into rowids takes 105- 99 = 6 seconds. Purely CPU consumption (no additional LIO, PIO etc) Lines 3) - 4) TABLE ACCESS BY LOCAL INDEX ROWID RB_FACT_PD_OUT_ITM PARTITION operation takes 731 - 105 = 626 seconds and pr=240,653 PIOS Not taking into account the CPU consumption of LIOs , each PIO takes 626 000 (ms seconds)/240,653 (PIOs) = ~ 2.6 ms

The SQL statent resouce profile (Top 3 wait events) though:

Statement Flat Profile

Time Per Call --------- Event Name % Time Seconds Calls Avg Min Max ---------------------------------------- -------- ------------ --------- ----------- ----------- ----------- db file sequential read 50.1% 412.9400s 240,657 0.0017s 0.0000s 0.3119s gc cr grant 2-way 28.9% 237.9957s 225,795 0.0010s 0.0000s 0.1638s FETCH calls [CPU] 11.9% 98.3680s 42,190 0.0023s 0.0000s 96.9783s

Avg time per call = 0.0017 sec = 1.7 ms ! The error (taking my calulations as base) is : (2.6-1.6/2.6)*100 = 38% ! It seemd a way too much for me. Now to the most interesting part:

Lines 2) - 3):

2) 10,095,369          PARTITION HASH INLIST PARTITION: KEY(INLIST) KEY(INLIST) (cr=318,103 pr=240,653 pw=0 time=888.6356s)
3) 10,095,369            TABLE ACCESS BY LOCAL INDEX ROWID RB_FACT_PD_OUT_ITM PARTITION: KEY(INLIST) KEY(INLIST) (cr=318,103 pr=240,653 pw=0 time=731.4175s) [81746]

All execution step statistics , apart from time, are the same (pr, cr). The PARTITION HASH INLIST PARTITION step itself takes 888.6356s - 731.4175s = ~ 157 seconds. And this time should be CPU time, but it is NOT accounted as such in the statement reource profile.

Lines 1) -2)

1) 10,095,369        PARTITION RANGE INLIST PARTITION: KEY(INLIST) KEY(INLIST) (cr=318,103 pr=240,653 pw=0 time=959.3032s)
2) 10,095,369          PARTITION HASH INLIST PARTITION: KEY(INLIST) KEY(INLIST) (cr=318,103 pr=240,653 pw=0 time=888.6356s)

The same story. Equal pr and cr. Time difference between steps is 959.3032s - 888.6356s = ~ 71 seconds

There also a sudden ( -297 seconds, this time oracle doesn't consume, but generate time ;( ) time leap between the steps:

10,095,369 HASH JOIN (cr=318,371 pr=260,367 pw=19,710 time=793.4486s) 10,095,369 HASH JOIN (cr=318,265 pr=240,653 pw=0 time=1,090.5466s)

The SQL statement resouce profile:

Statement Self Statistics

           Cache             --------- Seconds --------     Physical  ---- Logical Reads -----           
   Call   Misses      Count           CPU       Elapsed        Reads   Consistent      Current       Rows
-------  -------  ---------  ------------  ------------  -----------  -----------  -----------  ---------
  Parse        1          1       0.0330s       0.0563s            0            0            0          0
   Exec        0          1       0.0010s       0.0008s            0            0            0          0
  Fetch              42,190      98.3680s     790.3672s      260,367      318,371            0    632,821
-------  -------  ---------  ------------  ------------  -----------  -----------  -----------  ---------
  Total        1     42,192      98.4020s     790.4243s      260,367      318,371            0    632,821

Per Fch      0.0        1.0       0.0023s       0.0187s          6.2          7.5          0.0       15.0
Per Row      0.0        0.0       0.0001s       0.0012s          0.4          0.5          0.0        1.0

I am missing something in the whole picture ? From this execution plan I can not get a clear picture how much time gets consuled in each step.

Best Regards. Milen

Milen

Actually, partition count limit in 10gR1 is 64K-1, but 10gR2 is 1024K-1. So, 10.2.0.4 should support 1million+ partitions.

http://cs.felk.cvut.cz/10gr2/server.102/b14237/limits003.htm#sthref4186

I don't know, much about complexity of your application SQL or table design as to whether it is composite partitioned, range or hash partitioned, partition key properties , partition pruning etc, but you might want to take couple of heavily executed SQL and test it. Theoritically speaking, partition pruning is not a costly operation.

I will share my experience though. I had a client with 50K+ partitions in a table (simple range partitioning on a number column) and uses literals heavily. We didn't see much parsing issues when we added (actually, plsql code to split last partition in a binary tree fashion to be exact) 30,000 partitions over a weekend. As long as, partition and global statistics are accurate, you shouldn't see much increase in SQL execution time.

YMMV :-) Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com Specialists in Performance, Recovery and EBS11i Blog: http://orainternals.wordpress.com

-- 
Neu: GMX FreeDSL Komplettanschluss mit DSL 6.000 Flatrate + Telefonanschluss f�r nur 17,95 Euro/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 01 2009 - 07:52:11 CDT

This message: [ Message body ]
Next message: Subodh Deshpande: "Re: strange error on vipca"
Previous message: Andrew Kerber: "Re: strange error on vipca"
Maybe in reply to: Milen Kulev: "Re: sane number of the table partitions in DWH"
Next in thread: Riyaj Shamsudeen: "Re: sane number of the table partitions in DWH"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message