-- regular index
----------------

drop table t purge;
create table t (id number, sometext varchar2(50));
create index i on t(id);
create sequence id_seq;


create or replace procedure manyinserts as
     begin
      DBMS_APPLICATION_INFO.set_module(module_name => 'manyinserts', action_name => 'Do many insert');
      for i in 1..10000 loop
       insert into t values (id_seq.nextval, 'DOES THIS CAUSE BUFFER BUSY WAITS?');
      end loop;
      commit;
     end;
     /  


 create or replace procedure manysessions as
     v_jobno number:=0;
     begin
      for i in 1..50 loop
       dbms_job.submit(v_jobno,'manyinserts;', sysdate);
      end loop;
      commit;
     end;
     /

exec manysessions;

SQL> @ash/ashtop event2 "module='manyinserts'" sysdate-1/24/10 sysdate

    Total                                                                                                      Distinct Distinct
  Seconds     AAS %This   EVENT2                                     FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- --------
     1015     2.8   84% | buffer busy waits [data block]             2023-04-10 09:39:17 2023-04-10 09:39:46       1015       29
      121      .3   10% | enq: TX - index contention [mode=4]        2023-04-10 09:39:17 2023-04-10 09:39:41        121        8
       43      .1    4% | ON CPU                                     2023-04-10 09:39:17 2023-04-10 09:39:46         39       25
       14      .0    1% | row cache mutex                            2023-04-10 09:39:34 2023-04-10 09:39:35          2        2
       10      .0    1% | enq: CR - block range reuse ckpt [mode=6]  2023-04-10 09:39:35 2023-04-10 09:39:42         10        3
        7      .0    1% | library cache: mutex X                     2023-04-10 09:39:17 2023-04-10 09:39:41          7        3
        2      .0    0% | buffer deadlock                            2023-04-10 09:39:23 2023-04-10 09:39:27          2        2
        1      .0    0% | buffer busy waits [segment header]         2023-04-10 09:39:32 2023-04-10 09:39:32          1        1



exec dbms_stats.gather_table_stats(user,'T', method_opt=>'for columns ID size AUTO');

select  /*+ GATHER_PLAN_STATISTICS */ * from T where ID=100; 
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST  +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |      1 |        |       |     4 (100)|      0 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |      1 |    33 |     4   (0)|      0 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN                  | I    |      1 |      1 |       |     3   (0)|      0 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------------------


-- reverse index
----------------

drop index i;
truncate table t drop storage;
create index i on t(id) reverse;

create or replace procedure manyinserts as
     begin
      DBMS_APPLICATION_INFO.set_module(module_name => 'manyinserts_reverseind', action_name => 'Do many insert');
      for i in 1..10000 loop
       insert into t values (id_seq.nextval, 'DOES THIS CAUSE BUFFER BUSY WAITS?');
      end loop;
      commit;
     end;
     /  

exec manysessions;

SQL> @ash/ashtop event2 "module='manyinserts_reverseind'" sysdate-1/24/10 sysdate

    Total                                                                                                      Distinct Distinct
  Seconds     AAS %This   EVENT2                                     FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- --------
      830     2.3   86% | buffer busy waits [data block]             2023-04-10 09:47:01 2023-04-10 09:47:21        813       21
       61      .2    6% | row cache mutex                            2023-04-10 09:47:16 2023-04-10 09:47:19          2        4
       49      .1    5% | ON CPU                                     2023-04-10 09:47:01 2023-04-10 09:47:21         34       21
       13      .0    1% | enq: CR - block range reuse ckpt [mode=6]  2023-04-10 09:47:14 2023-04-10 09:47:21         13        5
        3      .0    0% | latch: redo copy                           2023-04-10 09:47:18 2023-04-10 09:47:18          1        1
        2      .0    0% | library cache: mutex X                     2023-04-10 09:47:18 2023-04-10 09:47:18          2        1
        2      .0    0% | reliable message                           2023-04-10 09:47:14 2023-04-10 09:47:18          2        2
        2      .0    0% | undo segment extension                     2023-04-10 09:47:20 2023-04-10 09:47:20          2        1
        1      .0    0% | buffer busy waits [undo header]            2023-04-10 09:47:04 2023-04-10 09:47:04          1        1



exec dbms_stats.gather_table_stats(user,'T', method_opt=>'for columns ID size AUTO');

select  /*+ GATHER_PLAN_STATISTICS */ * from T where ID=100; 
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST  +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |      1 |        |       |     4 (100)|      0 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |      1 |    33 |     4   (0)|      0 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN                  | I    |      1 |      1 |       |     3   (0)|      0 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------------------

select INDEX_TYPE from DBA_INDEXES where owner='SYS' and index_name='I';

INDEX_TYPE
---------------------------
NORMAL/REV


-- hash index
-------------

drop index i;
truncate table t drop storage;
create index i on t(id) global
     partition by hash(id) partitions 32;
 
create or replace procedure manyinserts as
     begin
      DBMS_APPLICATION_INFO.set_module(module_name => 'manyinserts_hashind', action_name => 'Do many insert');
      for i in 1..10000 loop
       insert into t values (id_seq.nextval, 'DOES THIS CAUSE BUFFER BUSY WAITS?');
      end loop;
      commit;
     end;
     /  


SQL> @ash/ashtop event2 "module='manyinserts_hashind'" sysdate-1/24/10 sysdate

    Total                                                                                                      Distinct Distinct
  Seconds     AAS %This   EVENT2                                     FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- --------
      776     2.2   80% | buffer busy waits [data block]             2023-04-10 09:50:56 2023-04-10 09:51:17        766       21
       69      .2    7% | row cache mutex                            2023-04-10 09:51:12 2023-04-10 09:51:15          1        4
       44      .1    5% | ON CPU                                     2023-04-10 09:50:56 2023-04-10 09:51:17         31       19
       34      .1    4% | log file switch (checkpoint incomplete)    2023-04-10 09:51:04 2023-04-10 09:51:16          7        3
       19      .1    2% | log file switch completion                 2023-04-10 09:51:13 2023-04-10 09:51:13          3        1
       13      .0    1% | enq: CR - block range reuse ckpt [mode=6]  2023-04-10 09:51:01 2023-04-10 09:51:16         10        4
        6      .0    1% | library cache: mutex X                     2023-04-10 09:51:07 2023-04-10 09:51:14          6        3
        3      .0    0% | reliable message                           2023-04-10 09:50:57 2023-04-10 09:51:10          3        3
        2      .0    0% | buffer busy waits [segment header]         2023-04-10 09:51:09 2023-04-10 09:51:09          2        1
        1      .0    0% | buffer busy waits [undo header]            2023-04-10 09:50:58 2023-04-10 09:50:58          1        1
        1      .0    0% | latch: cache buffers chains                2023-04-10 09:51:09 2023-04-10 09:51:09          1        1

set lines 200 pages 100
col OBJECT_NAME for a30
SUBOBJECT_NAME for a30
select object_name,subobject_name,valu

select object_name,subobject_name,value 
     from v$segment_statistics where owner='SYS' 
     and statistic_name='buffer busy waits'
     and object_name = 'I';

exec dbms_stats.gather_table_stats(user,'T', method_opt=>'for columns ID size AUTO');

select  /*+ GATHER_PLAN_STATISTICS */ * from T where ID=100; 
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST  +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |       |     2 (100)|       |       |      0 |00:00:00.01 |       2 |
|   1 |  PARTITION HASH SINGLE               |      |      1 |      1 |    33 |     2   (0)|    31 |    31 |      0 |00:00:00.01 |       2 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |      1 |    33 |     2   (0)|       |       |      0 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN                  | I    |      1 |      1 |       |     1   (0)|    31 |    31 |      0 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------------------------------------------


-- cleanup
----------
drop table t purge;
drop sequence id_seq;
drop procedure manyinserts;
drop procedure manysessions;


