188 lines
11 KiB
Plaintext
188 lines
11 KiB
Plaintext
-- 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;
|
|
|
|
|