Oracle 19c - Automatic Indexing Feature - Explored with an Demo!
Oracle 19c - Automatic Indexing - New Feature - Explored
Hi DBA Connections,
In this video, I briefly discussed all about Oracle Indexes and type of indexes in Oracle Database and also I just demonstrated the Oracle 19c Automatic Indexing feature with an demo.
High Level Steps:
Step 1:
==== Bounce the database with Exadata Feature on ====
sqlplus / as sysdba
show parameter _exadata_feature_on
alter system set "_exadata_feature_on"=TRUE scope=spfile;
shutdown immediate;
startup;
Step 2:
==== Create DEMO Schema and Load Random Data =====
set lines 300 pages 2000
col pdb_name form a40;
col owner form a10;
col table_name for a30;
set echo on;
select pdb_id,pdb_name from cdb_pdbs;
alter session set container=SRDBPLUG;
— CREATE USER DEMO
create user C##DEMO identified by demo12345 default tablespace users temporary tablespace temp quota unlimited on users;
grant create session, create view, alter session, create sequence to C##TEST;
grant create synonym, create database link, resource, unlimited tablespace to C##TEST;
connect C##DEMO/demo12345@SRDBPLUG;
— CREATE TABLE DEMO_DATA
create table demo_data (section number not null,id1 number not null,id2 number not null);
— INSERT RANDOM DATA TO DEMO_DATA TABLE
insert into demo_data select sections.n, gen.x, ceil(dbms_random.value(0, 100))
from (select level - 1 n from dual
connect by level < 300) sections , ( select level x from dual connect by level < 900000) gen where gen.x <= sections.n * 3000; -- -- GATHER STATS FOR DEMO_DATA TABLE -- begin dbms_stats.gather_table_stats(null, 'demo_data' , cascade => true);
end;
/
select table_name,num_rows, last_analyzed,chain_cnt,monitoring from user_tables where table_name='DEMO_DATA';
Step 3:
-------
==== Auto Index Configuration ===
— GET AUTO INDEX CONFIGURATION
set lines 300 pages 2000
column parameter_name for a40
column parameter_value for a15
select con_id, parameter_name, parameter_value from cdb_auto_index_config order by 1, 2;
— GET AUTO INDEX CONFIGURATION AT PDB
alter session set container=SRDBPLUG;
select con_id, parameter_name, parameter_value from cdb_auto_index_config order by 1, 2;
— CONFIGURE AUTO_INDEX_MODE TO IMPLEMENT TO START AUTO INDEXING
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
— CREATE NEW TABLESPACE FOR AUTOMATIC INDEXING AND USE IT FOR AUTOMATIC INDEXING
CREATE TABLESPACE AUTO_INDEX_TS DATAFILE '/u02/oradata/SRCDB2/SRDBPLUG/auto_index_ts.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M maxsize 30G;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEX_TS');
select con_id, parameter_name, parameter_value from cdb_auto_index_config order by 1, 2;
— CONFIGURE AUTOMATIC INDEXES AT SCHEMA LEVEL FOR C##TEST SCHEMA
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'C##TEST', allow => TRUE);
select con_id, parameter_name, parameter_value from cdb_auto_index_config order by 1, 2;
Step 4:
------
===== Simulate the Workload =====
set lines 300 pages 200
SET LONG 1000000 PAGESIZE 0
set echo on;
connect C##TEST/test12345;
CREATE OR REPLACE PACKAGE test_scalability IS
TYPE piped_output IS RECORD ( section NUMBER, seconds NUMBER, cnt_rows NUMBER);
TYPE piped_output_table IS TABLE OF piped_output;
FUNCTION run(sql_txt IN varchar2, n IN number)
RETURN test_scalability.piped_output_table PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY test_scalability
IS
TYPE tmp IS TABLE OF piped_output INDEX BY PLS_INTEGER;
FUNCTION run(sql_txt IN VARCHAR2, n IN NUMBER)
RETURN test_scalability.piped_output_table PIPELINED
IS
rec test_scalability.tmp;
r test_scalability.piped_output;
iter NUMBER;
sec NUMBER;
strt NUMBER;
exec_txt VARCHAR2(4000);
cnt NUMBER;
BEGIN
exec_txt := 'select count(*) from (' || sql_txt || ')';
iter := 0;
WHILE iter <= n LOOP
sec := 0;
WHILE sec < 300 LOOP
IF iter = 0 THEN
rec(sec).seconds := 0;
rec(sec).section := sec;
rec(sec).cnt_rows := 0;
END IF;
strt := DBMS_UTILITY.GET_TIME;
EXECUTE IMMEDIATE exec_txt INTO cnt USING sec;
rec(sec).seconds := rec(sec).seconds
+ (DBMS_UTILITY.GET_TIME - strt)/100;
rec(sec).cnt_rows:= rec(sec).cnt_rows + cnt;
IF iter = n THEN
PIPE ROW(rec(sec));
END IF;
sec := sec +1;
END LOOP;
iter := iter +1;
END LOOP;
RETURN;
END;
END test_scalability;
/
— SIMULATE SELECT QUERY ON DEMO_DATA WITH WHERE CONDITION ON SECTION AND ID2 COLUMNS
SELECT * FROM TABLE(test_scalability.run('SELECT * '
|| 'FROM demo_data '
||'WHERE section=:1 '
|| 'AND id2=CEIL(DBMS_RANDOM.value(1,100))', 10));
select avg(id1) from demo_data where section between 1 and 100;
select avg(id1) from demo_data where section between 1 and 200;
select avg(id1) from demo_data where section between 1 and 300;
select sum(id1) from demo_data where section between 1 and 100;
select sum(id1) from demo_data where section between 100 and 200;
select sum(id1) from demo_data where section between 200 and 300;
select sum(id1) from demo_data where section=1;
select sum(id1) from demo_data where section=2;
select sum(id1) from demo_data where section=3;
select count(id1) from demo_data where section between 1 and 100;
select distinct(id1) from demo_data where section between 1 and 100;
select distinct section from demo_data where id1 is not null;
Step 5:
-------
=== Generate Report =====
SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;
SELECT DBMS_AUTO_INDEX.report_last_activity() FROM dual;
SELECT DBMS_AUTO_INDEX.report_activity(
activity_start => SYSTIMESTAMP-2,
activity_end => SYSTIMESTAMP,
type => 'TEXT',
section => 'ALL',
"LEVEL" => 'ALL')
FROM dual;
SELECT DBMS_AUTO_INDEX.report_activity(
activity_start => SYSTIMESTAMP-2,
activity_end => SYSTIMESTAMP,
type => 'XML',
section => 'ALL',
"LEVEL" => 'ALL')
FROM dual;
SELECT DBMS_AUTO_INDEX.report_activity(
activity_start => SYSTIMESTAMP-2,
activity_end => SYSTIMESTAMP,
type => 'HTML')
FROM dual;
Regards,
Ramesh
Please follow me on:
DBA BLOG:https://www.sachinrameshoracledbablog.com
YOUTUBE: https://www.youtube.com/oraclef1
TWITTER: https://twitter.com/sachinrameshdba
FACEBOOK: https://www.facebook.com/rameshkumar.krishnamoorthy.9
LINKEDIN:https://www.linkedin.com/in/ramesh-kumar-krishnamoorthy-3a67ba69
INSTAGRAM: https://www.instagram.com/oraclef1
Email: oraclehelplines@gmail.com
Post a Comment: