Home » RDBMS Server » Performance Tuning » >>>Wired SQL Tuning issue, maybe Oracle's bug<<<< (ORACLE XE, 10G, Solaris, Windows)
>>>Wired SQL Tuning issue, maybe Oracle's bug<<<< [message #303059] Wed, 27 February 2008 18:53 Go to next message
elawcn
Messages: 1
Registered: February 2008
Junior Member
We have two enviornments, Oracle 10g Xe and Oracle 10g.

When running the below query, Index can be picked up by SQL properly in XE but 10g standard version. all the SQL and index are identical.

Does any expert know what happened?


select
/* +index(d_user_key_idx1) */u.*
from
cca_dw.d_user u inner join cca_dw.a_userstats a on a.user_key = u.user_key
where
u.user_key in(-1) OR (-1 in (-1))


XE's EP
Plan
SELECT STATEMENT ALL_ROWSCost: 1 Bytes: 208 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID TABLE CCA_DW.D_USER Cost: 1 Bytes: 195 Cardinality: 1
3 NESTED LOOPS Cost: 1 Bytes: 208 Cardinality: 1
1 INDEX FULL SCAN INDEX CCA_DW.A_USERSTSATS_IDX_USERKEY1 Cost: 0 Bytes: 13 Cardinality: 1
2 INDEX RANGE SCAN INDEX CCA_DW.D_USER_KEY_IDX1 Cost: 0 Cardinality: 1

Oracle Standard version
----------------------------------
Plan
SELECT STATEMENT ALL_ROWSCost: 1,792 Bytes: 195,514,410 Cardinality: 1,156,890
3 HASH JOIN Cost: 1,792 Bytes: 195,514,410 Cardinality: 1,156,890
1 TABLE ACCESS FULL TABLE CCA_DW.D_USER Cost: 57 Bytes: 1,360,370 Cardinality: 8,195
2 INDEX FAST FULL SCAN INDEX CCA_DW.A_USERSTSATS_IDX_USERKEY1 Cost: 800 Bytes: 3,470,670 Cardinality: 1,156,890

Re: >>>Wired SQL Tuning issue, maybe Oracle's bug<<<< [message #303066 is a reply to message #303059] Wed, 27 February 2008 20:55 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Look at the cardinality estimates. The standard edition is much higher. If you have the same data in both environments, then you should gather statistics with DBMS_STATS.GATHER_TABLE_STATS().

Your post was very hard to read. Please enclose code in [code]...[/code] tags next time to preserve formatting.

Ross Leishman
Previous Topic: Execution time differences between Devl and Test
Next Topic: Rebuild all the indexes of schema
Goto Forum:
  


Current Time: Mon Dec 05 19:32:54 CST 2016

Total time taken to generate the page: 0.12291 seconds