Home -> Mailing Lists -> Oracle-L -> RE: Hash cluster vs partition by hash

RE: Hash cluster vs partition by hash

From: McPeak, Matt <vxsmimmcp_at_subaru.com>
Date: Mon, 27 Jan 2014 21:47:03 +0000
Message-ID: <D7864FA3E7830B428CB2A5A5301B63EE7D295AF4_at_S7041VA005.soa.soaad.com> 





(I’m having some confusion, so sorry if this message gets posted 3 times… )



Thank you everyone for responding to my recent question about ITL deadlocks.



As an alternative to re-creating my hash clusters with INITRANS 8, I thought that if I could somehow partition by product line, I wouldn’t have a problem.  (INITRANS 8 is a reasonable solution, but I’m not happy about the performance hit.  Higher INITRANS means less rows per block means more I/Os per row on average).



As far as I know, you cannot partition a hash cluster in 11gR2.  (Please, please tell me I’m wrong!! ☺)



So I started thinking along these lines (see below).  The goal is to co-locate all the data by item/port (for performance), with no more than one product line in a given block (to avoid ITL deadlocks).



CREATE TABLE my_table


…


  SEGMENT CREATION DEFERRED --IMMEDIATE

STORAGE (INITIAL 8192 NEXT 8192) – block size is 8k


--  CLUSTER MY_TABLE_CLUS(item, port)




COMPRESS FOR OLTP



PARTITION BY LIST (product_line)

  SUBPARTITION BY HASH (item, port)
    SUBPARTITIONS 1024
  (PARTITION 
     VALUES ('Product Line 1'),
   PARTITION 
     VALUES ('Product Line 2'),
   PARTITION 
     VALUES ('Product Line 3'),
   PARTITION 
     VALUES ('Product Line 4'),
   PARTITION 
     VALUES ('Product Line 5'),
   PARTITION 
     VALUES ('Product Line 6'),
   PARTITION 
     VALUES ('Product Line 7'),
   PARTITION 
     VALUES ('Product Line 8'),
   PARTITION part_default
     VALUES (DEFAULT)
   );





I tried this, and then did a comparison of accessing the table for one item/port in the new structure vs the old (hash cluster) structure.



The partitioned table access plan was:





| Id  | Operation              | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |



|   0 | SELECT STATEMENT       |                              |      1 |        |     17 |00:00:00.01 |     224 |
|   1 |  PARTITION LIST ALL    |                              |      1 |     79 |     17 |00:00:00.01 |     224 |
|   2 |   PARTITION HASH SINGLE|                              |      9 |     79 |     17 |00:00:00.01 |     224 |
|*  3 |    TABLE ACCESS FULL   | MATT_VPS_DEMANDED_UNITS_PART |      9 |     79 |     17 |00:00:00.01 |     224 |


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




                                                                                                                 
Predicate Information (identified by operation id):                                                              


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


                                                                                                                 
   3 - filter(("MODEL_ITEM_ID"=7698083 AND "PORT_CODE"='020'))                                                   



And the hash cluster access plan was:





| Id  | Operation         | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |




|   0 | SELECT STATEMENT  |                         |      1 |        |     17 |00:00:00.01 |       1 |

|*  1 |  TABLE ACCESS HASH| VERP_VPS_DEMANDED_UNITS |      1 |     53 |     17 |00:00:00.01 |       1 |




                                                                                                       
Predicate Information (identified by operation id):                                                    


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


                                                                                                       


   1 - access("MODEL_ITEM_ID"=7698083 AND "PORT_CODE"='020')                                           




But here is what is confusing me (and, finally, my question…): where are the 224 buffer reads in the first result coming from?  I confirmed (via rowids) that all the data in my result was coming from just two database blocks.  As the extra buffer gets overhead by Oracle to read the list of partitions, etc?  And, if so, does that basically mean this alternative is unworkable?



Thanks, everyone, for any thoughts!



Matt





From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] 
Sent: Friday, January 24, 2014 5:21 PM


To: McPeak, Matt; Oracle Mailinglist


Subject: RE: ITL deadlocks question




You might look at the dbms_redefinition package. 
I won't be surprised if it excluded clustered tables - but it's worth checking.
(If it does allow them it will generate a lot of redo)




   


Regards


Jonathan Lewis


http://jonathanlewis.wordpress.com



_at_jloracle 




From: McPeak, Matt [vxsmimmcp_at_subaru.com]
Sent: 24 January 2014 20:43


To: Jonathan Lewis; Oracle Mailinglist


Subject: RE: ITL deadlocks question


That’s what I was afraid of…  thank you for the quick and definitive answer!
 


One follow-up, if I may…


 


>> You need to recreate the table with initrans set to at least 8 to be safe.
 


Since we’re talking about a single-table hash cluster here, am I correct in believing that there is no online way to do this in 11gR2?
 


 


From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] 
Sent: Friday, January 24, 2014 3:01 PM


To: McPeak, Matt; Oracle Mailinglist


Subject: RE: ITL deadlocks question

 
 
 




No. 


 


Unless it's changed recently, a session will try each ITL slot in turn for a few seconds and then stop on one of them once it's gone through the entire list.  If you're lucky the time it takes to cover the list means it will find a slot even if there were none when it started looking.  You need to recreate the table with initrans set to at least 8 to be safe.

 
 
   




Regards


Jonathan Lewis


http://jonathanlewis.wordpress.com



_at_jloracle 




From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of McPeak, Matt [vxsmimmcp_at_subaru.com]
Sent: 24 January 2014 19:24


To: Oracle Mailinglist


Subject: ITL deadlocks question


I have a situation where the users like to submit eight (8) copies of an I/O intensive job simultaneously – one job for each of our product lines.
 


The job operates on tables that are not partitioned (or otherwise physically separated) by product line, so that one database block may contain rows for many different product lines.
 


Occasionally, some of the processes are failing due to ITL deadlocks.
 


My question is: suppose you have:


 

Block 1  => ITL: txn A, txn B  with txn C waiting.
Block 2  => ITL: txn B, txn C  with txn A waiting…
Block 3  => ITL: txn C, txn *R*  with txn B waiting…


 


Is Oracle’s ITL deadlock detection smart enough to realize that, in block #1 for example, txn C is waiting for *either* txn A *or* txn B to end, but that it need not wait for both?
 


In other words, is it smart enough to know that the situation above is *not* a deadlock?  (Because txn R can still end, then txn B, then both txn C and txn A can continue.)
 


The two tables involved are each in their own single table hash cluster, if that matters.
 


Thanks in advance for any help!


 


Matt


 



†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^
Received on Mon Jan 27 2014 - 22:47:03 CET












Original text of this message