How to find the IO stats of filesystem?

Using v$filestat one can find the physical reads and writes to datafiles it also includes reads done by RMAN. So using this SQL one can find physical read and write on a filesystem. Note: The data reported is since the database started.

SQL> column filesystem format a40
SQL> SELECT substr(vdf.name, 1, instr(vdf.name, '/', -1)) filesystem, sum(vfs.phyrds) totalreads, sum(vfs.phywrts) totalwrts
FROM v$filestat vfs, v$datafile vdf
WHERE vfs.file# = vdf.file#
GROUP BY substr(vdf.name, 1, instr(vdf.name, '/', -1));

FILESYSTEM TOTALREADS TOTALWRTS
—————————————- ————— —————-
/u01/oradata/TEST/data01/ 33397136 1315151
/u02/oradata/TEST/idx01/ 71951 35720
….

Here is the description of the view V$FILESTAT and V$DATAFILE

Information on col_usage$

In 10g SMON gathers information on columns that are used in predicates and updates them in COL_USAGE$. Here is the column description of the table.

SQL> desc sys.col_usage$
Name Null? Type
—————————————– ——– —————————-
OBJ# – sys.obj$ -> obj#
INTCOL# – sys.col$ -> col#
EQUALITY_PREDS – Number of constant equality predicate
EQUIJOIN_PREDS – Number of equijoin of the form table1.column1 = table2.column2
NONEQUIJOIN_PREDS – Number of the form table1.column1 != table2.column2
RANGE_PREDS – Number of the form table1.column1 between value1 and value2
LIKE_PREDS – Number of times like is used
NULL_PREDS – Number of times NULL predicate is used
TIMESTAMP – Last time when it was updated

How to create a on-demand AWR snapshot?

Using dbms_workload_repository.create_snapshot() one can create AWR snapshot.

Example: Creates the snapshot and returns the snap id. If ‘ALL’ is not passed it defaults to typical (statistics_level).

SQL> select dbms_workload_repository.create_snapshot('ALL') from dual;

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()
——————————————
856

1 row selected.

How to generate AWR report?

There are few scripts that can be used to generate AWR HTML or text reports.
@?/rdbms/admin/awrrpt.sql – It is an interactive script that can be used to generate HTML or text reports. The script prompts for type of report requested and prompts for number of days of snapshots to choose from so based on that it displays snap ids to choose the start and end snapshot it.

…
796 27 Sep 2009 17:00 1
797 27 Sep 2009 18:00 1
798 27 Sep 2009 19:00 1
799 27 Sep 2009 20:00 1
800 27 Sep 2009 21:00 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 795
Begin Snapshot Id specified: 795

Enter value for end_snap: 800
End Snapshot Id specified: 800

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_795_800.html. To use this name,
press to continue, otherwise enter an alternative.
..

Another way of generating a report is one can query dba_hist_snapshot to find the snap id for the interval you are looking for.

SQL> column begin_interval_time format a25
SQL> column end_interval_time format a25
SQL> SELECT dbid, snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by begin_interval_time;
DBID SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
———- ———- ————————- ————————-
…
1992878807 798 27-SEP-09 06.00.13.120 PM 27-SEP-09 07.00.14.116 PM
1992878807 799 27-SEP-09 07.00.14.116 PM 27-SEP-09 08.00.15.051 PM
1992878807 800 27-SEP-09 08.00.15.051 PM 27-SEP-09 09.00.16.049 PM

SQL> SELECT output FROM TABLE (dbms_workload_repository.awr_report_text(1992878807, 1, 799, 800));

OUTPUT
——————————————————————————–
WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Release RAC Host
———— ———– ———— ——– ———– — ————
TEST 1992878807 TEST 1 10.2.0.1.0 NO localhost.lo

Snap Id Snap Time Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap: 799 27-Sep-09 20:00:15 15 2.0
End Snap: 800 27-Sep-09 21:00:16 15 2.0
…..
user_dump_dest /u01/oradata/admin/TEST/udump
————————————————————-

OUTPUT
——————————————————————————–

End of Report

@?/rdbms/admin/awrrpti.sql – Script can be used for the specified database and instance so the script in addition will prompt for dbid and instance number.
@?/rdbms/admin/awrddrpt.sql – Script can be used to generate a combine report of 2 different snapshots for comparison of performance in one report so it will prompt for two pairs of snapshot snapshots to compare against.
@?/rdbms/admin/awrinfo.sql – Script print AWR information like space usage by AWR, subset of AWR snap ids, Automatic Database Diagnostic Monitor (ADDM), Active Session History (ASH) information.

How to change AWR retention, interval,topnsql?

Using dbms_workload_repository.modify_snapshot_settings one can modify retention, interval and topnsql.

– get the dbid which is needed to passs to dbms_workload_repository.modify_snapshot_settings
SQL> select dbid from v$database;

DBID
———-
1992878807

– retention=>value in minutes so (45 days * 24 (hours per day) * 60 minutes per hour = 64800), max value can be set by passing a value of 0 which means forever
– internal=>60min (snap at this interval), a value of 0 will turn off AWR
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>64800, interval=>60, topnsql=>100, dbid=>1992878807);

PL/SQL procedure successfully completed.

– shows retention and interval after it was modified
SQL> select extract( day from snap_interval) *24*60+extract( hour from snap_interval) *60+extract( minute from snap_interval ) snapshot_interval,
extract( day from retention) *24*60+extract( hour from retention) *60+extract( minute from retention ) retention_interval,
topnsql
from dba_hist_wr_control;

Snapshot Interval Retention Interval topnsql
—————– —————— ———-
60 64800 100

How to find AWR snapshot interval and retention settings?

Using this SQL one can find the snapshot interval and snapshot retention.

SQL> SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval,
extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_Interval
FROM dba_hist_wr_control;

Snapshot_Interval Retention_Interval
—————– ——————
60 10080

How to backup and restore statistics using dbms_stats?

Using procedures in DBMS_STATS package one can backup statistics and restore them. Assumes the user scott already has access to execute DBMS_STATS and using “GRANT EXECUTE ON dbms_stats TO scott;” as sysdba one can grant execute access to DBMS_STATS.

– create table to backup statistics, ownname is statistics table owner and stattab is statistics table name
SQL> execute dbms_stats.create_stat_table(ownname= 'scott', stattab= 'backup_stats');

PL/SQL procedure successfully completed.

– procedure to export statistics, exports statistics scott.test into scott.backup_stats, cascade=>true means it will export index statistics too
SQL> exec dbms_stats.export_table_stats(ownname=>'scott', tabname=>'test', statown=>'scott', stattab=>'backup_stats', cascade=>true);

PL/SQL procedure successfully completed.

– import table stats
SQL> exec dbms_stats.import_table_stats(ownname=>'scott', tabname=>'test1', statown=>'scott', stattab=>'backup_stats', cascade=>true);

PL/SQL procedure successfully completed.

– drop statistics table
SQL> execute dbms_stats.drop_stat_table(ownname= 'scott', stattab= 'backup_stats');

PL/SQL procedure successfully completed.

How to find the tables that have stale statistics?

Using the code below one can find the tables/indexes that have stale statistics in a database, when options=>’GATHER AUTO’ is used oracle gathers statistics analyzes the tables/indexes that have stale statistics. Table monitoring is enabled by default in 10g to find table which is used to find table statistics, when STATISTICS_LEVEL is set to “BASIC” table monitoring is disabled. In 10g when the value of statistics_level is “typical” or “all” table monitoring is turned enabled. In 9i one can enable/disable table monitoring by calling DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(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;
/
– shows tables that have stale statistics
SYS.COL_USAGE$ TABLE
SYS.DEPENDENCY$ TABLE
SYS.HISTGRM$ TABLE
SYS.HIST_HEAD$ TABLE
…

Note: To find schema level stats that are stale one can call DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SCOTT’, objlist=>ObjList, options=>’LIST STALE’);

The view has the monitoring information about tables user_tab_modifications, all_tab_modifications and dba_tab_modifications.

How to enable/disable a scheduled job?

Using the package DBMS_SCHEDULER one can enable/disable jobs.

To disable job: This disables the job from running
SQL> exec dbms_scheduler.disable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

– check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB FALSE

To enable job:
SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

– check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB TRUE

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.

– create table
SQL> create table test ( x number );

Table created.

– create index
SQL> create index test_idx on test(x);

Index created.

– shows 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
—–
– lock statistics
SQL> exec dbms_stats.lock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

– shows 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

— try to gather statistics on locked table
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

– try to gather statistics on the index using analyze
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

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

PL/SQL procedure successfully completed.