How to lock/unlock statistics on a table

In certain cases you may want to lock statistics in a table in certain cases, for example if you want a table not be analyzed by automatic statistics job but analyze it later or in cases where you want prevent from analyzing statistics in cases where data in the table doesn’t change.
The following example shows how to lock table statistics and what happens when one tries to gather statistics on table that has statistics locked.

STEP 1: Create Table for test

SQL> create table test ( x number );

Table created.

STEP2: Create index on table.

SQL> create index test_idx on test(x);

Index created.

STEP3: Check stats are not locked on table.

When stats is not locked the value of stattype_locked is NULL

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–

STEP4: Lock the statistics

SQL> exec dbms_stats.lock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

STEP5: Now check stats are locked status

When stats is locked the value of stattype_locked is ALL

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
ALL

STEP 6: Try to gather statistics on locked table and index


SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');

BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1

SQL> analyze index ajaffer.test_idx compute statistics;

analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

STEP7: Unlock the statistics

SQL> exec dbms_stats.unlock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Perfomance Tuning Related Queries.

Listed below are some SQL queries which I find particularly useful for performance tuning. These are based on the Active Session History V$ View to get a current perspective of performance and the DBA_HIST_* AWR history tables for obtaining performance data pertaining to a period of time in the past.

Top Recent Wait Events

col EVENT format a60

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/
Top Wait Events Since Instance Startup
col event format a60

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
 where wait_class !='Idle'
 group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;

List Of Users Currently Waiting

col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120

select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;

Find The Main Database Wait Events In A Particular Time Interval

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum < 6;

Top CPU Consuming SQL During A Certain Time Period
Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM

select * from (
select
SQL_ID,
sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum < 6;

Which Database Objects Experienced the Most Number of Waits in the Past One Hour

set linesize 120
col event format a40
col object_name format a40
select * from
(
 select dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
 active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
            dba_objects
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4 desc)
where rownum < 6;

Top Segments ordered by Physical Reads

col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum < 6;

Top 5 SQL statements in the past one hour
select * from (
select active_session_history.sql_id,
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where
active_session_history.sample_time between sysdate -  1/24  and sysdate
 and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
order by 4 desc )
where rownum < 6;


SQL with the highest I/O in the past one day

select * from
(
SELECT /*+LEADING(x h) USE_NL(h)*/
          h.sql_id
,        SUM(10) ash_secs
FROM   dba_hist_snapshot x
,        dba_hist_active_sess_history h
WHERE   x.begin_interval_time > sysdate -1
AND      h.SNAP_id = X.SNAP_id
AND      h.dbid = x.dbid
AND      h.instance_number = x.instance_number
AND      h.event in  ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum < 6;

Top CPU consuming queries since past one day

select * from (
select
         SQL_ID,
         sum(CPU_TIME_DELTA),
         sum(DISK_READS_DELTA),
         count(*)
from
         DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
         group by
         SQL_ID
order by
         sum(CPU_TIME_DELTA) desc)
where rownum < 6;

Find what the top SQL was at a particular reported time of day

First determine the snapshot id values for the period in question.

In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
select * from
(
select
sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and
s.snap_id= &snapid
order by
c3 desc)
where rownum < 6;

Analyse a particular SQL ID and see the trends for the past day

select
s.snap_id,
to_char(s.begin_interval_time,'HH24:MI') c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.cpu_time_delta c6,
sql.elapsed_time_delta c7
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and s.begin_interval_time > sysdate -1
and
sql.sql_id='&sqlid'
order by c7;

Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance

select
 SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in (
'&sqlid')
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;

Top 5 Queries for past week based on ADDM recommendations

/*

Top 10 SQL_ID's for the last 7 days as identified by ADDM

from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log

*/

col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit"
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7
and  a.dbid = (select dbid from v$database)
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24')
and b.advisor_name = 'ADDM'
and b.task_id = l.task_id
and l.status = 'COMPLETED')
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;


Find locks Between 5 and 6 PM on 3/10/15

REM Find locks Between 5 and 6 PM on 3/10/15
set linesize 200
set pagesize 200
col sql_text format a40
col module format a20
SELECT  
            distinct a.sql_id,
            a.inst_id,
            a.blocking_session as BLK_sess,
            a.blocking_session_serial# as BLK_SESS_Serial#,
            a.user_id,
            s.sql_text,
            a.module
FROM  
            GV$ACTIVE_SESSION_HISTORY a,
            gv$sql s
where
            a.sql_id=s.sql_id
            and
            blocking_session is not null
            and
            a.user_id <> 0
            and
            a.sample_time between
                        to_timestamp('2015-03-10 19:00:00','YYYY-MM-DD HH24:MI:SS') and
                        to_timestamp('2015-03-10 18:00:00','YYYY-MM-DD HH24:MI:SS');


Find Invalid Objects

COLUMN object_name FORMAT A30
set linesize 200
set pagesize 200
col object_name format a30
select
  owner,
  object_name,
  object_type, status
from
  dba_objects
where
  owner not in
      ('SYS','SYSTEM','DBSNMP','OUTLN','OLAPSYS','MDSYS','ORDPLUGINS','XDB','DMSYS')
  and
  status='INVALID'
  and
  not object_name like 'BIN$%';


Find Largest Tables in Database (row wise)

WITH BigTbl AS (
SELECT DISTINCT num_rows, table_name, owner
FROM ALL_TABLES
WHERE num_rows >= 5
ORDER BY num_rows DESC)
SELECT ROWNUM RANK, num_rows, table_name, owner
FROM BigTbl
WHERE ROWNUM <= 10;


Find TOP 10 FULL TABLE SCAN SQLs

select * from
   (select b.username,
       t.sql_id,
       sql_fulltext,
       p.operation,
       p.options
   from
       v$sqlarea t,
       v$sql_plan p,
       v$session b
   where
       t.hash_value=p.hash_value
   and
        t.sql_id=b.sql_id
   and
       p.operation='TABLE ACCESS'
   and
       p.options='FULL'
   and
       p.object_owner not in ('SYS','SYSTEM')
   order by
       DISK_READS DESC,
       EXECUTIONS DESC)
where rownum <=10;

Shows Top “Running SQLs”

select  distinct spid,
       s.sid,
       s.serial#,
       to_char(sysdate - last_call_et/(24*3600),'mm/dd/yyhh24:mi:ss') "LAST_ACTIVITY",
       logon_time,
       osuser,
       s.program,
       schemaname,
       sql_text
from v$session s,
       v$process p,
       v$sql t
where s.paddr=p.addr
   and t.hash_value(+)=s.sql_hash_value
   and s.type !='BACKGROUND';


Temporary tablespace usage

SELECT
            s.sid,
            s.username,
            u.tablespace,
            s.sql_hash_value||'/'||u.sqlhash hash_value,
            u.segtype,
            u.contents,
            u.blocks
FROM
            v$session s,
            v$tempseg_usage u
WHERE
            s.saddr=u.session_addr
order by
            u.blocks;



REM DATAFILE I/O NOTES:
REM File Name - Datafile name
REM Physical Reads - Number of physical reads
REM Reads % - Percentage of physical reads
REM Physical Writes - Number of physical writes
REM Writes % - Percentage of physical writes
REM Total Block I/O's - Number of I/O blocks
REM Use this report to identify any "hot spots" or I/O contention

set linesize 200
set pagesize 200
col name format a46
set linesize 200
set pagesize 200
col name format a46
select      NAME,
      PHYRDS "Physical Reads",
      round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",
      PHYWRTS "Physical Writes",
      round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",
      fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's"
from (
      select      sum(PHYRDS) PHYS_READS,
            sum(PHYWRTS) PHYS_WRTS
      from        v$filestat
      ) pd,
      v$datafile df,
      v$filestat fs
where      
      df.FILE# = fs.FILE#
order by
      fs.PHYBLKRD+fs.PHYBLKWRT desc;

I hope this article helped you. Your suggestions/feedbacks are most welcome.

Keep learning... Have a great day!!!

How to compare two awr snapshot periods in a single file?

Sometimes User may say, my DB was good yesterday the same period but today it is very slow.

At these times, we may face a scenario where we need to compare DB performance of specific time period of two different dates.

There are 2 awr related sql scripts which may come handy in this case.

· awrddrpt.sql

· awrddrpi.sql

The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.

To generate an AWR Compare Periods report:

1. At the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

2. Specify whether you want an HTML or a text report:

Enter value for report_type: html

In this example, an HTML report is chosen.

3. Specify the number of days for which you want to list snapshot Ids in the first time period.

Enter value for num_days: 2

A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

4. Specify a beginning and ending snapshot ID for the first time period:

Enter value for begin_snap: 2

Enter value for end_snap: 3

In this example, the snapshot with a snapshot ID of 102 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 103 is selected as the ending snapshot for the first time period.

5. Specify the number of days for which you want to list snapshot Ids in the second time period.

Enter value for num_days2: 1

A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.

6. Specify a beginning and ending snapshot ID for the second time period:

Enter value for begin_snap2: 26

Enter value for end_snap2: 27

In this example, the snapshot with a snapshot ID of 126 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 127 is selected as the ending snapshot for the second time period.

7. Enter a report name, or accept the default report name:

Enter value for report_name:

Using the report name awrdiff_1_102_1_126.txt

In this example, the default name is accepted and an AWR report named awrdiff_1_102_126 is generated.

Sample comparison report :

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!