RE: db block gets /consistent gets

[Bobak, Mark]

Thanks.  I was having a bout of insomnia last night, so I'm just 
glad it came out sounding coherent! ;-)

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, December 18, 2003 
  12:54 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: db block gets /consistent getsMark, That 
  is perhaps the most concise and easy to understand explanation of current mode vs. consistent mode that I 
  have yet seen. Thanks for posting it. Jared 
  


  
  "Bobak, Mark" 
<[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 
 12/17/2003 11:24 PM 
 Please respond to ORACLE-L 
                  To:     
   Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>         cc:       
        
  Subject:        RE: db block gets /consistent 
getsSyed,Oracle accesses blocks in one of two modes, current or 
  consistent.A 'db block get' is a current mode get.  That is, it's 
  the most up-to-datecopy of the data in that block, as it is right now, or 
  currently.  Therecan only be one current copy of a block in the 
  buffer cache at any time.Db block gets generally are used when DML changes 
  data in the database.In that case, row-level locks are implicitly taken on 
  the updated rows.There is also at least one well-known case where a select 
  statement doesa db block get, and does not take a lock.  That is, 
  when it does a fulltable scan or fast full index scan, Oracle will read 
  the segment headerin current mode (multiple times, the number varies based 
  on Oracle version).A 'consistent get' is when Oracle gets the data in 
  a block which is consistentwith a given point in time, or SCN.  The 
  consistent get is at the heart ofOracle's read consistency mechanism. 
   When blocks are fetched in order tosatisfy a query result set, they 
  are fetched in consistent mode.  If noblock in the buffer cache is 
  consistent to the correct point in time, Oraclewill (attempt to) 
  reconstruct that block using the information in the rollbacksegments. 
   If it fails to do so, that's when a query errors out with the much 
  dreaded, much feared, and much misunderstood ORA-1555 "snapshot too 
  old".As to latching, and how it relates, well, consider that the block 
  buffersare in the SGA, which is shared memory.  To avoid corruption, 
  latches are used to serialize access to many linked lists and data 
  structures that pointto the buffers as well as the buffers themselves. 
   It is safe to say that each consistent get introduces serialization 
  to the system, and by tuningSQL to use more efficient access paths, you 
  can get the same answer to thesame query but do less consistent gets. 
   This not only consumes less CPU,it also can significantly reduce 
  latching which reduces serialization andmakes your system more 
  scalable.Well, that turned out longer than I planned.  If you're 
  still reading,I hope it helped!-Mark-Original 
  Message-Sent:                 
  Thu 12/18/2003 1:39 AMTo:               
    Multiple recipients of list ORACLE-LCc:         
          Hi list,What is db block gets and 
  consistent gets.?How can I reduce consistent gets ?Ask Tom says each 
  consistent gets is latch, how it could be?Thanks in 
  advanceSyed-- Please see the official 
  ORACLE-L FAQ: http://www.orafaq.net-- Author: Bobak, 
  Mark INET: [EMAIL PROTECTED]Fat City Network 
  Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
  California        -- Mailing list and web hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like 
subscribing).

RE: db block gets /consistent gets

[Jared . Still]

Mark,

That is perhaps the most concise and easy to understand
explanation of current mode vs. consistent mode that I have 
yet seen.

Thanks for posting it.

Jared







"Bobak, Mark" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 12/17/2003 11:24 PM
 Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: db block gets /consistent gets


Syed,

Oracle accesses blocks in one of two modes, current or consistent.

A 'db block get' is a current mode get.  That is, it's the most up-to-date
copy of the data in that block, as it is right now, or currently.  There
can only be one current copy of a block in the buffer cache at any time.
Db block gets generally are used when DML changes data in the database.
In that case, row-level locks are implicitly taken on the updated rows.
There is also at least one well-known case where a select statement does
a db block get, and does not take a lock.  That is, when it does a full
table scan or fast full index scan, Oracle will read the segment header
in current mode (multiple times, the number varies based on Oracle version).

A 'consistent get' is when Oracle gets the data in a block which is consistent
with a given point in time, or SCN.  The consistent get is at the heart of
Oracle's read consistency mechanism.  When blocks are fetched in order to
satisfy a query result set, they are fetched in consistent mode.  If no
block in the buffer cache is consistent to the correct point in time, Oracle
will (attempt to) reconstruct that block using the information in the rollback
segments.  If it fails to do so, that's when a query errors out with the 
much dreaded, much feared, and much misunderstood ORA-1555 "snapshot too old".

As to latching, and how it relates, well, consider that the block buffers
are in the SGA, which is shared memory.  To avoid corruption, latches are 
used to serialize access to many linked lists and data structures that point
to the buffers as well as the buffers themselves.  It is safe to say that 
each consistent get introduces serialization to the system, and by tuning
SQL to use more efficient access paths, you can get the same answer to the
same query but do less consistent gets.  This not only consumes less CPU,
it also can significantly reduce latching which reduces serialization and
makes your system more scalable.

Well, that turned out longer than I planned.  If you're still reading,
I hope it helped!

-Mark

-Original Message-
Sent:                 Thu 12/18/2003 1:39 AM
To:                 Multiple recipients of list ORACLE-L
Cc:                 
Hi list,
 
What is db block gets and consistent gets.?
How can I reduce consistent gets ?
Ask Tom says each consistent gets is latch, how it could be?
Thanks in advance
 
Syed
 
 
 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bobak, Mark
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: db block gets /consistent gets

[Sultan Syed]

Mark I know you from the metalink.
Thank you for your detailed explanation.
Syed

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, December 18, 2003 11:24 AM


> Syed,
>
> Oracle accesses blocks in one of two modes, current or consistent.
>
> A 'db block get' is a current mode get.  That is, it's the most up-to-date
> copy of the data in that block, as it is right now, or currently.  There
> can only be one current copy of a block in the buffer cache at any time.
> Db block gets generally are used when DML changes data in the database.
> In that case, row-level locks are implicitly taken on the updated rows.
> There is also at least one well-known case where a select statement does
> a db block get, and does not take a lock.  That is, when it does a full
> table scan or fast full index scan, Oracle will read the segment header
> in current mode (multiple times, the number varies based on Oracle
version).
>
> A 'consistent get' is when Oracle gets the data in a block which is
consistent
> with a given point in time, or SCN.  The consistent get is at the heart of
> Oracle's read consistency mechanism.  When blocks are fetched in order to
> satisfy a query result set, they are fetched in consistent mode.  If no
> block in the buffer cache is consistent to the correct point in time,
Oracle
> will (attempt to) reconstruct that block using the information in the
rollback
> segments.  If it fails to do so, that's when a query errors out with the
> much dreaded, much feared, and much misunderstood ORA-1555 "snapshot too
old".
>
> As to latching, and how it relates, well, consider that the block buffers
> are in the SGA, which is shared memory.  To avoid corruption, latches are
> used to serialize access to many linked lists and data structures that
point
> to the buffers as well as the buffers themselves.  It is safe to say that
> each consistent get introduces serialization to the system, and by tuning
> SQL to use more efficient access paths, you can get the same answer to the
> same query but do less consistent gets.  This not only consumes less CPU,
> it also can significantly reduce latching which reduces serialization and
> makes your system more scalable.
>
> Well, that turned out longer than I planned.  If you're still reading,
> I hope it helped!
>
> -Mark
>
> -Original Message-
> From: Sultan Syed [mailto:[EMAIL PROTECTED]
> Sent: Thu 12/18/2003 1:39 AM
> To: Multiple recipients of list ORACLE-L
> Cc:
> Subject: db block gets /consistent gets
> Hi list,
>
> What is db block gets and consistent gets.?
> How can I reduce consistent gets ?
> Ask Tom says each consistent gets is latch, how it could be?
> Thanks in advance
>
> Syed
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Bobak, Mark
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sultan Syed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: db block gets /consistent gets

[Bobak, Mark]

Syed,

Oracle accesses blocks in one of two modes, current or consistent.

A 'db block get' is a current mode get.  That is, it's the most up-to-date
copy of the data in that block, as it is right now, or currently.  There
can only be one current copy of a block in the buffer cache at any time.
Db block gets generally are used when DML changes data in the database.
In that case, row-level locks are implicitly taken on the updated rows.
There is also at least one well-known case where a select statement does
a db block get, and does not take a lock.  That is, when it does a full
table scan or fast full index scan, Oracle will read the segment header
in current mode (multiple times, the number varies based on Oracle version).

A 'consistent get' is when Oracle gets the data in a block which is consistent
with a given point in time, or SCN.  The consistent get is at the heart of
Oracle's read consistency mechanism.  When blocks are fetched in order to
satisfy a query result set, they are fetched in consistent mode.  If no
block in the buffer cache is consistent to the correct point in time, Oracle
will (attempt to) reconstruct that block using the information in the rollback
segments.  If it fails to do so, that's when a query errors out with the 
much dreaded, much feared, and much misunderstood ORA-1555 "snapshot too old".

As to latching, and how it relates, well, consider that the block buffers
are in the SGA, which is shared memory.  To avoid corruption, latches are 
used to serialize access to many linked lists and data structures that point
to the buffers as well as the buffers themselves.  It is safe to say that 
each consistent get introduces serialization to the system, and by tuning
SQL to use more efficient access paths, you can get the same answer to the
same query but do less consistent gets.  This not only consumes less CPU,
it also can significantly reduce latching which reduces serialization and
makes your system more scalable.

Well, that turned out longer than I planned.  If you're still reading,
I hope it helped!

-Mark

-Original Message-
From:   Sultan Syed [mailto:[EMAIL PROTECTED]
Sent:   Thu 12/18/2003 1:39 AM
To: Multiple recipients of list ORACLE-L
Cc:     
Subject:db block gets /consistent gets
Hi list,
 
What is db block gets and consistent gets.?
How can I reduce consistent gets ?
Ask Tom says each consistent gets is latch, how it could be?
Thanks in advance
 
Syed
 
 
 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bobak, Mark
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

db block gets /consistent gets

[Sultan Syed]

Hi list,
 
What is db block gets and consistent 
gets.?
How can I reduce consistent gets ?
Ask Tom says each consistent gets is latch, how it 
could be?
Thanks in advance
 
Syed
 
 
 

RE: consistent gets

[Sinardy Xing]

yeah rite, i just test u

-Original Message-
Sent: 08 October 2003 16:29
To: Multiple recipients of list ORACLE-L


Hi,
Consistent gets means the blocks oracle have to visit , it means the real cost of 
the SQL.(consisteng gets + db block gets)
Reduce SGA size has nothing to do with Consistent gets.
TO reduce consistent gets, only Tune the SQL or create proper index etc can help.


Zhu Chao.
- Original Message - 
To: Multiple recipients of list ORACLE-L 
Sent: Wednesday, October 08, 2003 3:34 PM



If you want to reduce your consistent gets, you can reduce your sga size 

-Original Message-
Sent: 08 October 2003 14:14
To: Multiple recipients of list ORACLE-L


Hi,

What does the meaning for this consistent gets.
Some time my statement return more consistent gets in statistics.
How I can reduce this Consistent gets.

Thanks in advance.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: zhu chao
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sinardy Xing
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: consistent gets

[zhu chao]

Hi,
Consistent gets means the blocks oracle have to visit , it means the real cost of 
the SQL.(consisteng gets + db block gets)
Reduce SGA size has nothing to do with Consistent gets.
TO reduce consistent gets, only Tune the SQL or create proper index etc can help.


Zhu Chao.
- Original Message - 
To: Multiple recipients of list ORACLE-L 
Sent: Wednesday, October 08, 2003 3:34 PM



If you want to reduce your consistent gets, you can reduce your sga size 

-Original Message-
Sent: 08 October 2003 14:14
To: Multiple recipients of list ORACLE-L


Hi,

What does the meaning for this consistent gets.
Some time my statement return more consistent gets in statistics.
How I can reduce this Consistent gets.

Thanks in advance.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: zhu chao
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: consistent gets

[Sinardy Xing]

If 
you want to reduce your consistent gets, you can reduce your sga size 

 

  -Original Message-From: Sultan Syed 
  [mailto:[EMAIL PROTECTED]Sent: 08 October 2003 14:14To: 
  Multiple recipients of list ORACLE-LSubject: consistent 
  gets
  Hi,
   
  What does the meaning for this consistent 
  gets.
  Some time my statement return more 
  consistent gets in statistics.
  How I can reduce this Consistent 
  gets.
   
  Thanks in advance.
   
   
   

Re: consistent gets

[bhabani s pradhan]

Hi

consistent gets are when the SQL needs the data in consistent mode i.e not the current 
data. SELECT statements contribute to consistent gets (read from the RBS). DML 
normally contribute to db block gets, but say an UPDATE based on a search criterion -- 
will contribute to consistent gets.

Thanks and Regards
Pradhan

-

On Wed, 08 Oct 2003 Sultan Syed wrote :
>Hi,
>
>What does the meaning for this consistent gets.
>Some time my statement return more consistent gets in statistics.
>How I can reduce this Consistent gets.
>
>Thanks in advance.
>
>

consistent gets

[Sultan Syed]

Hi,
 
What does the meaning for this consistent 
gets.
Some time my statement return more consistent 
gets in statistics.
How I can reduce this Consistent gets.
 
Thanks in advance.
 
 
 

RE: High consistent gets , 10046

[Gorbounov,Vadim]

Thank you, Jonathan, 

No need for apology, all you input is very valuable.  Note about
update/select for update just great, didn't realize this.
About chache chains. Taking real-time snapshots revealed breathtaking 

 FILE# DBABLK   COUNT(*)
-- -- --
 9  38644   2144
 9  77084 10
 9  68036  7

I remember, 9i's 6 block target length has been discussed recently. This is
an index on weblogic's JMS store table. Really hot spot.

Have a good day
Vadim


-Original Message-
Sent: Thursday, March 13, 2003 3:49 PM
To: Multiple recipients of list ORACLE-L



Vadim,

Apologies, I answered the question
you didn't ask - viz why does it take
so long, rather than the 'what are the
CR gets'.

Your second suggestion is the correct
one. It seems unreasonable, but when
you do the "select for update", Oracle
seems to go through a load of read-
consistency work for the block to roll
back the changes made by other
transactions.  The excess CR gets
are accesses to the UNDO blocks
need to build the CR image.

Strangely, if you just slam in the
'update', rather than 'select for update'
this phenomenon does not occur.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___April 8th
UK___April 22nd

Denmark May 21-23rd

USA_(FL)_May 2nd


Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

UK_(Manchester)_May
USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 13 March 2003 18:54


> Thank you, Jonathan,
>
> I'll continue looking for my options to cool down the hot spots. Not
sure if
> I can go for partitioning since Oracle charges $$$.
>
> Is it correct that oracle counts looking through the chain
> for the correct copy as many CR? Or the reason for these extra CR is
access
> to undo segments in attemt to reconstruct CR block aged out from
cache?
>
> Thanks
> Vadim
>
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gorbounov,Vadim
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: High consistent gets , 10046

[Jonathan Lewis]

Vadim,

Apologies, I answered the question
you didn't ask - viz why does it take
so long, rather than the 'what are the
CR gets'.

Your second suggestion is the correct
one. It seems unreasonable, but when
you do the "select for update", Oracle
seems to go through a load of read-
consistency work for the block to roll
back the changes made by other
transactions.  The excess CR gets
are accesses to the UNDO blocks
need to build the CR image.

Strangely, if you just slam in the
'update', rather than 'select for update'
this phenomenon does not occur.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___April 8th
UK___April 22nd

Denmark May 21-23rd

USA_(FL)_May 2nd


Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

UK_(Manchester)_May
USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 13 March 2003 18:54


> Thank you, Jonathan,
>
> I'll continue looking for my options to cool down the hot spots. Not
sure if
> I can go for partitioning since Oracle charges $$$.
>
> Is it correct that oracle counts looking through the chain
> for the correct copy as many CR? Or the reason for these extra CR is
access
> to undo segments in attemt to reconstruct CR block aged out from
cache?
>
> Thanks
> Vadim
>
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: High consistent gets , 10046

[Gorbounov,Vadim]

Thank you, Anjo.
I want to provide more information about this case:

 - In both cases binding is inside PL/SQL block, bind variable type is
correct.
   this must eliminate reasons 1 and 2
 - different  bind variables - yes, almost for sure they are different, but
this is PK, must make no difference.
 - contention for cache chain latches is high
 
I'm looking at Cary's article "Why you should focus on LIO... " Looks like
this count may only be caused by undo lookups. It still looks too high,
since hot blocks must not age out too fast unless Oracle is too aggressive
is keeping number of copies low. Not sure how Oracle counts access to
different block versions im memory at 10046, assuming as one access.
I'm heading to collect more information on cache chain latches and block
counts in x$bh and send it out .

Thanks again,
Vadim

-Original Message-
Sent: Thursday, March 13, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L


I can think of three reasons:

1) You are binding with the wrong datatype and you are getting a full table
access for the SELECT and then
 the rowid is remembered for the FOR UPDATE (results in 1 current get).
2) You are implicitly using array fetch in sqlplus, so the number of cr gets
will be lower but given the fact
that current gets is 1 in both cases, you can ignore this.
3) Different bind variable values.

Anjo.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, March 13, 2003 4:24 PM


> Dear listers,
>
> I'm hunting for top LIO consumers to give a relief to our DB cpu and found
> something that looks interesting.
>
> Many plain good queries show up way to high cr when executed in concurrent
> environment (50 threads) while perform as predicted when executed from
> SQL*PLUS.
>
> The example below is select by primary key, PK index height is 1.
>
> Trace taken in concurrent env shows cr=152
>
> =
> PARSING IN CURSOR #136 len=86 dep=1 uid=65 oct=3 lid=65
tim=1022957016971691
> hv=941708176 ad='61f780e8'
> SELECT samp_ver
>  FROM sub_svc
> WHERE sub_svc_id = :b1
>FOR UPDATE
> END OF STMT
> PARSE
#136:c=0,e=133,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1022957016971679
> EXEC
> #136:c=0,e=2185,p=0,cr=152,cu=1,mis=0,r=0,dep=1,og=4,tim=1022957016974087
> FETCH
#136:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1022957016974208
>
>
> When tested from SQL*plus prompt (server is idle), is falls to resonable
> cr=3
>
> =
> PARSING IN CURSOR #3 len=77 dep=1 uid=65 oct=3 lid=65 tim=1023016395834410
> hv=3412082965 ad='6344f6cc'
> SELECT samp_ver
>  FROM sub_svc
> WHERE sub_svc_id = :b1
>FOR UPDATE
> END OF STMT
> PARSE #3:c=0,e=626,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1023016395834397
> EXEC #3:c=0,e=936,p=0,cr=3,cu=1,mis=0,r=0,dep=1,og=4,tim=1023016395835517
> FETCH #3:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1023016395835612
> =
>
> As you may see, the different is quite essential.
>
> Does anybody have an idea why is so?
>
> This is 9.2.0.2 on Solaris
>
> TIA
>
> Vadim G
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Gorbounov,Vadim
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Anjo Kolk
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gorbounov,Vadim
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of

RE: High consistent gets , 10046

[Gorbounov,Vadim]

Thank you, Jonathan,

I'll continue looking for my options to cool down the hot spots. Not sure if
I can go for partitioning since Oracle charges $$$.

Is it correct that oracle counts looking through the chain 
for the correct copy as many CR? Or the reason for these extra CR is access
to undo segments in attemt to reconstruct CR block aged out from cache? 

Thanks 
Vadim


-Original Message-
Sent: Thursday, March 13, 2003 12:19 PM
To: Multiple recipients of list ORACLE-L



If you can check it in real time, you will probably
find that you have a very large number of CR copies
of the few blocks that are the focus of the concurrent
activity.

The excess time is likely to be down to a mixture
of CPU as Oracle trawls through the chain looking
for the correct copy, and latch contention because
of the time the latch has to be held whilst the
correct copy is being found.

'select for update ...'  seems to be particularly
prone to this problem - especially if you have
an over large db_cache_size, that allows for lots
of blocks in state 'FREE'.  (even a reasonably
size buffer can produce this effect if there is
a process elsewhere which is dropping or
truncating objects on a regular basis).

You may be able to reduce the impact of the
problem by spreading out the rows that need
to be updated - e.g. by increasing the number
of freelists, or hash partitioning the table.  If this
is a relatively small, static sized, table moving it
to a single table hash cluster may help.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___April 8th
UK___April 22nd

Denmark May 21-23rd

USA_(FL)_May 2nd


Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

UK_(Manchester)_May
USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 13 March 2003 15:24


> Dear listers,
>
> I'm hunting for top LIO consumers to give a relief to our DB cpu and
found
> something that looks interesting.
>
> Many plain good queries show up way to high cr when executed in
concurrent
> environment (50 threads) while perform as predicted when executed
from
> SQL*PLUS.
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gorbounov,Vadim
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: High consistent gets , 10046

[Jonathan Lewis]

If you can check it in real time, you will probably
find that you have a very large number of CR copies
of the few blocks that are the focus of the concurrent
activity.

The excess time is likely to be down to a mixture
of CPU as Oracle trawls through the chain looking
for the correct copy, and latch contention because
of the time the latch has to be held whilst the
correct copy is being found.

'select for update ...'  seems to be particularly
prone to this problem - especially if you have
an over large db_cache_size, that allows for lots
of blocks in state 'FREE'.  (even a reasonably
size buffer can produce this effect if there is
a process elsewhere which is dropping or
truncating objects on a regular basis).

You may be able to reduce the impact of the
problem by spreading out the rows that need
to be updated - e.g. by increasing the number
of freelists, or hash partitioning the table.  If this
is a relatively small, static sized, table moving it
to a single table hash cluster may help.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___April 8th
UK___April 22nd

Denmark May 21-23rd

USA_(FL)_May 2nd


Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

UK_(Manchester)_May
USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 13 March 2003 15:24


> Dear listers,
>
> I'm hunting for top LIO consumers to give a relief to our DB cpu and
found
> something that looks interesting.
>
> Many plain good queries show up way to high cr when executed in
concurrent
> environment (50 threads) while perform as predicted when executed
from
> SQL*PLUS.
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: High consistent gets , 10046

[Anjo Kolk]

I can think of three reasons:

1) You are binding with the wrong datatype and you are getting a full table
access for the SELECT and then
 the rowid is remembered for the FOR UPDATE (results in 1 current get).
2) You are implicitly using array fetch in sqlplus, so the number of cr gets
will be lower but given the fact
that current gets is 1 in both cases, you can ignore this.
3) Different bind variable values.

Anjo.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, March 13, 2003 4:24 PM


> Dear listers,
>
> I'm hunting for top LIO consumers to give a relief to our DB cpu and found
> something that looks interesting.
>
> Many plain good queries show up way to high cr when executed in concurrent
> environment (50 threads) while perform as predicted when executed from
> SQL*PLUS.
>
> The example below is select by primary key, PK index height is 1.
>
> Trace taken in concurrent env shows cr=152
>
> =
> PARSING IN CURSOR #136 len=86 dep=1 uid=65 oct=3 lid=65
tim=1022957016971691
> hv=941708176 ad='61f780e8'
> SELECT samp_ver
>  FROM sub_svc
> WHERE sub_svc_id = :b1
>FOR UPDATE
> END OF STMT
> PARSE
#136:c=0,e=133,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1022957016971679
> EXEC
> #136:c=0,e=2185,p=0,cr=152,cu=1,mis=0,r=0,dep=1,og=4,tim=1022957016974087
> FETCH
#136:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1022957016974208
>
>
> When tested from SQL*plus prompt (server is idle), is falls to resonable
> cr=3
>
> =
> PARSING IN CURSOR #3 len=77 dep=1 uid=65 oct=3 lid=65 tim=1023016395834410
> hv=3412082965 ad='6344f6cc'
> SELECT samp_ver
>  FROM sub_svc
> WHERE sub_svc_id = :b1
>FOR UPDATE
> END OF STMT
> PARSE #3:c=0,e=626,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1023016395834397
> EXEC #3:c=0,e=936,p=0,cr=3,cu=1,mis=0,r=0,dep=1,og=4,tim=1023016395835517
> FETCH #3:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1023016395835612
> =
>
> As you may see, the different is quite essential.
>
> Does anybody have an idea why is so?
>
> This is 9.2.0.2 on Solaris
>
> TIA
>
> Vadim G
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Gorbounov,Vadim
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Anjo Kolk
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

High consistent gets , 10046

[Gorbounov,Vadim]

Dear listers, 

I'm hunting for top LIO consumers to give a relief to our DB cpu and found
something that looks interesting.

Many plain good queries show up way to high cr when executed in concurrent
environment (50 threads) while perform as predicted when executed from
SQL*PLUS. 

The example below is select by primary key, PK index height is 1. 

Trace taken in concurrent env shows cr=152

=
PARSING IN CURSOR #136 len=86 dep=1 uid=65 oct=3 lid=65 tim=1022957016971691
hv=941708176 ad='61f780e8'
SELECT samp_ver
 FROM sub_svc
WHERE sub_svc_id = :b1
   FOR UPDATE
END OF STMT
PARSE #136:c=0,e=133,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1022957016971679
EXEC
#136:c=0,e=2185,p=0,cr=152,cu=1,mis=0,r=0,dep=1,og=4,tim=1022957016974087
FETCH #136:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1022957016974208


When tested from SQL*plus prompt (server is idle), is falls to resonable
cr=3

=
PARSING IN CURSOR #3 len=77 dep=1 uid=65 oct=3 lid=65 tim=1023016395834410
hv=3412082965 ad='6344f6cc'
SELECT samp_ver 
 FROM sub_svc
WHERE sub_svc_id = :b1
   FOR UPDATE
END OF STMT
PARSE #3:c=0,e=626,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1023016395834397
EXEC #3:c=0,e=936,p=0,cr=3,cu=1,mis=0,r=0,dep=1,og=4,tim=1023016395835517
FETCH #3:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1023016395835612
=

As you may see, the different is quite essential.

Does anybody have an idea why is so?

This is 9.2.0.2 on Solaris

TIA

Vadim G
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gorbounov,Vadim
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Re: more consistent gets, but more quickly?

[Jonathan Lewis]

When Oracle 'expects' to visit a buffer more than
once in a single call, it will hold the cache buffers
chains latch long enough to create a pin (in this
case a memory structure that associates the
session with the buffer) and link it into the linked
list of current users (x$bh.usprev, usnxt) of the
buffer.  On subsequent accesses to the block,
Oracle need not grab the latch and search the
bucket, instead it can jump to the block by
way of the pin which will definitely be there as
a pinned block may not be flushed from the buffer.

Visits which take this short-cut are recorded under
the 'buffer is pinned count' statistic.  So it is a
logical I/O, but using a shorter, often cheaper,
access path.

Typically it will be index leaf blocks that
show most pinning, as they tend to be
revisited during range scans.

In your case, I assumed that the rebuilt table
would result in there being more usable row entries
per leaf block than there had been, so more jumps
back and forth from index to table each time a leaf
was pinned - hence more pins, fewer gets.




Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___March 19th
USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 29 January 2003 06:42


Jonathan Lewis,
Can you interpret more about this statistics?How does this affect the
sql running time? And how did you think about this statistics that is
seldom used?
The following is the test result:

--sql1:
   00:00:01.58 00:00:01.59
NAME VALUE   VALUE
--- --   -
CPU used by this session   160 161
CPU used when call started 160 161
buffer is not pinned count   41612   41604
buffer is pinned count     1685183 1685183
consistent gets  43911   43907
no work - consistent read gets   43893   43889
session logical reads43914   43910


Elapsed:
--sql2: 00:00:01.69 00:00:01.71
NAME  VALUE   VALUE
 --  --
CPU used by this session170 171
CPU used when call started  170 171
buffer is not pinned count19889   19889
buffer is pinned count      1706898 1706898
consistent gets   22192   22192
no work - consistent read gets22174   22174
session logical reads 22195   22195



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Re: more consistent gets, but more quickly?

[chao_ping]

Jonathan Lewis,
Can you interpret more about this statistics?How does this affect the 
sql running time? And how did you think about this statistics that is seldom used? 
The following is the test result:

--sql1:
00:00:01.58
  00:00:01.59
NAME VALUE   VALUE   
--- --   -   
CPU used by this session   160 161   
CPU used when call started 160 161   
buffer is not pinned count   41612   41604   
buffer is pinned count 1685183 1685183   
consistent gets  43911   43907   
no work - consistent read gets   43893   43889   
session logical reads43914   43910   
   
   
Elapsed: 
--sql2: 00:00:01.69 
00:00:01.71
NAME  VALUE   VALUE 
 --  -- 
CPU used by this session170 171 
CPU used when call started  170 171 
buffer is not pinned count19889   19889 
buffer is pinned count  1706898 1706898 
consistent gets   22192   22192 
no work - consistent read gets22174   22174 
session logical reads 22195   22195 

difference value between sql1 and sql2:
buffer is not pinned count   sql2-sql1:-21723
buffer is pinned count:  sql2-sql1:21715

sql1: to query from the non-ordered ordered table UCM_USERCOMMENT_MAINTAIN_old; less 
consistent get one ,slower one.
sql2: to query from the ordered table UCM_USERCOMMENT_MAINTAIN, more consistent gets 
one ,faster one.


[oracle@app6 oracle]$ cat mystat.sql
col name format a80
set timing on
set line 200
select a.name,b.value from v$statname a,v$mystat b
where a.statistic#=b.statistic#
and a.name in ('CPU used by this session','CPU used when call started','buffer is not 
pinned count','buffer is pinned count','consistent gets','no work - consistent read 
gets','session logical reads')
order by a.name
/
--I only gathered these statistics because i found only these statistics is different 
from different tests.
--buffer is not pinned count 72 Number of times a buffer was free when visited. Useful
--only for internal debugging purposes.
--buffer is pinned count 72 Number of times a buffer was pinned when visited. Useful
--only for internal debugging purposes.

Regards
zhu chao
msn:[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(China Oracle User Group)

=== 2003-01-27 08:49:00 ,you wrote£º===

>Repeat the tests, but take a snapshot of v$sesstat
>for the session on each test.  I would guess that you
>will find that there is a more than balancing difference
>in the statistic "buffer is pinned count" which also
>records accesses to buffered block, but uses a
>different buffer access mechanism.
>
>Regards
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>Coming soon a new one-day tutorial:
>Cost Based Optimisation
>(see http://www.jlcomp.demon.co.uk/tutorial.html )
>
>UK___March
>USA_(FL)_May
>
>
>Next Seminar dates:
>(see http://www.jlcomp.demon.co.uk/seminar.html )
>
>USA_(CA, TX)_August
>
>
>The Co-operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>-----Original Message-
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Date: 27 January 2003 15:56
>
>
>>   As you see, the first sql generated 43629  consistent gets and the
>second sql  22083  consistent gets, this is ok, how ever, the first
>take 1.63 second and the second take 1.70 second.This seems strange,
>right? Since in most case, higher consistent gets means longer time.
>There is no one else running on this server, And I also tested with
>event 10046 with no wait event.I tested for several times, with the
>same result.
>> Can someone help me understand it?
>> Thanks very much.
>>
>>
>>
>>
>>Regards
>>zhu chao
>>msn:[EMAIL PROTECTED]
>>www.cnoug.org(China Oracle User Group)
>>
>
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author: Jonathan Lewis
>  INET: [E

RE: more consistent gets, but more quickly?

[Khedr, Waleed]

The stats output for your sql shows no physical reads.

This means either the whole table is cached or simply since you ran the test
many times you got all the blocks you're interested in cached.

Since all the needed blocks are cached, I do not think fetching the rows
using the rowid would be any different if the rows are in one cached block
or many cached blocks.

Waleed

-Original Message-
Sent: Monday, January 27, 2003 9:59 AM
To: Multiple recipients of list ORACLE-L


hi,  friends:
I hit some strange performance problem on my 9.2.0.2 on redhat
linux.
I want to show developer/manager why delete data for archiving
history data is not a good idear, and I did a test:
There is some big table in our app, and currently we use cron  to
delete rows everyday(delete rows before 15 days). I exported it from the
production and imported it to test env(with same hardware), the imported
table named UCM_USERCOMMENT_MAINTAIN_old, later I created a new table
UCM_USERCOMMENT_MAINTAIN as select * from UCM_USERCOMMENT_MAINTAIN_old order
by ucm_create_dtm;
And I want to tell developers the factor of CLUSTERING_FACTOR:

SQL> select table_name,index_name,CLUSTERING_FACTOR  from user_indexes where
table_name like 'UCM%';

TABLE_NAME INDEX_NAME
CLUSTERING_FACTOR
-- --
-
UCM_USERCOMMENT_MAINTAIN   IDX_UCM4
22165
UCM_USERCOMMENT_MAINTAIN_OLD   IDX_UCM5
49681

 And I am sure the following SQL:
select count(*) from  UCM_USERCOMMENT_MAINTAIN_old(UCM_USERCOMMENT_MAINTAIN
) 
WHERE ucm_create_dtm<(sysdate-(2/24)) AND  ucm_notify_email=1 AND
ucm_notify_sms=0 ;
 To query from the UCM_USERCOMMENT_MAINTAIN should be faster than to
query from UCM_USERCOMMENT_MAINTAIN_old, but the result is surprising:
SQL> select count(*) from  UCM_USERCOMMENT_MAINTAIN_old
  2  WHERE ucm_create_dtm<(sysdate-(2/24)) AND  ucm_notify_email=1 AND
ucm_notify_sms=0 ;

  COUNT(*)
--
350399

Elapsed: 00:00:01.63

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=472 Card=1 Bytes=11)
   10   SORT (AGGREGATE)
   21 TABLE ACCESS (BY INDEX ROWID) OF
'UCM_USERCOMMENT_MAINTAIN_OLD' (Cost=472 Card=10727 Bytes=117997)
   32   INDEX (RANGE SCAN) OF 'IDX_UCM5' (NON-UNIQUE) (Cost=24
Card=7724)


Statistics
--
  0  recursive calls
      0  db block gets
  43629  consistent gets
  0  physical reads
  0  redo size
381  bytes sent via SQL*Net to client
503  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

SQL> select count(*) from  UCM_USERCOMMENT_MAINTAIN 
  2  WHERE ucm_create_dtm<(sysdate-(2/24)) AND  ucm_notify_email=1 AND
ucm_notify_sms=0 ;

  COUNT(*)
--
350399

Elapsed: 00:00:01.70

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=224 Card=1 Bytes=11)
   10   SORT (AGGREGATE)
   21 TABLE ACCESS (BY INDEX ROWID) OF 'UCM_USERCOMMENT_MAINTAIN'
(Cost=224 Card=10916 Bytes=120076)
   32   INDEX (RANGE SCAN) OF 'IDX_UCM4' (NON-UNIQUE) (Cost=24
Card=7860)



Statistics
------
      0  recursive calls
  0  db block gets
  22083  consistent gets
  1  physical reads
  0  redo size
381  bytes sent via SQL*Net to client
503  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
      1  rows processed
 As you see, the first sql generated 43629  consistent gets and the
second sql  22083  consistent gets, this is ok, how ever, the first take
1.63 second and the second take 1.70 second.This seems strange, right? Since
in most case, higher consistent gets means longer time. There is no one else
running on this server, And I also tested with event 10046 with no wait
event.I tested for several times, with the same result.
Can someone help me understand it?
Thanks very much.




Regards
zhu chao
msn:[EMAIL PROTECTED]
www.cnoug.org(China Oracle User Group)


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: chao_ping
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a 

Re: Re: more consistent gets, but more quickly?

[chao_ping]

Jonathan Lewis,
You are really oracle expert!Can you interpret more about this 
statistics? And how did you think about this statistics that is seldom used? 
The following is the test result:

--sql1:
00:00:01.58
  00:00:01.59
NAME VALUE   VALUE   
--- --   -   
CPU used by this session   160 161   
CPU used when call started 160 161   
buffer is not pinned count   41612   41604   
buffer is pinned count 1685183 1685183   
consistent gets  43911   43907   
no work - consistent read gets   43893   43889   
session logical reads43914   43910   
   
   
Elapsed: 
--sql2: 00:00:01.69 
00:00:01.71
NAME  VALUE   VALUE 
 --  -- 
CPU used by this session170 171 
CPU used when call started  170 171 
buffer is not pinned count19889   19889 
buffer is pinned count  1706898 1706898 
consistent gets   22192   22192 
no work - consistent read gets22174   22174 
session logical reads 22195   22195 

difference value between sql1 and sql2:
buffer is not pinned count   sql2-sql1:-21723
buffer is pinned count:  sql2-sql1:21715

sql1: to query from the non-ordered ordered table UCM_USERCOMMENT_MAINTAIN_old; less 
consistent get one ,slower one.
sql2: to query from the ordered table UCM_USERCOMMENT_MAINTAIN, more consistent gets 
one ,faster one.


[oracle@app6 oracle]$ cat mystat.sql
col name format a80
set timing on
set line 200
select a.name,b.value from v$statname a,v$mystat b
where a.statistic#=b.statistic#
and a.name in ('CPU used by this session','CPU used when call started','buffer is not 
pinned count','buffer is pinned count','consistent gets','no work - consistent read 
gets','session logical reads')
order by a.name
/
--I only gathered these statistics because i found only these statistics is different 
from different tests.
--buffer is not pinned count 72 Number of times a buffer was free when visited. Useful
--only for internal debugging purposes.
--buffer is pinned count 72 Number of times a buffer was pinned when visited. Useful
--only for internal debugging purposes.

Regards
zhu chao
msn:[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(China Oracle User Group)

=== 2003-01-27 08:49:00 ,you wrote£º===

>Repeat the tests, but take a snapshot of v$sesstat
>for the session on each test.  I would guess that you
>will find that there is a more than balancing difference
>in the statistic "buffer is pinned count" which also
>records accesses to buffered block, but uses a
>different buffer access mechanism.
>
>Regards
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>Coming soon a new one-day tutorial:
>Cost Based Optimisation
>(see http://www.jlcomp.demon.co.uk/tutorial.html )
>
>UK___March
>USA_(FL)_May
>
>
>Next Seminar dates:
>(see http://www.jlcomp.demon.co.uk/seminar.html )
>
>USA_(CA, TX)_August
>
>
>The Co-operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>-----Original Message-
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Date: 27 January 2003 15:56
>
>
>>   As you see, the first sql generated 43629  consistent gets and the
>second sql  22083  consistent gets, this is ok, how ever, the first
>take 1.63 second and the second take 1.70 second.This seems strange,
>right? Since in most case, higher consistent gets means longer time.
>There is no one else running on this server, And I also tested with
>event 10046 with no wait event.I tested for several times, with the
>same result.
>> Can someone help me understand it?
>> Thanks very much.
>>
>>
>>
>>
>>Regards
>>zhu chao
>>msn:[EMAIL PROTECTED]
>>www.cnoug.org(China Oracle User Group)
>>
>
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author: Jonathan Lewis
>  INET: [EMAIL PROTE

Re: more consistent gets, but more quickly?

[Anjo Kolk]

There is no fixed cost for a logical I/O. The cost of a LIO will change 
depending on many factors.

Anjo.


On Monday 27 January 2003 06:59, chao_ping wrote:
> hi,  friends:
>   I hit some strange performance problem on my 9.2.0.2 on redhat linux.
>   I want to show developer/manager why delete data for archiving history
> data is not a good idear, and I did a test: There is some big table in our
> app, and currently we use cron  to delete rows everyday(delete rows before
> 15 days). I exported it from the production and imported it to test
> env(with same hardware), the imported table named
> UCM_USERCOMMENT_MAINTAIN_old, later I created a new table
> UCM_USERCOMMENT_MAINTAIN as select * from UCM_USERCOMMENT_MAINTAIN_old
> order by ucm_create_dtm; And I want to tell developers the factor of
> CLUSTERING_FACTOR:
>
> SQL> select table_name,index_name,CLUSTERING_FACTOR  from user_indexes
> where table_name like 'UCM%';
>
> TABLE_NAME INDEX_NAME
> CLUSTERING_FACTOR --
> -- - UCM_USERCOMMENT_MAINTAIN  
> IDX_UCM4   22165
> UCM_USERCOMMENT_MAINTAIN_OLD   IDX_UCM5  
> 49681
>
>  And I am sure the following SQL:
> select count(*) from  UCM_USERCOMMENT_MAINTAIN_old(UCM_USERCOMMENT_MAINTAIN
> ) WHERE ucm_create_dtm<(sysdate-(2/24)) AND  ucm_notify_email=1 AND
> ucm_notify_sms=0 ; To query from the UCM_USERCOMMENT_MAINTAIN should be
> faster than to query from UCM_USERCOMMENT_MAINTAIN_old, but the result is
> surprising: SQL> select count(*) from  UCM_USERCOMMENT_MAINTAIN_old
>   2  WHERE ucm_create_dtm<(sysdate-(2/24)) AND  ucm_notify_email=1 AND
> ucm_notify_sms=0 ;
>
>   COUNT(*)
> --
> 350399
>
> Elapsed: 00:00:01.63
>
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=472 Card=1 Bytes=11)
>10   SORT (AGGREGATE)
>21 TABLE ACCESS (BY INDEX ROWID) OF
> 'UCM_USERCOMMENT_MAINTAIN_OLD' (Cost=472 Card=10727 Bytes=117997) 32   
>INDEX (RANGE SCAN) OF 'IDX_UCM5' (NON-UNIQUE) (Cost=24  Card=7724)
>
>
> Statistics
> --
>   0  recursive calls
>   0  db block gets
>   43629  consistent gets
>   0  physical reads
>   0  redo size
> 381  bytes sent via SQL*Net to client
> 503  bytes received via SQL*Net from client
>   2  SQL*Net roundtrips to/from client
>   0  sorts (memory)
>   0  sorts (disk)
>   1  rows processed
>
> SQL> select count(*) from  UCM_USERCOMMENT_MAINTAIN
>   2  WHERE ucm_create_dtm<(sysdate-(2/24)) AND  ucm_notify_email=1 AND
> ucm_notify_sms=0 ;
>
>   COUNT(*)
> --
> 350399
>
> Elapsed: 00:00:01.70
>
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=224 Card=1 Bytes=11)
>10   SORT (AGGREGATE)
>21 TABLE ACCESS (BY INDEX ROWID) OF 'UCM_USERCOMMENT_MAINTAIN'
> (Cost=224 Card=10916 Bytes=120076) 32   INDEX (RANGE SCAN) OF
> 'IDX_UCM4' (NON-UNIQUE) (Cost=24 Card=7860)
>
>
>
> Statistics
> --
>   0  recursive calls
>   0  db block gets
>   22083  consistent gets
>   1  physical reads
>   0  redo size
> 381  bytes sent via SQL*Net to client
> 503  bytes received via SQL*Net from client
>   2  SQL*Net roundtrips to/from client
>       0  sorts (memory)
>   0  sorts (disk)
>   1  rows processed
>  As you see, the first sql generated 43629  consistent gets and the
> second sql  22083  consistent gets, this is ok, how ever, the first take
> 1.63 second and the second take 1.70 second.This seems strange, right?
> Since in most case, higher consistent gets means longer time. There is no
> one else running on this server, And I also tested with event 10046 with no
> wait event.I tested for several times, with the same result. Can someone
> help me understand it?
>   Thanks very much.
>
>
>
>
> Regards
> zhu chao
> msn:[EMAIL PROTECTED]
> www.cnoug.org(China Oracle User Group)

-- 

Anjo Kolk
http://www.oraperf.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Anjo Kolk
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-

Re: more consistent gets, but more quickly?

[Jonathan Lewis]

Repeat the tests, but take a snapshot of v$sesstat
for the session on each test.  I would guess that you
will find that there is a more than balancing difference
in the statistic "buffer is pinned count" which also
records accesses to buffered block, but uses a
different buffer access mechanism.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___March
USA_(FL)_May


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 27 January 2003 15:56


>   As you see, the first sql generated 43629  consistent gets and the
second sql  22083  consistent gets, this is ok, how ever, the first
take 1.63 second and the second take 1.70 second.This seems strange,
right? Since in most case, higher consistent gets means longer time.
There is no one else running on this server, And I also tested with
event 10046 with no wait event.I tested for several times, with the
same result.
> Can someone help me understand it?
> Thanks very much.
>
>
>
>
>Regards
>zhu chao
>msn:[EMAIL PROTECTED]
>www.cnoug.org(China Oracle User Group)
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

more consistent gets, but more quickly?

[chao_ping]

hi,  friends:
I hit some strange performance problem on my 9.2.0.2 on redhat linux.
I want to show developer/manager why delete data for archiving history data is 
not a good idear, and I did a test:
There is some big table in our app, and currently we use cron  to delete rows 
everyday(delete rows before 15 days). I exported it from the production and imported 
it to test env(with same hardware), the imported table named 
UCM_USERCOMMENT_MAINTAIN_old, later I created a new table UCM_USERCOMMENT_MAINTAIN as 
select * from UCM_USERCOMMENT_MAINTAIN_old order by ucm_create_dtm;
And I want to tell developers the factor of CLUSTERING_FACTOR:

SQL> select table_name,index_name,CLUSTERING_FACTOR  from user_indexes where 
table_name like 'UCM%';

TABLE_NAME INDEX_NAME CLUSTERING_FACTOR
-- -- -
UCM_USERCOMMENT_MAINTAIN   IDX_UCM4   22165
UCM_USERCOMMENT_MAINTAIN_OLD   IDX_UCM5   49681

 And I am sure the following SQL:
select count(*) from  UCM_USERCOMMENT_MAINTAIN_old(UCM_USERCOMMENT_MAINTAIN ) 
WHERE ucm_create_dtm<(sysdate-(2/24)) AND  ucm_notify_email=1 AND ucm_notify_sms=0 ;
 To query from the UCM_USERCOMMENT_MAINTAIN should be faster than to query 
from UCM_USERCOMMENT_MAINTAIN_old, but the result is surprising:
SQL> select count(*) from  UCM_USERCOMMENT_MAINTAIN_old
  2  WHERE ucm_create_dtm<(sysdate-(2/24)) AND  ucm_notify_email=1 AND 
ucm_notify_sms=0 ;

  COUNT(*)
--
350399

Elapsed: 00:00:01.63

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=472 Card=1 Bytes=11)
   10   SORT (AGGREGATE)
   21 TABLE ACCESS (BY INDEX ROWID) OF 'UCM_USERCOMMENT_MAINTAIN_OLD' 
(Cost=472 Card=10727 Bytes=117997)
   32   INDEX (RANGE SCAN) OF 'IDX_UCM5' (NON-UNIQUE) (Cost=24  Card=7724)


Statistics
--
  0  recursive calls
  0  db block gets
  43629  consistent gets
  0  physical reads
  0  redo size
381  bytes sent via SQL*Net to client
503  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

SQL> select count(*) from  UCM_USERCOMMENT_MAINTAIN 
  2  WHERE ucm_create_dtm<(sysdate-(2/24)) AND  ucm_notify_email=1 AND 
ucm_notify_sms=0 ;

  COUNT(*)
--
350399

Elapsed: 00:00:01.70

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=224 Card=1 Bytes=11)
   10   SORT (AGGREGATE)
   21 TABLE ACCESS (BY INDEX ROWID) OF 'UCM_USERCOMMENT_MAINTAIN' (Cost=224 
Card=10916 Bytes=120076)
   32   INDEX (RANGE SCAN) OF 'IDX_UCM4' (NON-UNIQUE) (Cost=24 Card=7860)



Statistics
--
  0  recursive calls
  0  db block gets
  22083  consistent gets
  1  physical reads
  0  redo size
381  bytes sent via SQL*Net to client
503  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed
 As you see, the first sql generated 43629  consistent gets and the second sql  
22083  consistent gets, this is ok, how ever, the first take 1.63 second and the 
second take 1.70 second.This seems strange, right? Since in most case, higher 
consistent gets means longer time. There is no one else running on this server, And I 
also tested with event 10046 with no wait event.I tested for several times, with the 
same result.
Can someone help me understand it?
Thanks very much.




Regards
zhu chao
msn:[EMAIL PROTECTED]
www.cnoug.org(China Oracle User Group)


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: chao_ping
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Negative value for Consistent gets etc. in V$Sesstat

[Anjo Kolk]

When the max value of the 4 bytes or 8 bytes have been reached the
values may become negative, if oracle keeps on adding to them.

Anjo.

-Original Message-
Nahata
Sent: Wednesday, November 20, 2002 11:34 AM
To: Multiple recipients of list ORACLE-L

Thanx Anjo,

Can you elaborate on 'values may wrap'?

Regards
Naveen

-Original Message-
Sent: Wednesday, November 20, 2002 2:34 PM
To: Multiple recipients of list ORACLE-L


1) values may wrap (and there are some obscure bugs that cause negative 
values)

2) You want to get the block in mode CR and there is actually no work
needed 
(like cleanout or rollback) to get to that mode.

Anjo.

On Tuesday 19 November 2002 21:34, you wrote:
> Hi All,
>
> There is one report which takes 12 hours to run during the off hours.
And
> that too on a database 6Gb in size! The report was designed by the
> consultants and all the queries in the report were doing a nested
loops
> joins on many tables, optimizer mode was RULE. After changing the
optimizer
> mode to CHOOSE, still no effect. Then I rewrote the report to use
joins
> instead of 'SELECT a row, run query for that row, then select another
row,
> run query for that row..'(written in Oracle Reports) the run time
came
> crashing down to 10 Secs. (Hard to believe!!).
>
> Now for the question.
>
> After running the report for a few hours I terminate the report and
see the
> values in V$sesstat. It is showing me negative values for Consistent
gets
> etc When the report was running i ran the same query on v$sesstat,
at
> that moment it was showing Consistent gets - 47 million approx.
>
> 1. Why is it showing negative values?
> 2. What does stat 'no work - consistent read gets' mean?
>
> OUTPUT ONE HOUR AFTER THE REPORT STARTED:
> -
> SQL> SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16
AND
> VALUE != 0
>   2  AND A.STATISTIC# = B.STATISTIC#
>   3  ORDER BY VALUE DESC
>   4  /
>
> NAME VALUE
> --- --
> session connect time     478385736
> process last non-idle time   478385736
> consistent gets   47024111
> session logical reads 47024106
> no work - consistent read gets36724753
> buffer is not pinned count36650911
> table fetch by rowid  36643847
> buffer is pinned count36569847
> session pga memory 1651312
> session pga memory max 1651312
> session uga memory 1589476
> session uga memory max 1589476
> bytes sent via SQL*Net to client156588
> CPU used when call started   81035
> CPU used by this session 81035
> bytes received via SQL*Net from client   48012
> sorts (rows) 16390
> table fetch continued row 6650
> SQL*Net roundtrips to/from client 2043
> user calls2033
> execute count  788
> calls to get snapshot scn: kcmgss  786
> parse count (total) 17
> opened cursors cumulative   12
> table scan blocks gotten11
> recursive calls  9
> parse count (hard)   9
> db block gets9
> opened cursors current   6
> enqueue requests 5
> enqueue releases 5
> cursor authentications   5
> parse time elapsed   4
> table scans (short tables)   3
> parse time cpu   2
> logons cumulative   

RE: Negative value for Consistent gets etc. in V$Sesstat

[Naveen Nahata]

Thanx Anjo,

Can you elaborate on 'values may wrap'?

Regards
Naveen

-Original Message-
Sent: Wednesday, November 20, 2002 2:34 PM
To: Multiple recipients of list ORACLE-L


1) values may wrap (and there are some obscure bugs that cause negative 
values)

2) You want to get the block in mode CR and there is actually no work needed 
(like cleanout or rollback) to get to that mode.

Anjo.

On Tuesday 19 November 2002 21:34, you wrote:
> Hi All,
>
> There is one report which takes 12 hours to run during the off hours. And
> that too on a database 6Gb in size! The report was designed by the
> consultants and all the queries in the report were doing a nested loops
> joins on many tables, optimizer mode was RULE. After changing the optimizer
> mode to CHOOSE, still no effect. Then I rewrote the report to use joins
> instead of 'SELECT a row, run query for that row, then select another row,
> run query for that row..'(written in Oracle Reports) the run time came
> crashing down to 10 Secs. (Hard to believe!!).
>
> Now for the question.
>
> After running the report for a few hours I terminate the report and see the
> values in V$sesstat. It is showing me negative values for Consistent gets
> etc When the report was running i ran the same query on v$sesstat, at
> that moment it was showing Consistent gets - 47 million approx.
>
> 1. Why is it showing negative values?
> 2. What does stat 'no work - consistent read gets' mean?
>
> OUTPUT ONE HOUR AFTER THE REPORT STARTED:
> -
> SQL> SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND
> VALUE != 0
>   2  AND A.STATISTIC# = B.STATISTIC#
>   3  ORDER BY VALUE DESC
>   4  /
>
> NAME VALUE
> --- --
> session connect time     478385736
> process last non-idle time   478385736
> consistent gets   47024111
> session logical reads 47024106
> no work - consistent read gets36724753
> buffer is not pinned count36650911
> table fetch by rowid  36643847
> buffer is pinned count36569847
> session pga memory 1651312
> session pga memory max 1651312
> session uga memory 1589476
> session uga memory max 1589476
> bytes sent via SQL*Net to client156588
> CPU used when call started   81035
> CPU used by this session 81035
> bytes received via SQL*Net from client   48012
> sorts (rows) 16390
> table fetch continued row 6650
> SQL*Net roundtrips to/from client 2043
> user calls2033
> execute count  788
> calls to get snapshot scn: kcmgss  786
> parse count (total) 17
> opened cursors cumulative   12
> table scan blocks gotten11
> recursive calls  9
> parse count (hard)   9
> db block gets9
> opened cursors current   6
> enqueue requests 5
> enqueue releases 5
> cursor authentications   5
> parse time elapsed   4
> table scans (short tables)   3
> parse time cpu   2
> logons cumulative1
> sorts (memory)   1
> logons current   1
>
> 38 rows selected.
>
> STATS 10 HOURS AFTER

Re: Negative value for Consistent gets etc. in V$Sesstat

[Anjo Kolk]

1) values may wrap (and there are some obscure bugs that cause negative 
values)

2) You want to get the block in mode CR and there is actually no work needed 
(like cleanout or rollback) to get to that mode.

Anjo.

On Tuesday 19 November 2002 21:34, you wrote:
> Hi All,
>
> There is one report which takes 12 hours to run during the off hours. And
> that too on a database 6Gb in size! The report was designed by the
> consultants and all the queries in the report were doing a nested loops
> joins on many tables, optimizer mode was RULE. After changing the optimizer
> mode to CHOOSE, still no effect. Then I rewrote the report to use joins
> instead of 'SELECT a row, run query for that row, then select another row,
> run query for that row..'(written in Oracle Reports) the run time came
> crashing down to 10 Secs. (Hard to believe!!).
>
> Now for the question.
>
> After running the report for a few hours I terminate the report and see the
> values in V$sesstat. It is showing me negative values for Consistent gets
> etc When the report was running i ran the same query on v$sesstat, at
> that moment it was showing Consistent gets - 47 million approx.
>
> 1. Why is it showing negative values?
> 2. What does stat 'no work - consistent read gets' mean?
>
> OUTPUT ONE HOUR AFTER THE REPORT STARTED:
> -
> SQL> SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND
> VALUE != 0
>   2  AND A.STATISTIC# = B.STATISTIC#
>   3  ORDER BY VALUE DESC
>   4  /
>
> NAME VALUE
> --- --
> session connect time         478385736
> process last non-idle time   478385736
> consistent gets   47024111
> session logical reads 47024106
> no work - consistent read gets36724753
> buffer is not pinned count36650911
> table fetch by rowid  36643847
> buffer is pinned count36569847
> session pga memory 1651312
> session pga memory max 1651312
> session uga memory 1589476
> session uga memory max 1589476
> bytes sent via SQL*Net to client156588
> CPU used when call started   81035
> CPU used by this session 81035
> bytes received via SQL*Net from client   48012
> sorts (rows) 16390
> table fetch continued row 6650
> SQL*Net roundtrips to/from client 2043
> user calls2033
> execute count  788
> calls to get snapshot scn: kcmgss  786
> parse count (total) 17
> opened cursors cumulative   12
> table scan blocks gotten11
> recursive calls  9
> parse count (hard)   9
> db block gets9
> opened cursors current   6
> enqueue requests 5
> enqueue releases 5
> cursor authentications   5
> parse time elapsed   4
> table scans (short tables)   3
> parse time cpu   2
> logons cumulative1
> sorts (memory)   1
> logons current   1
>
> 38 rows selected.
>
> STATS 10 HOURS AFTER THE REPORT STARTED RUNNING
> ---
>
> SQL> SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND
> VALUE != 0
>   2  AND A.STATISTIC# = B.STATISTIC#
>   3  ORDER BY 

Negative value for Consistent gets etc. in V$Sesstat

[Naveen Nahata]

Hi All,

There is one report which takes 12 hours to run during the off hours. And
that too on a database 6Gb in size! The report was designed by the
consultants and all the queries in the report were doing a nested loops joins
on many tables, optimizer mode was RULE. After changing the optimizer mode to
CHOOSE, still no effect. Then I rewrote the report to use joins instead of
'SELECT a row, run query for that row, then select another row, run query for
that row..'(written in Oracle Reports) the run time came crashing down to
10 Secs. (Hard to believe!!).

Now for the question.

After running the report for a few hours I terminate the report and see the
values in V$sesstat. It is showing me negative values for Consistent gets
etc When the report was running i ran the same query on v$sesstat, at
that moment it was showing Consistent gets - 47 million approx.

1. Why is it showing negative values?
2. What does stat 'no work - consistent read gets' mean?

OUTPUT ONE HOUR AFTER THE REPORT STARTED:
-
SQL> SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND
VALUE != 0
  2  AND A.STATISTIC# = B.STATISTIC#
  3  ORDER BY VALUE DESC
  4  /

NAME VALUE
--- --
session connect time 478385736
process last non-idle time       478385736
consistent gets   47024111
session logical reads 47024106
no work - consistent read gets36724753
buffer is not pinned count36650911
table fetch by rowid  36643847
buffer is pinned count36569847
session pga memory 1651312
session pga memory max 1651312
session uga memory 1589476
session uga memory max 1589476
bytes sent via SQL*Net to client156588
CPU used when call started   81035
CPU used by this session 81035
bytes received via SQL*Net from client   48012
sorts (rows) 16390
table fetch continued row 6650
SQL*Net roundtrips to/from client 2043
user calls2033
execute count  788
calls to get snapshot scn: kcmgss  786
parse count (total) 17
opened cursors cumulative   12
table scan blocks gotten11
recursive calls  9
parse count (hard)   9
db block gets9
opened cursors current   6
enqueue requests 5
enqueue releases 5
cursor authentications   5
parse time elapsed   4
table scans (short tables)   3
parse time cpu   2
logons cumulative1
sorts (memory)   1
logons current   1

38 rows selected.

STATS 10 HOURS AFTER THE REPORT STARTED RUNNING
---

SQL> SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND
VALUE != 0
  2  AND A.STATISTIC# = B.STATISTIC#
  3  ORDER BY VALUE DESC
  4  /

NAME   VALUE
- --
session connect time   478385736
process last non-idle time 478385736
bytes sent via SQL*Net to client 8649748
CPU used when call started   4599084
CPU used by this session 4599084
bytes received via SQL*Net from client   3243913
session pga memory   1659

Re: Consistent gets

[Jared . Still]

Anjo,

Why do they appear in the first place?

Jared





Anjo Kolk <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 10/09/2002 09:09 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Re: Consistent gets


No problem as long as the stat "no work - consistent read gets"
(v$sysstat) is also close to that number.

Anjo.



Stephane Faroult wrote:
> 
> Has anybody any idea why a query against tables on which very few if any 
update are applied would display a high number of consistent gets ?
> Details : Big query involving 4/5 tables, most of them partitioned. This 
is a test database, db block buffers about 100M, 5,000,000 of logical 
reads with 8K blocks which means that the SGA is flushed a number of 
times. The execution plan starts with a partition scan, then a series of 
nested loops (hash join disappointing). The number of db block gets 
corresponds to the number of blocks read during the partition scan; 
everything else appears as consistent gets. You can rule out delayed 
cleanout, since the same behaviour is displayed when the same query is run 
over and over and not update at all takes place. Another curious symptom 
is that the number of rows returned per second decreases by a factor 3 or 
4 between the beginning and the end of the query. Believe me, no hideous 
hidden scan of table of partition.
> 
> TIA,
> 
> Stephane Faroult
> Oriole
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Stephane Faroul
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Consistent gets

[Anjo Kolk]

No problem as long as the stat "no work - consistent read gets"
(v$sysstat) is also close to that number.

Anjo.



Stephane Faroult wrote:
> 
> Has anybody any idea why a query against tables on which very few if any update are 
>applied would display a high number of consistent gets ?
> Details : Big query involving 4/5 tables, most of them partitioned. This is a test 
>database, db block buffers about 100M, 5,000,000 of logical reads with 8K blocks 
>which means that the SGA is flushed a number of times. The execution plan starts with 
>a partition scan, then a series of nested loops (hash join disappointing). The number 
>of db block gets corresponds to the number of blocks read during the partition scan; 
>everything else appears as consistent gets. You can rule out delayed cleanout, since 
>the same behaviour is displayed when the same query is run over and over and not 
>update at all takes place. Another curious symptom is that the number of rows 
>returned per second decreases by a factor 3 or 4 between the beginning and the end of 
>the query. Believe me, no hideous hidden scan of table of partition.
> 
> TIA,
> 
> Stephane Faroult
> Oriole
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Stephane Faroul
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Consistent gets

[Stephane Faroult]

Has anybody any idea why a query against tables on which very few if any update are 
applied would display a high number of consistent gets ?
Details : Big query involving 4/5 tables, most of them partitioned. This is a test 
database, db block buffers about 100M, 5,000,000 of logical reads with 8K blocks which 
means that the SGA is flushed a number of times. The execution plan starts with a 
partition scan, then a series of nested loops (hash join disappointing). The number of 
db block gets corresponds to the number of blocks read during the partition scan; 
everything else appears as consistent gets. You can rule out delayed cleanout, since 
the same behaviour is displayed when the same query is run over and over and not 
update at all takes place. Another curious symptom is that the number of rows returned 
per second decreases by a factor 3 or 4 between the beginning and the end of the 
query. Believe me, no hideous hidden scan of table of partition. 

TIA,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroul
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: db block get vs consistent gets

[DENNIS WILLIAMS]

Stephane - I believe that Cary Millsap (www.hotsos.com) has studied this
issue in quite some detail.

 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]  


-Original Message-
Sent: Monday, September 23, 2002 3:14 PM
To: Multiple recipients of list ORACLE-L


Has anybody figures about the CPU cost of a consistent get vs a db block
get? I have always expected it to be higher but not extremely higher. I
have had a bad surprise today on a process-of-death which I am trying to
salvage (although it looks a bit more like the last sacrament rather
than a cure). Basically, the most costly part of a one-hour process is a
join between 3/4 tables, the biggest of which is 15 million rows. Nested
loops, nothing in the process likely to be helped by any parallelization
(?) on the 8 processors, a bit of hope with the partitioning of one of
the tables but this is not the question.
Basically, I had got rid of a UNION and a previous test had shown around
40% fewer logical reads, and I was putting some hope in it. In fact, it
was rather worse.
In the case with the UNION, I had about 22 million logical reads (don't
laugh) and about 863 seconds of CPU. Without the UNION, I was down to
about 17,5 million LR (more than expected), with about 15 million db
block gets and the rest of consistent reads. CPU consumption about
trebled. Concerning the whole process, the highest time spent waited no
longer was PIO waits (not surprising with a process accessing 190 G of
data and a 1.7 G SGA), but LIO CPU calls. Once again, I was expecting
some change, but not in such proportions.
Any figures would be welcome to confirm or infirm what I have seen.

TIA

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: db block get vs consistent gets

[Anjo Kolk]

Stephane,

The cost of an LIO is determined by many things. It used to be that one row
access was one LIO. Now a days you will see that many rows are gotten in a
single LIO. That means that the single LIO is more expensive. You will see
that depending on the access plan oracle will access one or multiple rows
and that will change the cost of the LIO.

Anjo.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, September 23, 2002 10:13 PM


> Has anybody figures about the CPU cost of a consistent get vs a db block
> get? I have always expected it to be higher but not extremely higher. I
> have had a bad surprise today on a process-of-death which I am trying to
> salvage (although it looks a bit more like the last sacrament rather
> than a cure). Basically, the most costly part of a one-hour process is a
> join between 3/4 tables, the biggest of which is 15 million rows. Nested
> loops, nothing in the process likely to be helped by any parallelization
> (?) on the 8 processors, a bit of hope with the partitioning of one of
> the tables but this is not the question.
> Basically, I had got rid of a UNION and a previous test had shown around
> 40% fewer logical reads, and I was putting some hope in it. In fact, it
> was rather worse.
> In the case with the UNION, I had about 22 million logical reads (don't
> laugh) and about 863 seconds of CPU. Without the UNION, I was down to
> about 17,5 million LR (more than expected), with about 15 million db
> block gets and the rest of consistent reads. CPU consumption about
> trebled. Concerning the whole process, the highest time spent waited no
> longer was PIO waits (not surprising with a process accessing 190 G of
> data and a 1.7 G SGA), but LIO CPU calls. Once again, I was expecting
> some change, but not in such proportions.
> Any figures would be welcome to confirm or infirm what I have seen.
>
> TIA
>
> Stephane Faroult
> Oriole Software
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Stephane Faroult
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

db block get vs consistent gets

[Stephane Faroult]

Has anybody figures about the CPU cost of a consistent get vs a db block
get? I have always expected it to be higher but not extremely higher. I
have had a bad surprise today on a process-of-death which I am trying to
salvage (although it looks a bit more like the last sacrament rather
than a cure). Basically, the most costly part of a one-hour process is a
join between 3/4 tables, the biggest of which is 15 million rows. Nested
loops, nothing in the process likely to be helped by any parallelization
(?) on the 8 processors, a bit of hope with the partitioning of one of
the tables but this is not the question.
Basically, I had got rid of a UNION and a previous test had shown around
40% fewer logical reads, and I was putting some hope in it. In fact, it
was rather worse.
In the case with the UNION, I had about 22 million logical reads (don't
laugh) and about 863 seconds of CPU. Without the UNION, I was down to
about 17,5 million LR (more than expected), with about 15 million db
block gets and the rest of consistent reads. CPU consumption about
trebled. Concerning the whole process, the highest time spent waited no
longer was PIO waits (not surprising with a process accessing 190 G of
data and a 1.7 G SGA), but LIO CPU calls. Once again, I was expecting
some change, but not in such proportions.
Any figures would be welcome to confirm or infirm what I have seen.

TIA

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Consistent Gets

[Cale, Rick T (Richard)]

CONSISTENT GETS is the number of blocks accessed in buffer cache for normal
queries (SELECTs without
 for update clause).

Rick

-Original Message-
Sent: Tuesday, October 30, 2001 4:46 PM
To: Multiple recipients of list ORACLE-L


Hi. What exactly does "Consistent Gets" mean?

Thanks

Jeff
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeff Wiegard
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cale, Rick T (Richard)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Consistent Gets

[K Gopalakrishnan]

Any logical read (which includes SCN verification)
Is called as consistent get.



Best Regards,
K Gopalakrishnan
Bangalore, INDIA
+ (91) 98451 78868

-Original Message-
Sent: Tuesday, October 30, 2001 1:46 PM
To: Multiple recipients of list ORACLE-L

Hi. What exactly does "Consistent Gets" mean?

Thanks

Jeff
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jeff Wiegard
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing). 


_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Consistent Gets

[Jeff Wiegard]

Hi. What exactly does "Consistent Gets" mean?

Thanks

Jeff
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeff Wiegard
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Higher Consistent Gets...

[Jonathan Lewis]

You don't quote a version of Oracle - which makes
a difference to the ROWS column on the execution
path.

However, I think you are seeing the extra I/O because
Oracle is driving off the main table, then executing the
subquery for each row returned - thus executing your
5 logical I/O many times.  Ideally you would probably
like Oracle to use the subquery to drive the main query.

You may be able to do this by converting the subquery
into an inline view, and joining it (perhaps with an ORDERED
hint) to the main query:

select /*+ ordered  */
...
from
(
select location_code
from gn_location
connect by prior location_code=parent_code
start with location_code='3142'
)v
fr_search_query pd
where
pd.location_code = v.location_code
and
etc.




Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 14 September 2001 18:10


|!! Please do not post Off Topic to this List !!
|
|Hi
|
|I am having problem with a query. This query fetches rows from a
table which
|has 15 million rows.
|
|The problem is, when I execute this query with subquery, the
consistent gets
|are 4700. Where us without the subquery the consistent gets are just
400. If
|I execute the subquery alone, the consistent gets are just 5.
|
|
|Here is the main query with subquery results in cons.gets of 4700:
|
|
|select pd.holiday_id holidayID,   pd.package_number l_package_number,
|   min(pd.tfr_price) l_tpr_price
|  from fr_search_query pd
| where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
| and TO_DATE('13/10/2001','dd/mm/')
|   and pd.location_code in (select location_code
|  from gn_location
|connect by prior
location_code=parent_code
|  start with location_code='3142')
|   and ROWNUM < 301
| group by pd.holiday_id ,   pd.package_number
| order by pd.location_name,   pd.location_code,   pd.accom_unit_name,
|  pd.accom_unit_code,   pd.departure_date,
min(pd.adult_price)
|


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Higher Consistent Gets...

[Khedr, Waleed]

!! Please do not post Off Topic to this List !!

Try this and let's know how it works:

select --+ ordered use_hash(vw1)
   pd.holiday_id holidayID,   pd.package_number l_package_number,
   min(pd.tfr_price) l_tpr_price
  from fr_search_query pd , 
   (select --+ no_merge 
   location_code
 from gn_location
  connect by prior location_code=parent_code
  start with location_code='3142') vw1
 where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
 and TO_DATE('13/10/2001','dd/mm/')
   and pd.location_code = vw1.location_code 
   and ROWNUM < 301
 group by pd.holiday_id ,   pd.package_number
 order by pd.location_name,   pd.location_code,   pd.accom_unit_name,
  pd.accom_unit_code,   pd.departure_date,   min(pd.adult_price)

Regards,

Waleed

-Original Message-
Sent: Friday, September 14, 2001 8:10 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!


Raj,

Which query is fastest?  You don't have any timing information.

Make sure that timed_statistics is on in the database:

alter system set timed_statistics = true;

Run your query with trace on:
   alter session set sql_trace = true;

Then run the resulting trace files through tkprof.

You'll have much more information to work with.

Jared




 

Raj Gopalan


ons.co.uk>   cc:

    Sent by: Subject: Higher
Consistent Gets... 
[EMAIL PROTECTED]

 

 

09/14/01 09:55 AM

Please respond to

ORACLE-L

 

 





!! Please do not post Off Topic to this List !!

Hi

I am having problem with a query. This query fetches rows from a table
which
has 15 million rows.

The problem is, when I execute this query with subquery, the consistent
gets
are 4700. Where us without the subquery the consistent gets are just 400.
If
I execute the subquery alone, the consistent gets are just 5.


Here is the main query with subquery results in cons.gets of 4700:


select pd.holiday_id holidayID,   pd.package_number l_package_number,
   min(pd.tfr_price) l_tpr_price
  from fr_search_query pd
 where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
 and TO_DATE('13/10/2001','dd/mm/')
   and pd.location_code in (select location_code
  from gn_location
connect by prior location_code=parent_code
  start with location_code='3142')
   and ROWNUM < 301
 group by pd.holiday_id ,   pd.package_number
 order by pd.location_name,   pd.location_code,   pd.accom_unit_name,
  pd.accom_unit_code,   pd.departure_date,   min(pd.adult_price)

Trace results

Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
 96   SORT (ORDER BY)
 96SORT (GROUP BY)
 96 COUNT (STOPKEY)
 96  NESTED LOOPS
   5137   INLIST ITERATOR
   5138TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
   'FR_SEARCH_QUERY'
   8566 INDEX   GOAL: ANALYZED (RANGE SCAN) OF
'FR_SEARCH_QUERY_IND_1' (NON-UNIQUE)
 96   VIEW
   5136SORT (UNIQUE)
  1 CONNECT BY
  2  INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF
 'PK_GN_LOCATION' (UNIQUE)
  1  TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
  1  TABLE ACCESS (FULL) OF 'GN_LOCATION'


Running just the subquery results in cons.gets of just 5.


  select location_code
from gn_location
 connect by prior location_code=parent_code
   start with location_code='3142'


Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
  1   CONNECT BY
  2INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GN_LOCATION'
   (UNIQUE)
  1TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
  1TABLE ACCESS (RANGE SCAN) OF 'GN_PARENT_LOCATION_CODE'
(NON-UNIQUE)


Both the tables, indexes are analyzed. The optimizer mode is choose.

How do I tune this or Am I missing something obivious??

Thanks

Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raj Gopalan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send a

Re: Higher Consistent Gets...

[Jared . Still]

!! Please do not post Off Topic to this List !!


Raj,

Which query is fastest?  You don't have any timing information.

Make sure that timed_statistics is on in the database:

alter system set timed_statistics = true;

Run your query with trace on:
   alter session set sql_trace = true;

Then run the resulting trace files through tkprof.

You'll have much more information to work with.

Jared




   
 
Raj Gopalan
 

ons.co.uk>   cc:   
 
Sent by: Subject: Higher Consistent 
Gets... 
[EMAIL PROTECTED]   
 
   
 
   
 
09/14/01 09:55 AM  
 
Please respond to  
 
ORACLE-L   
 
   
 
   
 




!! Please do not post Off Topic to this List !!

Hi

I am having problem with a query. This query fetches rows from a table
which
has 15 million rows.

The problem is, when I execute this query with subquery, the consistent
gets
are 4700. Where us without the subquery the consistent gets are just 400.
If
I execute the subquery alone, the consistent gets are just 5.


Here is the main query with subquery results in cons.gets of 4700:


select pd.holiday_id holidayID,   pd.package_number l_package_number,
   min(pd.tfr_price) l_tpr_price
  from fr_search_query pd
 where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
 and TO_DATE('13/10/2001','dd/mm/')
   and pd.location_code in (select location_code
  from gn_location
connect by prior location_code=parent_code
  start with location_code='3142')
   and ROWNUM < 301
 group by pd.holiday_id ,   pd.package_number
 order by pd.location_name,   pd.location_code,   pd.accom_unit_name,
  pd.accom_unit_code,   pd.departure_date,   min(pd.adult_price)

Trace results

Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
 96   SORT (ORDER BY)
 96SORT (GROUP BY)
 96 COUNT (STOPKEY)
 96  NESTED LOOPS
   5137   INLIST ITERATOR
   5138TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
   'FR_SEARCH_QUERY'
   8566 INDEX   GOAL: ANALYZED (RANGE SCAN) OF
'FR_SEARCH_QUERY_IND_1' (NON-UNIQUE)
 96   VIEW
   5136SORT (UNIQUE)
  1 CONNECT BY
  2  INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF
 'PK_GN_LOCATION' (UNIQUE)
  1  TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
  1  TABLE ACCESS (FULL) OF 'GN_LOCATION'


Running just the subquery results in cons.gets of just 5.


  select location_code
from gn_location
 connect by prior location_code=parent_code
   start with location_code='3142'


Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
  1   CONNECT BY
  2INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GN_LOCATION'
   (UNIQUE)
  1TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
  1TABLE ACCESS (RANGE SCAN) OF 'GN_PARENT_LOCATION_CODE'
(NON-UNIQUE)


Both the tables, indexes are analyzed. The optimizer mode is choose.

How do I tune this or Am I missing something obivious??

Thanks

Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raj Gopalan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mai

Higher Consistent Gets...

[Raj Gopalan]

!! Please do not post Off Topic to this List !!

Hi

I am having problem with a query. This query fetches rows from a table which
has 15 million rows. 

The problem is, when I execute this query with subquery, the consistent gets
are 4700. Where us without the subquery the consistent gets are just 400. If
I execute the subquery alone, the consistent gets are just 5.


Here is the main query with subquery results in cons.gets of 4700:


select pd.holiday_id holidayID,   pd.package_number l_package_number,
   min(pd.tfr_price) l_tpr_price
  from fr_search_query pd
 where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
 and TO_DATE('13/10/2001','dd/mm/')
   and pd.location_code in (select location_code
  from gn_location
connect by prior location_code=parent_code
  start with location_code='3142')
   and ROWNUM < 301
 group by pd.holiday_id ,   pd.package_number
 order by pd.location_name,   pd.location_code,   pd.accom_unit_name,
  pd.accom_unit_code,   pd.departure_date,   min(pd.adult_price)

Trace results

Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
 96   SORT (ORDER BY)
 96SORT (GROUP BY)
 96 COUNT (STOPKEY)
 96  NESTED LOOPS
   5137   INLIST ITERATOR
   5138TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
   'FR_SEARCH_QUERY'
   8566 INDEX   GOAL: ANALYZED (RANGE SCAN) OF
'FR_SEARCH_QUERY_IND_1' (NON-UNIQUE)
 96   VIEW
   5136SORT (UNIQUE)
  1 CONNECT BY
  2  INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF
 'PK_GN_LOCATION' (UNIQUE)
  1  TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
  1  TABLE ACCESS (FULL) OF 'GN_LOCATION'


Running just the subquery results in cons.gets of just 5.


  select location_code
from gn_location
 connect by prior location_code=parent_code
   start with location_code='3142'


Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
  1   CONNECT BY
  2INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GN_LOCATION'
   (UNIQUE)
  1TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
  1TABLE ACCESS (RANGE SCAN) OF 'GN_PARENT_LOCATION_CODE'
(NON-UNIQUE)


Both the tables, indexes are analyzed. The optimizer mode is choose. 

How do I tune this or Am I missing something obivious??

Thanks

Raj
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Raj Gopalan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Consistent Gets?

[Paul Parker]

Hi Steve,

Spot on!  Thanx for your help.
Up until now, I was unaware that ARRAYSIZE
impacts the no. of consistent gets.  Well, one
learns every day ...

Thanx once again for your assistance
Paul

--- Steve Adams <[EMAIL PROTECTED]> wrote:
> Hi Paul,
> 
> Thanks for sending the V$MYSTAT data offline.
> 
> Try this explanation ...
> The first query is fetching all the rows using
> an array size of 15.
> The V$MYSTAT data showed a difference of about
> 25000 'user calls',
> and the 'SQL*Net roundtrips to/from client'
> data below matches that.
> The number of additional 'consistent gets' is
> not much less than that.
> This suggests that there is an extra consistent
> get for each fetch,
> unless the previous fetch finished at a
> database block boundary.
> 
> @   Regards,
> @   Steve Adams
> @   http://www.ixora.com.au/
> @   http://www.christianity.net.au/
> 
> 
> -Original Message-
> From: Paul Parker
> [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, 13 February 2001 8:41
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Consistent Gets?
> 
> 
> Hi Steve, thanks for your response.
> 
> I had analyzed the tables - chain_cnt = 0. 
> Just
> to verify, I also checked "table fetch
> continued
> row" which was 0 on all my tests.
> 
> Any other ideas?
> 
> Paul
> 
> --- Steve Adams <[EMAIL PROTECTED]>
> wrote:
> > Hi Paul,
> > 
> > Analyze the table and see if you have any
> > chained rows. If there are chained
> > rows and if the STATE_CODE field is not
> always
> > in the last row piece, then a
> > extra consistent gets will be needed to get
> the
> > column values from the trailing
> > row pieces of chained rows that are not
> > excluded by the where clause predicates.
> > 
> > @   Regards,
> > @   Steve Adams
> > @   http://www.ixora.com.au/
> > @   http://www.christianity.net.au/
> > 
> > 
> > -Original Message-
> > Sent: Tuesday, 13 February 2001 3:31
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > Hi all,
> > 
> > Could someone attempt to explain the
> difference
> > in the no. of "consistent gets" reported for
> > these 2 queries?
> > 
> > I have a table (TEST1) made up of 11,333
> > blocks.
> > No indexes on this table.  I run two queries,
> > both reported to do full table scans (as
> > expected), one returning all the rows from
> the
> > table and one with a bogus condition
> resulting
> > in
> > no rows returned.  I expected, that since
> both
> > queries did full table scans, that the amount
> > of
> > IO would be the same.  Yet the query which
> > returned data did 3 times as much IO as the
> one
> > which did not.  Output follows :
> > 
> > 
> > 12:08:16 T10-SERVCBO-CH> @p2
> > 12:08:22 T10-SERVCBO-CH> set autotrace
> > traceonly
> > exp stat
> > 12:08:22 T10-SERVCBO-CH> select
> > 12:08:22   2  *
> > 12:08:22   3  from
> > 12:08:22   4  test1
> > 12:08:22   5  where
> > 12:08:22   6  pay_dealer_date >= '01/01/2000'
> > 12:08:22   7  -- and state_code = 'AB' 
> 
> > BOGUS CONDITION
> > 12:08:22   8  ;
> > 
> > 375043 rows selected.
> > 
> > Elapsed: 00:00:55.46
> > 
> > Execution Plan
> >
>
--
> >0  SELECT STATEMENT Optimizer=CHOOSE
> >10   TABLE ACCESS (FULL) OF 'TEST1'
> > 
> > Statistics
> >
>
--
> >   0  recursive calls
> >  15  db block gets
> >   35581  consistent gets
> >   10575  physical reads
> >   0  redo size
> >66817080  bytes sent via SQL*Net to client
> > 2775646  bytes received via SQL*Net from
> > client
> >   25004  SQL*Net roundtrips to/from
> client
> >   0  sorts (memory)
> >   0  sorts (disk)
> >  375043  rows processed
> > 
> > 
> > 12:09:18 T10-SERVCBO-CH> ed p2
> > 
> > 12:09:33 T10-SERVCBO-CH> @p2
> > 12:09:35 T10-SERVCBO-CH> set autotrace
> > traceonly
> > exp stat
> > 12:09:35 T10-SERVCBO-CH> select
> > 12:09:35   2  *
> > 12:09:35   3  from
> > 12:09:35   4  test1
> > 12:09:35   5  where
> > 12:09:35   6  pay_dealer_date >= '01/01/2000'
> > 12:09:35   7  and state_code = 'AB&

RE: Consistent Gets?

[Steve Adams]

Hi Paul,

Thanks for sending the V$MYSTAT data offline.

Try this explanation ...
The first query is fetching all the rows using an array size of 15.
The V$MYSTAT data showed a difference of about 25000 'user calls',
and the 'SQL*Net roundtrips to/from client' data below matches that.
The number of additional 'consistent gets' is not much less than that.
This suggests that there is an extra consistent get for each fetch,
unless the previous fetch finished at a database block boundary.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/


-Original Message-
Sent: Tuesday, 13 February 2001 8:41
To: Multiple recipients of list ORACLE-L


Hi Steve, thanks for your response.

I had analyzed the tables - chain_cnt = 0.  Just
to verify, I also checked "table fetch continued
row" which was 0 on all my tests.

Any other ideas?

Paul

--- Steve Adams <[EMAIL PROTECTED]> wrote:
> Hi Paul,
> 
> Analyze the table and see if you have any
> chained rows. If there are chained
> rows and if the STATE_CODE field is not always
> in the last row piece, then a
> extra consistent gets will be needed to get the
> column values from the trailing
> row pieces of chained rows that are not
> excluded by the where clause predicates.
> 
> @   Regards,
> @   Steve Adams
> @   http://www.ixora.com.au/
> @   http://www.christianity.net.au/
> 
> 
> -Original Message-
> Sent: Tuesday, 13 February 2001 3:31
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi all,
> 
> Could someone attempt to explain the difference
> in the no. of "consistent gets" reported for
> these 2 queries?
> 
> I have a table (TEST1) made up of 11,333
> blocks.
> No indexes on this table.  I run two queries,
> both reported to do full table scans (as
> expected), one returning all the rows from the
> table and one with a bogus condition resulting
> in
> no rows returned.  I expected, that since both
> queries did full table scans, that the amount
> of
> IO would be the same.  Yet the query which
> returned data did 3 times as much IO as the one
> which did not.  Output follows :
> 
> 
> 12:08:16 T10-SERVCBO-CH> @p2
> 12:08:22 T10-SERVCBO-CH> set autotrace
> traceonly
> exp stat
> 12:08:22 T10-SERVCBO-CH> select
> 12:08:22   2  *
> 12:08:22   3  from
> 12:08:22   4  test1
> 12:08:22   5  where
> 12:08:22   6  pay_dealer_date >= '01/01/2000'
> 12:08:22   7  -- and state_code = 'AB'
> BOGUS CONDITION
> 12:08:22   8  ;
> 
> 375043 rows selected.
> 
> Elapsed: 00:00:55.46
> 
> Execution Plan
>
------
>0  SELECT STATEMENT Optimizer=CHOOSE
>10   TABLE ACCESS (FULL) OF 'TEST1'
> 
> Statistics
>
--
>   0  recursive calls
>  15  db block gets
>   35581  consistent gets
>   10575  physical reads
>   0  redo size
>66817080  bytes sent via SQL*Net to client
> 2775646  bytes received via SQL*Net from
> client
>   25004  SQL*Net roundtrips to/from client
>   0  sorts (memory)
>   0  sorts (disk)
>  375043  rows processed
> 
> 
> 12:09:18 T10-SERVCBO-CH> ed p2
> 
> 12:09:33 T10-SERVCBO-CH> @p2
> 12:09:35 T10-SERVCBO-CH> set autotrace
> traceonly
> exp stat
> 12:09:35 T10-SERVCBO-CH> select
> 12:09:35   2  *
> 12:09:35   3  from
> 12:09:35   4  test1
> 12:09:35   5  where
> 12:09:35   6  pay_dealer_date >= '01/01/2000'
> 12:09:35   7  and state_code = 'AB' 
> BOGUS
> CONDITION
> 12:09:35   8  ;
> 
> no rows selected
> 
> Elapsed: 00:00:03.43
> 
> Execution Plan
>
--
>0  SELECT STATEMENT Optimizer=CHOOSE
>10   TABLE ACCESS (FULL) OF 'TEST1'
> 
> 
> 
> Statistics
>
--
>   0  recursive calls
>  15  db block gets
>   11337  consistent gets
>   10573  physical reads
>   0  redo size
>1860  bytes sent via SQL*Net to client
> 313  bytes received via SQL*Net from
> client
>   1  SQL*Net roundtrips to/from client
>   0  sorts (memory)
>   0  sorts (disk)
>   0  rows processed
> 
> 12:09:38 T10-SERVCBO-CH> spool off
> 
> 
> What am I missing here?  Any help appreciated.
> 
> Thanx
> Paul
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
>

RE: Consistent Gets?

[Paul Parker]

Hi Steve,

The 'no buffer to keep pinned count' statistic
before and after were both 0.  I have included
all stats from v$mystat for reference.  

NAMEBefore  After   Differerence
-
background checkpoints completed0   0   0
background checkpoints started  0   0   0
background timeouts 0   0   0
branch node splits  0   0   0
buffer is not pinned count  117564  153170  35606
buffer is pinned count  162016200
bytes received via SQL*Net from client  2802962
5582722 2779760
bytes received via SQL*Net from dblink  0   0   0
bytes sent via SQL*Net to client66850048
133685423   66835375
bytes sent via SQL*Net to dblink0   0   0
Cached Commit SCN referenced0   0   0
calls to get snapshot scn: kcmgss   902 919 17
calls to kcmgas 0   0   0
calls to kcmgcs 0   0   0
calls to kcmgrs 0   0   0
change write time   0   0   0
cleanouts and rollbacks - consistent read gets  0
0   0
cleanouts only - consistent read gets   1   1   0
cluster key scan block gets 414 414 0
cluster key scans   365 365 0
cold recycle reads  0   0   0
commit cleanout failures: block lost0   0   0
commit cleanout failures: buffer being written  0
0   0
commit cleanout failures: callback failure  0   0   0
commit cleanout failures: cannot pin0   0   0
commit cleanout failures: hot backup in progress
0   0   0
commit cleanout failures: write disabled0   0   0
commit cleanouts0   0   0
commit cleanouts successfully completed 0   0   0
Commit SCN cached   0   0   0
consistent changes  0   0   0
consistent gets 118519  154128  35609
CPU used by this session364749461299
CPU used when call started  364749461299
CR blocks created   0   0   0
current blocks converted for CR 0   0   0
cursor authentications  38  38  0
data blocks consistent reads - undo records
applied 0   0   0
db block changes44  52  8
db block gets   390 441 51
DBWR buffers scanned0   0   0
DBWR checkpoint buffers written 0   0   0
DBWR checkpoints0   0   0
DBWR cross instance writes  0   0   0
DBWR free buffers found 0   0   0
DBWR lru scans  0   0   0
DBWR make free requests 0   0   0
DBWR revisited being-written buffer 0   0   0
DBWR summed scan depth  0   0   0
DBWR transaction table writes   0   0   0
DBWR undo block writes  0   0   0
DDL statements parallelized 0   0   0
deferred (CURRENT) block cleanout applications  0
0   0
DFO trees parallelized  0   0   0
dirty buffers inspected 0   0   0
DML statements parallelized 0   0   0
enqueue conversions 0   0   0
enqueue deadlocks   0   0   0
enqueue releases64  64  0
enqueue requests66  66  0
enqueue timeouts0   0   0
enqueue waits   0   0   0
exchange deadlocks  0   0   0
execute count   890 903 13
free buffer inspected   0   0   0
free buffer requested   91463   102037  10574
global cache blocks corrupt 0   0   0
global cache convert time   0   0   0
global cache convert timeouts   0   0   0
global cache converts   0   0   0
global cache cr block log flush time0   0   0
global cache cr block log flushes   0   0   0
global cache cr block receive time  0   0   0
global cache cr block send time 0   0   0
global cache cr block serve time0   0   0
global cache cr blocks received 0   0   0
global cache cr blocks served   0   0   0
global cache cr requests blocked0   0   0
global cache cr timeouts0   0   0
global cache defers 0   0   0
global cache freelist waits 0   0   0
global cache get time   0   0   0
global cache gets   0   0   0
global cache prepare failures   0   0   0
global lock async converts  0   0   0
global lock async gets  0   0   0
global lock convert time0   0   0
global lock get time0   0   0
global lock releases0   0   0
global lock sync converts   0   0   0
global lock sync gets   0   0   0
hot buffers moved to head of LRU0   0   0
immediate (CR) block cleanout applications  1   1   0
immediate (CURRENT) block cleanout applications 0
0   0
index fast full scans (direct read) 0   0   0
index fast full scans (full)0   0   0
index fast full scans (rowid ranges)0   0   

RE: Consistent Gets?

[Steve Adams]

Hi Paul,

Is there a difference in the 'no buffer to keep pinned count' statistic in
V$MYSTAT?

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/


-Original Message-
Sent: Tuesday, 13 February 2001 8:41
To: Multiple recipients of list ORACLE-L


Hi Steve, thanks for your response.

I had analyzed the tables - chain_cnt = 0.  Just
to verify, I also checked "table fetch continued
row" which was 0 on all my tests.

Any other ideas?

Paul

--- Steve Adams <[EMAIL PROTECTED]> wrote:
> Hi Paul,
>
> Analyze the table and see if you have any
> chained rows. If there are chained
> rows and if the STATE_CODE field is not always
> in the last row piece, then a
> extra consistent gets will be needed to get the
> column values from the trailing
> row pieces of chained rows that are not
> excluded by the where clause predicates.
>
> @   Regards,
> @   Steve Adams
> @   http://www.ixora.com.au/
> @   http://www.christianity.net.au/
>
>
> -Original Message-
> Sent: Tuesday, 13 February 2001 3:31
> To: Multiple recipients of list ORACLE-L
>
>
> Hi all,
>
> Could someone attempt to explain the difference
> in the no. of "consistent gets" reported for
> these 2 queries?
>
> I have a table (TEST1) made up of 11,333
> blocks.
> No indexes on this table.  I run two queries,
> both reported to do full table scans (as
> expected), one returning all the rows from the
> table and one with a bogus condition resulting
> in
> no rows returned.  I expected, that since both
> queries did full table scans, that the amount
> of
> IO would be the same.  Yet the query which
> returned data did 3 times as much IO as the one
> which did not.  Output follows :
>
>
> 12:08:16 T10-SERVCBO-CH> @p2
> 12:08:22 T10-SERVCBO-CH> set autotrace
> traceonly
> exp stat
> 12:08:22 T10-SERVCBO-CH> select
> 12:08:22   2  *
> 12:08:22   3  from
> 12:08:22   4  test1
> 12:08:22   5  where
> 12:08:22   6  pay_dealer_date >= '01/01/2000'
> 12:08:22   7  -- and state_code = 'AB'
> BOGUS CONDITION
> 12:08:22   8  ;
>
> 375043 rows selected.
>
> Elapsed: 00:00:55.46
>
> Execution Plan
>
------
>0  SELECT STATEMENT Optimizer=CHOOSE
>10   TABLE ACCESS (FULL) OF 'TEST1'
>
> Statistics
>
--
>   0  recursive calls
>  15  db block gets
>   35581  consistent gets
>   10575  physical reads
>   0  redo size
>66817080  bytes sent via SQL*Net to client
> 2775646  bytes received via SQL*Net from
> client
>   25004  SQL*Net roundtrips to/from client
>   0  sorts (memory)
>   0  sorts (disk)
>  375043  rows processed
>
>
> 12:09:18 T10-SERVCBO-CH> ed p2
>
> 12:09:33 T10-SERVCBO-CH> @p2
> 12:09:35 T10-SERVCBO-CH> set autotrace
> traceonly
> exp stat
> 12:09:35 T10-SERVCBO-CH> select
> 12:09:35   2  *
> 12:09:35   3  from
> 12:09:35   4  test1
> 12:09:35   5  where
> 12:09:35   6  pay_dealer_date >= '01/01/2000'
> 12:09:35   7  and state_code = 'AB'
> BOGUS
> CONDITION
> 12:09:35   8  ;
>
> no rows selected
>
> Elapsed: 00:00:03.43
>
> Execution Plan
>
--
>0  SELECT STATEMENT Optimizer=CHOOSE
>10   TABLE ACCESS (FULL) OF 'TEST1'
>
>
>
> Statistics
>
--
>   0  recursive calls
>  15  db block gets
>   11337  consistent gets
>   10573  physical reads
>   0  redo size
>1860  bytes sent via SQL*Net to client
> 313  bytes received via SQL*Net from
> client
>   1  SQL*Net roundtrips to/from client
>   0  sorts (memory)
>   0  sorts (disk)
>   0  rows processed
>
> 12:09:38 T10-SERVCBO-CH> spool off
>
>
> What am I missing here?  Any help appreciated.
>
> Thanx
> Paul
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Steve Adams
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051
> FAX: (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send
> an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling
> of 'ListGuru') and i

RE: Consistent Gets?

[Paul Parker]

Hi Steve, thanks for your response.

I had analyzed the tables - chain_cnt = 0.  Just
to verify, I also checked "table fetch continued
row" which was 0 on all my tests.

Any other ideas?

Paul

--- Steve Adams <[EMAIL PROTECTED]> wrote:
> Hi Paul,
> 
> Analyze the table and see if you have any
> chained rows. If there are chained
> rows and if the STATE_CODE field is not always
> in the last row piece, then a
> extra consistent gets will be needed to get the
> column values from the trailing
> row pieces of chained rows that are not
> excluded by the where clause predicates.
> 
> @   Regards,
> @   Steve Adams
> @   http://www.ixora.com.au/
> @   http://www.christianity.net.au/
> 
> 
> -Original Message-
> Sent: Tuesday, 13 February 2001 3:31
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi all,
> 
> Could someone attempt to explain the difference
> in the no. of "consistent gets" reported for
> these 2 queries?
> 
> I have a table (TEST1) made up of 11,333
> blocks.
> No indexes on this table.  I run two queries,
> both reported to do full table scans (as
> expected), one returning all the rows from the
> table and one with a bogus condition resulting
> in
> no rows returned.  I expected, that since both
> queries did full table scans, that the amount
> of
> IO would be the same.  Yet the query which
> returned data did 3 times as much IO as the one
> which did not.  Output follows :
> 
> 
> 12:08:16 T10-SERVCBO-CH> @p2
> 12:08:22 T10-SERVCBO-CH> set autotrace
> traceonly
> exp stat
> 12:08:22 T10-SERVCBO-CH> select
> 12:08:22   2  *
> 12:08:22   3  from
> 12:08:22   4  test1
> 12:08:22   5  where
> 12:08:22   6  pay_dealer_date >= '01/01/2000'
> 12:08:22   7  -- and state_code = 'AB'
> BOGUS CONDITION
> 12:08:22   8  ;
> 
> 375043 rows selected.
> 
> Elapsed: 00:00:55.46
> 
> Execution Plan
>
------
>    0  SELECT STATEMENT Optimizer=CHOOSE
>10   TABLE ACCESS (FULL) OF 'TEST1'
> 
> Statistics
>
--
>   0  recursive calls
>  15  db block gets
>   35581  consistent gets
>   10575  physical reads
>   0  redo size
>66817080  bytes sent via SQL*Net to client
> 2775646  bytes received via SQL*Net from
> client
>   25004  SQL*Net roundtrips to/from client
>   0  sorts (memory)
>   0  sorts (disk)
>  375043  rows processed
> 
> 
> 12:09:18 T10-SERVCBO-CH> ed p2
> 
> 12:09:33 T10-SERVCBO-CH> @p2
> 12:09:35 T10-SERVCBO-CH> set autotrace
> traceonly
> exp stat
> 12:09:35 T10-SERVCBO-CH> select
> 12:09:35   2  *
> 12:09:35   3  from
> 12:09:35   4  test1
> 12:09:35   5  where
> 12:09:35   6  pay_dealer_date >= '01/01/2000'
> 12:09:35   7  and state_code = 'AB' 
> BOGUS
> CONDITION
> 12:09:35   8  ;
> 
> no rows selected
> 
> Elapsed: 00:00:03.43
> 
> Execution Plan
>
--
>0  SELECT STATEMENT Optimizer=CHOOSE
>10   TABLE ACCESS (FULL) OF 'TEST1'
> 
> 
> 
> Statistics
>
--
>   0  recursive calls
>  15  db block gets
>   11337  consistent gets
>   10573  physical reads
>   0  redo size
>1860  bytes sent via SQL*Net to client
> 313  bytes received via SQL*Net from
> client
>   1  SQL*Net roundtrips to/from client
>   0  sorts (memory)
>   0  sorts (disk)
>   0  rows processed
> 
> 12:09:38 T10-SERVCBO-CH> spool off
> 
> 
> What am I missing here?  Any help appreciated.
> 
> Thanx
> Paul
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Steve Adams
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051 
> FAX: (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send
> an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling
> of 'ListGuru') and in
> the message BODY, include a line containing:
> UNSUB ORACLE-L
> (or the name of mailing list you want to be
> removed from).  You may
> also send the HELP command for other
> information (like subscribing).


___

RE: Consistent Gets?

[Steve Adams]

Hi Paul,

Analyze the table and see if you have any chained rows. If there are chained
rows and if the STATE_CODE field is not always in the last row piece, then a
extra consistent gets will be needed to get the column values from the trailing
row pieces of chained rows that are not excluded by the where clause predicates.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/


-Original Message-
Sent: Tuesday, 13 February 2001 3:31
To: Multiple recipients of list ORACLE-L


Hi all,

Could someone attempt to explain the difference
in the no. of "consistent gets" reported for
these 2 queries?

I have a table (TEST1) made up of 11,333 blocks.
No indexes on this table.  I run two queries,
both reported to do full table scans (as
expected), one returning all the rows from the
table and one with a bogus condition resulting in
no rows returned.  I expected, that since both
queries did full table scans, that the amount of
IO would be the same.  Yet the query which
returned data did 3 times as much IO as the one
which did not.  Output follows :


12:08:16 T10-SERVCBO-CH> @p2
12:08:22 T10-SERVCBO-CH> set autotrace traceonly
exp stat
12:08:22 T10-SERVCBO-CH> select
12:08:22   2  *
12:08:22   3  from
12:08:22   4  test1
12:08:22   5  where
12:08:22   6  pay_dealer_date >= '01/01/2000'
12:08:22   7  -- and state_code = 'AB'  
BOGUS CONDITION
12:08:22   8  ;

375043 rows selected.

Elapsed: 00:00:55.46

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE
   10   TABLE ACCESS (FULL) OF 'TEST1'

Statistics
--
  0  recursive calls
     15  db block gets
  35581  consistent gets
  10575  physical reads
  0  redo size
   66817080  bytes sent via SQL*Net to client
2775646  bytes received via SQL*Net from
client
  25004  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
 375043  rows processed


12:09:18 T10-SERVCBO-CH> ed p2

12:09:33 T10-SERVCBO-CH> @p2
12:09:35 T10-SERVCBO-CH> set autotrace traceonly
exp stat
12:09:35 T10-SERVCBO-CH> select
12:09:35   2  *
12:09:35   3  from
12:09:35   4  test1
12:09:35   5  where
12:09:35   6  pay_dealer_date >= '01/01/2000'
12:09:35   7  and state_code = 'AB'    BOGUS
CONDITION
12:09:35   8  ;

no rows selected

Elapsed: 00:00:03.43

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE
   10   TABLE ACCESS (FULL) OF 'TEST1'



Statistics
------
  0  recursive calls
 15  db block gets
  11337  consistent gets
  10573  physical reads
  0  redo size
   1860  bytes sent via SQL*Net to client
313  bytes received via SQL*Net from
client
  1  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  0  rows processed

12:09:38 T10-SERVCBO-CH> spool off


What am I missing here?  Any help appreciated.

Thanx
Paul

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Adams
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Consistent Gets?

[Tim Sawmiller]

I believe it's because the query that returned rows had to assemble a result set, 
hence the extra consistent gets.  The second query had no result set, so much less 
work was done (less consistent gets).  

>>> [EMAIL PROTECTED] 02/12/01 12:30PM >>>
Hi all,

Could someone attempt to explain the difference
in the no. of "consistent gets" reported for
these 2 queries?

I have a table (TEST1) made up of 11,333 blocks. 
No indexes on this table.  I run two queries,
both reported to do full table scans (as
expected), one returning all the rows from the
table and one with a bogus condition resulting in
no rows returned.  I expected, that since both
queries did full table scans, that the amount of
IO would be the same.  Yet the query which
returned data did 3 times as much IO as the one
which did not.  Output follows :


12:08:16 T10-SERVCBO-CH> @p2
12:08:22 T10-SERVCBO-CH> set autotrace traceonly
exp stat
12:08:22 T10-SERVCBO-CH> select
12:08:22   2  *
12:08:22   3  from
12:08:22   4  test1
12:08:22   5  where
12:08:22   6  pay_dealer_date >= '01/01/2000'
12:08:22   7  -- and state_code = 'AB'  
BOGUS CONDITION
12:08:22   8  ;

375043 rows selected.

Elapsed: 00:00:55.46

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE  
   10   TABLE ACCESS (FULL) OF 'TEST1'   

Statistics
--
  0  recursive calls     
     15  db block gets   
  35581  consistent gets 
  10575  physical reads  
  0  redo size   
   66817080  bytes sent via SQL*Net to client
2775646  bytes received via SQL*Net from
client   
  25004  SQL*Net roundtrips to/from client   
  0  sorts (memory)  
  0  sorts (disk)
 375043  rows processed  


12:09:18 T10-SERVCBO-CH> ed p2

12:09:33 T10-SERVCBO-CH> @p2
12:09:35 T10-SERVCBO-CH> set autotrace traceonly
exp stat
12:09:35 T10-SERVCBO-CH> select
12:09:35   2  *
12:09:35   3  from
12:09:35   4  test1
12:09:35   5  where
12:09:35   6  pay_dealer_date >= '01/01/2000'
12:09:35   7  and state_code = 'AB'    BOGUS
CONDITION
12:09:35   8  ;

no rows selected

Elapsed: 00:00:03.43

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE  
   10   TABLE ACCESS (FULL) OF 'TEST1'   



Statistics
--
      0  recursive calls 
 15  db block gets   
  11337  consistent gets 
  10573  physical reads  
  0  redo size   
   1860  bytes sent via SQL*Net to client
313  bytes received via SQL*Net from
client   
  1  SQL*Net roundtrips to/from client   
  0  sorts (memory)  
  0  sorts (disk)
  0  rows processed  

12:09:38 T10-SERVCBO-CH> spool off


What am I missing here?  Any help appreciated.

Thanx
Paul


__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 
a year!  http://personal.mail.yahoo.com/ 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Paul Parker
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Consistent Gets?

[Paul Parker]

Thanks for your response Riyaj.  

I initially had the same thought so I had re-run
the queries a no. of times, all giving the same
(inconsistent) results.  I also confirmed from
v$mystat that "consistent gets" as reported by
autotrace were being reported proportionately to
"no work - consistent read gets" - an indication
to me anyway that it was not having to re-create 
the blocks for the consistent view.

Any other ideas?

Paul

--- [EMAIL PROTECTED] wrote:
> 
> Hi
>This may be due to commit cleanout
> mechanism. After populating the
> table, your commit simply marks the transaction
> as completed in the
> rollback segment header and does not clean the
> rows in the block. So the
> flags in the row header portion of the block
> indicates that the transaction
> is open and active. When you do a select on
> those rows Oracle sees that the
> transaction is open and goes to the rollback
> segment header to check the
> status of the transaction, and then marks the
> row headers to committed
> state.
> When you do the select second time, since
> the row headers indicates the
> commit status, the session doesn't need to do
> that much work to get the
> consistent data.
>To verify this behavior, do the first select
> again and you could see
> comparable consistent gets.
> 
> Thanks
> Riyaj "Re-yas" Shamsudeen
> Certified Oracle DBA
> "This is my opinion and does not bind my
> employer. Use at your own risk"
> 
> 
> 
>
>
> 
> Paul Parker
>
> 
> Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>  
> yahoo.com> cc: 
>
> 
> Sent by:  
> Subject: Consistent Gets?  
>  
> [EMAIL PROTECTED]
>
> 
> m  
>
> 
>
>
> 
>
>
> 
> 02/12/01 11:30 
>
> 
> AM 
>
> 
> Please respond 
>
> 
> to ORACLE-L
>
> 
>
>        
> 
>
>
> 
> 
> 
> 
> 
> Hi all,
> 
> Could someone attempt to explain the difference
> in the no. of "consistent gets" reported for
> these 2 queries?
> 
> I have a table (TEST1) made up of 11,333
> blocks.
> No indexes on this table.  I run two queries,
> both reported to do full table scans (as
> expected), one returning all the rows from the
> table and one with a bogus condition resulting
> in
> no rows returned.  I expected, that since both
> queries did full table scans, that the amount
> of
> IO would be the same.  Yet the query which
> returned data did 3 times as much IO as the one
> which did not.  Output follows :
> 
> 
> 12:08:16 T10-SERVCBO-CH> @p2
> 12:08:22 T10-SERVCBO-CH> set autotrace
> traceonly
> exp stat
> 12:08:22 T10-SERVCBO-CH> select
> 12:08:22   2  *
> 12:08:22   3  from
> 12:08:22   4  test1
> 12:08:22   5  where
> 12:08:22   6  pay_dealer_date >= '01/01/2000'
> 12:08:22   7  -- and state_code = 'AB'     
>
> BOGUS CONDITION
> 12:08:22   8  ;
> 
> 375043 rows selected.
> 
> Elapsed: 00:00:55.46
> 
> Execution Plan
>
--
>0  SELECT STATEMENT Optimizer=CHOOSE
>10   TABLE ACCESS (FULL) OF 'TEST1'
> 
> Statistics
>
--
>   

Re: Consistent Gets?

[Paul Parker]

Thanks for your response Riyaj.  

I initially had the same thought so I had re-run
the queries a no. of times, all giving the same
(inconsistent) results.  I also confirmed from
v$mystat that "consistent gets" as reported by
autotrace were being reported proportionately to
"no work - consistent read gets" - an indication
to me anyway that it was not having to re-create 
the blocks for the consistent view.

Any other ideas?

Paul

--- [EMAIL PROTECTED] wrote:
> 
> Hi
>This may be due to commit cleanout
> mechanism. After populating the
> table, your commit simply marks the transaction
> as completed in the
> rollback segment header and does not clean the
> rows in the block. So the
> flags in the row header portion of the block
> indicates that the transaction
> is open and active. When you do a select on
> those rows Oracle sees that the
> transaction is open and goes to the rollback
> segment header to check the
> status of the transaction, and then marks the
> row headers to committed
> state.
> When you do the select second time, since
> the row headers indicates the
> commit status, the session doesn't need to do
> that much work to get the
> consistent data.
>To verify this behavior, do the first select
> again and you could see
> comparable consistent gets.
> 
> Thanks
> Riyaj "Re-yas" Shamsudeen
> Certified Oracle DBA
> "This is my opinion and does not bind my
> employer. Use at your own risk"
> 
> 
> 
>
>
> 
> Paul Parker
>
> 
> Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>  
> yahoo.com> cc: 
>
> 
> Sent by:  
> Subject: Consistent Gets?  
>  
> [EMAIL PROTECTED]
>
> 
> m  
>
> 
>
>
> 
>
>
> 
> 02/12/01 11:30 
>
> 
> AM 
>
> 
> Please respond 
>
> 
> to ORACLE-L
>
> 
>
>        
> 
>
>
> 
> 
> 
> 
> 
> Hi all,
> 
> Could someone attempt to explain the difference
> in the no. of "consistent gets" reported for
> these 2 queries?
> 
> I have a table (TEST1) made up of 11,333
> blocks.
> No indexes on this table.  I run two queries,
> both reported to do full table scans (as
> expected), one returning all the rows from the
> table and one with a bogus condition resulting
> in
> no rows returned.  I expected, that since both
> queries did full table scans, that the amount
> of
> IO would be the same.  Yet the query which
> returned data did 3 times as much IO as the one
> which did not.  Output follows :
> 
> 
> 12:08:16 T10-SERVCBO-CH> @p2
> 12:08:22 T10-SERVCBO-CH> set autotrace
> traceonly
> exp stat
> 12:08:22 T10-SERVCBO-CH> select
> 12:08:22   2  *
> 12:08:22   3  from
> 12:08:22   4  test1
> 12:08:22   5  where
> 12:08:22   6  pay_dealer_date >= '01/01/2000'
> 12:08:22   7  -- and state_code = 'AB'     
>
> BOGUS CONDITION
> 12:08:22   8  ;
> 
> 375043 rows selected.
> 
> Elapsed: 00:00:55.46
> 
> Execution Plan
>
--
>0  SELECT STATEMENT Optimizer=CHOOSE
>10   TABLE ACCESS (FULL) OF 'TEST1'
> 
> Statistics
>
--
>   

Re: Consistent Gets?

[Riyaj_Shamsudeen]

Hi
   This may be due to commit cleanout mechanism. After populating the
table, your commit simply marks the transaction as completed in the
rollback segment header and does not clean the rows in the block. So the
flags in the row header portion of the block indicates that the transaction
is open and active. When you do a select on those rows Oracle sees that the
transaction is open and goes to the rollback segment header to check the
status of the transaction, and then marks the row headers to committed
state.
When you do the select second time, since the row headers indicates the
commit status, the session doesn't need to do that much work to get the
consistent data.
   To verify this behavior, do the first select again and you could see
comparable consistent gets.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"This is my opinion and does not bind my employer. Use at your own risk"



   
 
Paul Parker
 
  
yahoo.com> cc: 
 
Sent by:   Subject: Consistent Gets?   
 
[EMAIL PROTECTED]
 
m  
 
   
 
   
 
02/12/01 11:30 
 
AM 
 
Please respond 
 
to ORACLE-L
 
   
 
   
 




Hi all,

Could someone attempt to explain the difference
in the no. of "consistent gets" reported for
these 2 queries?

I have a table (TEST1) made up of 11,333 blocks.
No indexes on this table.  I run two queries,
both reported to do full table scans (as
expected), one returning all the rows from the
table and one with a bogus condition resulting in
no rows returned.  I expected, that since both
queries did full table scans, that the amount of
IO would be the same.  Yet the query which
returned data did 3 times as much IO as the one
which did not.  Output follows :


12:08:16 T10-SERVCBO-CH> @p2
12:08:22 T10-SERVCBO-CH> set autotrace traceonly
exp stat
12:08:22 T10-SERVCBO-CH> select
12:08:22   2  *
12:08:22   3  from
12:08:22   4  test1
12:08:22   5  where
12:08:22   6  pay_dealer_date >= '01/01/2000'
12:08:22   7  -- and state_code = 'AB' 
BOGUS CONDITION
12:08:22   8  ;

375043 rows selected.

Elapsed: 00:00:55.46

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE
   10   TABLE ACCESS (FULL) OF 'TEST1'

Statistics
--
  0  recursive calls
 15  db block gets
  35581  consistent gets
  10575  physical reads
  0  redo size
   66817080  bytes sent via SQL*Net to client
2775646  bytes received via SQL*Net from
client
  25004  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
 375043  rows processed


12:09:18 T10-SERVCBO-CH> ed p2

12:09:33 T10-SERVCBO-CH> @p2
12:09:35 T10-SERVCBO-CH> set autotrace traceonly
exp stat
12:09:35 T10-SERVCBO-CH> select
12:09:35   2  *
12:09:35   3  from
12:09:35   4  test1
12:09:35   5  where
12:09:35   6  pay_dealer_date >= '01/01/2000'
12:09:35   7  and state_code = 'AB'   BOGUS
CONDITION
12:09:35   8  ;

no rows selected

Elapsed: 00:00:03.43

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE
   10   TABLE ACCESS (FULL) OF 'TEST1'



Statistics
------
  0  recursive calls
 15  db block gets
  11337  consistent gets
  10573  physical reads
  0  redo size
   1860 

Consistent Gets?

[Paul Parker]

Hi all,

Could someone attempt to explain the difference
in the no. of "consistent gets" reported for
these 2 queries?

I have a table (TEST1) made up of 11,333 blocks. 
No indexes on this table.  I run two queries,
both reported to do full table scans (as
expected), one returning all the rows from the
table and one with a bogus condition resulting in
no rows returned.  I expected, that since both
queries did full table scans, that the amount of
IO would be the same.  Yet the query which
returned data did 3 times as much IO as the one
which did not.  Output follows :


12:08:16 T10-SERVCBO-CH> @p2
12:08:22 T10-SERVCBO-CH> set autotrace traceonly
exp stat
12:08:22 T10-SERVCBO-CH> select
12:08:22   2  *
12:08:22   3  from
12:08:22   4  test1
12:08:22   5  where
12:08:22   6  pay_dealer_date >= '01/01/2000'
12:08:22   7  -- and state_code = 'AB'  
BOGUS CONDITION
12:08:22   8  ;

375043 rows selected.

Elapsed: 00:00:55.46

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE  
   10   TABLE ACCESS (FULL) OF 'TEST1'   

Statistics
--
  0  recursive calls 
 15  db block gets   
  35581  consistent gets 
  10575  physical reads  
  0  redo size   
   66817080  bytes sent via SQL*Net to client
2775646  bytes received via SQL*Net from
client   
  25004  SQL*Net roundtrips to/from client   
  0  sorts (memory)  
  0  sorts (disk)
 375043  rows processed  


12:09:18 T10-SERVCBO-CH> ed p2

12:09:33 T10-SERVCBO-CH> @p2
12:09:35 T10-SERVCBO-CH> set autotrace traceonly
exp stat
12:09:35 T10-SERVCBO-CH> select
12:09:35   2  *
12:09:35   3  from
12:09:35   4  test1
12:09:35   5  where
12:09:35   6  pay_dealer_date >= '01/01/2000'
12:09:35   7  and state_code = 'AB'    BOGUS
CONDITION
12:09:35   8  ;

no rows selected

Elapsed: 00:00:03.43

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE  
   10   TABLE ACCESS (FULL) OF 'TEST1'   



Statistics
--
  0  recursive calls     
 15  db block gets   
  11337  consistent gets 
  10573  physical reads  
  0  redo size   
   1860  bytes sent via SQL*Net to client
313  bytes received via SQL*Net from
client   
  1  SQL*Net roundtrips to/from client   
  0  sorts (memory)  
  0  sorts (disk)
  0  rows processed  

12:09:38 T10-SERVCBO-CH> spool off


What am I missing here?  Any help appreciated.

Thanx
Paul


__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 
a year!  http://personal.mail.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Parker
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).