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 <[EMAIL PROTECTED]> 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 <[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
> > 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: [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 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: 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).
__________________________________________________
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).
