Buffer Gets stat in 9i - different?

[chrisrlong]

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

[abhaysk]

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?

[pando]

does it happen with all tables? may be the table in 9i has chanined/migrated rows...?

[chrisrlong]

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.

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

[chrisrlong]

Okay - had the DBA make changes to the init params. The only remaining differences are:

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

[pando]

check if there are chained rows

[chrisrlong]

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

[chrisrlong]

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

[jmodic]

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.

[adewri]

Originally posted by chrisrlong
SELECT /*test*/ * FROM (table) WHERE (tablePK) = (constant)
This statement should have buffer_gets larger than the expected 3-4.

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

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.