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

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 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_at_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:chao_ping_at_163.com
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 <ORACLE-L_at_fatcity.com>
>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:chao_ping_at_163.com
>>www.cnoug.org(China Oracle User Group)
>>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
>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: 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.net
-- 
Author: chao_ping
  INET: chao_ping_at_vip.163.com

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