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.
To view snapshot
SQL> select end_interval_time, error_count from DBA_HIST_SNAPSHOT where snap_id = 856;
END_INTERVAL_TIME ERROR_COUNT
————————————————————————— ———–
25-AUG-10 11.03.17.486 PM 0
sysaux – v$sysaux_occupants
– view to monitor space usage by different occupants and procedure to move the feature to a different tablespace.
SQL> COLUMN move_procedure FORMAT a35
SQL> COLUMN occupant_name FORMAT a30
SQL> SELECT occupant_name, move_procedure, space_usage_kbytes FROM v$sysaux_occupants;
OCCUPANT_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES
—————————— ———————————– ——————
LOGMNR SYS.DBMS_LOGMNR_D.SET_TABLESPACE 6080
LOGSTDBY SYS.DBMS_LOGSTDBY.SET_TABLESPACE 896
STREAMS 512
…
DBA_HIST_DATABASE_INSTANCE
In 10g there is a view called DBA_HIST_DATABASE_INSTANCE which has database instance information. It has history on information such as instance startup time, last ASH sample id.
Example:
SQL> column startup_time format a35
SQL> alter session set nls_timestamp_format=’DD-MON-YYYY HH24:MI:SS’;
Session altered.
SQL> SELECT startup_time, last_ash_sample_id FROM dba_hist_database_instance WHERE dbid = 1992878807 order by startup_time;
STARTUP_TIME LAST_ASH_SAMPLE_ID
—————————— ——————
12-AUG-2009 21:12:19 447735
21-AUG-2009 17:48:29 494755
..
27-SEP-2009 15:24:04 3036072
29-SEP-2009 21:44:22 0
8 rows selected.
Oracle Created (Default) Database Users
List of oracle default users and their password. After creating the database one may want to change the password for these users or lock the account if the feature is not being used.
http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_26.shtml
How to find definitions of V$, GV$ views?
From v$fixed_view_definition one can find the definitions of V$views which may one in understand them.
Example:
SQL> set pages 0
SQL> SELECT * FROM v$fixed_view_definition where view_name = 'V$SESSION';
V$SESSION
select SADDR , SID , SERIAL# , …. SQL_TRACE_BINDS from GV$SESSION where inst_id = USERENV('Instance')
If one tries to grant direct access to view you will receive ORA-02030 error
SQL> grant select on v$session to scott;
grant select on v$session to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
The views are created through the script $ORACLE_HOME/rdbms/admin/catalog.sql
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
<b>SQL> select dbid from v$database;</b>
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. So one would want to set for X days the value would be X * 24 (hours per day) * 60 (minutes per hour)
– internal=>60min (snap at this interval), a value of 0 will turn off AWR
– topnsql – top N sql size, specify value of NULL will keep the current setting
<b>SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>64800, interval=>60, topnsql=>100, dbid=>1992878807);</b>
PL/SQL procedure successfully completed.
– shows retention and interval after it was modified
<b>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;</b>
Snapshot Interval Retention Interval topnsql
—————– —————— ———-
60 64800 100
-- Change snapshot interval to 30mins
SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>30);
PL/SQL procedure successfully completed.
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
Substitution Variable Examples
http://www.oracle.com/technology/support/tech/sql_plus/htdocs/sub_var9.html
How to include instance name in spool filename?
Using the example below one can include instance name in the spool filename when using SQL*Plus
SQL> set termout off
SQL> set feedback off
SQL> undefine curdate dcol
SQL> column dcol new_value curdate noprint
SQL> column dbcol new_value db noprint
SQL> select sys_context(#&39;userenv#&39;,#&39;db_name#&39;) dbcol, to_char(sysdate,#&39;YYYYMMDD_HH24MISS#&39;) dcol from dual;
SQL> set termout on
SQL> set feedback on
SQL> spool &db._&curdate._spool.txt
…
SQL> spool off
