Re: Index Contention / Sequence Caching
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Mon, 17 Mar 2014 09:49:56 -0700 (PDT)
Message-ID: <1395074996.99649.YahooMailNeo_at_web124701.mail.ne1.yahoo.com>
Date: Mon, 17 Mar 2014 09:49:56 -0700 (PDT)
Message-ID: <1395074996.99649.YahooMailNeo_at_web124701.mail.ne1.yahoo.com>
The first example cited by Jonathan Lewis was for Oracle 8i; running the code on 11.2.0.3 I don't see that same behavior: SQL> select 2 /*+ 3 leading(t2) use_nl(t1) 4 */ 5 t2.object_name, t1.object_name 6 from 7 t2 t2, 8 t1_at_poojooba t1 9 where 10 t2.object_name = 'DUAL' 11 and t1.object_id = t2.object_id 12 ; OBJECT_NAME OBJECT_NAME ------------------------------ ------------------------------ DUAL DUAL DUAL DUAL Execution Plan ---------------------------------------------------------- Plan hash value: 3485226535 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT| ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 54 | 221 (0)| 00:00:03 | | | | 1 | NESTED LOOPS | | 1 | 54 | 221 (0)| 00:00:03 | | | |* 2 | TABLE ACCESS FULL| T2 | 1 | 24 | 134 (0)| 00:00:02 | | | | 3 | REMOTE | T1 | 1 | 30 | 87 (0)| 00:00:02 | POOJO~ | R->S | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T2"."OBJECT_NAME"='DUAL') Remote SQL Information (identified by operation id): ---------------------------------------------------- 3 - SELECT /*+ USE_NL ("T1") */ "OBJECT_NAME","OBJECT_ID" FROM "T1" "T1" WHERE "OBJECT_ID"=:1 (accessing 'POOJOOBA' ) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 478 consistent gets 0 physical reads 0 redo size 670 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed SQL> SQL> alter index t1_i1 rebuild reverse; Index altered. SQL> SQL> select 2 /*+ 3 leading(t2) use_nl(t1) 4 */ 5 t2.object_name, t1.object_name 6 from 7 t2 t2, 8 t1_at_poojooba t1 9 where 10 t2.object_name = 'DUAL' 11 and t1.object_id = t2.object_id 12 ; OBJECT_NAME OBJECT_NAME ------------------------------ ------------------------------ DUAL DUAL DUAL DUAL Execution Plan ---------------------------------------------------------- Plan hash value: 3485226535 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT| ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 54 | 221 (0)| 00:00:03 | | | | 1 | NESTED LOOPS | | 1 | 54 | 221 (0)| 00:00:03 | | | |* 2 | TABLE ACCESS FULL| T2 | 1 | 24 | 134 (0)| 00:00:02 | | | | 3 | REMOTE | T1 | 1 | 30 | 87 (0)| 00:00:02 | POOJO~ | R->S | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T2"."OBJECT_NAME"='DUAL') Remote SQL Information (identified by operation id): ---------------------------------------------------- 3 - SELECT /*+ USE_NL ("T1") */ "OBJECT_NAME","OBJECT_ID" FROM "T1" "T1" WHERE "OBJECT_ID"=:1 (accessing 'POOJOOBA' ) Statistics ---------------------------------------------------------- 0 recursive calls 1 db block gets 478 consistent gets 0 physical reads 256 redo size 670 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed SQL> I do not disagree with the points raised in Jonathan's second post; those are valid concerns and should be considered carefully before implementing a reverse-key index. David Fitzjarrell Primary author, "Oracle Exadata Survival Guide" On Monday, March 17, 2014 10:28 AM, Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com> wrote: Hello Stephen In addition to Houri (and JL) has pointed out already, effect of a reverse key index is, to spread the values among ALL the leaf blocks of the index. So, the buffer cache(s) can be polluted with all these leaf blocks potentially. This problem is magnified if the number of leaf block in the index is huge. Cheers Riyaj Shamsudeen Principal DBA, Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member Co-author of the books: Expert Oracle Practices, Pro Oracle SQL, Expert RAC Practices 12c. Expert PL/SQL practices
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 17 2014 - 17:49:56 CET