Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Performance problem with partitioned indexes & tables

Re: Performance problem with partitioned indexes & tables

From: Michael Burden <michael.burden_at_cgey.com>
Date: 12 Aug 2002 14:54:37 -0700
Message-ID: <8ea7fbb6.0208121354.d57e5af@posting.google.com> 





Ok, hopefully this confirms the performance problem with
Oracle&#8217;s iterator to access each partition. Note that I did get
the original performance figures I quoted out by a factor of 10 or so
but the differences are still over 100 times worse.



The PL/SQL snippet reads a policy&#8217;s data 10 times and prints out
the time taken, amount and count of rows using a table with 7
partitions.



    
  
  1.  	The first SQL statement accesses each partition separately to avoid
the use of Oracle&#8217;s iterator step.
  
  2.  	Whilst the second uses Oracle&#8217;s Iterator.






The values returned show that the same number of rows with the same
data is being hit.



The values given are the time taken in seconds to execute the above
SQL statements 10 times. Note that I did two runs.


		SQL 1 	SQL 2 
First run	0.02	2.02
Second run	0.01	1.88





This shows it takes 100 times longer for Oracle to access the data.
This seems to be the case for all our local indexes.



Below is the SQL along with the explain plans



Partition split



TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',



'NLS_CALENDAR=GREGORIA




TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',



'NLS_CALENDAR=GREGORIA




TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',



'NLS_CALENDAR=GREGORIA




TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',



'NLS_CALENDAR=GREGORIA




TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',



'NLS_CALENDAR=GREGORIA




TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',



'NLS_CALENDAR=GREGORIA



declare

 amount number(10,2);
 cnt    number(10,2);
 no     number(10) := 0;




 start_time number(10);


 totrows number(10) := 0;


begin


 /* To confirm the sql returns the same data lets print the sum of the
amounts and the number of rows */


 /* First lets use our own method for iterating through the partitions
*/


 start_time := DBMS_UTILITY.GET_TIME;


 while(no < 10) loop


  select sum(amount),sum(cnt)


  into amount,cnt


  from (

  select 	sum(amount) amount,count(*) cnt from unibas.transtest
  where 	policyn = '0123456789'
  and 	received_date < '20010401'
  union all
  select 	sum(amount),count(*) from unibas.transtest
  where 	policyn = '0123456789'
  and 	received_date between '20010401' and '20010630'
  union all
  select 	sum(amount),count(*) from unibas.transtest
  where 	policyn = '0123456789'
  and 	received_date between '20010701' and '20010930'
  union all
  select 	sum(amount),count(*) from unibas.transtest
  where 	policyn = '0123456789'
  and 	received_date between '20011001' and '20011231'
  union all 
  select 	sum(amount),count(*) from unibas.transtest
  where 	policyn = '0123456789'
  and 	received_date between '20020101' and '20020331'
  union all 
  select 	sum(amount),count(*) from unibas.transtest
  where 	policyn = '0123456789'
  and 	received_date between '20020401' and '20020630'
  union all 
  select 	sum(amount),count(*) from unibas.transtest
  where 	policyn = '0123456789'
  and 	received_date >= '20020701'




  );


  no:= no + 1;


  totrows:=totrows+cnt;


 end loop;


 dbms_output.put_line('Hard coded access of partitions. Time = ' || 
   (DBMS_UTILITY.GET_TIME - START_TIME) || ', ' || amount || ', ' ||
cnt || ', ' || totrows);


 /* Now lets use Oracle's iterator */


 start_time := DBMS_UTILITY.GET_TIME;


 no:=0;


 totrows:=0;


 while(no < 10) loop


  select 	sum(amount),count(*) 


  into amount,cnt


  from unibas.transtest


  where 	policyn = '0123456789'


  ;


  no:= no + 1;


  totrows:=totrows+cnt;


 end loop;


 dbms_output.put_line('Oracles itorator of partitions. Time = ' || 
   (DBMS_UTILITY.GET_TIME - START_TIME) || ', ' || amount || ', ' ||
cnt || ', ' || totrows);


end;


/



SQL VOL1 > SQL VOL1 > /



Hard coded access of partitions. Time = 2, 6732.08, 246, 2460
Oracles itorator of partitions. Time = 194, 6732.08, 246, 2460



PL/SQL procedure successfully completed.



SQL VOL1 > /



Hard coded access of partitions. Time = 2, 6732.08, 246, 2460
Oracles itorator of partitions. Time = 183, 6732.08, 246, 2460



SQL VOL1 > set serveroutput on


SQL VOL1 > @testprog


time = 2,6732.08,246


time = 202,6732.08,246



PL/SQL procedure successfully completed.



SQL VOL1 > /



time = 1,6732.08,246


time = 188,6732.08,246



PL/SQL procedure successfully completed.




SUM(AMOUNT)   SUM(CNT)



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


    6732.08        246




Execution Plan




   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)


   2    1     VIEW (Cost=40 Card=7 Bytes=182)
   3    2       UNION-ALL
   4    3         SORT (AGGREGATE)
   5    4           TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TRANSTEST'
           (Cost=5 Card=48 Bytes=1008)

   6    5             INDEX (RANGE SCAN) OF 'TRANSTEST_IDX3' (NON-UNIQ
          UE) (Cost=4 Card=48)

   7    3         SORT (AGGREGATE)
   7    3         SORT (AGGREGATE)
           (Cost=6 Card=97 Bytes=2037)

   9    8             INDEX (RANGE SCAN) OF 'TRANSTEST_IDX3' (NON-UNIQ
          UE) (Cost=4 Card=97)

  10    3         SORT (AGGREGATE)
  11   10           TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TRANSTEST'
           (Cost=3 Card=47 Bytes=987)

  12   11             INDEX (RANGE SCAN) OF 'TRANSTEST_IDX3' (NON-UNIQ
          UE) (Cost=4 Card=47)

  13    3         SORT (AGGREGATE)
  14   13           TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TRANSTEST'
           (Cost=14 Card=283 Bytes=5943)

  15   14             INDEX (RANGE SCAN) OF 'TRANSTEST_IDX3' (NON-UNIQ
          UE) (Cost=4 Card=283)

  16    3         SORT (AGGREGATE)
  17   16           TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TRANSTEST'
           (Cost=9 Card=92 Bytes=1932)

  18   17             INDEX (RANGE SCAN) OF 'TRANSTEST_IDX3' (NON-UNIQ
          UE) (Cost=4 Card=92)

  19    3         SORT (AGGREGATE)
  20   19           TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TRANSTEST'
           (Cost=2 Card=15 Bytes=315)

  21   20             INDEX (RANGE SCAN) OF 'TRANSTEST_IDX3' (NON-UNIQ
          UE) (Cost=3 Card=15)

  22    3         SORT (AGGREGATE)
  23   22           TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TRANSTEST'
           (Cost=1 Card=2 Bytes=42)

  24   23             INDEX (RANGE SCAN) OF 'TRANSTEST_IDX3' (NON-UNIQ
          UE) (Cost=1 Card=2)









Statistics



          0  recursive calls
          1  rows processed





SUM(AMOUNT)   COUNT(*)



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


    6732.08        246




Execution Plan




   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=15)
   1    0   SORT (AGGREGATE)



   2    1     SORT* (AGGREGATE)                                       

:Q107440
                                                                      


00



   3    2       PARTITION RANGE* (ALL)                                

:Q107440
                                                                      


00



   4    3         TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'TRANSTEST' 

:Q107440


          (Cost=4 Card=270 Bytes=4050)                                
00



   5    4           INDEX* (RANGE SCAN) OF 'TRANSTEST_IDX3' (NON-UNIQU

:Q107440


          E) (Cost=4 Card=270)                                        
00





   2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */

SYS_OP_MSR(COUNT(*),SU


                                   M(A1.C2)) FROM (SELECT /*+


NO_EXPAND



   3 PARALLEL_COMBINED_WITH_PARENT
   4 PARALLEL_COMBINED_WITH_PARENT
   5 PARALLEL_COMBINED_WITH_PARENT






Statistics



         19  recursive calls
          1  rows processed








Michael.Burden_at_CGEY.COM (Michael Burden) wrote in message news:<3bbc0756.0208060032.250b13d_at_posting.google.com>...

> We have recently partitioned a large table (500 Million) rows over 7
> partitions using local indexes
> 
> However, we experienced major performance problems with small row fetches
> (i.e. < 100) using non-unique indexes. The issue is that an SQL statement
> executes an all partition fetch which, in this test case, returns no rows.
> Total number of rows scanned on the base tables was zero because the key
> didn't exist. However the SQL statement takes 3 to 4 seconds to respond.
> This means our major batch programs which usually sees fetches in the order
> of 100ths of a second are now taking a touch longer. Obviously we have
> reverted to global indexes in the mean time, and reported it to Oracle as a
> problem and set up a smaller test table to prove the problem did not go
> away.
> 
> I changed the SQL to hit a single partition and this returned the
> performance back to expected. Now the point is I only have seven partitions
> so I can write the program to hit each partition separately sort the results
> put them in an email send them round the world to myself open and read the
> mail before Oracle returns the results using it's all partition scan method.
> 
> So I believed this to be a problem and Oracle's reply is that there is a
> performance overhead with partition indexes and we should use global
> indexes.
> 
> Does anyone know whether this is a known bug/issue/problem and has any one
> else experienced something similar. If it is then there should be a warning
> message stating:
> 
> DONT USE PARTITIONED INDEXES AS THEY CAN SERIOUSLY DAMAGE PERFORMANCE  -
> UNTIL IT'S FIXED.


Received on Mon Aug 12 2002 - 16:54:37 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US