| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning - using more than one partition
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> schrieb im Newsbeitrag
news:du4cpd$i91$1_at_nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> "Volker Hauswurz" <volker.hauswurz_at_materna.de> wrote in message
> news:du49f8$joi$1_at_pentheus.materna.de...
> > Hello,
> >
> > we are using
> >
> > Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
> > Production
> >
> >
> >
> > -- Here we see PARTITION RANGE (ITERATOR) 2 times which is what we
> > expected
> > -- so at runtime just the important partitions get to be visited
> >
>
> No, we don't see anthing.
>
>
> > Here we get
> >
> > ....
> >
> > | 6 | PARTITION RANGE ALL | | | | | 1 | 3 |
> >
> > | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 3 | 6033 | 2 (50)| 1 | 3
> >
> > |* 8 | INDEX RANGE SCAN | I3 | 3 | | 4 (0)| 1 | 3 |
> >
> > | 9 | PARTITION RANGE ALL | | | | | 1 | 3 |
> >
> > | 10 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 3 | 6033 | 2 (50)| 1 |
3
> >
> > |* 11 | INDEX RANGE SCAN | I | 3 | | 3 (0)| 1 | 3 |
> >
> > | 12 | PARTITION RANGE ALL | | | | | 1 | 3 |
> >
> > | 13 | TABLE ACCESS BY LOCAL INDEX ROWID | TEST | 3 | 6033 | 2 (50)| 1 |
3
> >
> > |* 14 | INDEX RANGE SCAN | I | 3 | | 3 (0)| 1 | 3 |
> >
>
> But it would be nice to see the joins and the
> predicates in lines 8,11 and 14 at least.
>
>
>
> You need to post both complete plans if you
> want to give people some chance of working
> out the answer to this one.
>
> Given your example, it may simply be that the
> cost based optimizer has worked out that there
> is no benefit in trying to be clever with these
> tables.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
>
>
Thanks for the quick response.
This what we see in detail:
SQL> explain plan for select * from vap v1, vap v2
2 where v1.n2=:b1
3 and v1.n1=v2.n1
4 /
SQL> set linesize 200
SQL> select plan_table_output from
table(dbms_xplan.display('plan_table',null,'serial'))
2 /
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------- ------------------------ | 0 | SELECT STATEMENT | | 1 | 4048 | 4 (0)| 00:00:01 | | | | 1 | TABLE ACCESS BY LOCAL INDEX ROWID | TEST | 1 | 2011 | 1 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 4048 | 4 (0)| 00:00:01 | | | | 3 | NESTED LOOPS | | 1 | 2037 | 3 (0)| 00:00:01 | | | | 4 | MERGE JOIN CARTESIAN | | 1 | 26 | 2 (0)| 00:00:01 | | | 5 | INDEX FULL SCAN | I_TMP | 1 | 13 | 1 (0)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
| 6 | BUFFER SORT | | 1 | 13 | 1 (0)| 00:00:01 | | | | 7 | INDEX FULL SCAN | I_TMP | 1 | 13 | 1 (0)| 00:00:01 | | | | 8 | PARTITION RANGE ITERATOR | | 1 | 2011 | 1 (0)| 00:00:01 | KEY | | 9 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 1 | 2011 | 1 (0)| 00:00:01 | KEY |* 10 | INDEX RANGE SCAN | I3 | 1 | | 1 (0)| 00:00:01 | KEY | KEY | 11 | PARTITION RANGE ITERATOR | | 1 | | 1 (0)| 00:00:01 | KEY | |* 12 | INDEX RANGE SCAN | I | 1 | |1 (0)| 00:00:01 | KEY | KEY |
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
10 - access("TEST"."N2"=TO_NUMBER(:B1) AND "TEST"."P"="TMP"."P") 12 - access("TEST"."N1"="TEST"."N1" AND "TEST"."P"="TMP"."P")
25 Zeilen ausgewählt.
SQL>
SQL>
SQL> delete from plan_table
2 /
13 Zeilen wurden gelöscht.
SQL>
SQL> explain plan for select * from vap v1, vap v2, vap v3
2 where v1.n2=:b1
3 and v1.n1=v2.n1
4 and v1.n1=v3.n1
5 /
EXPLAIN PLAN ausgeführt.
SQL> select plan_table_output from
table(dbms_xplan.display('plan_table',null,'serial'))
2 /
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------- ------------------------ | 0 | SELECT STATEMENT | | 1 | 6072 | 6 (0)| 00:00:01 | | | | 1 | NESTED LOOPS | | 1 | 6072 | 6 (0)| 00:00:01 | | | | 2 | NESTED LOOPS | | 1 | 6059 | 5 (0)| 00:00:01 | | | | 3 | NESTED LOOPS | | 1 | 6046 | 4 (0)| 00:00:01 | | | | 4 | NESTED LOOPS | | 1 | 4035 | 3 (0)| 00:00:01 | | | | 5 | NESTED LOOPS | | 1 | 2024 | 2 (0)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
| 6 | INDEX FULL SCAN | I_TMP | 1 | 13 | 1 (0)| 00:00:01 | | | | 7 | PARTITION RANGE ITERATOR | | 1 | 2011 | 1 (0)| 00:00:01 | KEY | KEY | | 8 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST | 1 | 2011 | 1 (0)| 00:00:01 | K |* 9 | INDEX RANGE SCAN | I3 | 1 | | 1 (0)| 00:00:01 | KEY | KEY | | 10 | PARTITION RANGE ALL | | 3 | 6033 | 1 (0)| 00:00:01 | 1 | 3 | | 11 | TABLE ACCESS BY LOCAL INDEX ROWID | TEST | 3 | 6033 | 1 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | I | 3 | | 1 (0)| 00:00:01 | 1 | 3 | | 13 | PARTITION RANGE ALL | | 3 | 6033 | 1 (0)| 00:00:01 | 1 | 3 | | 14 | TABLE ACCESS BY LOCAL INDEX ROWID | TEST | 3 | 6033 | 1 (0)| 00:00:01 | |* 15 | INDEX RANGE SCAN | I | 3 | | 1 (0)| 00:00:01 | 1 | 3 | |* 16 | INDEX UNIQUE SCAN | I_TMP | 1 | 13 | 1 (0)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
|* 17 | INDEX UNIQUE SCAN | I_TMP | 1 | 13 | 1 (0)| 00:00:01 | | | ---------------------------------------------------------------------------- ------------------------
Predicate Information (identified by operation id):
9 - access("TEST"."N2"=TO_NUMBER(:B1) AND "TEST"."P"="TMP"."P")
12 - access("TEST"."N1"="TEST"."N1")
15 - access("TEST"."N1"="TEST"."N1")
16 - access("TEST"."P"="TMP"."P")
17 - access("TEST"."P"="TMP"."P")
33 Zeilen ausgewählt.
SQL>
SQL>
We do not understand why the plan completely changes when we use three
tables instead of two (and it seems to be a bad plan...)
Thanks
Volker Received on Wed Mar 01 2006 - 09:20:39 CST
 |
 |