set pagesize 999 lines 70 verify off heading off feedback off termout off
col start_up format a45 justify right
col sp_size format 999,999,999 justify right
col x_sp_used format 999,999,999 justify right
col sp_used_shr format 999,999,999 justify right
col sp_used_per format 999,999,999 justify right
col sp_used_run format 999,999,999 justify right
col sp_avail format 999,999,999 justify right
col sp_sz_pins format 999,999,999 justify right
col sp_no_pins format 999,999 justify right
col sp_no_obj format 999,999 justify right
col sp_no_stmts format 999,999 justify right
col sp_sz_kept_chks format 999,999,999 justify right
col sp_no_kept_chks format 999,999 justify right
col 1time_sum_pct format 999 justify right
col 1time_ttl_pct format 999 justify right
col ltime_ttl format 999,999,999 justify right
col 1time_sum format 999,999,999,999 justify right
col tot_lc format 999,999,999,999 justify right
col sp_free format 999,999,999,999 justify right
col val1 new_val x_sgasize noprint
col val2 new_val x_sp_size noprint
col val3 new_val x_lp_size noprint
col val4 new_val x_jp_size noprint
col val5 new_val x_bc_size noprint
col val6 new_val x_other_size noprint
col val7 new_val x_str_size noprint
col val8 new_val x_KGH noprint
select val1, val2, val3, val4, val5, val6, val7, val8
from
(select sum(bytes) val1 from v$sgastat) s1,
(select nvl(sum(bytes),0) val2 from v$sgastat where pool='shared pool') s2,
(select nvl(sum(bytes),0) val3 from v$sgastat where pool='large pool') s3,
(select nvl(sum(bytes),0) val4 from v$sgastat where pool='java pool') s4,
(select nvl(sum(bytes),0) val5 from v$sgastat where name='buffer_cache') s5,
(select nvl(sum(bytes),0) val6 from v$sgastat where name in ('log_buffer','fixed_sga')) s6,
(select nvl(sum(bytes),0) val7 from v$sgastat where pool='streams pool') s7,
(select nvl(sum(bytes),0) val8 from v$sgastat where pool='shared pool' and name='KGH: NO ACCESS') s8;
col val1 new_val x_sp_used noprint
col val2 new_val x_sp_used_shr noprint
col val3 new_val x_sp_used_per noprint
col val4 new_val x_sp_used_run noprint
col val5 new_val x_sp_no_stmts noprint
col val6 new_val x_sp_vers noprint
select sum(sharable_mem+persistent_mem+runtime_mem) val1,sum(sharable_mem) val2, sum(runtime_mem) val4, sum(persistent_mem) val3,count(*) val5, max(version_count) val6 from v$sqlarea;
col val1 new_val x_1time_sum noprint
col val2 new_val x_1time_ttl noprint
select sum(sharable_mem+persistent_mem+runtime_mem) val1, count(*) val2 from v$sqlarea
where executions=1;
col val1 new_val x_ra noprint
select round(nvl((used_space+free_space),0),2) val1 from v$shared_pool_reserved;
col val2 new_val x_sp_no_obj noprint
select count(*) val2 from v$db_object_cache;
col val2 new_val x_sp_no_kept_chks noprint
col val3 new_val x_sp_sz_kept_chks noprint
select decode(count(*),'',0,count(*)) val2, decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3
from v$db_object_cache where kept='YES';
col val2 new_val x_sp_free_chks noprint
select sum(bytes) val2 from v$sgastat where name='free memory' and pool='shared pool';
col val2 new_val x_sp_no_pins noprint
select count(*) val2 from v$session a, v$sqltext b where a.sql_address||a.sql_hash_value = b.address||b.hash_value;
col val2 new_val x_sp_sz_pins noprint
select sum(sharable_mem+persistent_mem+runtime_mem) val2 from v$session a,v$sqltext b,v$sqlarea c
where a.sql_address||a.sql_hash_value = b.address||b.hash_value
and b.address||b.hash_value = c.address||c.hash_value;
col val3 new_val x_tot_lc noprint
select nvl(sum(lc_inuse_memory_size)+sum(lc_freeable_memory_size),0) val3 from v$library_cache_memory;
col val2 new_val x_sp_avail noprint
select &x_sp_size-(&x_tot_lc*1024*1024)-&x_sp_used val2 from dual;
col val2 new_val x_sp_other noprint
select &x_sp_size-(&x_tot_lc*1024*1024) val2 from dual;
col val1 new_val x_trend_4031 noprint
col val2 new_val x_trend_size noprint
col val3 new_val x_trend_rS noprint
col val4 new_val x_trend_rs_size noprint
select request_misses val1,decode(request_misses,0,0,last_Miss_Size) val2,request_failures val3,decode(request_failures,0,0,last_failure_size) val4 from v$shared_pool_reserved;
set termout on heading off
ttitle -
center 'SGA/Shared Pool Breakdown' skip 2
spool sga.out
select ' *** If database started recently, this data is not as useful ***',' ','Database Started: '||to_char(startup_time, 'Mon/dd/yyyy hh24:mi:ss') start_up,'Instance Name/No: '||instance_name||'-'||instance_number,' ','Breakdown of SGA '||round((&x_sgasize/1024/1024),2)||'M ',
' Shared Pool Size : '||round((&x_sp_size/1024/1024),2)||'M ('||round((&x_sp_size/&x_sgasize)*100,0)||'%) Reserved ' ||round((&x_ra/1024/1024),2)||'M ('||round((&x_ra/&x_sp_size)*100,0)||'%)' sp_size,
' Large Pool : '||round((&x_lp_size/1024/1024),2)||'M ('||round((&x_lp_size/&x_sgasize)*100,0)||'%)',
' Java Pool : '||round((&x_jp_size/1024/1024),2)||'M ('||round((&x_jp_size/&x_sgasize)*100,0)||'%)',
' Buffer Cache : '||round((&x_bc_size/1024/1024),2)||'M ('||round((&x_bc_size/&x_sgasize)*100,0)||'%)',
' Streams Pool : '||round((&x_str_size/1024/1024),2)||'M ('||round((&x_str_size/&x_sgasize)*100,0)||'%)',
' Other Areas in SGA : '||round((&x_other_size/1024/1024),2)||'M ('||round((&x_other_size/&x_sgasize)*100,0)||'%)',' ',' *** High level breakdown of memory ***',' ',
' sharable : '||round((&x_sp_used_shr/1024/1024),2)||'M' sp_used_shr,
' persistent : '||round((&x_sp_used_per/1024/1024),2)||'M' sp_used_per,
' runtime : '||round((&x_sp_used_run/1024/1024),2)||'M' sp_used_run,
' ','SQL Memory Usage (total) : '||round((&x_sp_used/1024/1024),2)||'M ('||round((&x_sp_used/&x_sp_size)*100,0)||'%)',' ',' *** No guidelines on SQL in Library Cache, but if ***',
' *** pinning a lot of code--may need larger Shared Pool ***',' ',
'# of SQL statements : '||&x_sp_no_stmts sp_no_stmts,'# of pinned SQL statements : '||&x_sp_no_pins sp_no_pins,'# of programmatic constructs : '||&x_sp_no_obj sp_no_obj,'# of pinned programmatic construct : '
||&x_sp_no_kept_chks sp_no_kept_chks,' ','Efficiency Analysis: ',' *** High versions (100s) could be bug ***',' ',' Max Child Cursors Found : '||&x_sp_vers,' Programmatic construct memory size (Kept) : '
||round((&x_sp_sz_kept_chks/1024/1024),2)||'M' sp_sz_kept_chks,' Pinned SQL statements memory size (active sessions) : '||round((&x_sp_sz_pins/1024/1024),2)||'M' sp_sz_pins,
' ',' *** LC at 50% or 60% of Shared Pool not uncommon ***',' ',' Estimated Total Library Cache Memory Usage : '||&x_tot_lc||'M ('||100*(round(((&x_tot_lc) / (&x_sp_size/1024/1024)),2))||'%)' perc_lc,
' Other Shared Pool Memory : '||round((&x_sp_other/1024/1024),2)||'M',' Shared Pool Free Memory Chunks : '||round(((&x_sp_free_chks) /1024/1024),2)||'M ('||100*(round((&x_sp_free_chks / &x_sp_size),2))||'%)' perc_free,
' ',' ****Ideal percentages for 1 time executions is 20% or lower**** ',
' ',' # of objects executed only 1 time : '||&x_1time_ttl||' ('||100*round(((&x_1time_ttl / &x_sp_no_stmts)),2)||'%)',' Memory for 1 time executions: : '||round((&x_1time_sum/1024/1024),2)||'M ('||
100*round(((&x_1time_sum / &x_sp_used)),2)||'%)',' ',' ***If these chunks are growing, SGA_TARGET may be too low***',' ',' Current KGH: NO ACCESS Allocations: '||round((&x_KGH/1024/1024),2)||'M ('||100*round((&x_KGH/&x_sp_size),2)||'%)',
' ',' ***0 misses is ideal, but if growing value points to memory issues***',
' ',' # Of Misses for memory : '|| &x_trend_rs,' Size of last miss : '|| &x_trend_rs_size,' # Of Misses for Reserved Area : '|| &x_trend_4031,' Size of last miss Reserved Area : '|| &x_trend_size
from v$instance;
spool off
ttitle off
set heading feedback on
clear col
SGA/Shared Pool Breakdown
*** If database started recently, this data is not as useful ***
Database Started: Jun/21/2016 13:49:53
Instance Name/No: arac1-1
Breakdown of SGA 16384.12M
Shared Pool Size : 2048M (12%) Reserved 202.73M (10%)
Large Pool : 288M (2%) Java Pool : 128M (1%)
Buffer Cache : 13376.24M (82%) Streams Pool : 0M (0%)
Other Areas in SGA : 32M (0%)
*** High level breakdown of memory *** sharable : 307.46M
persistent : 270.54M runtime : 255.69M
SQL Memory Usage (total) : 833.69M (41%)
*** No guidelines on SQL in Library Cache, but if ***
*** pinning a lot of code--may need larger Shared Pool ***
# of SQL statements : 3418 # of pinned SQL statements : 4
# of programmatic constructs : 24033
# of pinned programmatic construct : 4433 Efficiency Analysis:
*** High versions (100s) could be bug ***
Max Child Cursors Found : 18
Programmatic construct memory size (Kept) : 17.78M
Pinned SQL statements memory size (active sessions) : .45M
*** LC at 50% or 60% of Shared Pool not uncommon ***
Estimated Total Library Cache Memory Usage : 336M (16%)
Other Shared Pool Memory : 1712M
Shared Pool Free Memory Chunks : 440.09M (21%)
****Ideal percentages for 1 time executions is 20% or lower****
# of objects executed only 1 time : 2206 (65%)
Memory for 1 time executions: : 495.83M (59%)
***If these chunks are growing, SGA_TARGET may be too low***
Current KGH: NO ACCESS Allocations: 0M (0%)
***0 misses is ideal, but if growing value points to memory issues***
# Of Misses for memory : 0 Size of last miss : 0
# Of Misses for Reserved Area : 0
Size of last miss Reserved Area : 0
Sunday, 16 October 2011
Oracle SGA Info
DATABASE GROWTH / Disk Used by Object Type
DATABASE GROWTH / Disk Used by Object Type
define schema_name='SYS'
define days_back=10
select sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner = '&schema_name'
and space_used_delta > 0;
Space used (M) Total Schema Size (M) Percent of Total Disk Usage
-------------- --------------------- -----------------------------------------
19626.4335 1564335.63 1.25%
DATABASE GROWTH delta
define schema_name='SYS'
define days_back=10
ttitle "Total Disk Used by Object Type"
select c.segment_type, sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes)/1024/1024 "Total Space (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and space_used_delta > 0
and c.owner = '&schema_name'
group by rollup(segment_type);
SEGMENT_TYPE Space used (M) Total Space (M) Percent of Total Disk Usage
------------------ -------------- --------------- -----------------------------------------
INDEX 421.957651 357853.375 .12%
INDEX PARTITION 1297.58915 33745 3.85%
TABLE 119.543896 434165.25 .03%
TABLE PARTITION 17787.3428 738572 2.41%
19626.4335 1564335.63 1.25%
SQL>
Object Growth
set linesize 300 pagesize 300
col OWNER for a20
col OBJECT_NAME for a35
with
awr_seg_stat as (
select
ts#,obj#,dataobj#
,snap_id
,space_used_total
,space_used_delta
,space_allocated_total
,space_allocated_delta
,physical_writes_delta
,physical_write_requests_delta
from dba_hist_seg_stat s
--where obj# in (select object_id from dba_objects where owner='')
)
,top_segs_by_growth as (
select *
from (
select
ts#,obj#
,sum(space_allocated_delta) delta
,dense_rank()over(order by sum(space_allocated_delta) desc) N
from awr_seg_stat
group by ts#,obj#
)
where N<=10 -- topN
)
select
s.n
,o.owner,o.object_type,o.object_name
,round(s.delta/1024/1024) "Delta(MB)"
from top_segs_by_growth s ,dba_objects o
where s.obj# = o.object_id
order by 1
/
N OWNER OBJECT_TYPE OBJECT_NAME Delta(MB)
---------- -------------------- ----------------------- ----------------------------------- ----------
6 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 37
6 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 37
6 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 37
6 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 37
7 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 37
7 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 37
8 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 36
9 SYS TABLE PARTITION WRH$_ACTIVE_SESSION_HISTORY 31
10 SYS TABLE PARTITION WRH$_ACTIVE_SESSION_HISTORY 30
SQL>
Saturday, 15 October 2011
EXCEPTION CASE_NOT_FOUND
Oracle function Case
Oracle case
SET SERVEROUT ON
DECLARE
p BINARY_INTEGER:= 0;
q BINARY_INTEGER := 0;
BEGIN
BEGIN
CASE
WHEN p>100 THEN q:= 1;
WHEN p>50 THEN q:= 2;
WHEN p>10 THEN q:= 3;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
q := 5;
END;
DBMS_OUTPUT.PUT_LINE('q = ' || q);
END;
/
case for null
SET SERVEROUT ON
DECLARE
p BINARY_INTEGER:= null;
-- p BINARY_INTEGER:= 0;
q BINARY_INTEGER := 0;
BEGIN
BEGIN
CASE
WHEN p is null then q:=10000;
WHEN p>100 THEN q:= 1;
WHEN p>50 THEN q:= 2;
WHEN p>10 THEN q:= 3;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
q := 4;
END;
DBMS_OUTPUT.PUT_LINE('q = ' || q);
END;
/
Oracle case
SET SERVEROUT ON
DECLARE
p BINARY_INTEGER:= 0;
q BINARY_INTEGER := 0;
BEGIN
BEGIN
CASE
WHEN p>100 THEN q:= 1;
WHEN p>50 THEN q:= 2;
WHEN p>10 THEN q:= 3;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
q := 5;
END;
DBMS_OUTPUT.PUT_LINE('q = ' || q);
END;
/
case for null
SET SERVEROUT ON
DECLARE
p BINARY_INTEGER:= null;
-- p BINARY_INTEGER:= 0;
q BINARY_INTEGER := 0;
BEGIN
BEGIN
CASE
WHEN p is null then q:=10000;
WHEN p>100 THEN q:= 1;
WHEN p>50 THEN q:= 2;
WHEN p>10 THEN q:= 3;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
q := 4;
END;
DBMS_OUTPUT.PUT_LINE('q = ' || q);
END;
/
ORA-01476: divisor is equal to zero
to avoid ORA-01476: divisor is equal to zero
declare
n1 number := 155;
n2 number := 0;
begin
n1 := n1/n2; -- divide by zero
dbms_output.put_line(n1);
exception
when ZERO_DIVIDE
then dbms_output.put_line('You Divided by Zero');
end;
/
declare
n1 number := 155;
n2 number := 0;
begin
n1 := n1/n2; -- divide by zero
dbms_output.put_line(n1);
exception
when ZERO_DIVIDE
then dbms_output.put_line('You Divided by Zero');
end;
/
DBMS_STATS.GATHER_SCHEMA_STATS
Schema stats
DBMS_STATS state gather
DBMS_STATS
declare
olist dbms_stats.objecttab ;
begin
declare
olist dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER STALE',objlist=>olist);
dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;
end;
method_opt=>'for all indexed columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for columns size auto'
There are several values for the OPTIONS parameter that we need to know about:
GATHER - Reanalyzes the whole schema
GATHER EMPTY - only analyzes tables that have no existing statistics
GATHER STALE - only reanalyzes tables with more than 10 percent modifications (inserts, updates, deletes)
GATHER AUTO - will reanalyze objects that currently have no statistics and objects with stale statistics. Using GATHER AUTO is like combining GATHER STALE and GATHER EMPTY.
Example :
exec DBMS_STATS.gather_schema_stats(
ownname => 'ANUJ',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
cascade => true )
SQL> show user
USER is "SCOTT"
SQL> /
Objects Analyzed: 0
PL/SQL procedure successfully completed.
SQL> r
1 declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER STALE',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14* end;
Objects Analyzed: 0
PL/SQL procedure successfully completed.
ORA-20001: Illegal option GATHER REANALYZES: must be GATHER | GATHER AUTO | GATHER STALE | GATHER EMPTY | LIST AUTO | LIST STALE | LIST EMPTY
'GATHER REANALYZES' reanalyzes
declare
olist dbms_stats.objecttab ;
begin
declare
olist dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER AUTO',objlist=>olist);
dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;
end;
SQL> show user
USER is "SCOTT"
SQL> declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER AUTO',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14 end;
15
16
17 /
Objects Analyzed: 15
Object Name: ANUJ10
Object Name: BONUS
Object Name: DEPT
Object Name: EMP
Object Name: MYEMP
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: SALGRADE
Object Name: MYEMP_IDX
Object Name: PK_DEPT
Object Name: SYS_C0022541
Object Name: SYS_C0022542
Object Name: SYS_C0022543
PL/SQL procedure successfully completed.
declare
olist dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER',objlist=>olist);
dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;
end;
SQL> declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14 end;
15 /
Objects Analyzed: 7
Object Name: ANUJ10
Object Name: BONUS
Object Name: DEPT
Object Name: EMP
Object Name: MYEMP
Object Name: MYEMP_WORK
Object Name: SALGRADE
PL/SQL procedure successfully completed.
SQL> declare
2 a dbms_stats.objecttab;
3 begin
4 dbms_stats.gather_database_stats (OPTIONS=>'LIST EMPTY',OBJLIST=>a);
5
6 for i in 1 .. a.count
7 loop
8 dbms_output.put_line( a(i).ownname );
9 dbms_output.put_line( a(i).objType );
10 dbms_output.put_line( a(i).objName );
11 dbms_output.put_line( a(i).PartName );
12 dbms_output.put_line( a(i).subPartName );
13 dbms_output.put_line ( '-------------------------' );
15 end loop;
16 end;
17 /
SH
INDEX
SYS_IOT_TOP_74375
-------------------------
SH
INDEX
SYS_IOT_TOP_74380
-------------------------
PL/SQL procedure successfully completed.
DBMS_STATS state gather
DBMS_STATS
declare
olist dbms_stats.objecttab ;
begin
declare
olist dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER STALE',objlist=>olist);
dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;
end;
method_opt=>'for all indexed columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for columns size auto'
There are several values for the OPTIONS parameter that we need to know about:
GATHER - Reanalyzes the whole schema
GATHER EMPTY - only analyzes tables that have no existing statistics
GATHER STALE - only reanalyzes tables with more than 10 percent modifications (inserts, updates, deletes)
GATHER AUTO - will reanalyze objects that currently have no statistics and objects with stale statistics. Using GATHER AUTO is like combining GATHER STALE and GATHER EMPTY.
Example :
exec DBMS_STATS.gather_schema_stats(
ownname => 'ANUJ',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
cascade => true )
SQL> show user
USER is "SCOTT"
SQL> /
Objects Analyzed: 0
PL/SQL procedure successfully completed.
SQL> r
1 declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER STALE',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14* end;
Objects Analyzed: 0
PL/SQL procedure successfully completed.
ORA-20001: Illegal option GATHER REANALYZES: must be GATHER | GATHER AUTO | GATHER STALE | GATHER EMPTY | LIST AUTO | LIST STALE | LIST EMPTY
'GATHER REANALYZES' reanalyzes
declare
olist dbms_stats.objecttab ;
begin
declare
olist dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER AUTO',objlist=>olist);
dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;
end;
SQL> show user
USER is "SCOTT"
SQL> declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER AUTO',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14 end;
15
16
17 /
Objects Analyzed: 15
Object Name: ANUJ10
Object Name: BONUS
Object Name: DEPT
Object Name: EMP
Object Name: MYEMP
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: SALGRADE
Object Name: MYEMP_IDX
Object Name: PK_DEPT
Object Name: SYS_C0022541
Object Name: SYS_C0022542
Object Name: SYS_C0022543
PL/SQL procedure successfully completed.
declare
olist dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER',objlist=>olist);
dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;
end;
SQL> declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14 end;
15 /
Objects Analyzed: 7
Object Name: ANUJ10
Object Name: BONUS
Object Name: DEPT
Object Name: EMP
Object Name: MYEMP
Object Name: MYEMP_WORK
Object Name: SALGRADE
PL/SQL procedure successfully completed.
SQL> declare
2 a dbms_stats.objecttab;
3 begin
4 dbms_stats.gather_database_stats (OPTIONS=>'LIST EMPTY',OBJLIST=>a);
5
6 for i in 1 .. a.count
7 loop
8 dbms_output.put_line( a(i).ownname );
9 dbms_output.put_line( a(i).objType );
10 dbms_output.put_line( a(i).objName );
11 dbms_output.put_line( a(i).PartName );
12 dbms_output.put_line( a(i).subPartName );
13 dbms_output.put_line ( '-------------------------' );
15 end loop;
16 end;
17 /
SH
INDEX
SYS_IOT_TOP_74375
-------------------------
SH
INDEX
SYS_IOT_TOP_74380
-------------------------
PL/SQL procedure successfully completed.
===============
#!/bin/bash
. /home/oracle/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/12.2/db_home
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=orcl
export DATE=$(date +%y-%m-%d_%H%M%S)
# Gather ANUJ schema stats
sqlplus / as sysdba << EOF > /tmp/ANUJ_stats_gather_$DATE.log
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;
EXEC DBMS_STATS.gather_schema_stats('ANUJ');
EOF
echo "Stats gathered succeeded"
********
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;
/home/oracle/bin/run_gather_database_stats.sh orcl &> /dev/null
cat /home/oracle/bin/run_gather_database_stats.sh
#! /bin/bash
#
source /home/oracle/.bash_profile
sid=$1
export OUTFILE=/var/log/oracle/gather_database_stats_$sid.log
export ORACLE_SID=${sid}
$echo $ORACLE_SID
sqlplus /nolog << EOF
connect / as sysdba
set line 999
set pagesize 999
set trimspool on
set echo on
spool $OUTFILE
@/home/oracle/bin/gather_databse_stats.sql
spool off
exit
cat /home/oracle/bin/gather_databse_stats.sql
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
exec DBMS_STATS.GATHER_DATABASE_STATS (options=> 'GATHER EMPTY');
select sysdate from dual;
exec DBMS_STATS.GATHER_DATABASE_STATS (options=> 'GATHER STALE');
select sysdate from dual;
[oracle@lon1-rac01 ~]$
=============
on root and pdbs !!!!
===================
check this
https://anuj-singh.blogspot.com/search?q=catcon.pl
set verify off time on timing on
alter session set nls_date_format='dd-mon-yyyy';
select sysdate from dual;
prompt ========================================
prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ()
prompt ========================================
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM')
prompt ========================================
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');
prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS')
prompt ========================================
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
prompt ========================================
prompt DBMS_STATS.GATHER_DICTIONARY_STATS
prompt ========================================
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS ();
====================
or
set echo on time on timing on;
prompt Stats on Container;
BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');
DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
DBMS_STATS.GATHER_DICTIONARY_STATS ();
END;
alter session set container=XXXXX;
prompt Stats on Pluggable;
BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');
DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
DBMS_STATS.GATHER_DICTIONARY_STATS ();
END;
/
====
SET SERVEROUTPUT ON
DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_SCHEMA_stats(OWnNAME=>'&schema_name',objlist=>ObjList, options=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/
=====================
nohup sqlplus '/ as sysdba' @run_gather_stats.sql > run_gather_stats1.log 2>&1 &
change pdb name
set time on timing on
timing start
alter session set nls_date_format='dd-mon-yyyy hh24:mi';
select sysdate from dual;
prompt ========================================
prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
prompt ========================================
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM',degree => DBMS_STATS.default_degree)
prompt ========================================
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM',degree => DBMS_STATS.default_degree);
prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS',degree => DBMS_STATS.default_degree)
prompt ========================================
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',degree => DBMS_STATS.default_degree);
prompt 'alter session set container=XXXXX' ------<<<<<<
alter session set container=XXXXXXX ;
show pdbs
alter session set nls_date_format='dd-mon-yyyy hh24:mi';
prompt ========================================
prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
prompt ========================================
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM',degree => DBMS_STATS.default_degree)
prompt ========================================
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM',degree => DBMS_STATS.default_degree);
prompt ========================================
prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS',degree => DBMS_STATS.default_degree)
prompt ========================================
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',degree => DBMS_STATS.default_degree);
select sysdate from dual;
/
timing stop
*****************
to check
show pdbs
alter session set nls_date_format='DD-MM-YYYY hh24:mi';
col last_analyzed for a13
set termout off
set trimspool off
set feedback off
spool dictionary_statistics.txt append
prompt 'Statistics for SYS tables'
SELECT NVL(TO_CHAR(last_analyzed, 'DD-MM-YYYY'), 'NO STATS') last_analyzed, COUNT(*) sys_dictionary_tables
FROM dba_tables
WHERE owner = 'SYS'
GROUP BY TO_CHAR(last_analyzed, 'DD-MM-YYYY')
ORDER BY 1;
prompt 'Statistics for SYSTEM Objects'
SELECT NVL(TO_CHAR(last_analyzed, 'DD-MM-YYYY'), 'NO STATS') last_analyzed, COUNT(*) system_dictionary_tables
FROM dba_tables
WHERE owner = 'SYSTEM'
GROUP BY TO_CHAR(last_analyzed, 'DD-MM-YYYY')
ORDER BY 1;
prompt 'Statistics for Fixed Objects'
select NVL(TO_CHAR(last_analyzed, 'DD-MM-YYYY'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'DD-MM-YYYY');
/
==================
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'SYS',
options => 'GATHER AUTO'
);
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'SYSTEM',
options => 'GATHER AUTO'
);
DBMS_STATS.GATHER_DICTIONARY_STATS;
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
DBMS_STATS.GATHER_INDEX_STATS('SYS','I_TS#');
DBMS_STATS.GATHER_INDEX_STATS('SYS','I_USER#');
DBMS_STATS.GATHER_INDEX_STATS('SYS','I_TOID_VERSION#');
DBMS_STATS.GATHER_INDEX_STATS('SYS','I_MLOG#');
DBMS_STATS.GATHER_INDEX_STATS('SYS','I_RG#');
DBMS_STATS.GATHER_INDEX_STATS('SYS','I_FILE#_BLOCK#');
DBMS_STATS.GATHER_INDEX_STATS('SYS','I_OBJ2');
DBMS_STATS.GATHER_INDEX_STATS('SYS','I_DEPENDENCY2');
DBMS_STATS.GATHER_INDEX_STATS('SYS','I_OBJ1');
DBMS_STATS.GATHER_INDEX_STATS('SYS','I_OBJ#');
END;
/
prompt to delete Stats !!
prompt "EXEC DBMS_STATS.delete_database_stats"
EXEC DBMS_STATS.delete_database_stats;
prompt "EXEC DBMS_STATS.delete_dictionary_stats"
EXEC DBMS_STATS.delete_dictionary_stats;
prompt "EXEC DBMS_STATS.delete_SCHEMA_STATS('SYS')"
EXEC DBMS_STATS.delete_SCHEMA_STATS('SYS');
prompt "EXEC DBMS_STATS.delete_SCHEMA_STATS('SYSTEM')"
EXEC DBMS_STATS.delete_SCHEMA_STATS('SYSTEM');
prompt "EXEC DBMS_STATS.delete_FIXED_OBJECTS_STATS ()"
EXEC DBMS_STATS.delete_FIXED_OBJECTS_STATS ();
**********************************ww
BEGIN
DBMS_STATS.GATHER_DICTIONARY_STATS (degree => DBMS_STATS.DEFAULT_DEGREE);
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM',degree => DBMS_STATS.DEFAULT_DEGREE);
DBMS_STATS.GATHER_SCHEMA_STATS('SYS',degree => DBMS_STATS.DEFAULT_DEGREE);
--DBMS_STATS.GATHER_database_stats(degree => DBMS_STATS.DEFAULT_DEGREE);
DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC ;
END;
/
********************************** BEGIN DBMS_STATS.delete_database_stats; DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (); DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM'); DBMS_STATS.GATHER_SCHEMA_STATS('SYS'); DBMS_STATS.GATHER_DICTIONARY_STATS (); END; /
column owner format a6 column table_name format a30 set pages 4000 select count(*), owner, to_char(last_analyzed,'YYYY-MM-DD') LAST_ANALYZED from dba_tab_statistics where owner in ('SYS','SYSTEM','XDB') GROUP BY owner, to_char(last_analyzed,'YYYY-MM-DD') ORDER BY LAST_ANALYZED DESC;
set linesize 500 pagesize 300 DEFINE 3="timestamp'2025-07-15 04:00:00'" DEFINE 4="timestamp'2026-07-15 07:00:00'" -- Format text columns (stops them from stretching to 128 chars) COLUMN owner FORMAT A15 HEADING "Owner" COLUMN table_name FORMAT A25 TRUNCATE HEADING "Table Name" COLUMN partition_name FORMAT A20 TRUNCATE HEADING "Partition" COLUMN subpartition_name FORMAT A20 TRUNCATE HEADING "Subpartition" COLUMN object_type FORMAT A12 HEADING "Obj Type" COLUMN scope FORMAT A10 HEADING "Scope" -- Format dates and timestamps COLUMN last_anal FORMAT A11 HEADING "Last Anal" COLUMN im_stat_update_time FORMAT A19 HEADING "IM Stat Update" -- Format numeric columns COLUMN num_rows FORMAT 999,999,999 HEADING "Num Rows" COLUMN sample_size FORMAT 999,999,999 HEADING "Sample Size" COLUMN avg_row_len FORMAT 999,999 HEADING "Avg Row|Len" COLUMN blocks FORMAT 999,999,999 HEADING "Blocks" COLUMN empty_blocks FORMAT 999,999 HEADING "Empty|Blocks" COLUMN seconds FORMAT 99,999 HEADING "Seconds" COLUMN bytes FORMAT 999,999,999 HEADING "Bytes" COLUMN sample_ratio FORMAT 990.99 HEADING "Sample|Ratio" COLUMN partition_position FORMAT 99999 HEADING "Part|Pos" COLUMN subpartition_position FORMAT 99999 HEADING "Subpart|Pos" -- Format minor status/flag columns COLUMN glo FORMAT A3 HEADING "Glo" COLUMN use FORMAT A3 HEADING "Use" COLUMN statt FORMAT A5 HEADING "Statt" COLUMN sta FORMAT A3 HEADING "Sta" select v.last_analyzed, table_name, num_rows, sample_size, avg_row_len ,(v.last_analyzed - lag(v.last_analyzed) over (order by v.last_analyzed))*86400 seconds ,(num_rows * avg_row_len) bytes ,(sample_size/nullif(num_rows, 0)) sample_ratio ,v.* from dba_tab_statistics v where object_type in ('FIXED TABLE') --and v.last_analyzed between timestamp'2016-05-17 09:00:00' and timestamp'2016-05-17 10:00:00' and v.last_analyzed between &3 and &4 order by seconds desc nulls last;
col con_id head "Con|tai|ner" form 999
col window_name head "Window" form a16
col wst head "Window|Start|Time" form a20
col window_duration head "Window|Duration|Hours" form 999999
col jobs_created head "Jobs|Created" form 999
col jobs_started head "Jobs|Started" form 999
col jobs_completed head "Jobs|Completed" form 999
col wet head "Window|End|Time" form a20
select con_id, window_name, to_char(window_start_time, 'DD-MON-YYYY HH24:MI') wst,
extract(hour from window_duration) + round(extract(minute from window_duration)/60) window_duration,
jobs_created, jobs_started, jobs_completed,
to_char(window_end_time, 'DD-MON HH24:MI') wet
from cdb_autotask_client_history
where client_name = 'auto optimizer stats collection'
--and con_id = 3
order by window_start_time, con_id
/
col OPERATION form a40
col job_name form a25
col st form a20
col max_duration format 9999
col status form a15
col TARGET for a20
select id, operation, JOB_NAME, target, to_char(start_time, 'DD-MON-YYYY HH24:MI') st,
extract(hour from (end_time - start_time)) * 60 + extract(minute from (end_time - start_time)) max_duration,
status
from dba_optstat_operations
where operation = 'gather_database_stats (auto)'
order by start_time
/
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
set linesize 300
col PROGRAM for a50
col MACHINE for a30
SELECT sid, serial#,con_id,MACHINE ,PROGRAM FROM SYS.V_$SESSION WHERE SID = (SELECT DISTINCT SID FROM SYS.V_$MYSTAT);
set pagesize 0
select 'alter system kill session '''||sid||','||serial#||''' IMMEDIATE;' FROM SYS.V_$SESSION
WHERE SID = (SELECT DISTINCT SID FROM SYS.V_$MYSTAT);
set pagesize 80
set time on timing on
BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');
DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
DBMS_STATS.GATHER_DICTIONARY_STATS ();
END;
/
alter session set container=ANUJ;
prompt 'Stats on Pluggable'
BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');
DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
DBMS_STATS.GATHER_DICTIONARY_STATS ();
END;
/
spool off
nohup sqlplus '/ as sysdba' @sysstats.sql > sysstats_`date '+%d%m%y_%H%M%S'`.log 2>&1 &
set linesize 300 pagesize 300
col con_id head "Con|tai|ner" form 999
col window_name head "window" form a16
col wst head "window|start|time" form a12
col window_duration head "window|dura|tion|hours" form 999999
col job_name head "job name" form a22
col jst head "job|start|time" form a12
col job_duration head "job|dura|tion|mins" form 999999
col job_status head "job|status" form a10
col job_error head "job error" form 99
col job_info head "job info" form a40
select con_id, window_name, to_char(window_start_time, 'DD-MON HH24:MI') wst,
extract(hour from window_duration) + round(extract(minute from window_duration)/60) window_duration,
job_name, to_char(job_start_time, 'DD-MON HH24:MI') jst, job_status,
extract(hour from job_duration)*60 + round(extract(minute from job_duration)) job_duration,
job_error, job_info
from cdb_autotask_job_history
where 1=1
--and client_name = 'auto optimizer stats collection'
and client_name like '%auto optimizer%'
order by job_start_time, con_id
/
col con_id head "Con|tai|ner" form 999
col id head "Opera|tion|ID" form 9999999
col operation head "Operation" form a30
col job_name head "job name" form a22
col target head "Target" form a10
col jst head "Operation|start|time" form a12
col duration head "Operation|dura|tion|mins" form 999999
col status head "Operation|status" form a10
select con_id, id, operation, job_name, target, to_char(start_time, 'DD-MON HH24:MI') jst,
extract(hour from (end_time - start_time))*60 + extract(minute from (end_time - start_time)) duration,
status
from cdb_optstat_operations
where operation = 'gather_database_stats (auto)'
order by start_time, con_id
/
col con_id head "Con|tai|ner" form 999
col id head "Opera|tion|ID" form 9999999
col operation head "Operation" form a30
col job_name head "job name" form a35
col target head "Target" form a55
col jst head "Operation|start|time" form a12
col duration head "Operation|dura|tion|mins" form 999999
col status head "Operation|status" form a10
select con_id, id, operation, job_name, target, to_char(start_time, 'DD-MON HH24:MI') jst,
extract(hour from (end_time - start_time))*60 + extract(minute from (end_time - start_time)) duration,
status
from cdb_optstat_operations
where 1=1
--and operation = 'gather_database_stats (auto)'
and operation like '%gather_table_stats%'
order by start_time, con_id
/
col con_id head "Con|tai|ner" form 999
col window_name head "Window" form a16
col wst head "Window|Start|Time" form a12
col window_duration head "Window|Duration|Hours" form 999999
col jobs_created head "Jobs|Created" form 999
col jobs_started head "Jobs|Started" form 999
col jobs_completed head "Jobs|Completed" form 999
col wet head "Window|End|Time" form a12
select con_id, window_name, to_char(window_start_time, 'DD-MON HH24:MI') wst,
extract(hour from window_duration) + round(extract(minute from window_duration)/60) window_duration,
jobs_created, jobs_started, jobs_completed,
to_char(window_end_time, 'DD-MON HH24:MI') wet
from cdb_autotask_client_history
where client_name = 'auto optimizer stats collection'
and con_id = 3
order by window_start_time, con_id
/
col con_id head "Con|tai|ner" form 999
col jst head "Operation|start|time" form a12
col target head "Target" form a60
col target_type head "Target Type" form a15
col status head "Operation|status" form a10
col duration head "Dura|tion|mins" form 999
select con_id, to_char(start_time, 'DD-MON HH24:mi') jst,
target, target_type, status,
extract(hour from (end_time - start_time))*60 + extract(minute from (end_time - start_time)) duration
from cdb_optstat_operation_tasks
where opid=585868
order by start_time, con_id
/
=====================================
Automatic Statistics Gathering does not Complete - Diagnostics Interpretation Guidelines (Doc ID 1902112.1)
exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
set pagesize 9999
spool dba_autotask_client.html
set markup html on
select * from DBA_AUTOTASK_CLIENT;
select * from DBA_AUTOTASK_CLIENT_HISTORY;
select * from DBA_AUTOTASK_CLIENT_JOB;
select * from DBA_AUTOTASK_JOB_HISTORY order by JOB_START_TIME;
select * from DBA_AUTOTASK_OPERATION;
select * from DBA_AUTOTASK_SCHEDULE order by START_TIME;
select * from DBA_AUTOTASK_TASK;
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
select * from DBA_AUTOTASK_WINDOW_HISTORY order by WINDOW_START_TIME;
select * from dba_scheduler_windows;
select * from dba_scheduler_window_groups;
select * from dba_scheduler_job_run_details order by ACTUAL_START_DATE;
select * from DBA_SCHEDULER_JOB_LOG;
SELECT program_name, program_action, enabled FROM dba_scheduler_programs;
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
set pagesize 9999
spool dba_autotask_client.html
set markup html on
select * from DBA_AUTOTASK_CLIENT;
select * from DBA_AUTOTASK_CLIENT_HISTORY;
select * from DBA_AUTOTASK_CLIENT_JOB;
select * from DBA_AUTOTASK_JOB_HISTORY order by JOB_START_TIME;
select * from DBA_AUTOTASK_OPERATION;
select * from DBA_AUTOTASK_SCHEDULE order by START_TIME;
select * from DBA_AUTOTASK_TASK;
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
select * from DBA_AUTOTASK_WINDOW_HISTORY order by WINDOW_START_TIME;
select * from dba_scheduler_windows;
select * from dba_scheduler_window_groups;
select * from dba_scheduler_job_run_details order by ACTUAL_START_DATE;
select * from DBA_SCHEDULER_JOB_LOG;
SELECT program_name, program_action, enabled FROM dba_scheduler_programs;
spool off
Following command can be used to get the report:-
SET LINES 200 PAGES 0 LONG 100000
COLUMN REPORT FORMAT A200
VARIABLE my_report CLOB;
BEGIN
:my_report := DBMS_STATS.REPORT_STATS_OPERATIONS (
since => SYSDATE-1
, until => SYSDATE
, detail_level => 'TYPICAL'
, format => 'HTML'
, auto_only => TRUE
);
END;
/
spool stats_output.html
print my_report;
spool off;
to generate the status of all objects touched by AUTO statistics job:
SET LINES 200 PAGES 0
SET LONG 100000
COLUMN REPORT FORMAT A200
VARIABLE my_rep CLOB;
BEGIN
:my_rep :=DBMS_STATS.REPORT_SINGLE_STATS_OPERATION (OPID =>444, FORMAT => 'HTML');
END;
/
PL/SQL procedure successfully completed.
SQL> spool stats_task_output.html
SQL> print my_rep;
SQL> spool off;
#
for trace
SQL> conn / as sysdba
SQL> alter session set tracefile_identifier = '10046_auto_stats';
SQL> alter session SET MAX_DUMP_FILE_SIZE = UNLIMITED;
SQL> exec DBMS_STATS.SET_GLOBAL_PREFS('trace', 2+4+8+16+64+512+1024+2048 /* 3678 */);
SQL>exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS; -- Run auto stats job manually.
-- Once Job completes disable Trace
SQL> exec DBMS_STATS.SET_GLOBAL_PREFS('trace',0);
In Oracle Null=null ? ---- False
In Oracle Null is not equal to null :) !!!!!!!!!!
select case when null = null
then 1 ----true
else 2 ----flase
end
from dual ;
CASEWHENNULL=NULLTHEN1----TRUEELSE2----FLASEEND
-----------------------------------------------
2 ---- so false
so you can not equate the null with null ( null=null) --<<--- false
select decode(null,null, 1, 2) from dual; --<<<<--- in decode
DECODE(NULL,NULL,1,2)
---------------------
1 <<<---- true
select case when null = null
then 1
else 2
end "Case",
decode(null,null, 1, 2) "Decode" from dual
SQL> /
Case Decode
---------- ----------
2 1
SELECT * FROM dual WHERE NULL = NULL;
No rows selected.
SELECT * FROM dual WHERE NULL <> NULL;
No rows selected.
select case when null = null
then 1 ----true
else 2 ----flase
end
from dual ;
CASEWHENNULL=NULLTHEN1----TRUEELSE2----FLASEEND
-----------------------------------------------
2 ---- so false
so you can not equate the null with null ( null=null) --<<--- false
select decode(null,null, 1, 2) from dual; --<<<<--- in decode
DECODE(NULL,NULL,1,2)
---------------------
1 <<<---- true
select case when null = null
then 1
else 2
end "Case",
decode(null,null, 1, 2) "Decode" from dual
SQL> /
Case Decode
---------- ----------
2 1
SELECT * FROM dual WHERE NULL = NULL;
No rows selected.
SELECT * FROM dual WHERE NULL <> NULL;
No rows selected.
Thursday, 13 October 2011
Oracle Active session and session kill
logged on session
current session
current user
session kill
SET linesize 200 PAGESIZE 1000 FEED OFF;
COLUMN "sid,serial#" FORMAT A12
COLUMN "pid" FORMAT 999
COLUMN "status" FORMAT A8
COLUMN "schema" FORMAT A10
COLUMN "osuser" FORMAT A10
COLUMN "box" FORMAT A16
COLUMN "prg" FORMAT A30
--
SELECT
''''
|| s.sid
|| ','
|| s.serial#
|| ''';' "sid,serial#"
, p.spid "pid"
, s.status "status"
, s.schemaname "schema"
, s.osuser "osuser"
, s.machine "box"
, s.program "prg"
, TO_CHAR(s.logon_time, 'DD.MM.YYYY HH24:MI') "logon_time"
,sql_id
,PREV_SQL_ID
FROM
v$session s,
v$process p
WHERE s.paddr = p.addr
and s.serial#>10
ORDER BY s.username
/
prompt alter system kill session 'sid,serial#' IMMEDIATE;
prompt alter system kill session 'sid,serial#,@1'; RAC
current session
current user
session kill
SET linesize 200 PAGESIZE 1000 FEED OFF;
COLUMN "sid,serial#" FORMAT A12
COLUMN "pid" FORMAT 999
COLUMN "status" FORMAT A8
COLUMN "schema" FORMAT A10
COLUMN "osuser" FORMAT A10
COLUMN "box" FORMAT A16
COLUMN "prg" FORMAT A30
--
SELECT
''''
|| s.sid
|| ','
|| s.serial#
|| ''';' "sid,serial#"
, p.spid "pid"
, s.status "status"
, s.schemaname "schema"
, s.osuser "osuser"
, s.machine "box"
, s.program "prg"
, TO_CHAR(s.logon_time, 'DD.MM.YYYY HH24:MI') "logon_time"
,sql_id
,PREV_SQL_ID
FROM
v$session s,
v$process p
WHERE s.paddr = p.addr
and s.serial#>10
ORDER BY s.username
/
prompt alter system kill session 'sid,serial#' IMMEDIATE;
prompt alter system kill session 'sid,serial#,@1'; RAC
Subscribe to:
Posts (Atom)
