Home » RDBMS Server » Performance Tuning » Why does t.rowid = t.rowid improves performance? (Oracle Enterprise Edition Release 9.2.0.6.0)
Why does t.rowid = t.rowid improves performance? [message #350767] Fri, 26 September 2008 05:03 Go to next message
emil_m
Messages: 5
Registered: September 2008
Junior Member
Hello everyone,

Recently, by mistake, I've discovered a strange(for me at least) tuning method - forcing tables to access specific table indexes using where conditions like table.rowid = table.rowid (the same table).

Does anyone has any idea why this works?

Many thanks,
Emil
Re: Why does t.rowid = t.rowid improves performance? [message #350773 is a reply to message #350767] Fri, 26 September 2008 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Condition "table.rowid = table.rowid" (alone) NEVER uses index.
You surely have something else.

Post what is your "discovering". If it is an article post the link.

Regards
Michel
Re: Why does t.rowid = t.rowid improves performance? [message #350784 is a reply to message #350767] Fri, 26 September 2008 06:07 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Emil,

Strangely enough (for how you worded that) I think I know exactly what you are referring to but I've yet to find the
"magic" -

For example, to force an INDEX FAST FULL SCAN
over a table scan with a query such as


SELECT FIELD1,FIELD2,FIELD3 FROM TABLE_1;



Where FIELD1 has an index, you can specify

SELECT FIELD1,FIELD2,FIELD3 FROM TABLE_1 T1 
WHERE EXISTS 
(SELECT /*+ INDEX_FFS(T2) */ T2.FIELD1
  FROM T2 WHERE T1.ROWID = T2.ROWID AND 
       T2.FIELD1 IS NOT NULL);



The "IS NOT NULL" I believe is a required constraint to
invoke.

I came across this syntax a while back in Toad's Query
Optimization Tool and since the code looked odd I examined
the plan to see why it was up to this.

Needless to say, it was far from an optimized alternative
to the query I was working on, and ever since, I never
come across a situation where forcing something like this
ever beat an Optimized Oracle execution plan.

Best Regards
Harry
Re: Why does t.rowid = t.rowid improves performance? [message #350786 is a reply to message #350773] Fri, 26 September 2008 06:12 Go to previous messageGo to next message
emil_m
Messages: 5
Registered: September 2008
Junior Member
Hi,

I hope this will help, I've copied from pl/sql developers's explain plan:

First, the plan for the query without the condition tab_1.rowid=tab_1.rowid
SELECT STATEMENT, GOAL = CHOOSE			Cost=22172	Cardinality=1	Bytes=136
 FILTER					
  TABLE ACCESS BY INDEX ROWID	Object owner=OWNER	Object name=tab_8	Cost=3	Cardinality=1	Bytes=13
   NESTED LOOPS			Cost=22172	Cardinality=1	Bytes=136
    NESTED LOOPS			Cost=22169	Cardinality=1	Bytes=123
     NESTED LOOPS			Cost=22167	Cardinality=1	Bytes=115
      NESTED LOOPS			Cost=21480	Cardinality=229	Bytes=22442
       NESTED LOOPS			Cost=14238	Cardinality=3621	Bytes=177429
        [B]HASH JOIN[/B]			Cost=2820	Cardinality=3806	Bytes=49478
         TABLE ACCESS BY GLOBAL INDEX ROWID	Object owner=OWNER	Object name=tab_1	Cost=10	Cardinality=6492	Bytes=45444
          INDEX RANGE SCAN	Object owner=OWNER	Object name=tab_1_idx	Cost=2	Cardinality=6	
         INDEX FAST FULL SCAN	Object owner=OWNER	Object name=tab_2_PK	Cost=2073	Cardinality=22025937	Bytes=132155622
        TABLE ACCESS BY INDEX ROWID	Object owner=OWNER	Object name=tab_3_pk	Cost=3	Cardinality=1	Bytes=36
         INDEX RANGE SCAN	Object owner=OWNER	Object name=tab_3_idx	Cost=2	Cardinality=1	
       TABLE ACCESS BY INDEX ROWID	Object owner=OWNER	Object name=tab_4	Cost=2	Cardinality=1	Bytes=49
        INDEX UNIQUE SCAN	Object owner=OWNER	Object name=tab_4_pk	Cost=1	Cardinality=1	
      TABLE ACCESS BY INDEX ROWID	Object owner=OWNER	Object name=tab_5	Cost=3	Cardinality=1	Bytes=17
       INDEX RANGE SCAN	Object owner=OWNER	Object name=tab_5_idx	Cost=2	Cardinality=1	
     TABLE ACCESS BY INDEX ROWID	Object owner=OWNER	Object name=tab_6	Cost=2	Cardinality=1	Bytes=8
      INDEX UNIQUE SCAN	Object owner=OWNER	Object name=tab_6_pk	Cost=1	Cardinality=1	
    INDEX RANGE SCAN	Object owner=OWNER	Object name=tab_7_idx	Cost=2	Cardinality=1	

Then, with the condition tab_1.rowid=tab_1.rowid
SELECT STATEMENT, GOAL = CHOOSE			Cost=1298	Cardinality=1	Bytes=143
 FILTER					
  TABLE ACCESS BY INDEX ROWID	Object owner=OWNER	Object name=tab_8	Cost=3	Cardinality=1	Bytes=13
   NESTED LOOPS			Cost=1298	Cardinality=1	Bytes=143
    NESTED LOOPS			Cost=1295	Cardinality=1	Bytes=130
     NESTED LOOPS			Cost=1293	Cardinality=1	Bytes=122
      NESTED LOOPS			Cost=1260	Cardinality=11	Bytes=1155
       NESTED LOOPS			Cost=898	Cardinality=181	Bytes=10136
        [B]NESTED LOOPS[/B]			Cost=328	Cardinality=190	Bytes=3800
         TABLE ACCESS BY GLOBAL INDEX ROWID	Object owner=OWNER	Object name=tab_1	Cost=3	Cardinality=325	Bytes=4550
          INDEX RANGE SCAN	Object owner=OWNER	Object name=tab_1_idx	Cost=2	Cardinality=8486	
         INDEX UNIQUE SCAN	Object owner=OWNER	Object name=tab_2_PK	Cost=1	Cardinality=1	Bytes=6
        TABLE ACCESS BY INDEX ROWID	Object owner=OWNER	Object name=tab_3_pk	Cost=3	Cardinality=1	Bytes=36
         INDEX RANGE SCAN	Object owner=OWNER	Object name=tab_3_idx	Cost=2	Cardinality=1	
       TABLE ACCESS BY INDEX ROWID	Object owner=OWNER	Object name=tab_4	Cost=2	Cardinality=1	Bytes=49
        INDEX UNIQUE SCAN	Object owner=OWNER	Object name=tab_4_pk	Cost=1	Cardinality=1	
      TABLE ACCESS BY INDEX ROWID	Object owner=OWNER	Object name=tab_5	Cost=3	Cardinality=1	Bytes=17
       INDEX RANGE SCAN	Object owner=OWNER	Object name=tab_5_idx	Cost=2	Cardinality=1	
     TABLE ACCESS BY INDEX ROWID	Object owner=OWNER	Object name=tab_6	Cost=2	Cardinality=1	Bytes=8
      INDEX UNIQUE SCAN	Object owner=OWNER	Object name=tab_6_pk	Cost=1	Cardinality=1	
    INDEX RANGE SCAN	Object owner=OWNER	Object name=tab_7_idx	Cost=2	Cardinality=1	


So, the ideea is that instead of using hash join on tab_1, the CBO decides to use nested loops and the result is excelent, from about 20 minutes to less then a second.

Thanks,
Emil
Re: Why does t.rowid = t.rowid improves performance? [message #350789 is a reply to message #350786] Fri, 26 September 2008 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A plan without a query is useless.

Regards
Michel
Re: Why does t.rowid = t.rowid improves performance? [message #350794 is a reply to message #350789] Fri, 26 September 2008 06:40 Go to previous messageGo to next message
emil_m
Messages: 5
Registered: September 2008
Junior Member
Sorry, here it is:
select fields...
  from tab_1 act
    join tab_2 os on act.id_os = os.id
    join tab_3 c on c.id_os = os.id
    join tab_5 l on l.id_os = c.id_os and l.nr_lucrare = c.nr_lucrare
    join tab_8 a on a.id_lprodso = l.id
    join tab_4 cer on cer.id = c.id_cerere
    join tab_6 cl on cl.id = cer.id_client
  where cer.cod_tip_cerere = 'XXX'
    and act.respins = 'Y'
    and cer.validata = 'Y'
    and act.respins_data between (sysdate - 7) and sysdate 
    and act.rowid = act.rowid
Re: Why does t.rowid = t.rowid improves performance? [message #350891 is a reply to message #350767] Fri, 26 September 2008 20:31 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I cannot claim to really know why, but what the hell, that never stopped me from offering up an opinion so...

Every optimizer operates using some combination of statistics and hueristics. Even Oracle's cost based optimization has hueristic components to it. Described badly but simply, hueristics means it takes a short cut based on a rule of thumb. Partition Pruning for example could be looked at as hueristic in nature.

In any hueristic methodology there are always favored constructs that have a "special" meaning. Consider pre-8i optimization of outer-join. Outer-join used to force join direction from the inner table to the outer table. This means you had to always access the inner table first then do tuple substitution againt the outer table to do the join. Outer-join also caused Oracle to NOT do index merging (this was seen as the AND=INDEXES optimization path (you could not get AND=INDEXES as an optimization if you had an outer-join)). ROWNUM = 1 is another example. It short circuits queries under certain conditions because it has special meaning to the optimizer.

Aside from a neat history here, the point is that these "special" expressions when recognized by the optimizer can make the optimizer change its behavior for no apparent reason. One would presume there is a good idea behind it all but in the end that is hueristics in action.

I would postulate that if what you are showing us is infact reproducible, then it may simply be that you have happed along one of the specials.

I am hoping someone out there really knows for sure but that likely would take talking to one of the Oracle Internals guys and I dont' get out to the conferences these days.

Good luck, Kevin
Re: Why does t.rowid = t.rowid improves performance? [message #350927 is a reply to message #350767] Sat, 27 September 2008 13:03 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Its possible that Oracle thing ths index is too fragmented to do an index unique scan, so instead opts for full scan.

And that adding the rowid construct just tilts the optimiser edge back to a unique scan.

You need to do a full execution trace with tkprof to see the cost of each alternative to the query to find out why oracle chose that path.

Are you able to rebuild the index on the table in question? Maybe it get get clustered better on the disk, and make the optimiser make an easier choice.
Re: Why does t.rowid = t.rowid improves performance? [message #350969 is a reply to message #350767] Sun, 28 September 2008 07:25 Go to previous messageGo to next message
emil_m
Messages: 5
Registered: September 2008
Junior Member
I've done this on two big querys until know and worked for both, tomorrow when I'll get to work I'll present the other query.

Sorry coleing, I've only been working in SQL and Oracle for about 6 month, by rebuilding the index you mean analyzing it? If so, yes, I've analyzed ALL tables using dbms_stats.gather_table_info('OWNER', table, cascade => true). The thing is that the CBO was choosing just the wrong execution plan for these querys on different databases, no matter if the tables were analyzed or not.

My managers have strong hard feelings against using hints on production database, so I'm trying to convince them that using the t.rowid = t.rowid is not a hint and it works excellent, just that I don't have any arguments on it and neither can they explain it
Re: Why does t.rowid = t.rowid improves performance? [message #350970 is a reply to message #350927] Sun, 28 September 2008 07:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That is a curious one. The optimiser is supposed to filter out always-true predicates.

You can see this in evidence when you look at the ACCESS_PREDICATES and FILTER_PREDICATES columns of the Explain Plan.

I just ran some tests on 11g, and predicates such as COL=COL and ROWID=ROWID were both filtered out by the optimiser. ie. They didn't appear in the Explain Plan, so understandably there was no change in plan.

I'm pretty sure COL=COL has been filtered by the optimiser for some versions now. Perhaps they just missed ROWID=ROWID and only fixed it in 11g.

Anyone want to check for evidence in ACCESS_PREDICATES / FILTER_PREDICATES in earlier versions?

Ross Leishman
Re: Why does t.rowid = t.rowid improves performance? [message #351037 is a reply to message #350767] Mon, 29 September 2008 01:24 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Hi,

By Re-building the index, I mean drop it and re-create it.

Re: Why does t.rowid = t.rowid improves performance? [message #351054 is a reply to message #350767] Mon, 29 September 2008 02:26 Go to previous messageGo to next message
emil_m
Messages: 5
Registered: September 2008
Junior Member
Hi,

I've looked at the plan table and the Filter_predicate has a table.rowid is not null condition, so, basically, this condition also worked instead of table.rowid = table.rowid. But, when I've tried it on a test 10.2.0.4.0 Oracle database the plan was ignoring the rowid condition:(

I've even tried some tricks like "join TABLE t_temp on t_temp.rowid = table.rowid" or "where exists (select 1 from table where rowid = initial_table.rowid)" but still no improvement in the plan.

So, since we are planing to soon migrate at 10g, my solution is useless:(

Thanks everyone for the help,
Emil
Re: Why does t.rowid = t.rowid improves performance? [message #351058 is a reply to message #351054] Mon, 29 September 2008 02:39 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Looks like a shortcoming in the 9i optimizer where the true condition was not rewritten out of the statement.

Interesting one though. Thanks for the update.

Ross Leishman
Re: Why does t.rowid = t.rowid improves performance? [message #351181 is a reply to message #350767] Mon, 29 September 2008 12:29 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Did you try rebuilding the index?

Check out the CLUSTERING factor on the 10g version of the index vs the 9i index. Are they similar?
Previous Topic: Two 30 million records tables join query needs tuning
Next Topic: Pagination in big result sets
Goto Forum:
  


Current Time: Fri Dec 02 14:15:25 CST 2016

Total time taken to generate the page: 0.15630 seconds