Home » RDBMS Server » Performance Tuning » bad index choose when using rownum < 9 or less (10.2.0.4 AIX 5)
bad index choose when using rownum < 9 or less [message #395079] Tue, 31 March 2009 04:44 Go to next message
lionel.ducret
Messages: 1
Registered: March 2009
Junior Member
Hello,
i have a performance problem with rownum selection when i use a number less than 10, oracle doesn't use the good index
this is my SAP production database
i made a copy of this database (300go) on another server, it's working correctly
select * from sapr3.ekpo 
where mandt='100' and afnam='A' and rownum<10
explain :
SELECT STATEMENT   Cost = 33
  COUNT STOPKEY  
    TABLE ACCESS BY INDEX ROWID EKPO  
      INDEX RANGE SCAN EKPO~A

response time 0.03
select * from sapr3.ekpo 
where mandt='100' and afnam='A' and rownum<9
explain:
SELECT STATEMENT   Cost = 35
  COUNT STOPKEY  
    TABLE ACCESS BY INDEX ROWID EKPO
      INDEX SKIP SCAN EKPO~FPL  

response time 3.2

Indexes :
EKPO~FPL : FPLNR MANDT
EKPO~A : MANDT AFNAM BSTYP
Re: bad index choose when using rownum < 9 or less [message #395339 is a reply to message #395079] Wed, 01 April 2009 03:15 Go to previous message
dingwei
Messages: 3
Registered: April 2009
Junior Member
Please set 10053 CBO trace, and put the logs.
You can tell from the statistics.
Previous Topic: KEEP POOL
Next Topic: Performance degrades suddenly.
Goto Forum:
  


Current Time: Tue Dec 06 00:12:20 CST 2016

Total time taken to generate the page: 0.05447 seconds