136 lines
6.3 KiB
Plaintext
136 lines
6.3 KiB
Plaintext
-- https://easyteam.fr/limpact-du-facteur-dordonnancement-sur-les-performances-clustering-factor/
|
|
|
|
create pluggable database NIHILUS admin user NIHILUS$OWNER identified by secret;
|
|
alter pluggable database NIHILUS open;
|
|
alter pluggable database NIHILUS save state;
|
|
|
|
|
|
alter session set container=NIHILUS;
|
|
|
|
create tablespace USERS datafile size 32M autoextend ON next 32M;
|
|
alter database default tablespace USERS;
|
|
|
|
create user adm identified by "secret";
|
|
grant sysdba to adm;
|
|
|
|
create user usr identified by "secret";
|
|
grant CONNECT,RESOURCE to usr;
|
|
grant alter session to usr;
|
|
alter user usr quota unlimited on USERS;
|
|
|
|
alias adm_NIHILUS='rlwrap sqlplus adm/"secret"@bakura:1521/NIHILUS as sysdba'
|
|
alias usr_NIHILUS='rlwrap sqlplus usr/"secret"@ba
|
|
|
|
|
|
|
|
create table USR.TABLE_LIST_DISPLAY_PATTERNS (
|
|
LIST_ID number not null,
|
|
DISPLAY_PATTERN_ID varchar(1000) not null
|
|
);
|
|
|
|
|
|
begin
|
|
for i in 1..100 loop
|
|
insert into USR.TABLE_LIST_DISPLAY_PATTERNS select i, lpad('x',1000,'x') from dba_objects where rownum < 35 order by 1;
|
|
end loop;
|
|
end;
|
|
/
|
|
commit;
|
|
|
|
|
|
create index USR.LIST_DISPLAY_PATTERNS_IDX on USR.TABLE_LIST_DISPLAY_PATTERNS(LIST_ID);
|
|
|
|
|
|
create table USR.TABLE_LIST_DISPLAY_RAND as
|
|
select * from USR.TABLE_LIST_DISPLAY_PATTERNS order by DBMS_RANDOM.RANDOM;
|
|
|
|
create index USR.LIST_DISPLAY_RAND_IDX on USR.TABLE_LIST_DISPLAY_RAND(LIST_ID);
|
|
|
|
exec dbms_stats.gather_table_stats('USR','TABLE_LIST_DISPLAY_PATTERNS', method_opt=>'for all columns size AUTO');
|
|
exec dbms_stats.gather_table_stats('USR','TABLE_LIST_DISPLAY_RAND', method_opt=>'for all columns size AUTO');
|
|
|
|
|
|
|
|
|
|
SQL> @tab USR.TABLE_LIST_DISPLAY_PATTERNS
|
|
Show tables matching condition "%USR.TABLE_LIST_DISPLAY_PATTERNS%" (if schema is not specified then current user's tables only are shown)...
|
|
|
|
OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESS
|
|
-------------------- ------------------------------ ---- ------------ ------------- --------- ------ ------ ------------------- ---------------------------------------- --------
|
|
USR TABLE_LIST_DISPLAY_PATTERNS TAB 3400 496 0 0 1004 2023-06-25 15:41:27 1 DISABLED
|
|
|
|
1 row selected.
|
|
|
|
SQL> @ind USR.LIST_DISPLAY_PATTERNS_IDX
|
|
Display indexes where table or index name matches %USR.LIST_DISPLAY_PATTERNS_IDX%...
|
|
|
|
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
|
|
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
|
|
USR TABLE_LIST_DISPLAY_PATTERNS LIST_DISPLAY_PATTERNS_IDX 1 LIST_ID
|
|
|
|
|
|
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
|
|
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
|
|
USR TABLE_LIST_DISPLAY_PATTERNS LIST_DISPLAY_PATTERNS_IDX NORMAL NO VALID NO N 2 7 100 3400 551 2023-06-25 15:41:27 1 VISIBLE
|
|
|
|
|
|
-- each LIST_ID is stored in how many distinct blocks?
|
|
|
|
alter session set current_schema=USR;
|
|
|
|
select
|
|
norm.list_id, norm.cnt normanized_blocks , random.cnt randomanized_blocks
|
|
from
|
|
(select list_id, count(distinct(dbms_rowid.ROWID_BLOCK_NUMBER(rowid))) cnt
|
|
from TABLE_LIST_DISPLAY_PATTERNS
|
|
group by list_id)norm
|
|
,
|
|
( select list_id, count(distinct(dbms_rowid.ROWID_BLOCK_NUMBER(rowid))) cnt
|
|
from TABLE_LIST_DISPLAY_RAND
|
|
group by list_id) random
|
|
where norm.list_id = random.list_id
|
|
order by list_id;
|
|
|
|
|
|
|
|
|
|
set lines 256 pages 999
|
|
|
|
var LID NUMBER;
|
|
execute :LID:=20;
|
|
|
|
select /*+ GATHER_PLAN_STATISTICS */
|
|
* from USR.TABLE_LIST_DISPLAY_PATTERNS where LIST_ID=:LID;
|
|
|
|
|
|
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 | | | 7 (100)| 34 |00:00:00.01 | 14 |
|
|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_LIST_DISPLAY_PATTERNS | 1 | 34 | 34136 | 7 (0)| 34 |00:00:00.01 | 14 |
|
|
|* 2 | INDEX RANGE SCAN | LIST_DISPLAY_PATTERNS_IDX | 1 | 34 | | 1 (0)| 34 |00:00:00.01 | 5 |
|
|
--------------------------------------------------------------------------------------------------------------------------------------------------
|
|
|
|
|
|
select /*+ GATHER_PLAN_STATISTICS */
|
|
* from USR.TABLE_LIST_DISPLAY_RAND where LIST_ID=:LID;
|
|
|
|
|
|
|
|
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 | | | 35 (100)| 34 |00:00:00.01 | 39 |
|
|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_LIST_DISPLAY_RAND | 1 | 34 | 34136 | 35 (0)| 34 |00:00:00.01 | 39 |
|
|
|* 2 | INDEX RANGE SCAN | LIST_DISPLAY_RAND_IDX | 1 | 34 | | 1 (0)| 34 |00:00:00.01 | 5 |
|
|
----------------------------------------------------------------------------------------------------------------------------------------------
|
|
|
|
|
|
|
|
|
|
|