RE: Consistent Gets?

From: Paul Parker <paul_g_parker_at_yahoo.com>
Date: Mon, 12 Feb 2001 21:23:17 -0800
Message-ID: <F001.002B226B.20010212211125@fatcity.com>

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.

NAME Before After Differerence

background checkpoints completed        0       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 1620 1620 0 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 client        66850048
133685423       66835375
bytes sent via SQL*Net to dblink        0       0       0
Cached Commit SCN referenced    0       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 lost    0       0       0
commit cleanout failures: buffer being written  0
0       0
commit cleanout failures: callback failure      0       0       0
commit cleanout failures: cannot pin    0       0       0
commit cleanout failures: hot backup in progress
0       0       0
commit cleanout failures: write disabled        0       0       0
commit cleanouts        0       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 session        3647    4946    1299
CPU used when call started      3647    4946    1299
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 changes        44      52      8
db block gets   390     441     51
DBWR buffers scanned    0       0       0
DBWR checkpoint buffers written 0       0       0
DBWR checkpoints        0       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 releases        64      64      0
enqueue requests        66      66      0
enqueue timeouts        0       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 time    0       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 time        0       0       0
global cache cr blocks received 0       0       0
global cache cr blocks served   0       0       0
global cache cr requests blocked        0       0       0
global cache cr timeouts        0       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 time        0       0       0
global lock get time    0       0       0
global lock releases    0       0       0
global lock sync converts       0       0       0
global lock sync gets   0       0       0
hot buffers moved to head of LRU        0       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       0
instance recovery database freeze count 0       0       0
kcmccs called get current scn   0       0       0
kcmgss read scn without going to DLM    0       0       0
kcmgss waited for batching      0       0       0
leaf node splits        0       0       0
logons cumulative       1       1       0
logons current  1       1       0
messages received       0       0       0
messages sent   0       0       0
native hash arithmetic execute  0       0       0
native hash arithmetic fail     0       0       0
next scns gotten without going to DLM   0       0       0
no buffer to keep pinned count  0       0       0

no work - consistent read gets 116772 152373 35601

opened cursors cumulative       548     561     13
opened cursors current  3       3       0
opens of replaced files 0       0       0
opens requiring cache replacement       0       0       0
OS Block input operations       0       0       0
OS Block output operations      0       0       0
OS Integral shared text size    0       0       0
OS Integral unshared data size  0       0       0
OS Integral unshared stack size 0       0       0
OS Involuntary context switches 0       0       0
OS Maximum resident set size    0       0       0
OS Page faults  0       0       0
OS Page reclaims        0       0       0
OS Signals received     0       0       0
OS Socket messages received     0       0       0
OS Socket messages sent 0       0       0
OS Swaps        0       0       0
OS System time used     0       0       0
OS User time used       0       0       0
OS Voluntary context switches   0       0       0
Parallel operations downgraded 1 to 25 pct      0       0       0
Parallel operations downgraded 25 to 50 pct     0       0       0
Parallel operations downgraded 50 to 75 pct     0       0       0
Parallel operations downgraded 75 to 99 pct     0       0       0
Parallel operations downgraded to serial        0       0       0
Parallel operations not downgraded      0       0       0
parse count (hard)      83      85      2
parse count (total)     632     645     13
parse time cpu  167     167     0
parse time elapsed      293     295     2
physical reads  91457   102030  10573
physical reads direct   0       0       0
physical writes 0       0       0
physical writes direct  0       0       0
physical writes non checkpoint  0       0       0
pinned buffers inspected        0       0       0
prefetched blocks       90442   100233  9791
prefetched blocks aged out before use   0       0       0
process last non-idle time      982038050       982038050       0
PX local messages recv'd        0       0       0
PX local messages sent  0       0       0
PX remote messages recv'd       0       0       0
PX remote messages sent 0       0       0
queries parallelized    0       0       0
recovery array read time        0       0       0
recovery array reads    0       0       0
recovery blocks read    0       0       0
recursive calls 13865   13931   66
recursive cpu usage     105     105     0
redo blocks written     0       0       0
redo buffer allocation retries  0       0       0
redo entries    22      26      4
redo log space requests 0       0       0
redo log space wait time        0       0       0
redo log switch interrupts      0       0       0
redo ordering marks     0       0       0
redo size       7208    8584    1376
redo synch time 0       0       0
redo synch writes       0       0       0
redo wastage    0       0       0
redo write time 0       0       0
redo writer latching time       0       0       0
redo writes     0       0       0
remote instance undo block writes       0       0       0
remote instance undo header writes      0       0       0
rollback changes - undo records applied 0       0       0
rollbacks only - consistent read gets   0       0       0
rows fetched via callback       84      84      0
serializable aborts     0       0       0
session connect time    982038050       982038050       0
session cursor cache count      0       0       0
session cursor cache hits       0       0       0
session logical reads   118909  154569  35660
session pga memory      997476  997476  0
session pga memory max  997476  997476  0
session stored procedure space  0       0       0
session uga memory      56288   56288   0
session uga memory max  103068  103068  0
sorts (disk)    0       0       0
sorts (memory)  211     215     4
sorts (rows)    3435    3708    273
SQL*Net roundtrips to/from client       25190   50219
25029
SQL*Net roundtrips to/from dblink       0       0       0
summed dirty queue length       0       0       0
switch current to new buffer    5       6       1
table fetch by rowid    1561    1566    5
table fetch continued row       85      85      0
table scan blocks gotten        115388  150984  35596
table scan rows gotten  3012949 3388016 375067
table scans (cache partitions)  0       0       0
table scans (direct read)       0       0       0
table scans (long tables)       9       10      1
table scans (rowid ranges)      0       0       0
table scans (short tables)      55      63      8
total file opens        8       8       0
transaction lock background get time    0       0       0
transaction lock background gets        0       0       0
transaction lock foreground requests    0       0       0
transaction lock foreground wait time   0       0       0
transaction rollbacks   0       0       0
transaction tables consistent read rollbacks    0       0

0
transaction tables consistent reads - undo

records applied 0       0       0
Unnecesary process cleanup for SCN batching     0       0       0
user calls      25245   50283   25038
user commits    0       0       0
user rollbacks  0       0       0
write clones created in background      0       0       0
write clones created in foreground      0       0       0

Steve Adams <steve.adams_at_ixora.com.au> wrote:
> 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 <steve.adams_at_ixora.com.au>
> 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
> > 1 0 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
> > 1 0 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: steve.adams_at_ixora.com.au
> >
> > 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: ListGuru_at_fatcity.com (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 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: paul_g_parker_at_yahoo.com
>
> 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: ListGuru_at_fatcity.com (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: Steve Adams
> INET: steve.adams_at_ixora.com.au
>
> 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: ListGuru_at_fatcity.com (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 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: paul_g_parker_at_yahoo.com

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: ListGuru_at_fatcity.com (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).

Received on Mon Feb 12 2001 - 23:23:17 CST