OTN Discussion Forums : CBC Latch and Buffer Busy wait on same ...

YasserRACDBA

Posts: 280
Registered: 12/09/07

CBC Latch and Buffer Busy wait on same table.
Posted: Jun 21, 2009 12:18 AM

Hi All,

Environment details:
Oracle 8i 8.1.6.0.0
SunOS 5.6 sparc SUNW,Ultra-Enterprise
Number of CPU: 6
RAM size: 6GB

Every day some times server CPU utiliztion spikes to 100%, and Wait Events in database shows all Latch Free(CBC type) and also some Buffer Busy Waits(Data Block).

All these wait events point to LOG table in the database and all top CPU consumtion processes perform select query on this LOG table with CBC Latch as shown below:

pid = 27514
SELECT ERROR,TIME_STAMP,O_RESOURCE,QUEUE,NEW_QUEUE FROM LOG WHERE ID = '09126AMR1A7A25E' AND TYPE IN (11, 28, 25, 18, 60, 13) AND (LOG_SEQ>'188738265' OR TYPE = 18 AND LOG_SEQ='188738265') ORDER BY TIME_STAMP ASC
 
pid = 27481
SELECT ERROR,TIME_STAMP,O_RESOURCE,QUEUE,NEW_QUEUE FROM LOG WHERE ID = '09126AMR1A788F9' AND TYPE IN (11, 28, 25, 18, 60, 13) AND (LOG_SEQ>'188724229' OR TYPE = 18 AND LOG_SEQ='188724229') ORDER BY TIME_STAMP ASC
 
pid = 27535
SELECT ERROR,TIME_STAMP,O_RESOURCE,QUEUE,NEW_QUEUE FROM LOG WHERE ID = '09126AMR1A7A4B3' AND TYPE IN (11, 28, 25, 18, 60, 13) AND (LOG_SEQ>'188739602' OR TYPE = 18 AND LOG_SEQ='188739602') ORDER BY TIME_STAMP ASC
 
pid = 27582
SELECT ERROR,TIME_STAMP,O_RESOURCE,QUEUE,NEW_QUEUE FROM LOG WHERE ID = '09126AMR1A7A489' AND TYPE IN (11, 28, 25, 18, 60, 13) AND (LOG_SEQ>'188739507' OR TYPE = 18 AND LOG_SEQ='188739507') ORDER BY TIME_STAMP ASC

Cursor_sharing parameter has been set to EXACT, and its obvious that hard parsing is high in this database as sql queries are using literal values.But thats not problem for us now.

LOG table size is around 6GB having 121 Columns and is residing in DMT tablespace with PCTFREE as 10 and PCT_USED as 40.

SQL> select TABLE_NAME,INITIAL_EXTENT,NEXT_EXTENT,PCT_INCREASE,FREELISTS,NUM_ROWS,BLOCKS,CHAIN_CNT,AVG_ROW_LEN from dba_tables where table_name='LOG' and owner='AMRWF1';
TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE  FREELISTS   NUM_ROWS     BLOCKS  CHAIN_CNT AVG_ROW_LEN
------------------------------ -------------- ----------- ------------ ---------- ---------- ---------- ---------- -----------
LOG                                    131072      524288            0          1    8341667     787676          0         214

Hence i need to resolve this CBC latch contention, i have got some thoughts to resolve this.....

1. Can i covert this table to IOT table??
2. Will reducing rows per block helps..??
3. Will rebuilding table helps as i think table is fragmented??

NOTE: Also some times insert operation is performed on LOG table every day.

Please help me to resolve this issue.
Let me know if any information is required.

-Yasser

Satish Kandi

Posts: 7,832
Registered: 02/20/01

Re: CBC Latch and Buffer Busy wait on same table.
Posted: Jun 21, 2009 12:33 AM

in response to: YasserRACDBA

Reply

Whats the execution plan for this query?

I fail to understand this part (to me, brackets are missing here).

SELECT ERROR,TIME_STAMP,O_RESOURCE,QUEUE,NEW_QUEUE FROM LOG WHERE ID = '09126AMR1A7A25E' AND TYPE IN (11, 28, 25, 18, 60, 13) AND (*LOG_SEQ>'188738265' OR TYPE = 18 AND LOG_SEQ='188738265'*) ORDER BY TIME_STAMP ASC

Besides, if this is a logging table, why do you need to have an application continuously looking at the log table?

YasserRACDBA

Posts: 280
Registered: 12/09/07

Re: CBC Latch and Buffer Busy wait on same table.
Posted: Jun 21, 2009 12:43 AM

in response to: Satish Kandi

Reply

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=28388 Card=1389 Byte
          s=87507)
 
   1    0   SORT (ORDER BY) (Cost=28388 Card=1389 Bytes=87507)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'LOG' (Cost=28371 Card=
          1389 Bytes=87507)
 
   3    2       INDEX (RANGE SCAN) OF 'LOG_ID' (NON-UNIQUE) (Cost=251
          Card=1389)

Could you please let me know where bracket are missing??

I am not sure about logging table as Application are from third party..

-Yasser

Liron Amitzi

Posts: 204
Registered: 06/11/09

Re: CBC Latch and Buffer Busy wait on same table.
Posted: Jun 21, 2009 12:49 AM

in response to: YasserRACDBA

Reply

Hi,
Just to add information, you're working on 8.1.6 which is a very old version and not supported.
Now, I don't know about 8.1.6, but in 8.1.7.0 there were a lot of bugs related to latch problems (mainly shared pool latches, but still), and these were fixed only in patchsets. It could be that you have the same problems.

Liron Amitzi
Senior DBA consultant
[www.dbsnaps.com]
[www.orbiumsoftware.com]

Charles Hooper

Posts: 843
Registered: 01/27/08

Re: CBC Latch and Buffer Busy wait on same table.
Posted: Jun 21, 2009 7:35 AM

in response to: YasserRACDBA

Reply

YasserRACDBA wrote:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=28388 Card=1389 Byte
          s=87507)
 
   1    0   SORT (ORDER BY) (Cost=28388 Card=1389 Bytes=87507)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'LOG' (Cost=28371 Card=
          1389 Bytes=87507)
 
   3    2       INDEX (RANGE SCAN) OF 'LOG_ID' (NON-UNIQUE) (Cost=251
          Card=1389)

Could you please let me know where bracket are missing??

I am not sure about logging table as Application are from third party..

-Yasser

Note in the SQL statement:

SELECT
  ERROR,
  TIME_STAMP,
  O_RESOURCE,
  QUEUE,
  NEW_QUEUE
FROM
  LOG
WHERE
  ID = '09126AMR1A7A4B3'
  AND TYPE IN (11, 28, 25, 18, 60, 13)
  AND (LOG_SEQ>'188739602' OR TYPE = 18 AND LOG_SEQ='188739602')
ORDER BY
  TIME_STAMP ASC

The final condition in the WHERE clause is missing a set of ( ). Does that portion of the WHERE clause mean:

AND ((LOG_SEQ>'188739602' OR TYPE = 18)  AND LOG_SEQ='188739602')
 
-or-
  AND ((LOG_SEQ>'188739602' AND LOG_SEQ='188739602')  OR TYPE = 18)
 
-or-
  AND (LOG_SEQ>'188739602' OR (TYPE = 18 AND LOG_SEQ='188739602')
 
-or-
something else?

Questions for the application designer:
* What is the expected order of evaluation of that portion of the WHERE clause?
* Why is an apparent numeric value contained in an apparent VARCHAR2 column?

Back to the original question, a quick search of the Oracle documentation (the 11g documentation in this case) for cache buffers chains latch causes:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/instance_tune.htm#sthref798
"These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block)."

Jumping to the book "Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning" pages 162-167, the causes for latch contention for the cache buffers chains latch are listed as:
* Inefficient SQL statements
* Hot blocks
* Long hash chains

I suspect that the source of the problem is inefficient SQL statements. Note that the predicted plan shows 1389 rows to be returned by the index look up, per execution and that the predicted plan shows that 1389 rows will make it through the remaining table predicates (conditions in the WHERE clause) to the sort operation. It might prove to be helpful to enable a 10046 trace on several of the sessions so that you may see the actual execution plan, actual number of rows returned from the index and table, and to also see the wait events.

You mentioned that you are also seeing buffer busy waits. I suspect that this is caused by multiple sessions trying to read the same block from disk at the same time. When this happens, one of the sessions will wait on db file sequential read, and the other sessions will wait on the buffer busy wait event. It could be that the rows of interest in the data and indexes are so spread out into different blocks that many blocks need to be read from disk. Possibly DBWR performance is also playing a part in the problem - as the CPU utilization drives toward 100% utilization, fewer CPU cycles are available for DBWR and LGWR to write dirty blocks to disk and write the redo stream to disk, respectively.

So, what is the cause of the excessive CPU consumption? Sessions spinning trying to get a latch? Maybe someone adjusted the SPIN_COUNT parameter as a quick fix for a problem, and now instead of sessions spinning for a short time trying to get a latch, and then sleeping after failing to acquire the latch, the sessions now spin 5, 10, 100, etc. times longer than normal before sleeping.

Suggestions:
* Gather 10046 traces for multiple sessions before and during problems.
* During problem periods repeatedly query V$SESSION_WAIT
* Review (and post if possible) the parameters in the init.ora for the database instance.
* Review the table AND index statistics - DBMS_STATS worked OK in 8.1.7.3, but I do not believe that is the case in the base release of 8.1.6.
* Determine the intentions of the programmer for the SQL statement that is causing the contention.
* Check the changes (delta values) of CPU consumption in V$SESSTAT by examining the values of 'CPU used when call started' - also check the CPU statistics at the operating system level to see which processes are consuming the most CPU.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

YasserRACDBA

Posts: 280
Registered: 12/09/07

Re: CBC Latch and Buffer Busy wait on same table.
Posted: Jun 21, 2009 11:24 PM

in response to: Charles Hooper

Reply

Thanks for your detail suggestions.

I doubled cheked the sql query....but its same as i mentioned in my previuos post.

SQL> !top
last pid: 21124;  load averages: 25.80, 25.73, 26.07                                                                                                02:17:54
248 processes: 219 sleeping, 21 running, 8 on cpu
CPU states:  0.0% idle, 89.7% user, 10.3% kernel,  0.0% iowait,  0.0% swap
Memory: 6144M real, 187M free, 136M swap in use, 1764M swap free
 
  PID USERNAME THR PRI NICE  SIZE   RES STATE   TIME    CPU COMMAND
20465 oracle    19  43    0  987M  970M cpu18  49:07  4.12% oracle
19953 oracle    18  43    0  986M  969M run    37:03  4.11% oracle
19152 oracle     1  42    0  986M  970M run     4:26  4.08% oracle
20204 oracle    18  59    0  987M  971M sleep  10:29  3.77% oracle
20597 oracle    18  33    0  986M  969M run    43:50  3.69% oracle
20064 oracle    18  42    0  986M  970M run    39:23  3.66% oracle
16508 oracle    18  21    0  990M  974M run    57:53  3.65% oracle
20630 oracle    18  41    0  986M  969M run    43:01  3.59% oracle
19904 oracle    19  41    0  987M  971M run    41:13  3.54% oracle
20694 oracle    18  42    0  985M  969M run    41:37  3.50% oracle
20601 oracle    18  32    0  987M  971M run    44:24  3.43% oracle
20533 oracle    18  33    0  985M  969M run    46:34  3.43% oracle
20587 oracle    18  59    0  987M  971M cpu6   46:23  3.41% oracle
19892 oracle    18  32    0  986M  970M run    44:44  3.38% oracle
20461 oracle    18  21    0  987M  971M run    48:30  3.37% oracle
 
 
SQL> select sql_text from v$process a,v$session b,v$sql c
         where a.addr = b.paddr and
         b.sql_hash_value = c.hash_value and
         a.spid = &PID;  
Enter value for pid: 20465
old   7:     a.spid = &PID
new   7:     a.spid = 20465
SELECT ERROR,TIME_STAMP,O_RESOURCE,QUEUE,NEW_QUEUE FROM LOG WHERE ID = '09153AMR1BD8AB0' AND TYPE IN (11, 28, 25, 18, 60, 13) AND (LOG_SEQ>'197667693' OR TYPE = 18 AND LOG_SEQ='197667693') ORDER BY TIME_STAMP ASC
 
 
SQL> @?/rdbms/admin/utlxpls.sql
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |   603 |   37K|  30492 |       |       |
|  SORT ORDER BY            |          |   603 |   37K|  30492 |       |       |
|   CONCATENATION           |          |       |      |        |       |       |
|    TABLE ACCESS BY INDEX R|LOG       |   602 |   37K|  30480 |       |       |
|     INDEX UNIQUE SCAN     |PK_LOG_LO |   602 |      |   1166 |       |       |
|    TABLE ACCESS BY INDEX R|LOG       |   602 |   37K|  30480 |       |       |
|     INDEX RANGE SCAN      |PK_LOG_LO |   602 |      |   1166 |       |       |
--------------------------------------------------------------------------------
 
SQL> set autotrace traceonly exp
SQL> SELECT ERROR,TIME_STAMP,O_RESOURCE,QUEUE,NEW_QUEUE FROM AMRWF1.LOG WHERE ID = '09153AMR1BD8AB0' AND TYPE IN (11, 28, 25, 18, 60, 13) AND (LOG_SEQ>'197667693' OR TYPE = 18 AND LOG_SEQ='197667693') ORDER BY TIME_STAMP ASC;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=32223 Card=637 Bytes
          =40131)
 
   1    0   SORT (ORDER BY) (Cost=32223 Card=637 Bytes=40131)
   2    1     CONCATENATION
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'LOG' (Cost=32210 Car
          d=636 Bytes=40068)
 
   4    3         INDEX (UNIQUE SCAN) OF 'PK_LOG_LOG_SEQ' (UNIQUE) (Co
          st=1232 Card=636)
 
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'LOG' (Cost=32210 Car
          d=636 Bytes=40068)
 
   6    5         INDEX (RANGE SCAN) OF 'PK_LOG_LOG_SEQ' (UNIQUE) (Cos
          t=1232 Card=636)

Could you please guide if any thing is wrong in this SQL??

Yes you were correct...i use to get db file sequential read along with buffer busy waits for LOG table.

Also how can i resolve this CPU issue by minimizing records per block for LOG table, as i suspect hot block contention??

-Yasser

hkchital

Posts: 4,974
Registered: 11/06/98

Re: CBC Latch and Buffer Busy wait on same table.
Posted: Jun 21, 2009 11:46 PM

in response to: YasserRACDBA

Reply

As Satish and Charles have pointed out, you must work with the developers to cleanup those SQLs. The WHERE clauses could mean anything -- it isn't clear which combination of predicates where actually required. The query may well be fetching the wrong set of rows, not exactly what the user wanted.

Those SQLs should be fixed before you start looking at the waits. A fast query which returns the wrong results is much worse than a slow query that returns the right results.

Jonathan Lewis

Posts: 1,911
Registered: 01/23/07

Re: CBC Latch and Buffer Busy wait on same table.
Posted: Jun 22, 2009 1:05 AM

in response to: YasserRACDBA

Reply

There isn't really any need to get too excited about the brackets:

SELECT 
	ERROR,TIME_STAMP,O_RESOURCE,QUEUE,NEW_QUEUE 
FROM 
	LOG 
WHERE 
	ID = '09126AMR1A7A25E' 
AND	TYPE IN (11, 28, 25, 18, 60, 13) 
AND	(LOG_SEQ>'188738265' OR TYPE = 18 AND LOG_SEQ='188738265') 
ORDER BY 
	TIME_STAMP ASC
;

In the absence of explicit brackets, "AND" take precedence over "OR" - sot eh last predicate above will be interpreted as: (LOG_SEQ>'188738265' OR (TYPE = 18 AND LOG_SEQ='188738265')) - which is clearly the intent of the query anyway. It is nice, and helpful, and avoids future errors, if you make this type of bracketing explicit - but it isn't logically an error.

We might get a little warmed up by the fact that (a) you've shown us two execution plans for this query, and (b) we've discussed [this query in the past|http://forums.oracle.com/forums/thread.jspa?messageID=3464516].

You have two plans - one of them is precise, one of them is very imprecise and does far too much work by using the the wrong index to visit too many blocks.

If you visit too many blocks you:

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan

hkchital

Posts: 4,974
Registered: 11/06/98

Re: CBC Latch and Buffer Busy wait on same table.
Posted: Jun 22, 2009 1:17 AM

in response to: Jonathan Lewis

Reply

If I were the developer, I would have written it ias

(LOG_SEQ>'188738265' OR LOG_SEQ='188738265' AND TYPE = 18)

to make it clear that "TYPE=18" goes with "LOG_SEQ=constant" against "LOG_SEQ>constant".

However, if these queries are generated by front-end code (as they seem to be -- possibly user's enter the the LOG_SEQ value) and if the front-end code asks for the LOG_SEQ value twice (once for the "greater than condition" and once for the "equality condition with TYPE=18" and the user enters (mistakenly) two different values then the meaning changes.
The examples we have seen have the same value for LOG_SEQ so we assume that TYPE=18 always goes with the equality.

I just like SQL code to be explicit.

See, of the 4 persons who have seen it, 3 have found issue with it because it is not explicit, even if construct are logically matching implicit order of precedence.

Jonathan Lewis

Posts: 1,911
Registered: 01/23/07

Re: CBC Latch and Buffer Busy wait on same table.
Posted: Jun 22, 2009 1:56 AM

in response to: hkchital

Reply

Hemant K Chitale wrote:
If I were the developer, I would have written it ias

(LOG_SEQ>'188738265' OR LOG_SEQ='188738265' AND TYPE = 18)

Objectively, that doesn't make the clause any clearer at all - in fact, sticking my tongue firmly in my cheek and playing the devil's advocate and I don't really mean it, you've just put the two occurrences of log_seq side by side because you want to show that they don't go together. (Any bets that with your ordering someone who wasn't so well informed on the precedence of AND and OR will one day change your clause to: LOG_SEQ>='188738265' AND TYPE = 18)

There are four ways to write this clause (so that it still has its current meaning, and doesn't use any extra brackets) - I think I could create a reasonable-sounding argument for each that it is "the clearest" - and then I could find a way to highlight the weakness of each argument.

The only IMPORTANT point about the way we could write the clause is that one extra pair of brackets reduces the risk of misinterpretation. (And that, of course, agrees with your underlying point that it is VERY important to try to present the business intentions in the way you write your SQL)

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

"For every expert there is an equal and opposite expert"
Arthur C. Clarke

YasserRACDBA

Posts: 280
Registered: 12/09/07

Re: CBC Latch and Buffer Busy wait on same table.
Posted: Jun 22, 2009 8:31 AM

in response to: Jonathan Lewis

Reply

Thanks a lot pointing out AND operator precedence over OR.

You are right...i am getting two type of execution plan as shown below:

SELECT ERROR,TIME_STAMP,O_RESOURCE,QUEUE,NEW_QUEUE FROM AMRWF1.LOG WHERE ID = '09155AMR1BEE6F9' AND TYPE IN (11, 28, 25, 18, 60, 13) AND (LOG_SEQ>'198270119' OR TYPE = 18 AND LOG_SEQ='198270119') ORDER BY TIME_STAMP ASC
SQL> @?/rdbms/admin/utlxpls.sql
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |   570 |   35K|  28827 |       |       |
|  SORT ORDER BY            |          |   570 |   35K|  28827 |       |       |
|   CONCATENATION           |          |       |      |        |       |       |
|    TABLE ACCESS BY INDEX R|LOG       |   569 |   35K|  28815 |       |       |
|     INDEX UNIQUE SCAN     |PK_LOG_LO |   569 |      |   1102 |       |       |
|    TABLE ACCESS BY INDEX R|LOG       |   569 |   35K|  28815 |       |       |
|     INDEX RANGE SCAN      |PK_LOG_LO |   569 |      |   1102 |       |       |
--------------------------------------------------------------------------------
 
 
 
SELECT ERROR,TIME_STAMP,O_RESOURCE,QUEUE,NEW_QUEUE FROM AMRWF1.LOG WHERE ID = '09126AMR1A7A25E' AND TYPE IN (11, 28, 25, 18, 60, 13) AND (LOG_SEQ>'188738265' OR TYPE = 18 AND LOG_SEQ='188738265') ORDER BY TIME_STAMP ASC
SQL> @?/rdbms/admin/utlxpls.sql
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     1K|  100K|  43088 |       |       |
|  SORT ORDER BY            |          |     1K|  100K|  43088 |       |       |
|   TABLE ACCESS BY INDEX RO|LOG       |     1K|  100K|  43069 |       |       |
|    INDEX RANGE SCAN       |LOG_ID    |     1K|      |    271 |       |       |
--------------------------------------------------------------------------------
 
 
 
 
SQL> SELECT ERROR,TIME_STAMP,O_RESOURCE,QUEUE,NEW_QUEUE FROM AMRWF1.LOG WHERE ID = '09126AMR1A7A25E' AND TYPE IN (11, 28, 25, 18, 60, 13) AND (LOG_SEQ>'188738265' OR TYPE = 18 AND LOG_SEQ='188738265') ORDER BY TIME_STAMP ASC;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=43088 Card=1629 Byte
          s=102627)
 
   1    0   SORT (ORDER BY) (Cost=43088 Card=1629 Bytes=102627)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'LOG' (Cost=43069 Card=
          1629 Bytes=102627)
 
   3    2       INDEX (RANGE SCAN) OF 'LOG_ID' (NON-UNIQUE) (Cost=271
          Card=1629)
 
 
 
 
SQL> SELECT ERROR,TIME_STAMP,O_RESOURCE,QUEUE,NEW_QUEUE FROM AMRWF1.LOG WHERE ID = '09155AMR1BEE6F9' AND TYPE IN (11, 28, 25, 18, 60, 13) AND (LOG_SEQ>'198270119' OR TYPE = 18 AND LOG_SEQ='198270119') ORDER BY TIME_STAMP ASC;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=28827 Card=570 Bytes
          =35910)
 
   1    0   SORT (ORDER BY) (Cost=28827 Card=570 Bytes=35910)
   2    1     CONCATENATION
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'LOG' (Cost=28815 Car
          d=569 Bytes=35847)
 
   4    3         INDEX (UNIQUE SCAN) OF 'PK_LOG_LOG_SEQ' (UNIQUE) (Co
          st=1102 Card=569)
 
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'LOG' (Cost=28815 Car
          d=569 Bytes=35847)
 
   6    5         INDEX (RANGE SCAN) OF 'PK_LOG_LOG_SEQ' (UNIQUE) (Cos
          t=1102 Card=569)

I am not sure how come two different plans has been generated just by change in ID and LOG_SEQ values.

I am really sorry for evoking this problem once again to you.

Could please guide me how these plans are changed for same query??

Will it be helpful if i get rid of poor execution plan to precise plan??If yes how can i ??

UPDATE: When i am forcing index on ID column as shown below i am getting precise plan:

SQL> SELECT /*+ index(log log_id) */ ERROR,TIME_STAMP,O_RESOURCE,QUEUE,NEW_QUEUE FROM AMRWF1.LOG WHERE ID = '09155AMR1BEE6F9' AND TYPE IN (11, 28, 25, 18, 60, 13) AND (LOG_SEQ>'198270119' OR TYPE = 18 AND LOG_SEQ='198270119') ORDER BY TIME_STAMP ASC;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=43078 Card=569 Bytes
          =35847)
 
   1    0   SORT (ORDER BY) (Cost=43078 Card=569 Bytes=35847)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'LOG' (Cost=43069 Card=
          569 Bytes=35847)
 
   3    2       INDEX (RANGE SCAN) OF 'LOG_ID' (NON-UNIQUE) (Cost=271
          Card=569)

Here also i found card is differing by one....with hint=569 and without hint=570.....will this means number of rows returned are differing by one row??
Now why Optimizer is not choosing LOG_ID index??How to tell optimizer to choose LOG_ID index without giving hints??

-Yasser

Edited by: YasserRACDBA on Jun 22, 2009 9:36 PM

Edited by: YasserRACDBA on Jun 22, 2009 10:04 PM