7.6 KiB
7.6 KiB
Setup
Check if Automatic SQL Tuning Sets (ASTS) is activated (enabled) and get the last execution time of the automatic schedule:
set lines 200
col task_name for a22
select * from dba_autotask_schedule_control where task_name = 'Auto STS Capture Task';
To enable:
exec dbms_auto_task_admin.enable(client_name => 'Auto STS Capture Task', operation => NULL, window_name => NULL);
No way to change the interval and maximum run time
To disable:
exec dbms_auto_task_admin.disable(client_name => 'Auto STS Capture Task', operation => NULL, window_name => NULL);
To manually run the job:
exec dbms_scheduler.run_job('ORA$_ATSK_AUTOSTS');
List last job executions:
col ACTUAL_START_DATE for a45
select ACTUAL_START_DATE,STATUS from dba_scheduler_job_run_details where JOB_NAME='ORA$_ATSK_AUTOSTS'
order by ACTUAL_START_DATE desc fetch first 10 rows only;
More statistics on the task job:
WITH dsjrd AS
(
SELECT (TO_DATE('1','j')+run_duration-TO_DATE('1','j'))* 86400 duration_sec,
(TO_DATE('1','j')+cpu_used-TO_DATE('1','j'))* 86400 cpu_used_sec
FROM dba_scheduler_job_run_details
WHERE job_name = 'ORA$_ATSK_AUTOSTS'
)
SELECT MIN(duration_sec) ASTS_Min_Time_Sec,
MAX(duration_sec) ASTS_Max_Time_Sec,
AVG(duration_sec) ASTS_Average_Time_Sec,
AVG(cpu_used_sec) ASTS_Average_CPU_Sec
FROM dsjrd;
How many SQL statements we have actually in the SYS_AUTO_STS SQL Tuning Set (STS):
set lines 200
col name for a15
col description for a30
col owner for a10
select name, owner, description, created, last_modified, statement_count from dba_sqlset where name='SYS_AUTO_STS';
To purge all statements:
exec dbms_sqlset.drop_sqlset(sqlset_name => 'SYS_AUTO_STS', sqlset_owner => 'SYS');
How much space it takes in your SYSAUX tablesapce:
col table_name for a30
col table_size_mb for 999999.99
col total_size_mb for 999999.99
select
table_name,
round(sum(size_b) / 1024 / 1024, 3) as table_size_mb,
round(max(total_size_b) / 1024 / 1024, 3) as total_size_mb
from
(
select
table_name,
size_b,
sum(size_b) over() as total_size_b
from
(
select
segment_name as table_name,
bytes as size_b
from dba_segments
where
segment_name not like '%WORKSPA%'
and owner = 'SYS'
and (segment_name like 'WRI%SQLSET%' or segment_name like 'WRH$_SQLTEXT')
union all
select
t.table_name,
bytes as size_b
from dba_segments s,
(select
table_name,
segment_name
from dba_lobs
where table_name in ('WRI$_SQLSET_PLAN_LINES', 'WRH$_SQLTEXT')
and owner = 'SYS'
) t
where s.segment_name = t.segment_name
)
)
group by table_name
order by table_size_mb desc;
Test case
DROP TABLE test01 purge;
CREATE TABLE test01(id NUMBER, descr VARCHAR(50)) TABLESPACE users;
DECLARE
i NUMBER;
nbrows NUMBER;
BEGIN
i:=1;
nbrows:=50000;
LOOP
EXIT WHEN i>nbrows;
IF (i=1) THEN
INSERT INTO test01 VALUES(1,RPAD('A',49,'A'));
ELSE
INSERT INTO test01 VALUES(nbrows,RPAD('A',49,'A'));
END IF;
i:=i+1;
END LOOP;
COMMIT;
END;
/
CREATE INDEX test01_idx_id ON test01(id);
exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'test01', method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');
No histogram will be calculated:
col column_name for a20
select column_name,num_distinct,density,num_nulls,num_buckets,sample_size,histogram
from user_tab_col_statistics
where table_name='TEST01';
select /*+ GATHER_PLAN_STATISTICS */ * FROM test01 WHERE id=1;
ID DESCR
---------- --------------------------------------------------
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
The optimize we will choose a full scan:
SQL_ID 28stunrv2985c, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * FROM test01 WHERE id=1
Plan hash value: 262542483
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 136 (100)| 1 |00:00:00.01 | 443 |
|* 1 | TABLE ACCESS FULL| TEST01 | 1 | 25000 | 732K| 136 (0)| 1 |00:00:00.01 | 443 |
-----------------------------------------------------------------------------------------------------------
Wait for next Auto STS Capture Task schedule or run the job manually. The SQL_ID will be captured bu ASTS:
col SQLSET_NAME for a30
col PARSING_SCHEMA_NAME for a30
select SQLSET_NAME,PLAN_HASH_VALUE,PARSING_SCHEMA_NAME,BUFFER_GETS from DBA_SQLSET_STATEMENTS where SQL_ID='28stunrv2985c';
SQLSET_NAME PLAN_HASH_VALUE PARSING_SCHEMA_NAME BUFFER_GETS
------------------------------ --------------- ------------------------------ -----------
SYS_AUTO_STS 262542483 RED 453
Gather the stats again:
exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'test01', method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');
Oracle learned from its mistake and will calulate histograms:
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
-------------------- ------------ ---------- ---------- ----------- ----------- ---------------
ID 2 .00001 0 2 50000 FREQUENCY
Flush the shared pool and re-execute the query:
alter system flush shared_pool;
select /*+ GATHER_PLAN_STATISTICS */ * FROM test01 WHERE id=1;
As expected, the index has been used:
SQL_ID 28stunrv2985c, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * FROM test01 WHERE id=1
Plan hash value: 4138272685
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| 1 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST01 | 1 | 1 | 30 | 2 (0)| 1 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | TEST01_IDX_ID | 1 | 1 | | 1 (0)| 1 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------------------------------------------
Wait for next Auto STS Capture Task schedule and check if the SQL_ID is in with both executions.
For me the manual execution does not add the 2-end plan to ASTS.
select SQLSET_NAME,PLAN_HASH_VALUE,PARSING_SCHEMA_NAME,BUFFER_GETS from DBA_SQLSET_STATEMENTS where SQL_ID='28stunrv2985c';
SQLSET_NAME PLAN_HASH_VALUE PARSING_SCHEMA_NAME BUFFER_GETS
------------------------------ --------------- ------------------------------ -----------
SYS_AUTO_STS 262542483 RED 453
SYS_AUTO_STS 4138272685 RED 203