RE: Index Contention / Sequence Caching
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 17 Mar 2014 22:48:53 +0000
David,
If you look at your example you can see from the cost that the local optimizer
is expecting to do a tablescan on the remote table (221 = 137 + 87 ... the 87
is NOT an estimated cost of doing a single indexed access for one row.)
The reason you get a nested loop join is because the local driver is expected
to return a single row - which means
the cost of the join for the nest loop is
cost of getting driving row + 1 * cost of getting related rows (tablescan).
while the cost of the join for a hash join would be
cost of getting hash table + cost of getting hash table (tablescan) + cost
of performance hash join
It's the classic case of "when the cardinality drops to 1 the next join may be
a disaser".
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of David Fitzjarrell [oratune@xxxxxxxxx]
Sent: 17 March 2014 16:49
To: riyaj.shamsudeen@xxxxxxxxx; Mohamed Houri
Cc: suzzell; oracle-l@xxxxxxxxxxxxx
Subject: Re: Index Contention / Sequence Caching
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@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@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@xxxxxxxxx> 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<http://www.orainternals.com/> -
Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/
Oracle ACE Director and OakTable member<http://www.oaktable.com/>
Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL,
<http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8> Expert RAC Practices
12c.<http://tinyurl.com/expert-rac-12c> Expert PL/SQL
practices<http://tinyurl.com/book-expert-plsql-practices>
<http://tinyurl.com/book-expert-plsql-practices>
Other related posts:
