153 lines
3.7 KiB
Markdown
153 lines
3.7 KiB
Markdown
set lines 256
|
|
|
|
column client_name format a35
|
|
column task_name format a30
|
|
|
|
column last_try_date format a20
|
|
column last_good_date format a20
|
|
column next_try_date format a20
|
|
|
|
alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss';
|
|
|
|
select
|
|
client_name, task_name, status,
|
|
to_char(last_try_date,'yyyy-mm-dd hh24:mi:ss') as last_try_date,
|
|
to_char(last_good_date,'yyyy-mm-dd hh24:mi:ss') as last_good_date,
|
|
to_char(next_try_date,'yyyy-mm-dd hh24:mi:ss') as next_try_date
|
|
from dba_autotask_task;
|
|
|
|
|
|
|
|
SQL> show parameter optimizer%baselines
|
|
|
|
NAME TYPE VALUE
|
|
------------------------------------ ----------- ------------------------------
|
|
optimizer_capture_sql_plan_baselines boolean FALSE
|
|
optimizer_use_sql_plan_baselines boolean TRUE
|
|
|
|
|
|
|
|
|
|
set lines 200
|
|
set pages 1000
|
|
col parameter_name for a35
|
|
col parameter_value for a30
|
|
col last_modified for a30
|
|
col modified_by for a30
|
|
|
|
select * from dba_sql_management_config where parameter_name like 'AUTO_SPM_EVOLVE_TASK%';
|
|
|
|
|
|
|
|
exec dbms_spm.configure('AUTO_SPM_EVOLVE_TASK','ON');
|
|
exec dbms_spm.configure('AUTO_SPM_EVOLVE_TASK','OFF');
|
|
|
|
The list of tunable parameters with DBMS_SPM.CONFIGURE:
|
|
|
|
col description FOR a40 word_wrapped
|
|
SET pages 1000
|
|
|
|
select parameter_name, parameter_value, description
|
|
from dba_advisor_parameters
|
|
where task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
|
|
and parameter_value != 'UNUSED';
|
|
|
|
|
|
|
|
set lines 256
|
|
|
|
col DBID noprint
|
|
col TASK_ID noprint
|
|
col TASK_NAME noprint
|
|
|
|
select *
|
|
from dba_autotask_schedule_control
|
|
where dbid = sys_context('userenv','con_dbid')
|
|
and task_name = 'Auto SPM Task';
|
|
|
|
-- last task details
|
|
SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 256 LINESIZE 256
|
|
|
|
SELECT DBMS_SPM.report_auto_evolve_task
|
|
FROM dual;
|
|
|
|
|
|
CREATE TABLE test1(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 test1 VALUES(1,RPAD('A',49,'A'));
|
|
ELSE
|
|
INSERT INTO test1 VALUES(nbrows,RPAD('A',49,'A'));
|
|
END IF;
|
|
i:=i+1;
|
|
END LOOP;
|
|
COMMIT;
|
|
END;
|
|
/
|
|
|
|
CREATE INDEX test1_idx_id ON test1(id) TABLESPACE users;
|
|
|
|
|
|
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 2');
|
|
|
|
|
|
ALTER SYSTEM flush shared_pool;
|
|
|
|
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM test1 WHERE id=1;
|
|
|
|
SELECT sql_id,child_number,plan_hash_value,is_bind_sensitive,is_bind_aware,is_shareable,is_obsolete,sql_plan_baseline
|
|
FROM v$sql
|
|
WHERE sql_id='4q7zcj8kp9q2r';
|
|
|
|
|
|
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>user, tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1');
|
|
|
|
|
|
SELECT
|
|
plan_hash_value,
|
|
cpu_time,
|
|
buffer_gets,
|
|
disk_reads,
|
|
direct_writes,
|
|
rows_processed,
|
|
fetches,
|
|
executions,
|
|
optimizer_cost,
|
|
TO_CHAR(plan_timestamp,'dd-mon-yyyy hh24:mi:ss') AS plan_timestamp
|
|
FROM dba_sqlset_statements
|
|
WHERE sqlset_name='SYS_AUTO_STS'
|
|
AND sql_id='4q7zcj8kp9q2r'
|
|
ORDER BY plan_timestamp DESC;
|
|
|
|
|
|
select * from SYS.WRI$_ADV_EXECUTIONS where exec_type='SPM EVOLVE' order by exec_start desc;
|
|
|
|
select * from SYS.WRI$_ADV_EXECUTIONS where exec_type='SPM EVOLVE'
|
|
where exec_start between date'2025-05-25 09:00:00' and date'2025-05-25 19:00:00'
|
|
order by exec_start desc;
|
|
|
|
|
|
|
|
select
|
|
sql_id
|
|
,plan_hash_value
|
|
,LAST_MODIFIED
|
|
from(
|
|
select
|
|
dbms_sql_translator.sql_id(sql_text) sql_id,
|
|
(select to_number(regexp_replace(plan_table_output,'^[^0-9]*'))
|
|
from table(dbms_xplan.display_sql_plan_baseline(sql_handle,plan_name))
|
|
where plan_table_output like 'Plan hash value: %') plan_hash_value,
|
|
bl.*
|
|
from dba_sql_plan_baselines bl
|
|
)
|
|
;
|