--
-- currentry running procedure (entry level), from 10g
--
set lines 150
column name format a51
set pagesize 3000
select p.owner||'.'||p.object_name||'.'||p.procedure_name name, '('||v.sid||', '||v.serial#||')', v.machine||':'||v.program||':'||v.event||':'||v.state||':'||v.osuser
from dba_procedures p
, (select plsql_entry_object_id, plsql_entry_subprogram_id, sid, serial#, machine, program, event, state, osuser from v$session where status = 'ACTIVE') v
where p.object_id = v.plsql_entry_object_id
and p.subprogram_id = v.plsql_entry_subprogram_id
;
--
-- set new plan for one sql (11g), will force full scan instead of index :)
--
-- 1. load old plan
23:02:08 4294967295 SYS@db11g> undef sql_id_orig
23:02:17 4294967295 SYS@db11g> declare
23:02:17 2 my_plans pls_integer;
23:02:17 3 BEGIN
23:02:17 4 my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
23:02:17 5 sql_id => '&&sql_id_orig');
23:02:17 6 END;
23:02:17 7 /
Enter value for sql_id_orig: fbk5w851v47p9
PL/SQL procedure successfully completed.
--2. check if it is load
23:02:45 4294967295 SYS@db11g> SELECT sql_handle, sql_text, plan_name, enabled, accepted FROM
23:03:04 2 dba_sql_plan_baselines
23:03:04 3 WHERE sql_text LIKE '%&&sql_text%'
23:03:04 4 /
SQL_HANDLE | SQL_TEXT | PLAN_NAME | ENA | ACC
------------------------------ | -------------------------------------------------------------------------------- | ------------------------------ | --- | ---
SYS_SQL_bb70d509955b3714 | select count(*) from tsql.tb t where object_name = 'T' | SQL_PLAN_bqw6p16apqdsnb325979c | YES | YES
1 row selected.
Elapsed: 00:00:00.07
--3. disable old plan
23:03:04 4294967295 SYS@db11g> variable cnt number;
23:03:11 4294967295 SYS@db11g> exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE( -
23:03:11 > SQL_HANDLE => '&SQL_HANDLE',-
23:03:11 > PLAN_NAME -
23:03:11 > => '&PLAN_NAME', -
23:03:11 > ATTRIBUTE_NAME => 'enabled', -
23:03:11 > ATTRIBUTE_VALUE => 'NO');
Enter value for sql_handle: SYS_SQL_bb70d509955b3714
Enter value for plan_name: SQL_PLAN_bqw6p16apqdsnb325979c
PL/SQL procedure successfully completed.
CNT
----------
1
Elapsed: 00:00:00.01
--4. find new plan with hints
23:04:22 4294967295 SYS@db11g> select /*+ full(t) */count(*) from tsql.tb t where object_name = 'T';
COUNT(*)
----------
1
1 row selected.
Elapsed: 00:00:00.82
23:04:36 4294967295 SYS@db11g> explain plan for select /*+ full(t) */count(*) from tsql.tb t where object_name = 'T';
Explained.
Elapsed: 00:00:00.00
23:04:43 4294967295 SYS@db11g> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
Plan hash value: 2989731593
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 18127 (1)| 00:03:38 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | TABLE ACCESS FULL| TB | 106 | 2650 | 18127 (1)| 00:03:38 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME"='T')
14 rows selected.
Elapsed: 00:00:00.02
23:05:01 4294967295 SYS@db11g> SELECT sql_id, plan_hash_value, sql_fulltext
23:05:12 2 FROM V$SQL
23:05:12 3 WHERE sql_text LIKE '%&&sql_text%'
23:05:12 4 /
SQL_ID | PLAN_HASH_VALUE | SQL_FULLTEXT
------------- | --------------- | --------------------------------------------------------------------------------
fbk5w851v47p9 | 2694567437 | select count(*) from tsql.tb t where object_name = 'T'
38spus6gwsg16 | 903671040 | SELECT sql_id, plan_hash_value, sql_fulltext
| | FROM V$SQL
| | WHERE sql_text LIKE '%tsql.tb%'
b69zk715md4b9 | 2694567437 | select /*+ full */count(*) from tsql.tb t where object_name = 'T'
fjf5sskpm59f2 | 3334935598 | SELECT sql_handle, sql_text, plan_name, enabled, accepted FROM
| | dba_sql_plan_baselines
| | WHERE sql_text LIKE '%tsql.tb%'
4suh1m5usuz7v | 2989731593 | explain plan for select /*+ full(t) */count(*) from tsql.tb t where object_name
| | = 'T'
2bqc3jhn1qzhf | 3855356117 | SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled', '
| | false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(S
| | UM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("ST") FULL("ST") NO_PARAL
| | LEL_INDEX("ST") */ 1 AS C1, 1 AS C2 FROM SYS."SQL$TEXT" "ST" WHERE "ST"."SQL_TE
| | XT" LIKE '%tsql.tb%') SAMPLESUB
0r0xdj4rum1qs | 2694567437 | explain plan for select /*+ full */count(*) from tsql.tb t where object_name = '
| | T'
57r0vrsrwmptr | 2989731593 | select /*+ full(t) */count(*) from tsql.tb t where object_name = 'T'
8 rows selected.
Elapsed: 00:00:00.04
--5. associate new sql plan with original sql handle
23:05:12 4294967295 SYS@db11g> exec :cnt:=dbms_spm.load_plans_from_cursor_cache( -
23:05:29 > sql_id => '&sql_id_new', -
23:05:29 > plan_hash_value => &plan_hash_value_new, -
23:05:29 > sql_handle => '&sql_handle_orig');
Enter value for sql_id_new: 57r0vrsrwmptr
Enter value for plan_hash_value_new: 2989731593
Enter value for sql_handle_orig: SYS_SQL_bb70d509955b3714
PL/SQL procedure successfully completed.
CNT
----------
1
Elapsed: 00:00:00.05
23:05:55 4294967295 SYS@db11g> SELECT sql_handle, sql_text, plan_name, enabled, accepted FROM
23:06:03 2 dba_sql_plan_baselines
23:06:03 3 WHERE sql_text LIKE '%&&sql_text%'
23:06:03 4 /
SQL_HANDLE | SQL_TEXT | PLAN_NAME | ENA | ACC
------------------------------ | -------------------------------------------------------------------------------- | ------------------------------ | --- | ---
SYS_SQL_bb70d509955b3714 | select count(*) from tsql.tb t where object_name = 'T' | SQL_PLAN_bqw6p16apqdsnb325979c | NO | YES
SYS_SQL_bb70d509955b3714 | select count(*) from tsql.tb t where object_name = 'T' | SQL_PLAN_bqw6p16apqdsnd9fb8633 | YES | YES
2 rows selected.
Elapsed: 00:00:00.00
23:06:03 4294967295 SYS@db11g> select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(SQL_HANDLE => '&SQL_HANDLE', PLAN_NAME => '&PLAN_NAME'));
Enter value for sql_handle: SYS_SQL_bb70d509955b3714
Enter value for plan_name: SQL_PLAN_bqw6p16apqdsnb325979c
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_bb70d509955b3714
SQL text: select count(*) from tsql.tb t where object_name = 'T'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_bqw6p16apqdsnb325979c Plan id: 3005585308
Enabled: NO Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 2694567437
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | INDEX RANGE SCAN| TBI | 106 | 2650 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='T')
25 rows selected.
Elapsed: 00:00:00.08
23:06:20 4294967295 SYS@db11g> select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(SQL_HANDLE => '&SQL_HANDLE', PLAN_NAME => '&PLAN_NAME'));
Enter value for sql_handle: SYS_SQL_bb70d509955b3714
Enter value for plan_name: SQL_PLAN_bqw6p16apqdsnd9fb8633
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_bb70d509955b3714
SQL text: select count(*) from tsql.tb t where object_name = 'T'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_bqw6p16apqdsnd9fb8633 Plan id: 3657139763
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 2989731593
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 18127 (1)| 00:03:38 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | TABLE ACCESS FULL| TB | 106 | 2650 | 18127 (1)| 00:03:38 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME"='T')
25 rows selected.
Elapsed: 00:00:00.05
23:06:34 4294967295 SYS@db11g>
--now new plan set
23:12:42 4294967295 SYS@db11g> select count(*) from tsql.tb t where object_name = 'T'
23:13:06 2 /
COUNT(*)
----------
1
1 row selected.
Elapsed: 00:00:00.32
23:13:08 4294967295 SYS@db11g> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
--------------------------------------------------
Plan hash value: 2989731593
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 18127 (1)| 00:03:38 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | TABLE ACCESS FULL| TB | 106 | 2650 | 18127 (1)| 00:03:38 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME"='T')
14 rows selected.
--
-- Stored outlines (example)
--
23:39:23 71329 T1@db11g> alter session set create_stored_outlines=mycat;
Session altered.
Elapsed: 00:00:00.00
23:39:43 71329 T1@db11g> select count(*) from t where id = 100;
COUNT(*)
------------------------------------
1.0
1 row selected.
Elapsed: 00:00:00.00
23:39:58 71329 T1@db11g> alter session set create_stored_outlines=false;
Session altered.
Elapsed: 00:00:00.00
23:40:07 71329 T1@db11g> set autotrace on
23:40:22 71329 T1@db11g> select count(*) from t where id = 100;
COUNT(*)
------------------------------------
1.0
1 row selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
122 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
23:40:39 71329 T1@db11g> create index ti on t(id);
Index created.
Elapsed: 00:00:00.14
23:40:55 71329 T1@db11g> select count(*) from t where id = 100;
COUNT(*)
------------------------------------
1.0
1 row selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 647456754
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| TI | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
4 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
23:40:58 71329 T1@db11g> alter session set use_stored_outlines=mycat;
Session altered.
Elapsed: 00:00:00.00
23:41:20 71329 T1@db11g> select count(*) from t where id = 100;
COUNT(*)
------------------------------------
1.0
1 row selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=100)
Note
-----
- outline "SYS_OUTLINE_12082923395822524" used for this statement
Statistics
----------------------------------------------------------
34 recursive calls
147 db block gets
144 consistent gets
0 physical reads
616 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
23:41:22 71329 T1@db11g>
--
--os/db cpu history + db stat pct
--
set pagesize 30
col snap_id format 9999999
set lines 150
col os_cpu_pct format 999999.999
col db_stat_pct format 999999.999
col db_cpu_pct format 999999999.999
col db_cpu_min format 99999.999
col available_cpu_min format 99999.999
col stat_name format a15 truncate
break on snap_id on os_cpu_pct on t on available_cpu_min on db_cpu_min on db_cpu_pct
select b.snap_id
, TO_CHAR(dhs.begin_interval_time, 'dd.mm.yyyy hh24:mi:ss') t
, round(((b.t1 / (b.t1 + i.t1 + nvl(io.t1, 0)))) * 100, 3) os_cpu_pct
,dhs.t1 * cpu.cpu_count available_cpu_min
,dcpu.t1/1000000/60 db_cpu_min
,((dcpu.t1/1000000/60)/(dhs.t1 * cpu.cpu_count)) * 100 db_cpu_pct
, stat_name
, round((s.t1/a.t1) * 100, 3) db_stat_pct
from
(select snap_id, value - lag(value) over(order by snap_id) t1 from dba_hist_osstat where stat_name = 'BUSY_TIME') b
,(select snap_id, value - lag(value) over(order by snap_id) t1 from dba_hist_osstat where stat_name = 'IDLE_TIME') i
,(select snap_id, value - lag(value) over(order by snap_id) t1 from dba_hist_osstat where stat_name = 'IOWAIT_TIME') io
,(select snap_id, value cpu_count from dba_hist_osstat where stat_name = 'NUM_CPUS') cpu
,(select snap_id, value - lag(value) over(order by snap_id) t1 from DBA_HIST_SYS_TIME_MODEL where stat_name = 'DB time') a
,(select snap_id, value - lag(value) over(order by snap_id) t1 from DBA_HIST_SYS_TIME_MODEL where stat_name = 'DB CPU') dcpu
,(select snap_id, stat_name, value - lag(value) over(partition by stat_name order by snap_id) t1 from DBA_HIST_SYS_TIME_MODEL where stat_name <> 'DB time' and stat_name not like 'background%') s
,
(
select
dhs.snap_id
,((EXTRACT(DAY FROM (dhs.end_interval_time-dhs.begin_interval_time) )*86400+
EXTRACT(HOUR FROM (dhs.end_interval_time-dhs.begin_interval_time) )*3600+
EXTRACT(MINUTE FROM (dhs.end_interval_time-dhs.begin_interval_time) )*60+
EXTRACT(SECOND FROM (dhs.end_interval_time-dhs.begin_interval_time) ))/60) t1
, dhs.begin_interval_time
from dba_hist_snapshot dhs
) dhs
where i.snap_id = b.snap_id and s.snap_id = i.snap_id and a.snap_id = i.snap_id and io.snap_id (+)= i.snap_id
and round((s.t1/a.t1) * 100, 3) > 10
and dhs.snap_id = b.snap_id
and cpu.snap_id = b.snap_id
and dcpu.snap_id = b.snap_id
order by b.snap_id, db_stat_pct desc;
--
-- No FK Index (from Tom Kyte), a bit modified
--
column columns format a20 word_wrapped
column table_name format a30 word_wrapped
select a.table_owner, decode( b.table_name, NULL, '****', 'ok' ) Status,
a.table_name, a.columns, b.columns
from
( select a.owner table_owner, substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
from dba_cons_columns a, dba_constraints b
where a.owner not in ('SYS', 'SYSMAN', 'ORDDATA', 'MDSYS', 'OLAPSYS', 'LBACSYS', 'EXFSYS', 'SYSTEM', 'DBSNMP')
and a.owner = b.owner
and a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.owner, substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select table_owner, substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
from dba_ind_columns
group by table_owner, substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_owner = b.table_owner (+)
and a.table_name = b.table_name (+)
and b.table_name is null
and b.columns (+) like a.columns || '%'
/
--changes in exec time between snaphosts
undef snap_id1
undef snap_id2
col s1 format 999
col s2 format 999
col s format 999
col ROWS_DELTA_S1 format 999999999
col ROWS_DELTA_S2 format 999999999
col EXE_DELTA_S1 format 999999
col EXE_DELTA_S2 format 999999
col PARSING_SCHEMA_NAME format a7
select *
from
(
select TO_CHAR(s.BEGIN_INTERVAL_TIME, 'dd.mm.yyyy hh24:mi:ss') begin_time, h.sql_id, h.PARSING_SCHEMA_NAME
, lag(trunc(h.ELAPSED_TIME_DELTA/DECODE(h.executions_delta, 0, 1, h.executions_delta)/1000000)) over(partition by h.sql_id order by h.snap_id) s1,
trunc(h.ELAPSED_TIME_DELTA/DECODE(h.executions_delta, 0, 1, h.executions_delta)/1000000) s2,
trunc(h.ELAPSED_TIME_DELTA/DECODE(h.executions_delta, 0, 1, h.executions_delta)/1000000) -
lag(trunc(h.ELAPSED_TIME_DELTA/DECODE(h.executions_delta, 0, 1, h.executions_delta)/1000000)) over(partition by h.sql_id order by h.snap_id) s
, lag(h.ROWS_PROCESSED_DELTA) over (partition by h.sql_id order by h.snap_id) ROWS_DELTA_s1
, h.ROWS_PROCESSED_DELTA ROWS_DELTA_s2
, lag(h.executions_delta) over (partition by h.sql_id order by h.snap_id) EXE_DELTA_S1
, h.executions_delta EXE_DELTA_S2
, decode(h.executions_delta, h.END_OF_FETCH_COUNT_DELTA, NULL, 'Continue running?') txt
from DBA_HIST_SQLSTAT h, DBA_HIST_SNAPSHOT s
where s.snap_id in (&&snap_id1, &&snap_id2)
and s.snap_id = h.snap_id
order by h.snap_id
)
where s <> 0
;
-- currentry running procedure (entry level), from 10g
--
set lines 150
column name format a51
set pagesize 3000
select p.owner||'.'||p.object_name||'.'||p.procedure_name name, '('||v.sid||', '||v.serial#||')', v.machine||':'||v.program||':'||v.event||':'||v.state||':'||v.osuser
from dba_procedures p
, (select plsql_entry_object_id, plsql_entry_subprogram_id, sid, serial#, machine, program, event, state, osuser from v$session where status = 'ACTIVE') v
where p.object_id = v.plsql_entry_object_id
and p.subprogram_id = v.plsql_entry_subprogram_id
;
--
-- set new plan for one sql (11g), will force full scan instead of index :)
--
-- 1. load old plan
23:02:08 4294967295 SYS@db11g> undef sql_id_orig
23:02:17 4294967295 SYS@db11g> declare
23:02:17 2 my_plans pls_integer;
23:02:17 3 BEGIN
23:02:17 4 my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
23:02:17 5 sql_id => '&&sql_id_orig');
23:02:17 6 END;
23:02:17 7 /
Enter value for sql_id_orig: fbk5w851v47p9
PL/SQL procedure successfully completed.
--2. check if it is load
23:02:45 4294967295 SYS@db11g> SELECT sql_handle, sql_text, plan_name, enabled, accepted FROM
23:03:04 2 dba_sql_plan_baselines
23:03:04 3 WHERE sql_text LIKE '%&&sql_text%'
23:03:04 4 /
SQL_HANDLE | SQL_TEXT | PLAN_NAME | ENA | ACC
------------------------------ | -------------------------------------------------------------------------------- | ------------------------------ | --- | ---
SYS_SQL_bb70d509955b3714 | select count(*) from tsql.tb t where object_name = 'T' | SQL_PLAN_bqw6p16apqdsnb325979c | YES | YES
1 row selected.
Elapsed: 00:00:00.07
--3. disable old plan
23:03:04 4294967295 SYS@db11g> variable cnt number;
23:03:11 4294967295 SYS@db11g> exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE( -
23:03:11 > SQL_HANDLE => '&SQL_HANDLE',-
23:03:11 > PLAN_NAME -
23:03:11 > => '&PLAN_NAME', -
23:03:11 > ATTRIBUTE_NAME => 'enabled', -
23:03:11 > ATTRIBUTE_VALUE => 'NO');
Enter value for sql_handle: SYS_SQL_bb70d509955b3714
Enter value for plan_name: SQL_PLAN_bqw6p16apqdsnb325979c
PL/SQL procedure successfully completed.
CNT
----------
1
Elapsed: 00:00:00.01
--4. find new plan with hints
23:04:22 4294967295 SYS@db11g> select /*+ full(t) */count(*) from tsql.tb t where object_name = 'T';
COUNT(*)
----------
1
1 row selected.
Elapsed: 00:00:00.82
23:04:36 4294967295 SYS@db11g> explain plan for select /*+ full(t) */count(*) from tsql.tb t where object_name = 'T';
Explained.
Elapsed: 00:00:00.00
23:04:43 4294967295 SYS@db11g> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
Plan hash value: 2989731593
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 18127 (1)| 00:03:38 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | TABLE ACCESS FULL| TB | 106 | 2650 | 18127 (1)| 00:03:38 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME"='T')
14 rows selected.
Elapsed: 00:00:00.02
23:05:01 4294967295 SYS@db11g> SELECT sql_id, plan_hash_value, sql_fulltext
23:05:12 2 FROM V$SQL
23:05:12 3 WHERE sql_text LIKE '%&&sql_text%'
23:05:12 4 /
SQL_ID | PLAN_HASH_VALUE | SQL_FULLTEXT
------------- | --------------- | --------------------------------------------------------------------------------
fbk5w851v47p9 | 2694567437 | select count(*) from tsql.tb t where object_name = 'T'
38spus6gwsg16 | 903671040 | SELECT sql_id, plan_hash_value, sql_fulltext
| | FROM V$SQL
| | WHERE sql_text LIKE '%tsql.tb%'
b69zk715md4b9 | 2694567437 | select /*+ full */count(*) from tsql.tb t where object_name = 'T'
fjf5sskpm59f2 | 3334935598 | SELECT sql_handle, sql_text, plan_name, enabled, accepted FROM
| | dba_sql_plan_baselines
| | WHERE sql_text LIKE '%tsql.tb%'
4suh1m5usuz7v | 2989731593 | explain plan for select /*+ full(t) */count(*) from tsql.tb t where object_name
| | = 'T'
2bqc3jhn1qzhf | 3855356117 | SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled', '
| | false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(S
| | UM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("ST") FULL("ST") NO_PARAL
| | LEL_INDEX("ST") */ 1 AS C1, 1 AS C2 FROM SYS."SQL$TEXT" "ST" WHERE "ST"."SQL_TE
| | XT" LIKE '%tsql.tb%') SAMPLESUB
0r0xdj4rum1qs | 2694567437 | explain plan for select /*+ full */count(*) from tsql.tb t where object_name = '
| | T'
57r0vrsrwmptr | 2989731593 | select /*+ full(t) */count(*) from tsql.tb t where object_name = 'T'
8 rows selected.
Elapsed: 00:00:00.04
--5. associate new sql plan with original sql handle
23:05:12 4294967295 SYS@db11g> exec :cnt:=dbms_spm.load_plans_from_cursor_cache( -
23:05:29 > sql_id => '&sql_id_new', -
23:05:29 > plan_hash_value => &plan_hash_value_new, -
23:05:29 > sql_handle => '&sql_handle_orig');
Enter value for sql_id_new: 57r0vrsrwmptr
Enter value for plan_hash_value_new: 2989731593
Enter value for sql_handle_orig: SYS_SQL_bb70d509955b3714
PL/SQL procedure successfully completed.
CNT
----------
1
Elapsed: 00:00:00.05
23:05:55 4294967295 SYS@db11g> SELECT sql_handle, sql_text, plan_name, enabled, accepted FROM
23:06:03 2 dba_sql_plan_baselines
23:06:03 3 WHERE sql_text LIKE '%&&sql_text%'
23:06:03 4 /
SQL_HANDLE | SQL_TEXT | PLAN_NAME | ENA | ACC
------------------------------ | -------------------------------------------------------------------------------- | ------------------------------ | --- | ---
SYS_SQL_bb70d509955b3714 | select count(*) from tsql.tb t where object_name = 'T' | SQL_PLAN_bqw6p16apqdsnb325979c | NO | YES
SYS_SQL_bb70d509955b3714 | select count(*) from tsql.tb t where object_name = 'T' | SQL_PLAN_bqw6p16apqdsnd9fb8633 | YES | YES
2 rows selected.
Elapsed: 00:00:00.00
23:06:03 4294967295 SYS@db11g> select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(SQL_HANDLE => '&SQL_HANDLE', PLAN_NAME => '&PLAN_NAME'));
Enter value for sql_handle: SYS_SQL_bb70d509955b3714
Enter value for plan_name: SQL_PLAN_bqw6p16apqdsnb325979c
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_bb70d509955b3714
SQL text: select count(*) from tsql.tb t where object_name = 'T'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_bqw6p16apqdsnb325979c Plan id: 3005585308
Enabled: NO Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 2694567437
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | INDEX RANGE SCAN| TBI | 106 | 2650 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='T')
25 rows selected.
Elapsed: 00:00:00.08
23:06:20 4294967295 SYS@db11g> select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(SQL_HANDLE => '&SQL_HANDLE', PLAN_NAME => '&PLAN_NAME'));
Enter value for sql_handle: SYS_SQL_bb70d509955b3714
Enter value for plan_name: SQL_PLAN_bqw6p16apqdsnd9fb8633
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_bb70d509955b3714
SQL text: select count(*) from tsql.tb t where object_name = 'T'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_bqw6p16apqdsnd9fb8633 Plan id: 3657139763
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 2989731593
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 18127 (1)| 00:03:38 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | TABLE ACCESS FULL| TB | 106 | 2650 | 18127 (1)| 00:03:38 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME"='T')
25 rows selected.
Elapsed: 00:00:00.05
23:06:34 4294967295 SYS@db11g>
--now new plan set
23:12:42 4294967295 SYS@db11g> select count(*) from tsql.tb t where object_name = 'T'
23:13:06 2 /
COUNT(*)
----------
1
1 row selected.
Elapsed: 00:00:00.32
23:13:08 4294967295 SYS@db11g> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
--------------------------------------------------
Plan hash value: 2989731593
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 18127 (1)| 00:03:38 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | TABLE ACCESS FULL| TB | 106 | 2650 | 18127 (1)| 00:03:38 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME"='T')
14 rows selected.
--
-- Stored outlines (example)
--
23:39:23 71329 T1@db11g> alter session set create_stored_outlines=mycat;
Session altered.
Elapsed: 00:00:00.00
23:39:43 71329 T1@db11g> select count(*) from t where id = 100;
COUNT(*)
------------------------------------
1.0
1 row selected.
Elapsed: 00:00:00.00
23:39:58 71329 T1@db11g> alter session set create_stored_outlines=false;
Session altered.
Elapsed: 00:00:00.00
23:40:07 71329 T1@db11g> set autotrace on
23:40:22 71329 T1@db11g> select count(*) from t where id = 100;
COUNT(*)
------------------------------------
1.0
1 row selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
122 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
23:40:39 71329 T1@db11g> create index ti on t(id);
Index created.
Elapsed: 00:00:00.14
23:40:55 71329 T1@db11g> select count(*) from t where id = 100;
COUNT(*)
------------------------------------
1.0
1 row selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 647456754
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| TI | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
4 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
23:40:58 71329 T1@db11g> alter session set use_stored_outlines=mycat;
Session altered.
Elapsed: 00:00:00.00
23:41:20 71329 T1@db11g> select count(*) from t where id = 100;
COUNT(*)
------------------------------------
1.0
1 row selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=100)
Note
-----
- outline "SYS_OUTLINE_12082923395822524" used for this statement
Statistics
----------------------------------------------------------
34 recursive calls
147 db block gets
144 consistent gets
0 physical reads
616 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
23:41:22 71329 T1@db11g>
--
--os/db cpu history + db stat pct
--
set pagesize 30
col snap_id format 9999999
set lines 150
col os_cpu_pct format 999999.999
col db_stat_pct format 999999.999
col db_cpu_pct format 999999999.999
col db_cpu_min format 99999.999
col available_cpu_min format 99999.999
col stat_name format a15 truncate
break on snap_id on os_cpu_pct on t on available_cpu_min on db_cpu_min on db_cpu_pct
select b.snap_id
, TO_CHAR(dhs.begin_interval_time, 'dd.mm.yyyy hh24:mi:ss') t
, round(((b.t1 / (b.t1 + i.t1 + nvl(io.t1, 0)))) * 100, 3) os_cpu_pct
,dhs.t1 * cpu.cpu_count available_cpu_min
,dcpu.t1/1000000/60 db_cpu_min
,((dcpu.t1/1000000/60)/(dhs.t1 * cpu.cpu_count)) * 100 db_cpu_pct
, stat_name
, round((s.t1/a.t1) * 100, 3) db_stat_pct
from
(select snap_id, value - lag(value) over(order by snap_id) t1 from dba_hist_osstat where stat_name = 'BUSY_TIME') b
,(select snap_id, value - lag(value) over(order by snap_id) t1 from dba_hist_osstat where stat_name = 'IDLE_TIME') i
,(select snap_id, value - lag(value) over(order by snap_id) t1 from dba_hist_osstat where stat_name = 'IOWAIT_TIME') io
,(select snap_id, value cpu_count from dba_hist_osstat where stat_name = 'NUM_CPUS') cpu
,(select snap_id, value - lag(value) over(order by snap_id) t1 from DBA_HIST_SYS_TIME_MODEL where stat_name = 'DB time') a
,(select snap_id, value - lag(value) over(order by snap_id) t1 from DBA_HIST_SYS_TIME_MODEL where stat_name = 'DB CPU') dcpu
,(select snap_id, stat_name, value - lag(value) over(partition by stat_name order by snap_id) t1 from DBA_HIST_SYS_TIME_MODEL where stat_name <> 'DB time' and stat_name not like 'background%') s
,
(
select
dhs.snap_id
,((EXTRACT(DAY FROM (dhs.end_interval_time-dhs.begin_interval_time) )*86400+
EXTRACT(HOUR FROM (dhs.end_interval_time-dhs.begin_interval_time) )*3600+
EXTRACT(MINUTE FROM (dhs.end_interval_time-dhs.begin_interval_time) )*60+
EXTRACT(SECOND FROM (dhs.end_interval_time-dhs.begin_interval_time) ))/60) t1
, dhs.begin_interval_time
from dba_hist_snapshot dhs
) dhs
where i.snap_id = b.snap_id and s.snap_id = i.snap_id and a.snap_id = i.snap_id and io.snap_id (+)= i.snap_id
and round((s.t1/a.t1) * 100, 3) > 10
and dhs.snap_id = b.snap_id
and cpu.snap_id = b.snap_id
and dcpu.snap_id = b.snap_id
order by b.snap_id, db_stat_pct desc;
--
-- No FK Index (from Tom Kyte), a bit modified
--
column columns format a20 word_wrapped
column table_name format a30 word_wrapped
select a.table_owner, decode( b.table_name, NULL, '****', 'ok' ) Status,
a.table_name, a.columns, b.columns
from
( select a.owner table_owner, substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
from dba_cons_columns a, dba_constraints b
where a.owner not in ('SYS', 'SYSMAN', 'ORDDATA', 'MDSYS', 'OLAPSYS', 'LBACSYS', 'EXFSYS', 'SYSTEM', 'DBSNMP')
and a.owner = b.owner
and a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.owner, substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select table_owner, substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
from dba_ind_columns
group by table_owner, substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_owner = b.table_owner (+)
and a.table_name = b.table_name (+)
and b.table_name is null
and b.columns (+) like a.columns || '%'
/
--changes in exec time between snaphosts
undef snap_id1
undef snap_id2
col s1 format 999
col s2 format 999
col s format 999
col ROWS_DELTA_S1 format 999999999
col ROWS_DELTA_S2 format 999999999
col EXE_DELTA_S1 format 999999
col EXE_DELTA_S2 format 999999
col PARSING_SCHEMA_NAME format a7
select *
from
(
select TO_CHAR(s.BEGIN_INTERVAL_TIME, 'dd.mm.yyyy hh24:mi:ss') begin_time, h.sql_id, h.PARSING_SCHEMA_NAME
, lag(trunc(h.ELAPSED_TIME_DELTA/DECODE(h.executions_delta, 0, 1, h.executions_delta)/1000000)) over(partition by h.sql_id order by h.snap_id) s1,
trunc(h.ELAPSED_TIME_DELTA/DECODE(h.executions_delta, 0, 1, h.executions_delta)/1000000) s2,
trunc(h.ELAPSED_TIME_DELTA/DECODE(h.executions_delta, 0, 1, h.executions_delta)/1000000) -
lag(trunc(h.ELAPSED_TIME_DELTA/DECODE(h.executions_delta, 0, 1, h.executions_delta)/1000000)) over(partition by h.sql_id order by h.snap_id) s
, lag(h.ROWS_PROCESSED_DELTA) over (partition by h.sql_id order by h.snap_id) ROWS_DELTA_s1
, h.ROWS_PROCESSED_DELTA ROWS_DELTA_s2
, lag(h.executions_delta) over (partition by h.sql_id order by h.snap_id) EXE_DELTA_S1
, h.executions_delta EXE_DELTA_S2
, decode(h.executions_delta, h.END_OF_FETCH_COUNT_DELTA, NULL, 'Continue running?') txt
from DBA_HIST_SQLSTAT h, DBA_HIST_SNAPSHOT s
where s.snap_id in (&&snap_id1, &&snap_id2)
and s.snap_id = h.snap_id
order by h.snap_id
)
where s <> 0
;
Комментариев нет:
Отправить комментарий