Re: Why such high buffer gets for a simple insert and inconsistent in every run

  • From: Hemant K Chitale <hemantkchitale@xxxxxxxxx>
  • To: krishsingh.111@xxxxxxxxx
  • Date: Wed, 8 Aug 2018 08:16:44 +0800

A SQL Trace (dbms_session.enable_trace) will tell you the buffer gets from
recursive SQLs.

You could selectively trace specific executions in the session with
disable/enable.

On Mon, 6 Aug 2018, 00:09 kunwar singh, <krishsingh.111@xxxxxxxxx> wrote:

Hello Performance Experts,

I always thought simple insert ( table doesnt have a LOB)  would always
return very few buffer gets ( if only 1 index on the table) . But below
testcase shows 9 or 11 buffer gets everytime i insert these records . If i
keep on repeating the same inserts it drops to ~6 gets/exec.
Can anyone tell why this is the case? I always thought it would be 3
gets/exec for every execution in such a simple insert into and single index
table.

Version:12.1.0.1.0

TESTCASE:
++++++ ++++++ ++++++

13:48:51 SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04
13:48:55 SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.17
13:49:03 SQL> create index emp_ix on emp(empno);
Index created.
Elapsed: 00:00:00.16
13:49:08 SQL> exec dbms_stats.gather_table_stats(user, upper('EMP'),
null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.92
select *from user_tables where table_name='EMP'

================================================================================
TABLE_NAME                     --> EMP
TABLESPACE_NAME                --> USERS
CLUSTER_NAME                   -->
IOT_NAME                       -->
STATUS                         --> VALID
PCT_FREE                       --> 10
PCT_USED                       -->
INI_TRANS                      --> 1
MAX_TRANS                      --> 255
INITIAL_EXTENT                 --> 65536
NEXT_EXTENT                    --> 1048576
MIN_EXTENTS                    --> 1
MAX_EXTENTS                    --> 2147483645
PCT_INCREASE                   -->
FREELISTS                      -->
FREELIST_GROUPS                -->
LOGGING                        --> YES
BACKED_UP                      --> N
NUM_ROWS                       --> 14
BLOCKS                         --> 5
EMPTY_BLOCKS                   --> 0
AVG_SPACE                      --> 0
CHAIN_CNT                      --> 0
AVG_ROW_LEN                    --> 38
AVG_SPACE_FREELIST_BLOCKS      --> 0
NUM_FREELIST_BLOCKS            --> 0
DEGREE                         -->          1
INSTANCES                      -->          1
CACHE                          -->     N
TABLE_LOCK                     --> ENABLED
SAMPLE_SIZE                    --> 14
LAST_ANALYZED                  --> 05-AUG-18
PARTITIONED                    --> NO
IOT_TYPE                       -->
TEMPORARY                      --> N
SECONDARY                      --> N
NESTED                         --> NO
BUFFER_POOL                    --> DEFAULT
FLASH_CACHE                    --> DEFAULT
CELL_FLASH_CACHE               --> DEFAULT
ROW_MOVEMENT                   --> DISABLED
GLOBAL_STATS                   --> YES
USER_STATS                     --> NO
DURATION                       -->
SKIP_CORRUPT                   --> DISABLED
MONITORING                     --> YES
CLUSTER_OWNER                  -->
DEPENDENCIES                   --> DISABLED
COMPRESSION                    --> DISABLED
COMPRESS_FOR                   -->
DROPPED                        --> NO
READ_ONLY                      --> NO
SEGMENT_CREATED            &nbs