Forum Stats

  • 3,782,046 Users
  • 2,254,585 Discussions
  • 7,879,901 Comments

Discussions

After partitioning, it runs slower

548150
548150 Member Posts: 1,045
edited Jul 20, 2009 2:57AM in General Database Discussions
After partitioning, it runs slower. Why?

I have an application job running on the Oracle 11g database with Windows 2003 server environment.
Because of the bad performance, I make a diagnosis and find the Sequential Read Wait event is the main root cause. This event always related with the hot object issue or the I/O problems. In order to spread all the data around multi-segments, I partition all the large tables and its indexes (using the LOCAL option). But unexpected result comes out. The performance get worse.

Counld you tell me why?
Tagged:
«1

Answers

  • Mohammed Taj
    Mohammed Taj Member Posts: 3,356
    Hi,
    Partitioning not an option for best performance.

    anyway
    you should provide us exeution plan of sql stat.

    Regards,
    Taj
  • 185957
    185957 Member Posts: 1
    Partitioning data doesn't equal faster all the time. Sometimes partitioning could make things slower.
    If you want to distribute I/O over a number of disks make sure you created your tablespace using separate physical disks.
    Or just use ASM to spread the load across many disks.

    A 10046 trace of a query running before and after you partitioned would be helpful.
  • 635471
    635471 Member Posts: 1,937
    If you had a sequential read problem before then partitioning the table with local indexes is almost certain to make it worse.

    The reason for this is that sequential reads are associated with index based access. Where Oracle would have probed a single index segment on the non-partitioned table, in the absence of partition pruning it now has to probe every index partition to see if there are values to retrieve from the associated table partition. If you have used 8 partitions you now have up to 8 times the amount of logical reads (although it may be less due to reduced height of the partitioned index segments).

    Partitioning is no magic wand. Back to the drawing board I'm afraid.
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    After partitioning, it runs slower.
    Where is it written that partitioning ALWAYS makes SQL run faster?

    It is like adding more fuel to a race car & expecting the car to go faster;
    when the added weight just makes the car slower.
  • 709798
    709798 Member Posts: 175
    I don't agree with you. It is true that Sequential read wait event often result from indexes access because all the data access must through single-segment index. So the index become the hot object. The most serious condition is segment (index) and block contention. After partitioning, you can distribute the data access workload to multi-segment, and then avoid the hot object contention. Local partitioned indexes is for this purpose.
  • 643638
    643638 Member Posts: 80
    - Run dbms_stats for getting statistics
    - Check the execution plan for your querys, maybe your querys are doing full scan
    - Check if the querys are using the indexes
    - Run addm or awr to indentify the worse querys
  • 635471
    635471 Member Posts: 1,937
    junez wrote:
    It is true that Sequential read wait event often result from indexes access because all the data access must through single-segment index. So the index become the hot object.
    I don't understand what you mean here. Sequential read wait events don't result from the data access being performed against a sing index segment -- they are just the mechanism by which single block access occurs, usually associated with index-based access methods. Sure, the index is "hot" because it is frequently accessed, but index blocks being read frequently is not inherently a problem.
    junez wrote:
    The most serious condition is segment (index) and block contention. After partitioning, you can distribute the data access workload to multi-segment, and then avoid the hot object contention. Local partitioned indexes is for this purpose.
    ... if the great majority of queries that access the index can leverage single partition pruning. Otherwise you just have X-times the amount of "hot object contention" (whatever that really means) where X is the number of partitions.

    In any case, the OP specifically mentions a problem with sequential reads, so I'll just repeat that in the absence of partition pruning, local partitioning of indexes will probably increase the number of single block reads (of course in some cases it will reduce them because the optimiser will now see a full table or multiple partition scan as being the more efficient access method).
  • Uwehesse-Oracle
    Uwehesse-Oracle Member Posts: 1,767
    It is perfectly possible that local index partitioning slows down performance, if the local index is nonprefixed and accessed serially.

    That is in fact the most prominent scenario where partioning can lead to a significant slow down. When you query with a single server process (OLTP, typically, a DW could use parallel query likely instead) and have a nonprefixed local index (the index key is not the partitioning key of the index), then this one process has to scan through multiple index partitions serially instead of one large index tree (non partitioned index).

    As a rule of thumb: Do not create nonprefixed local indexes in an OLTP like database.

    Kind regards
    Uwe

    http://uhesse.wordpress.com
  • Surachart Opun
    Surachart Opun Member Posts: 1,662
    Sequential Read Wait event is the main root cause.
    - Check execution plan:
    SQL> set autot on

    SQL> sql_statement

    - Check youe session... about waiting
    SQL> alter session set sql_trace=true;

    SQL> sql_statement

    while you query... go to trace file at udump and check it.

    - Change your query
    After you do partition tables. try to use parallel query
    http://www.dba-oracle.com/concepts/parallel_query_option.htm

    By the way, make sure your query use index... and use sql_trace to find waited event;)

    Good Luck
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,849 Gold Crown
    edited Jul 19, 2009 4:10PM
    Uwe Hesse wrote:
    It is perfectly possible that local index partitioning slows down performance, if the local index is nonprefixed and accessed serially.

    As a rule of thumb: Do not create nonprefixed local indexes in an OLTP like database.
    I disagree with your comments about prefixed/non-prefixed (although I agree with the comment that local indexes can slow down performance). I believe the whole 'prefixed/non-prefixed' thing came about as a response to a limitation in the optimizer in 8.0.

    The only significant factors are
    <ul>
    (a) does the query include the partitioning columns in the where clause, and if so
    (b) does including the partitioning columns in the index materially reduce the number of table accesses
    </ul>

    If the where clause includes the partitioning columns then the optimizer can (from 8i) still do partition elimination on the index partitions even when the partitioning columns do not appear in the index. For example (in 10.2.0.3):
    create table pt_range (
    	id	not null,
    	grp,
    	small_vc,
    	padding
    )
    partition by range(id) (
    	partition p200 values less than (200),
    	partition p400 values less than (400),
    	partition p600 values less than (600)
    )
    as
    select
    	rownum				id,
    	trunc(rownum/50)		grp,
    	to_char(trunc(rownum/20))	small_vc,
    	rpad('x',100)			padding
    from
    	all_objects
    where 
    	rownum < 600
    ;
    
    create index ptr_grp on pt_range(grp) local;
    
    begin
    	dbms_stats.gather_table_stats(
    		ownname		 => user,
    		tabname		 =>'PT_RANGE',
    		cascade		 => true,
    		estimate_percent => 100,
    		granularity      => 'DEFAULT',
    		method_opt 	 => 'for all columns size 1'
    	);
    end;
    /
    
    set autotrace traceonly explain
    
    select 
    	/*+ index(ptr) */
    	*
    from
    	pt_range ptr
    where
    	ptr.id = 300
    and	ptr.grp = 25
    ;
    
    set autotrace off
    
    set doc off
    doc
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3862623763
    
    ---------------------------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                   |          |     1 |   110 |     2   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION RANGE SINGLE            |          |     1 |   110 |     2   (0)| 00:00:01 |     2 |     2 |
    |*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PT_RANGE |     1 |   110 |     2   (0)| 00:00:01 |     2 |     2 |
    |*  3 |    INDEX RANGE SCAN                | PTR_GRP  |     1 |       |     1   (0)| 00:00:01 |     2 |     2 |
    ---------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("PTR"."ID"=300)
       3 - access("PTR"."GRP"=25)
    
    #
    As you can see from the execution plan, the optimizer has accessed only one partition in the index and table - and the partition is the one identified by the filter predicate on the table.

    Suggesting that local indexes on OLTP systems should always be prefixed indexes is a bad idea.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk


    "Science is more than a body of knowledge; it is a way of thinking"
    Carl Sagan
This discussion has been closed.