Oracle Wait Events !!!!!

                           Oracle Wait Events !!!!!

######################## v$session:

select username, SQL_ID, SID, event , status from v$session where EVENT='cr request retry'

;

select event, count(*) from v$session group by event order by 2 ;

select sql_id, count(*) from v$session where EVENT='cr request retry' group by sql_id ;

select sum (wait_time+ SECONDS_IN_WAIT) , event from v$session  group by event order by 1 ;

select sum (SECONDS_IN_WAIT) , event from v$session  group by event order by 1 ;

select sum (wait_time) , event from v$session  group by event order by 1 ;

select p1,p2, event , sum(wait_time)  from v$session

group by p1,p2,event

order by sum(wait_time);

SELECT a.sid, c.pid, c.spid, a.username, b.event, b.wait_time, b.seconds_in_wait, b.p1, b.p2, b.p3

FROM v$session a, v$session_wait b, v$process c

WHERE a.sid = b.sid

AND a.paddr = c.addr

AND b.event LIKE 'enq: RO%';

######################## v$session_wait

SELECT         p1 "file#", p2 "block#", p3 "class#" FROM v$session_wait WHERE event = 'read by

other session';

with temp as (select sid,event,WAIT_TIME+SECONDS_IN_WAIT "TIME_WAITED" from v$session_wait order by 3 desc) select * from temp where rownum < 50;

column sid format 990

column seq# format 99990

column wait_time heading 'WTime' format 99990

column event format a30

column p1 format 9999999990

column p2 format 9999999990

column p3 format 9990

select sid,event,seq#,p1,p2,p3,wait_time

from V$session_wait

order by sid

/

######################## v$session_wait_history

--> gives last ten waits for all active sessions

select seq# , event , wait_time , p1,p2,p3

from v$session_wait_history where sid=988

order by seq# ;

###################### v$session_event

select event,count(*) from v$session_event where event='cr request retry' group by event ;

select event, sum( time_waited) from v$session_event group by event ;

select event, total_waits from v$session_event where event = 'enq: TM - contention';;

select event,sum(TOTAL_WAITS) "TOTAL_WAITS" , sum(TOTAL_TIMEOUTS) "TOTAL_TIMEOUTS" ,sum(TIME_WAITED) "TIME_WAITED",avg (AVERAGE_WAIT) "AVERAGE_WAIT" from v$session_event where event is not null and wait_class <>'Idle' group by event order by 4;

###################### v$system_event

with temp as (select EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,AVERAGE_WAIT from v$system_event where wait_class <> 'Idle' order by 4 desc ) select * from temp where rownum < 50;

select event, total_waits from v$system_event where event = 'enq: TM - contention';;

select event,count(*) from v$system_event where event='cr request retry' group by event ;

select event, sum( time_waited) from v$system_event group by event ;

/* session_events.sql */

column SID            format 9999

column TOTAL_WAITS    format 999999 heading TOT|WAIT

column AVERAGE_WAIT   format 99999  heading AVG|WAIT

column EVENT          format a30

select

   SID,

   EVENT,

   TOTAL_WAITS,

   TIME_WAITED,

   AVERAGE_WAIT,

   MAX_WAIT

from

   V$SESSION_EVENT

where

   SID in ("&sid_list") -- sid list generated from above query

order by

   TOTAL_WAITS;

##################### V$statname

select a.name,sum(b.value)

from V$statname a, v$sesstat b

where a.statistic#=b.statistic#

group by a.name

order by 2;

select a.name,sum(b.value)

from V$statname a, v$sysstat b

where a.statistic#=b.statistic#

group by a.name

order by 2;

############################# v$enqueue_statistics

select eq_name, eq_type, req_reason  from v$enqueue_statistics  3  where eq_type = 'TM';

with temp as (select EQ_NAME,TOTAL_REQ#,TOTAL_WAIT#,SUCC_REQ#,FAILED_REQ# from v$enqueue_statistics order by 3 desc ) select * from temp where rownum <50;

########################### v$event_histogram

Select * from v$event_histogram

select * from  gV$EVENT_HISTOGRAM  ;

####################### v$active_session_history

SELECT sample_time, event, wait_time

FROM gv$active_session_history

WHERE session_id = 147

AND session_serial# = 1715;

select

       o.object_name obj,

       o.object_type otype,

       ash.SQL_ID,

       w.class

from v$active_session_history ash,

     ( select rownum class#, class from v$waitstat ) w,

      all_objects o

where event='buffer busy waits'

   and w.class#(+)=ash.p3

   and o.object_id (+)= ash.CURRENT_OBJ#

Order by sample_time;

select event, count(*) from v$active_session_history  group by event ;

with temp as (select session_id,event,(time_waited+wait_time) "TOTAL_TIME_WAITED" from v$active_session_history where event is not null order by time_waited+wait_time desc)

select * from temp where rownum <50;

select event ,sum(wait_time+time_waited) from v$active_session_history

##where snap_id=8987

and event='cr request retry'

group by event

order by 2 ;

select sum (wait_time+time_waited) , event from v$active_session_history   group by event

order by 1 ;

select sum (wait_time) , event from v$active_session_history   group by event order by 1 ;

select sum (time_waited) , event from v$active_session_history  

where sample_time between sysdate - 15/1440 and sysdate ;

group by event order by 1 ;

############################## dba_hist_active_sess_history

select sql_id ,sum(wait_time+time_waited) from dba_hist_active_sess_history where

snap_id=8987

and event='cr request retry' group by sql_id order by 2 ;

select event ,sum(wait_time+time_waited) from dba_hist_active_sess_history

##where snap_id=8987

and event='cr request retry'

group by event

order by 2 ;

with temp as (select session_id,event,(time_waited+wait_time) "TOTAL_TIME_WAITED" from

dba_hist_active_sess_history where event is not null order by time_waited+wait_time desc)

select * from temp where rownum <50;

####################### session wait on object

SELECT

  A.CURRENT_OBJ#,

  d.OBJECT_NAME,

  d.OBJECT_TYPE,

  A.EVENT,

  SUM(A.WAIT_TIME + A.TIME_WAITED) TOTAL_WAIT_TIME

FROM

  V$ACTIVE_SESSION_HISTORY A,

  DBA_OBJECTS D

#WHERE   A.SAMPLE_TIME BETWEEN SYSDATE – 30/2880 AND SYSDATE

 where A.CURRENT_OBJ# = D.OBJECT_ID

GROUP BY

  A.CURRENT_OBJ#,

  D.OBJECT_NAME,

  D.OBJECT_TYPE,

  A.EVENT

ORDER BY

  TOTAL_WAIT_TIME;

select * from (

SELECT

  A.inst_id,

  A.CURRENT_OBJ#,

  D.OBJECT_NAME,

  D.OBJECT_TYPE,

  d.owner,

  A.EVENT,

  a.sql_id,

  SUM(A.WAIT_TIME + A.TIME_WAITED) TOTAL_WAIT_TIME

FROM

  gV$ACTIVE_SESSION_HISTORY A,

  DBA_OBJECTS D

WHERE

  A.CURRENT_OBJ# = D.OBJECT_ID

GROUP BY

  A.inst_id,

  A.CURRENT_OBJ#,

  D.OBJECT_NAME,

  D.OBJECT_TYPE,

  A.EVENT,

 d.owner,

 a.sql_id

ORDER BY

  TOTAL_WAIT_TIME

)

where rownum<=20

/

###################### object having wait event

col object_name for a25

col statistic_name for a35

col owner for a15

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='db block changes' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='segment scans' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='row lock waits' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='buffer busy waits' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='physical reads direct' order

by 4 desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='physical reads' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='physical writes' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='physical writes direct' order

by 4 desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='logical reads' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='ITL waits' order by 4 desc)

select * from temp where rownum < 30;

SELECT object_name, value

FROM V$SEGMENT_STATISTICS

WHERE statistic_name = 'buffer busy waits' AND

value > 20000;

SELECT a.current_obj#, o.object_name, o.object_type, a.event,

SUM(a.wait_time +

a.time_waited) total_wait_time

FROM v$active_session_history a,

dba_objects o

WHERE a.sample_time between sysdate – 30/2880 and sysdate

AND a.current_obj# = o.object_id

GROUP BY a.current_obj#, o.object_name, o.object_type, a.event

ORDER BY total_wait_time;

select p1text, p1, p2text, p2, p3text, p3, a.event

from v$active_session_history a

WHERE a.sample_time between sysdate – 30/2880 and sysdate

AND a.current_obj# = 1938000

####################### session wait on file

while (true)

do

sqlplus -s "/ as sysdba" <<EOF | tee -a pteh.out

set lines 200

set heading off;

set feedback off;

set echo off;

set pages 1000

column event format a30

column file_name format a55

select '=============================================' || to_char(sysdate,'DD-MON-YY

HH24:MI:SS') from dual;

select a.sid, a.event, b.tablespace_name,b.file_name,a.p1, a.p2, a.p3, a.p1raw from

v\$session_wait a, dba_data_files b

where a.p1=b.file_id and  a.sid in (select sid from v\$session where username='TEH')order

by 2;

EOF

sleep 15

done

SELECT * FROM DBA_EXTENTS WHERE FILE_ID=&FILEID AND &BLOCKID BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS

è check buffer busy wait:

column buffer_busy_wait format 999,999,999

select sn.snap_id,

to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,

avg(new.buffer_busy_wait-old.buffer_busy_wait) buffer_busy_wait

from

dba_hist_buffer_pool_stat old,

dba_hist_buffer_pool_stat new,

dba_hist_snapshot sn

where new.snap_id = sn.snap_id

and new.snap_id = sn.snap_id

and old.snap_id = sn.snap_id-1

having avg(new.buffer_busy_wait-old.buffer_busy_wait) > 100

group by to_char(sn.end_interval_time,'dd Mon HH24:mi:ss'), sn.snap_id;

select

obj.object_type,

obj.object_name,

to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,

avg(new.buffer_busy_wait-old.buffer_busy_wait) buffer_busy_wait

from

dba_hist_buffer_pool_stat old,

dba_hist_buffer_pool_stat new,

dba_hist_snapshot sn,

dba_hist_active_sess_history ash,

dba_data_files df,

dba_objects obj

where new.snap_id = sn.snap_id

and new.snap_id = sn.snap_id

and old.snap_id = sn.snap_id-1

and df.file_id = ash.current_file#

and obj.object_id = ash.current_obj#

and ash.snap_id = sn.snap_id

and sn.snap_id = 15882

having avg(new.buffer_busy_wait-old.buffer_busy_wait) > 100

group by to_char(sn.end_interval_time,'dd Mon HH24:mi:ss'), obj.object_type,

select BUFFER_BUSY_WAITS_TOTAL,

BUFFER_BUSY_WAITS_DELTA,

obj.object_type,

obj.object_name,

to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,

avg(new.buffer_busy_wait-old.buffer_busy_wait) buffer_busy_wait

from

dba_hist_buffer_pool_stat old,

dba_hist_buffer_pool_stat new,

dba_hist_snapshot sn,

dba_hist_active_sess_history ash,

dba_data_files df,

dba_objects obj,

DBA_HIST_SEG_STAT dss

where new.snap_id = sn.snap_id

and new.snap_id = sn.snap_id

and old.snap_id = sn.snap_id-1

and df.file_id = ash.current_file#

and obj.object_id = ash.current_obj#

and dss.snap_id = sn.snap_id

and dss.OBJ# = obj.object_id

and ash.snap_id = sn.snap_id

and sn.snap_id = 15882

having avg(new.buffer_busy_wait-old.buffer_busy_wait) > 100

group by

to_char(sn.end_interval_time,'dd Mon HH24:mi:ss'), obj.object_type, obj.object_name, BUFFER_BUSY_WAITS_TOTAL, BUFFER_BUSY_WAITS_DELTA;

è RAC waits :

######### node performance comparision ######

WITH sysstats AS (

SELECT inst_id,

 SUM(CASE WHEN name LIKE 'gc%received'

THEN VALUE END) gc_blocks_recieved,

 SUM(CASE WHEN name = 'session logical reads'

 THEN VALUE END) logical_reads,

SUM(CASE WHEN name = 'physical reads'

THEN VALUE END) physical_reads

 FROM gv$sysstat

 GROUP BY inst_id)

SELECT instance_name, logical_reads, gc_blocks_recieved, physical_reads,

ROUND(physical_reads*100/logical_reads,2) phys_to_logical_pct,

 ROUND(gc_blocks_recieved*100/logical_reads,2) gc_to_logical_pct

 FROM sysstats JOIN gv$instance

USING (inst_id);

#########  rac top wait event ######

 WITH sys_time AS (

SELECT inst_id, SUM(CASE stat_name WHEN 'DB time'

THEN VALUE END) db_time,

SUM(CASE WHEN stat_name IN ('DB CPU', 'background cpu time')

THEN VALUE END) cpu_time

FROM gv$sys_time_model

GROUP BY inst_id )

SELECT instance_name,

ROUND(db_time/1000000,2) db_time_secs,

 ROUND(db_time*100/SUM(db_time) over(),2) db_time_pct,

 ROUND(cpu_time/1000000,2) cpu_time_secs,

 ROUND(cpu_time*100/SUM(cpu_time) over(),2) cpu_time_pct

 FROM sys_time

 JOIN gv$instance USING (inst_id);

World of Oracle statistics

                          World of Oracle statistics 

è Difference between analyze and dbms_stats

The difference is described in the Note:114671.1

-- Analyze cannot process virtual columns

-- ANALYZE can't retain old staticstics, dbms_stats can.

-- analyze doesn't collect global statistics for partitioned tables

-- use ANALYZE to LIST CHAINED ROWS

-- DBMS_STATS is able to parallelize its workload

-- DBMS_STATS allows you transfer stats between servers

è stats history :

select * from DBA_OPTSTAT_OPERATIONS  where lower(operation) like '%system%'

select * from DBA_OPTSTAT_OPERATIONS where START_TIME like '%MAR-12%'

AND TARGET IN ( 'ADVCLM.TRX_BATCH','ADVCLM.TRX','ADVCLM.TRX_BATCH_HANDLER','ADVCLM.TRX_BATCH_GROUP','ADVCLM.IXML_STATUS','ADVCLM.IXML_CLAIM')

 order by START_TIME ;

connect / as sysdba

pause see the opstat operations

col start_time for a32

col end_time for a32

col operation for a30

col target for a10

select *

from  dba_optstat_operations;

pause see stats history

col owner for a14

col table_name for a16

col partition_name for a10

col subpartition_name for a10

break on owner skip 1

select *

from dba_tab_stats_history;

clear break

############

DBA_OPTSTAT_OPERATIONS  --> see all gather stats run using dbms_stats

select * from DBA_OPTSTAT_OPERATIONS  where lower(operation) like '%system%'

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

                         31

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY

---------------------------------------------------------------------------

26-NOV-10 10.23.55.988228000 PM +04:00

SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where occupant_name=’SM/OPTSTAT’;

OCCUPANT_DESC                                                    SPACE_USAGE_KBYTES

---------------------------------------------------------------- ------------------

Server Manageability - Optimizer Statistics History                          192576

SQL> exec dbms_stats.alter_stats_history_retention(40);

PL/SQL procedure successfully completed.

DBMS_STATS.GET_STATS_HISTORY_RETENTION

 RETURN NUMBER;

DBMS_STATS.FLUSH_SCHEMA_MONITORING_INFO (

   ownname VARCHAR2 DEFAULT NULL);

DBMS STATS.ALTER STATS HISTORY RETENTION

This procedure changes the statistics history retention value. Statistics history retention is used by both the automatic purge and PURGE_STATS Procedure.

Syntax

DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (

   retention       IN     NUMBER);

Parameter        Description

retention         

The retention time in days. The statistics history will be retained for at least these many number of days.The valid range is [1,365000]. Also you can use the following values for special purposes:

0 - old statistics are never saved. The automatic purge will delete all statistics history

1 - statistics history is never purged by automatic purge.

NULL - change statistics history retention to default value

Usage Notes

To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.

Table Stats History

 -- All stats history for user objects

 -- Sort: most recent at top

 SELECT do.owner,

        do.object_name,

        oth.*

   FROM SYS.WRI$_OPTSTAT_TAB_HISTORY oth join dba_objects do on oth.obj#=do.object_id

  WHERE OWNER NOT IN ('SYS','SYSTEM')

  ORDER BY savtime desc;

-- Get historical Statistics info for 1+ Tables

WITH TableList as (select object_id, object_name

               from dba_objects

              where object_type='TABLE'

                and object_name IN ('TABLE_1','TABLE_2',...))

select OBJECT_NAME as TABLE_NAME,

       TO_CHAR(ANALYZETIME,'MM/DD/YYYY HH24:MI') as AnalyzeTime_Formatted,

       OTH.*

  From SYS.WRI$_OPTSTAT_TAB_HISTORY OTH, TableList

 where OTH.OBJ# = TableList.OBJECT_ID

 ORDER BY table_name, analyzetime;

è default database statistics collection using  statistics_level:

Ø  statistics_level :

Ÿ default :

No advisory stats data is collect . we can set external parameter like time_statistics , db_cache_advice , etc.

Ÿ typical  :

This is default value . data is collectd for segment-level statistics , timesd statistics and all advisories

The value of all other statistics collection parameter are overridden .

Ÿ all :

All typical level statistics data , timed operation system statistics , row source execution stats are collected . value for other stats collection parameter are overridden ,

Select statistics_name, activation_level from v$statistics_level order by 2 ;

conn / as sysdba

pause

col activation_level for a10

col statistics_name for a38

col statistics_view_name for a30

desc v$statistics_level

pause

select activation_level, statistics_name, statistics_view_name,

session_status,session_settable,system_status

from v$statistics_level

order by 1;

EXEC DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');

exec dbms_stats.set_param('METHOD_OPT','FOR ALL INDEXED COLUMNS SIZE 254');

SELECT dbms_stats.get_param('METHOD_OPT') FROM dual;

exit

END

è table stats :

EXEC DBMS_STATS.GATHER_TABLE_STATS( OWNNAME=>'&OWNER',TABNAME=>'&TAB', partname => '&PART',DEGREE=>24, ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'FOR ALL COLUMNS SIZE REPEAT',NO_INVALIDATE=>FALSE);

execute dbms_stats.gather_table_stats(ownname => 'RELODS', tabname =>'WEBTOKEN', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO')

dbms_stats.gather_table_stats(ownname=>'VISION',tabname=>'PATIENT_PAYMENT',partname=>null,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,block_sample=>false,method_opt=>'for all columns size 1',

degree=>1,granularity=>'DEFAULT',cascade=>true,stattab=>null,statid=>null,statown=>null,no_invalidate=>false);

set pagesize 0

set heading off

set feedback off

spool c:\temp\alyz.sql

select

'ANALYZE TABLE '||OWNER||'.'||TABLE_NAME||' ESTIMATE STATISTICS;'

from dba_tables

--where owner not in ('SYS', 'SYSTEM')

where owner = 'BMR'

order by owner;

spool off

set feedback on

set heading on

@@ C:\temp\alyz

@@ d:\boban\scr\validate.sql

set pages 140

set lines 180

select 'exec dbms_stats.gather_table_stats('''||TABLE_OWNER||''','''||TABLE_NAME||''',method_opt=>''for columns status size 75'',cascade=>true)'

from dba_ind_columns where column_name='STATUS' and column_position=1

/

select 'exec dbms_stats.gather_table_stats(ownname=>'||chr(39)||OWNER||chr(39)||',tabname=>'||chr(39)||table_name||chr(39)||',estimate_percent=>'||100||',degree=>'||4||',cascade=>TRUE );'

from dba_tables where owner=UPPER ('AMS');

select 'analyze table ''||owner||''.''|table_name||'' compute statistics;' from dba_tables where owner='HR';

analyze table abcd estimate statistics sample 5 percentage for all indexed columns ;

analyze index  abcd estimate statistics sample 5 percentage fro all indexed columns ;

analyze table emp compute statistics for all indexed columns ;

analyze table abcd delete statistics ;

è 11g set stats preference:

exec dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT','13');

SELECT dbms_stats.get_prefs('STALE_PERCENT','HAFEEZ','SECURE')FROM dual;

exec dbms_stats.set_table_prefs('SH','CUSTOMERS','PUBLISH','false');

select * from DBA_TAB_STAT_PREFS ;

PREFERENCES ::

ESTIMATE_PERCENT

NO_INVALIDATE

METHOD_OPT

GRANULARITY

INCREMENTAL

PUBLISH

STALE_PERCENT

scope :

DATABASE LEVEL

SCHEMA LEVEL

TABLE LEVEL

STATEMENT LEVEL

GLOBAL LEVEL

Below can be used to set global preference 

SQL> column sname format a32
SQL> column spare4 format a32
SQL> select sname,spare4 from OPTSTAT_HIST_CONTROL$;

SNAME                 SPARE4
-------------------------------- --------------------------------
SKIP_TIME
STATS_RETENTION
TRACE                 0
DEBUG                 0
SYS_FLAGS             1
APPROXIMATE_NDV          TRUE
CASCADE              DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT         DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE                 2
METHOD_OPT             FOR TABLE FOR ALL INDEXED COLUMN
                 S SIZE 254

NO_INVALIDATE             DBMS_STATS.AUTO_INVALIDATE
GRANULARITY             AUTO
PUBLISH              TRUE
STALE_PERCENT             10
INCREMENTAL             FALSE
INCREMENTAL_INTERNAL_CONTROL     TRUE
AUTOSTATS_TARGET         AUTO
CONCURRENT             TRUE

18 rows selected

select dbms_stats.get_prefs('method_opt') a from dual;

exec dbms_stats.set_global_prefs('method_opt','FOR ALL COLUMNS SIZE 1')

select dbms_stats.get_prefs('method_opt') a from dual;

è 11g pending stats :

SQL> SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;

SQL> show parameter pending

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SAPSR3','MYTEST');

SQL> select TABLE_NAME, BLOCKS FROM DBA_TAB_STATISTICS where table_name = 'MYTEST';

SQL> select INDEX_NAME, LEAF_BLOCKS from DBA_IND_STATISTICS where table_name = 'MYTEST';

SQL> select TABLE_NAME, BLOCKS FROM DBA_TAB_PENDING_STATS where table_name = 'MYTEST';

SQL> select INDEX_NAME, LEAF_BLOCKS from DBA_IND_PENDING_STATS where table_name = 'MYTEST';

SQL> exec dbms_stats.set_table_prefs('SAPSR3','MYTEST','PUBLISH', 'false');

SQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'SALES' ) FROM DUAL;

SQL> exec DBMS_STATS.SET_TABLE_STATS('SAPSR3','MYTEST',numblks=>'5000');

SQL> exec DBMS_STATS.SET_INDEX_STATS('SAPSR3','MYTEST_I',numlblks=> '5000');

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');

SQL> select TABLE_NAME, BLOCKS FROM DBA_TAB_STATISTICS where table_name = 'MYTEST';

SQL> select INDEX_NAME, LEAF_BLOCKS from DBA_IND_STATISTICS where table_name = 'MYTEST';

SQL> select TABLE_NAME, BLOCKS FROM DBA_TAB_PENDING_STATS where table_name = 'MYTEST';

SQL> select INDEX_NAME, LEAF_BLOCKS from DBA_IND_PENDING_STATS where table_name = 'MYTEST';

alter session set optimizer_use_pending_statistics = true;

Execute/ test / check plan   your workload from the same session.

exec dbms_stats.publish_pending_stats('SH','CUSTOMERS');

SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='SALES';

SELECT COUNT(*) FROM DBA_TAB_PENDING_STATS;

alter session set optimizer_use_pending_statistics=FALSE;

SQL> exec dbms_stats.delete_pending_stats('ARUP','RES');

SQL> select TABLE_NAME, BLOCKS FROM DBA_TAB_STATISTICS where table_name = 'MYTEST';

SQL> select INDEX_NAME, LEAF_BLOCKS from DBA_IND_STATISTICS where table_name = 'MYTEST';

SQL> select TABLE_NAME, BLOCKS FROM DBA_TAB_PENDING_STATS where table_name = 'MYTEST';

SQL>  select INDEX_NAME, LEAF_BLOCKS from DBA_IND_PENDING_STATS where table_name = 'MYTEST';

Note: To analyze the differences between the pending statistics and the current ones, you could

export the pending statistics to your own statistics table and then use the new

DBMS_STAT.DIFF_TABLE_STATS function.

è compare stats :

1)

dbms_stat.diff_table_stats_in_stattab:  Here is an example of the diff_table_stats procedure for statistics that have been saved into a table:

set longchunksize 99999;

select * from table(

   dbms_stat.diff_table_stats_in_stattab( 'SCOTT', 'EMP', 'STAT_TAB_OLD', 'STAT_TAB_NEW'));

2)

dbms_stat.diff_table_stats_in_history:  Here is an example of the diff_table_stats procedure for statistics that have been collected at a specific data in history:

set longchunksize 99999;

select *

from table(

   dbms_stats.diff_table_stats_in_history(

      ownname => 'SCOTT',

      tabname => 'EMP',

      time1 => systimestamp,

      time2 => to_timestamp(to_date('2012-04-12','yyyy-mm-dd')),

      pctthreshold => 10));

3)

dbms_stat.diff_table_stats_in_pending:   Here is an example of the diff_table_stats procedure for statistics that are current (or older with the time_stamp argument), compared with statistics that are in a pending state:

set longchunksize 99999;

select *

from table(

   dbms_stats.diff_table_stats_in_pending(

      ownname => 'SCOTT',

      tabname => 'EMP',

      time_stamp => NULL,

      pctthreshold => 10));

Note that the pctthreshold parameter is used to filter-out report data, showing only data that exceeds the threshold value in change, as a percentage.  For example, pctthreshold=10 will only report on statistics that are greater then 10% changed from the baseline statistics.

Compare the current object statistics with the previous ones.

SQL> SELECT *

  2  FROM table(dbms_stats.diff_table_stats_in_history(

  3               ownname => user,

  4               tabname => 't',

  5               time1 => localtimestamp,

  6               time2 => localtimestamp-to_dsinterval('0 00:00:15'),

  7               pctthreshold => 0

  8             ));

STATISTICS DIFFERENCE REPORT FOR:

.................................

TABLE         : T

OWNER         : OPS$CHA

SOURCE A      : Statistics as of 21-SEP-08 06.39.37.597595 PM +02:00

SOURCE B      : Statistics as of 21-SEP-08 06.39.22.597595 PM +02:00

PCTTHRESHOLD  : 0

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:

.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE

...............................................................................

T                           T   A   1500000    40091      183        500000

                                B   500000     8614       116        500000

T_FEB_2008                  P   A   1000000    31477      216        1000000

                                B   NO_STATS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:

.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ

...............................................................................

D               A   31      .032258064 NO   0       8    786C0 786C0 1500000

                B   31      .032258064 NO   0       8    786C0 786C0 500000

ID              A   500000  .000002    NO   0       6    C4020 C404  1500000

                B   500000  .000002    NO   0       6    C4020 C4023 500000

N               A   11      .000001998 YES  749500  2    80    C10C  750500

                B   11      .000001998 YES  249750  2    80    C10B  250250

PAD             A   1       1          NO   0       168  2A2A2 2A2A2 1500000

                B   1       1          NO   0       101  2A2A2 2A2A2 500000

                              PARTITION: T_FEB_2008

                              .....................

D               A   29      .034482758 NO   0       8    786C0 786C0 1000000

                B   NO_STATS

ID              A   1000000 .000001    NO   0       6    C4030 C404  1000000

                B   NO_STATS

N               A   12      .000000999 YES  499750  2    80    C10C  500250

                B   NO_STATS

PAD             A   1       1          NO   0       201  2A2A2 2A2A2 1000000

                B   NO_STATS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:

.............................................

OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ

...............................................................................

                                    INDEX: I

                                    ........

I               I   A   750500  1221    12      101   3332  39991   2   750500

                    B   250250  407     11      37    778   8565    1   250250

T_FEB_2008      P   A   500250  814     12      67    2618  31426   2   500250

                    B   NO_STATS

è stale statistics :

-->  statistics are consider to be stale when there is a 10% change

--> Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use     the DBMS_STATS. FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept     in the memory.

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

exec dbms_stats.flush_database_monitoring_info;

select * from sys.dba_tab_modifications where table_name = 'T_STAT';

select stale_stats from sys.dba_tab_statistics where table_name = 'T_STAT';

SELECT t.table_name,

           last_analyzed,

           SUM(inserts),

           SUM(updates),

           SUM(deletes)

      FROM user_tables t,

           user_tab_modifications m

     WHERE t.table_name = m.table_name

       AND timestamp > last_analyzed

       AND t.table_name = <your table name>

  GROUP BY t.table_name,

           last_analyzed

  /

è lock table and partition  stats :

exec dbms_stats.lock_partition_stats('OKC997','LOCATION_DETAILS','M201202');

lock/unlock stats :

begin

                DBMS_STATS.unlock_table_stats('INFO10','JRL_AG_DIAGRAMME');

                DBMS_STATS.GATHER_TABLE_STATS(

                OwnName        => 'INFO10'

,TabName        => 'JRL_AG_DIAGRAMME'

,Estimate_Percent  => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE

,Degree            => NULL

,Cascade           => TRUE

,No_Invalidate     => FALSE);

                DBMS_STATS.lock_table_stats('INFO10','JRL_AG_DIAGRAMME');

END;

/

exec dbms_stats.lock_partition_stats('OKC997','LOCATION_DETAILS','M201202');

è copy partition stats

exec dbms_stats.copy_table_stats(ownname => 'OKC997',tabname =>'ISA_DETAILS',srcpartname =>'M201201',dstpartname =>'M201202');

exec dbms_stats.copy_table_stats(ownname => 'OKC997',tabname =>'ISA_DETAILS',srcpartname =>'M201201',dstpartname =>'M201202');

è set table stats :

exec dbms_stats.set_table_stats( user, 'MAP', numrows => 100000, numblks => 10000 );

SQL> exec DBMS_STATS.SET_TABLE_STATS('SAPSR3','MYTEST',numblks=>'5000');

SQL> exec DBMS_STATS.SET_INDEX_STATS('SAPSR3','MYTEST_I',numlblks=> '5000');

SQL> exec dbms_stats.set_table_stats('ADAM','SALES',numrows=>100,numblks=>1)

SQL> select num_rows,blocks from user_tables;

è gather partition stats :

exec DBMS_STATS.GATHER_TABLE_STATS('CLM','CLM_FIXED_FORMAT_INFO',estimate_percent=>10,cascade=>TRUE,degree=>12, granularity=>'PARTITION',method_opt=>'for all columns size 1',  partname=>'P201201');

exec DBMS_STATS.GATHER_TABLE_STATS('CLM','CLM_HOME_HEALTH',estimate_percent=>10,cascade=>TRUE,degree=>12, granularity=>'PARTITION',method_opt=>'for all columns size 1',  partname=>'P201201');

exec DBMS_STATS.GATHER_TABLE_STATS('CLM','AFILE',estimate_percent=>10,cascade=>TRUE,degree=>12, granularity=>'PARTITION',method_opt=>'for all columns size 1',  partname=>'P201112' );             

è gather index stats :

Execute DBMS_STATS.GATHER_INDEX_STATS (‘HR’,’LOC_COUNTRY_IX’);

Alter index hr.loc_country_ix rebuild compute statistics;

execute dbms_stats.gather_index_stats(ownname=>user, indname=>'i', granularity=>'global')

dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||'.....');

dbms_stats.gather_index_stats('SHEMA1', ind.object_name , estimate_percent=>100);

dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||' is Complete!');

end loop;

end;

/

è table stats backup

select 'exec dbms_stats.create_stat_table(ownname => ' || '''PAYVAR''' || ', STATTAB  =>' || '''PAYVAR_'  || to_char(sysdate,'dd_MON_yyyy') ||  ''');' from dual;

EXEC DBMS_STATS.EXPORT_TABLE_STATS(ownname =>'RDB',tabname => 'D_PAYER', stattab => 'STAT_TAB',cascade => true,statown=>'RDB',statid=>'Feb18');

Statid : use the current date for easy identification

select 'exec dbms_stats.export_schema_stats(ownname => ' || '''PAYVAR''' || ', STATTAB =>' || '''PAYVAR_'  || to_char(sysdate,'dd_MON_yyyy') || ''');' from dual;

EXEC DBMS_STATS.IMPORT_TABLE_STATS(ownname =>'RDB',tabname => 'D_PAYER', stattab => 'STAT_TAB',cascade => true,statid=>'Feb18', no_invalidate => false);

exec dbms_stats.import_schema_stats ( ownname => 'hr' , stattab => 'hr' , statid => 'name_21nov09' , statown => 'own' );

è dictionary and fixed table stats :

EXEC DBMS_STATS.gather_dictionary_stats;

Identify if data dictionary statistics have been collected:

If there are performance problems due to large data dictionary access, it is advisable to check if data dictionary statistics have been collected. It can be easily displayed by querying data dictionary tables belonging to sys.

select table_name, last_analyzed

from dba_tables where owner='SYS'

and table_name='FET$';

This other query is useful to know how many sys tables have statistics:

select distinct trunc(last_analyzed), count(*)

from dba_tables

where owner='SYS'

group by trunc(last_analyzed)

order by 1 ;

Fixed object stats :

EXEC DBMS_STATS.gather_fixed_objects_stats;

execute  dbms_stats.gather_fixed_objects_stats();

It is important to make certain that the fixed object statistics are collected.  If the statistics are not collected, there is a strong chance that some queries of Oracle Database views will be slow, or even result in the crashing of the user’s session

to check if fixed object stats is not gathered :

-------------------------------------------------

select * from tab_stats$;

è database stats :

Exec dbms_stats.gather_database_stats(dbms_stats.auto_sample_size,method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>TRUE);

sqlplus -s /nolog << EOF

   connect system/$PWD@$TNS

   set feedback on

   set timing on

   set echo on

   BEGIN

     DBMS_STATS.GATHER_DATABASE_STATS(

       ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,

       OPTIONS=>'GATHER STALE',

       METHOD_OPT=>'FOR ALL COLUMNS SIZE REPEAT',

       DEGREE=>4

    );

   END;

/

   BEGIN

     DBMS_STATS.GATHER_DATABASE_STATS(

       ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,

       OPTIONS=>'GATHER EMPTY',

       METHOD_OPT=>'FOR ALL COLUMNS SIZE REPEAT',

       DEGREE=>4

    );

   END;

/

To gather the dictionary stats, execute one of following:-
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS

è system stats ( workload/ non-worklod ) :

System stats value :

exec   dbms_stats.gather_system_stats('START')

exec   dbms_stats.gather_system_stats('STOP')

select sname,pname,  pval1 , pval2 from   sys.aux_stats$  where  sname like 'SYSSTATS%' ;

select (select pval1

         from   sys.aux_stats$

          where  sname = 'SYSSTATS_MAIN' and

                 pname = 'MBRC') *

       (select to_number(value) block_size

         from   v$parameter

         where  name = 'db_block_size') /

      (select pval1

        from   sys.aux_stats$

         where  sname = 'SYSSTATS_MAIN' and

                pname = 'MREADTIM')

         /1024 sys_stat_mread_speed

from dual

  /

  

#########################  work lod stats ###################

Gathering Workload Statistics   :::::::::::::::::::

To gather workload statistics, either:

Run the dbms_stats.gather_system_stats('start') procedure at the beginning of the workload window, then the dbms_stats.gather_system_stats('stop') procedure at the end of the workload window.

Run dbms_stats.gather_system_stats('interval', interval=>N) where N is the number of minutes when statistics gathering will be stopped automatically.

To delete system statistics, run dbms_stats.delete_system_stats(). Workload statistics will be deleted and reset to the default noworkload statistics.

Workload statistics, this type of statistic takes into account the actual load on the system at a certain time interval. To collect this type of statistics gathering  mode INTERVAL  or  START and STOP is used by procedure DBMS_STATS.GATHER_SYSTEM_STATS.

SQL> begin

  2  sys.dbms_stats.gather_system_stats(

  3                                     gathering_mode=>'INTERVAL',

  4                                     interval=>720

  5                                    );

  6  end;

  7  /

SQL> exec sys.dbms_stats.gather_system_stats('START');

-- After some time

SQL> exec sys.dbms_stats.gather_system_stats('STOP');

mbrc

mreadtim

sreadtim

cpuspeed

maxthr

slavethr

###################  noworkload stats #################

Optimizer system statistics parameters computed by Noworkload collection are

cpuspeedNW

ioseektim

iotfrspeed

SQL> exec sys.dbms_stats.gather_system_stats();

è upgrade stats table :

exec dbms_stats.upgrade_stat_table('SYS','STAT_TABLE');

GIS_ADMIN

GIS_CC

SYS

SQL> BEGIN dbms_stats.export_schema_stats('GIS_ADMIN','STAT_TABLE'); EN

D;

*

ERROR at line 1:

ORA-20002: Version of statistics table GIS_ADMIN.STAT_TABLE is too old.  Please

try upgrading it with dbms_stats.upgrade_stat_table

ORA-06512: at "SYS.DBMS_STATS", line 11271

ORA-06512: at "SYS.DBMS_STATS", line 11288

ORA-06512: at "SYS.DBMS_STATS", line 12222

ORA-06512: at line 1

Elapsed: 00:00:06.09

06:00:08 SQL> BEGIN dbms_stats.export_schema_stats('GIS_CC','STAT_TABLE'); END;

*

ERROR at line 1:

ORA-20002: Version of statistics table GIS_CC.STAT_TABLE is too old.  Please

try upgrading it with dbms_stats.upgrade_stat_table

ORA-06512: at "SYS.DBMS_STATS", line 11271

ORA-06512: at "SYS.DBMS_STATS", line 11288

ORA-06512: at "SYS.DBMS_STATS", line 12222

ORA-06512: at line 1

Elapsed: 00:00:02.20

06:00:10 SQL> BEGIN dbms_stats.export_dictionary_stats('STAT_TABLE'); END;

*

ERROR at line 1:

ORA-20002: Version of statistics table SYS.STAT_TABLE is too old.  Please try

upgrading it with dbms_stats.upgrade_stat_table

ORA-06512: at "SYS.DBMS_STATS", line 11271

ORA-06512: at "SYS.DBMS_STATS", line 11288

ORA-06512: at "SYS.DBMS_STATS", line 26704

ORA-06512: at line 1

Elapsed: 00:00:01.93

  

è schema stats :

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'COND_DBA',ESTIMATE_PERCENT=>100, DEGREE=>4, CASCADE=>TRUE);

exec dbms_stats.gather_schema_stats

(ownname =>'DATA_FR',estimate_percent =>100,method_opt=>'for all indexed columns size 1',CASCADE=>TRUE,degree=>4);

EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent => 15);

exec dbms_utility.analyze_schema('PIDB','COMPUTE');

exec dbms_stats.gather_schema_stats('MUREXSBY',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS size 1',options=>'GATHER');

EXEC DBMS_STATS.gather_schema_stats('CP',DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>8 ,cascade=>TRUE,granularity =>'ALL');

EXEC DBMS_STATS.delete_schema_stats('SCOTT');

set lines 160

set pages 0

set echo off

set feedback off

spool gather_schema_stat_tn.sql

select 'EXEC DBMS_STATS.GATHER_SCHEMA_STATS('''||USERNAME||''',ESTIMATE_PERCENT=>10,METHOD_OPT=>''FOR ALL COLUMNS SIZE REPEAT'',CASCADE=>TRUE)'

from dba_users where username not in ('SYS','SYSTEM','SNAPXIUS','OUTLN','DBSNMP','WMSYS','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','XDB','ANONYMOUS','WKSYS','WKPROXY','ODM','ODM_MTR','OLAPSYS')

/

spool off

set echo on

set pages 100

set timing on

set time on

set feedback on

@gather_schema_stat_tn.sql

--exit

exec dbms_utility.analyze_schema(schema=>'&schema_owner',method=>'&method', estimate_rows=>&est_rows, estimate_percent=>&est_pct);

  

 è Restoring old stats gathered by Oracle from 10g

old version of stats are saved automatically in 10g before  gatering new stats .

so we can restore old stats  using below . use DBA_OPTSTAT_OPERATIONS and  DBA_TAB_STATS_HISTORY  to see stats history .

exec dbms_stats.restore_table_stats({owner} ,{table name}, {timestamp});

alter session set NLS_TIMESTAMP_TZ_FORMAT='yyyy-mm-dd:hh24:mi:ss';

select table_name,stats_update_time from user_tab_stats_history;

begin

dbms_stats.restore_table_stats('ADAM','SALES',

to_timestamp('2010-05-18:09:47:38','yyyy-mm-dd:hh24:mi:ss'));

end;

/

alter system flush shared_pool;