Re: Index Contention / Sequence Caching
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Tue, 18 Mar 2014 11:32:07 +0100
David
Here a simple test I did using a real two databases local (t1) and distant
(t2) . I don't trust a lot tests done with a loopback db link.
*Local database*
create table t1
as
select
rownum n1,
trunc(dbms_random.value(1,1000)) n2
from dual
connect by level <= 1e5;
--PK
alter table t1 add constraint t1_pk primary key (n1);
BEGIN
dbms_stats.gather_table_stats(USER
, 't1'
, method_opt => 'FOR ALL COLUMNS SIZE 1'
, estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE
, CASCADE => true);
END;
/
*distant database*
create table t2
as
select
rownum n1
,mod(rownum,5) + mod(rownum,10)* 10 n2
from dual
connect by level <= 1e5;
create index t2_i_fk on t2(n1);
BEGIN
dbms_stats.gather_table_stats(USER
, 't2'
, method_opt => 'FOR ALL COLUMNS SIZE 1'
, estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE
, CASCADE => true);
END;
/
And now from the local database I have the following simple select with the
corresponding execution plan
SQL> select t1.*
from
t1, t2@XXX_1 t2
where
t1.n1 = t2.n1
and t2.n1 = 42
;
Plan hash value: 582650634
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)|
| 1 | NESTED LOOPS | | 1 | 17 | 3 (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 9 | 2 (0)|
|* 3 | INDEX UNIQUE SCAN | T1_PK | 1 | | 1 (0)|
| 4 | REMOTE | T2 | 1 | 8 | 1 (0)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."N1"=42)
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT "N1" FROM "T2" "T2" WHERE "N1"=42 (accessing 'XXX_1.WORLD' )
But when I reverse the index in the distant database I have the following
plan
SQL> alter index t2_i_fk rebuild reverse;
Index altered.
Plan hash value: 582650634
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 87 (100)|
| 1 | NESTED LOOPS | | 1 | 17 | 87 (10)|
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 9 | 2 (0)|
|* 3 | INDEX UNIQUE SCAN | T1_PK | 1 | | 1 (0)|
| 4 | REMOTE | T2 | 1 | 8 | *85*
(10)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."N1"=42)
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT "N1" FROM "T2" "T2" WHERE "N1"=42 (accessing 'XXX_1.WORLD' )
The cost accessing REMOTE went from *1* to *85* after reversing the index.
I know another subtle situation about reversing the index. If you have a
SPM plan baseline based on an index and you reverse this index then the SPM
baseline will still be used; and in addition to that the CBO will come up
with exactly the same plan and the same plan_hash_value2 (phv2) in this
case. Because there will be no new plan added to the baseline for future
evolution.
It seems also, hopefully, that reversing an index will not make the CBO
doing wrong estimations if it was using initially the same index for its
estimations.
Best regards
Mohamed Houri
www.hourim.wordpress.com
2014-03-17 23:48 GMT+01:00 Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>:
>
> 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 - 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>
>
>
>
>
>
--
Bien Respectueusement
Mohamed Houri
Other related posts:
