Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query which looks like this:

select * 
from ORDERDOC, ORDERPOSITION 
where ORDERDOC.id = ORDERPOSITION.orderId --some other conditions;

Execution plan looks like this:

0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=1 Bytes=77)
1    0   NESTED LOOPS
2    1     NESTED LOOPS (Cost=10 Card=1 Bytes=77)
3    2       TABLE ACCESS (BY INDEX ROWID) OF 'ORDERPOSITION' (TABLE) (Cost=8 Card=1 Bytes=47)
4    3         INDEX (RANGE SCAN) OF 'ORDERPOS_ORDERNR_IDX' (INDEX) (Cost=4 Card=7)
5    2       INDEX (UNIQUE SCAN) OF 'ORDERDOC_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
6    1     TABLE ACCESS (BY INDEX ROWID) OF 'ORDERDOC' (TABLE) (Cost=3 Card=1 Bytes=30)

and statistics looks like this:

     15  recursive calls
      0  db block gets
    820  consistent gets
      0  physical reads
      0  redo size
    809  bytes sent via SQL*Net to client
    422  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

But sometimes something goes wrong and exactly the same query last very long and statistics are:

     15  recursive calls
      2  db block gets
4496563  consistent gets
      0  physical reads
      0  redo size
    809  bytes sent via SQL*Net to client
    422  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

Another thing is that when I add hint to force Oracle to use HashJoin then such situation doesn't happen.

I'm using Oracle 11.2.0.3.0 on 64-bit Linux. Can anyone give me a hint what might cause that difference?

share|improve this question
2  
It seems that you have multiple execution plan for the same sqlid. You can check it in the v$sql_plan (multiple sqlid). –  Nicolas Durand Jan 7 at 15:08
    
Thank you. Indeed there are two execution plans. –  Paweł Adamski Jan 8 at 10:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.