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).