Gather stats example 2

To check tables and indexes last analyzed date and Database stats

set pages 200

col index_owner form a10
col table_owner form a10
col owner form a10

spool checkstat.lst

PROMPT Regular Tables

select owner,table_name,last_analyzed, global_stats
from dba_tables
where owner not in (‘SYS’,’SYSTEM’)
order by owner,table_name
/

PROMPT Partitioned Tables

select table_owner, table_name, partition_name, last_analyzed, global_stats
from dba_tab_partitions
where table_owner not in (‘SYS’,’SYSTEM’)
order by table_owner,table_name, partition_name
/

PROMPT Regular Indexes

select owner, index_name, last_analyzed, global_stats
from dba_indexes
where owner not in (‘SYS’,’SYSTEM’)
order by owner, index_name
/

PROMPT Partitioned Indexes

select index_owner, index_name, partition_name, last_analyzed, global_stats
from dba_ind_partitions
where index_owner not in (‘SYS’,’SYSTEM’)
order by index_owner, index_name, partition_name
/

spool off

To check last collected stats for database

a) Show the current stats history configuration:

select dbms_stats.get_stats_history_availability from dual;

b) Show the current history level:

select dbms_stats.get_stats_history_availability from dual;

c) Disable automatic purge ( -1 = statistics history never purged by autopurge):

exec dbms_stats.alter_stats_history_retention(-1);

d) Purge by hand running successively:

exec dbms_stats.purge_stats(sysdate-&days);

using &days = n, n-1, n-2, …, n-x

e) Show the new history level

select dbms_stats.get_stats_history_availability from dual;

This should show that the GET_STATS_HISTORY_AVAILABILITY is indeed equal to sysdate – (n-x).

After they are purged, set the desired retention.

It is recommended to set &days to purge little by little.

AWR load spikes

AWR query to find load spikes
select
to_char(round(sub1.sample_time, ‘HH24′), ‘YYYY-MM-DD HH24:MI’) as sample_hour,
round(avg(sub1.on_cpu),1) as cpu_avg,
round(avg(sub1.waiting),1) as wait_avg,
round(avg(sub1.active_sessions),1) as act_avg,
round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
( — sub1: one row per ASH/AWR sample observation
select
sample_id,
sample_time,
sum(decode(session_state, ‘ON CPU’, 1, 0))  as on_cpu,
sum(decode(session_state, ‘WAITING’, 1, 0)) as waiting,
count(*) as active_sessions
from
dba_hist_active_sess_history
where
sample_time > sysdate – (&hours/24)
group by
sample_id,
sample_time
) sub1
group by
round(sub1.sample_time, ‘HH24′)
order by
round(sub1.sample_time, ‘HH24′)
;

Gather stats database

set lines 100
col operation form a40 wrap head 'operation(on)'
col target form a1
spool show_auto_stat_runs.lst
select operation||decode(target,null,null,'-'||target) operation
      ,to_char(start_time,'YYMMDD HH24:MI:SS.FF4') start_time
      ,to_char(  end_time,'YYMMDD HH24:MI:SS.FF4') end_time
from dba_optstat_operations
order by start_time desc
/




EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_dictionary_stats;



set pages 50000 lines 32767
col owner format a15
col object_type format a20
col object_name format a30
select owner, object_name, object_type,status,created,last_ddl_time from dba_objects where object_name in ('&object_name') and owner='&owner' order by object_type, object_name desc
/


To Gathers statistics for all objects in a schema

SQL> select owner, table_name,last_analyzed from dba_tables where owner='SCOTT' order by last_analyzed;

SQL> select ocwner, index_name, last_analyzed from dba_indexes where owner='SCOTT' order by last_analyzed;

SQL> exec dbms_stats.gather_schema_stats(ownname => 'SCOTT', options => 'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL> select owner, table_name,last_analyzed from dba_tables where owner='SCOTT' order by last_analyzed;

SQL> select owner, index_name, last_analyzed from dba_indexes where owner='SCOTT' order by last_analyzed;


select client_name, status from dba_autotask_operation;

CLIENT_NAME                                      STATUS
---------------------------------------------- ------------
auto optimizer stats collection                ENABLED
auto space advisor                             ENABLED
sql tuning advisor                             ENABLED



missing index



select * from (
  select 'the column ' || c.name || ' of the table ' || us.name || '.' || o.name || ' was used ' || u.equality_preds || ' times in an equality predicate and ' || u.equijoin_preds || ' times in an equijoin predicate and is not indexed' as colum_to_index
  from sys.col_usage$ u,
       sys.obj$ o,
       sys.col$ c,
       sys.user$ us
  where u.obj# = o.obj#
  and   u.obj# = c.obj#
  and   us.user# = o.owner#
  and   u.intcol# = c.col#
  and   us.name='&SCHEMA_NAME'
  and   c.name not in (select column_name from dba_ind_columns where index_owner ='&SCHEMA_NAME')
  and   (u.equality_preds > 100 OR u.equijoin_preds > 100)
  order by u.equality_preds+u.equijoin_preds desc)
WHERE rownum <11;


To Gather statistics for tables in a schema

SQL>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname => 'EMP',estimate_percent=> 100,cascade => true);


SELECT * FROM dba_autotask_schedule;



select m.TABLE_OWNER,
 m.TABLE_NAME,
 m.INSERTS,
 m.UPDATES,
 m.DELETES,
 m.TRUNCATED,
 m.TIMESTAMP as LAST_MODIFIED,
 round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
 t.num_rows as last_known_rows_number,
 t.last_analyzed
From dba_tab_modifications m,
 dba_tables t
where m.table_owner=t.owner
and m.table_name=t.table_name
and table_owner not in ('SYS','SYSTEM')
and ((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
order by timestamp desc;

long running session

COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#;


***************************************************


SELECT l.sid, l.start_time, l.username, l.elapsed_seconds,
a.sql_text, a.elapsed_time
FROM v$session_longops l, v$sqlarea a
WHERE a.elapsed_time = l.elapsed_seconds
AND l.elapsed_seconds > 1;