Buffer Gets stat in 9i - different?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Buffer Gets stat in 9i - different?

  1. #1
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339

    Buffer Gets stat in 9i - different?

    Okay, so they're finally moving to 9i here.

    I have a database in 9i that is very similar to another one I have in 8i - both copies of production probably within a couple of weeks.

    On a very simple query that looks up a single record by PK... 8i says it used 3 buffer gets. 9i says it used 27!

    Did something fundamentally change in 9i? The plans are exactly the same. The costs are the same. The tables and indexes are the same. I checked and double-checked my results and how I got them. I used TOAD (which uses v$sqlarea) and my own package that queries V$SESSTAT.

    What am I missing here?

    Thanks,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Chris :

    I checked in 8.x DB and 9.x DB, both reporting 3 Buffer Gets, for single record read by PK.

    Are your DB_Block_Buffers of 8.x and 9.x same?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    does it happen with all tables? may be the table in 9i has chanined/migrated rows...?

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I checked two other tables quick - got 11 and 49 LRs in 9i and 3 each in 8i.

    Here are the non-version, non-file/directory differences in parameters between the two databases.
    Code:
    PARAMETER				9i		8i
    -----------------------------	-----------	--------------
    db_block_checksum			TRUE		FALSE
    dml_locks				988		2440
    enqueue_resources			2030		3482
    fast_start_io_target		0		5000
    java_pool_size			33554432	20000K
    job_queue_processes			0		9
    log_archive_max_processes		2		1
    log_buffer				10485760	15360000
    max_rollback_segments		49		122
    O7_DICTIONARY_ACCESSIBILITY	FALSE		TRUE
    optimizer_max_permutations	2000		80000
    parallel_max_servers		5		16
    parallel_min_servers		0		1
    processes				200		500
    query_rewrite_enabled		TRUE		FALSE
    query_rewrite_integrity		trusted	enforced
    sessions				225		555
    shared_pool_reserved_size		10905190	5120000
    shared_pool_size			218103808	102400000
    transactions				247		610
    Any more ideas?

    Thanks,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay - had the DBA make changes to the init params. The only remaining differences are:

    Code:
    PARAMETER				9i		8i
    -----------------------------	-----------	--------------
    db_block_checksum			TRUE		FALSE
    fast_start_io_target		2000		5000
    java_pool_size			33554432	20000K
    log_archive_max_processes		2		1
    O7_DICTIONARY_ACCESSIBILITY	FALSE		TRUE
    shared_pool_reserved_size		10905190	5120000
    shared_pool_size			218103808	102400000
    Ran the same three statements and got 28, 358 and 1171 LRs, respectively.

    So I ran the first one a second time and the LRs increased to 32, then 36 on the next run.

    What is going on??

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    check if there are chained rows

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    More info:

    Statement:
    SELECT /* test1 */ * FROM ORG WHERE ORG_PK = :ORG_PK

    Run once - LRs=118
    Run again - now LRs=121 (cumulative), so a delta of 3
    Can do this several times and it increases by 3 each time.

    Change comment (thereby creating a new statement in the SGA)
    Run once - LRs=118
    Every run thereafter of that same statement increases the LRs by 3

    For similar SELECT of INDVL by INDVL_PK, Base=28 and delta=3

    For similar SELECT of DSCLR by DSCLR_PK, Base=11 and delta=3

    So has the definition of the buffer gets changed to include the hard parse cost or something?

    Version = 9.2.0.3.0

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay, test this for me:

    ------------------------------

    SELECT /*test*/ * FROM (table) WHERE (tablePK) = (constant)

    Search for 'test' in v$sqlarea.

    This statement should have buffer_gets larger than the expected 3-4.

    Run the statement again and search for it again.

    Now the statement should have increased by the expected 3-4

    Then run this:

    SELECT /*test*/ * FROM (table) WHERE (tablePK) = (bind variable)

    ...and get the buffer_gets. It will be 3-4

    Then run this and get the buffer_gets:

    SELECT /*test*/ * FROM (table) WHERE ROWNUM = 1

    The buffer_gets will be larger than the expected 1-4

    Run it again and now the buffer_gets should increase by 1-4 only.

    -----------------------------

    Apparently the optimizer is doing something different, but I can't figure out what.

    What I can say is that this is seriously screwing up my ability to optimize statements. Having significantly different buffer_gets stats based on whether the statement uses a value versus a bind (with the same value, generating the same plan) or has been run once already is completely unacceptable.

    Any help would be appreciated,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I can only test this on 9.0.1 right now, but my findings differ from yours - I'm getting buffer gets statistics from v$sqlarea exactly as expected:
    Originally posted by chrisrlong
    SELECT /*test*/ * FROM (table) WHERE (tablePK) = (constant)
    This statement should have buffer_gets larger than the expected 3-4.
    In my case it was exactly as expected - 3 buffer gets.

    SELECT /*test*/ * FROM (table) WHERE ROWNUM = 1

    The buffer_gets will be larger than the expected 1-4
    3 buffer gets in my case.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by chrisrlong
    SELECT /*test*/ * FROM (table) WHERE (tablePK) = (constant)
    This statement should have buffer_gets larger than the expected 3-4.

    Code:
    SQL*Plus: Release 9.2.0.3.0 - Production on Thu Aug 14 00:40:09 2003
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
    With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.3.0 - Production
    
    sys@ORCL.WORLD> exec print_table ('select * from  v$sqlarea where SQL_TEXT like ''%DNAME%''');
    SQL_TEXT                      : select DNAME from dept where deptno=10
    SHARABLE_MEM                  : 6836
    PERSISTENT_MEM                : 568
    RUNTIME_MEM                   : 1612
    SORTS                         : 0
    VERSION_COUNT                 : 1
    LOADED_VERSIONS               : 1
    OPEN_VERSIONS                 : 1
    USERS_OPENING                 : 1
    FETCHES                       : 1
    EXECUTIONS                    : 1
    USERS_EXECUTING               : 0
    LOADS                         : 1
    FIRST_LOAD_TIME               : 2003-08-14/00:42:45
    INVALIDATIONS                 : 0
    PARSE_CALLS                   : 1
    DISK_READS                    : 1
    BUFFER_GETS                   : 2
    ROWS_PROCESSED                : 1
    COMMAND_TYPE                  : 3
    OPTIMIZER_MODE                : CHOOSE
    PARSING_USER_ID               : 59
    PARSING_SCHEMA_ID             : 59
    KEPT_VERSIONS                 : 0
    ADDRESS                       : 69BC3E44
    HASH_VALUE                    : 3193107723
    MODULE                        : SQL*Plus
    MODULE_HASH                   : -625018272
    ACTION                        :
    ACTION_HASH                   : -265190056
    SERIALIZABLE_ABORTS           : 0
    CPU_TIME                      : 0
    ELAPSED_TIME                  : 2117
    IS_OBSOLETE                   : N
    CHILD_LATCH                   : 1
    I got 2.

    Originally posted by chrisrlong
    SELECT /*test*/ * FROM (table) WHERE ROWNUM = 1

    The buffer_gets will be larger than the expected 1-4
    Code:
    sys@ORCL.WORLD> exec print_table ('select * from  v$sqlarea where SQL_TEXT like ''%DNAME%''');
    SQL_TEXT                      : select DNAME from dept where rownum=1
    SHARABLE_MEM                  : 6375
    PERSISTENT_MEM                : 568
    RUNTIME_MEM                   : 1764
    SORTS                         : 0
    VERSION_COUNT                 : 1
    LOADED_VERSIONS               : 1
    OPEN_VERSIONS                 : 1
    USERS_OPENING                 : 1
    FETCHES                       : 2
    EXECUTIONS                    : 1
    USERS_EXECUTING               : 0
    LOADS                         : 1
    FIRST_LOAD_TIME               : 2003-08-14/00:46:54
    INVALIDATIONS                 : 0
    PARSE_CALLS                   : 1
    DISK_READS                    : 0
    BUFFER_GETS                   : 3
    ROWS_PROCESSED                : 1
    COMMAND_TYPE                  : 3
    OPTIMIZER_MODE                : CHOOSE
    PARSING_USER_ID               : 59
    PARSING_SCHEMA_ID             : 59
    KEPT_VERSIONS                 : 0
    ADDRESS                       : 69B6EB9C
    HASH_VALUE                    : 2093180772
    MODULE                        : SQL*Plus
    MODULE_HASH                   : -625018272
    ACTION                        :
    ACTION_HASH                   : -265190056
    SERIALIZABLE_ABORTS           : 0
    CPU_TIME                      : 10015
    ELAPSED_TIME                  : 1705
    IS_OBSOLETE                   : N
    CHILD_LATCH                   : 1
    I got 3.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width