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 reads 43914 43910
Elapsed:
------sql2: 00:00:01.69
00:00:01.71
NAME VALUE VALUE
------------------------------------ ---------- ----------
CPU used by this session 170 171
CPU used when call started 170 171
buffer is not pinned count 19889 19889
buffer is pinned count 1706898 1706898
consistent gets 22192 22192
no work - consistent read gets 22174 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 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: 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).
