Monitoring Scripts

::::::::::::::
ASM_check.sql
::::::::::::::

set line 220 pages 1000 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
col db_unique_name for a10
spool 1_asm_data.log appendSELECT d.db_unique_name,v.name
diskgroup,round((total_mb/1024),1) "Total_GB",round((free_mb/1024),1) "FREE_GB",round(((total_mb-free_mb)/total_mb*100),1) as Percentage FROM V$asm_diskgroup v, v$database d;
spool off

exit

::::::::::::::
DB_check_PDB.sql
::::::::::::::

set line 220 pages 100 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
col OPEN_MODE for a10
col OPEN_TIME for a40
col name for a10
col RESTRICTED for a12

spool pdb_1_highlevel.log append
select inst_id,name, OPEN_MODE,RESTRICTED,OPEN_TIME,(select round(sum(bytes)/1024/1024/1024,0) from dba_segments) size_gb from gv$PDBS;

spool off

spool 4_outstandingalerts.log append
col reason for a70
col creation_time for a35
col suggested_action for a70
select name Database,creation_time,message_type,reason,suggested_action from dba_outstanding_alerts,v$database;
spool off

spool 5_tablespace.log append
select name Database,tablespace_name,round(((used_space*8192)/1024/1024/1024),1) "USED_GB",round(((tablespace_size*8192)/1024/1024/1024),1) "TOTAL_GB",round(USED_PERCENT,1) Percentage_USED FROM dba_tablespace_usage_metrics,v$database where USED_PERCENT>80;

spool off

exit

::::::::::::::
DB_check_RO.sql
::::::::::::::

set line 220 pages 1000 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

col name for a10
col instance_name for a15
col host_name for a35
col open_mode for a21
col database_role for a17
col SWITCHOVER_STATUS for a20
col startup_time for a20
col flashback_on for a12
col processes for a15
col sessions for a15

spool 1_highlevel.log append

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,

(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes,(select CURRENT_UTILIZATION || '/' || trim(limit_value)||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions,(select round(sum(bytes)/1024/1024/1024,1) from dba_segments) "Size(GB)" from v$database a,v$instance b;

set heading off

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions 
from gv$database a,gv$instance b where a.inst_id=b.inst_id and a.inst_id=2;

spool off

spool 2_standbylag.log append

col source for a10
col target for a10
col primary_time for a20
col standby_TIME for a20

SELECT source, target, primary_log, standby_log,primary_log-standby_log LOG_GAP, sysdate primary_time,standby_TIME,round((sysdate-standby_TIME)*1440,0) Time_gap_min
FROM
(
SELECT db_unique_name source
FROM v$database
),
(
SELECT MAX(SEQUENCE#) primary_log
FROM v$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT name target,MAX(SEQUENCE#) standby_log,MAX(COMPLETION_TIME) standby_TIME
FROM v$ARCHIVED_LOG WHERE name='DSNASB' AND APPLIED='YES' group by name
)
where standby_TIME>sysdate-30
/

spool off

spool 3_otherinfo.log append

col spfile for a65
col last_DB_bkp for a20
col last_arc_bkp for a20
col MMON for a44

select
(select instance_name from v$instance) name,
(select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and STATUS='COMPLETED' and OBJECT_TYPE='DB FULL' or OBJECT_TYPE='DB INCR') last_DB_bkp,(select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and OBJECT_TYPE='ARCHIVELOG' and STATUS='COMPLETED') last_arc_bkp,(select DISPLAY_VALUE from v$parameter where name='spfile') spfile
from dual;

spool off

spool 4_outstandingalerts.log append

col reason for a70
col creation_time for a35
col suggested_action for a70

select name Database,creation_time,message_type,reason,suggested_action from dba_outstanding_alerts,v$database;

spool off

spool 5_tablespace.log append

select name Database,tablespace_name,round(((used_space*8192)/1024/1024/1024),1) "USED_GB",round(((tablespace_size*8192)/1024/1024/1024),1) "TOTAL_GB",round(USED_PERCENT,1) Percentage_USED FROM dba_tablespace_usage_metrics,v$database where USED_PERCENT>80;

spool off

spool 6_recoveryarea.log append

col RECOVERY_AREA_USAGE for a30

select i.instance_name as DB_NAME,flashback_on, (select count(*) from v$restore_point) RESTORE_POINTS, round((f.space_used/1024/1024/1024),0)||'/'||(f.space_limit/1024/1024/1024)||' ('||round((f.space_used/space_limit*100),1)||'%)' "RECOVERY_AREA_USAGE" from v$database d, v$instance i,v$recovery_file_dest f;

spool off

exit

::::::::::::::

DB_check.sql

::::::::::::::

set line 220 pages 1000 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
col name for a10
col instance_name for a15
col host_name for a35
col open_mode for a21
col database_role for a17
col SWITCHOVER_STATUS for a20
col startup_time for a20
col flashback_on for a12
col processes for a15
col sessions for a15

spool 1_highlevel.log append

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions,(select round(sum(bytes)/1024/1024/1024,1) from dba_segments) "Size(GB)" from v$database a,v$instance b;

set heading off

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes, (select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions from gv$database a,gv$instance b where a.inst_id=b.inst_id and a.inst_id=2;

spool off

spool 2_standbylag.log append

col source for a10
col target for a10
col primary_time for a20
col standby_TIME for a20

SELECT source, target, primary_log, standby_log,primary_log-standby_log LOG_GAP, sysdate primary_time,standby_TIME,round((sysdate-standby_TIME)*1440,0) Time_gap_min
FROM
(
SELECT db_unique_name source
FROM v$database
),
(
SELECT MAX(SEQUENCE#) primary_log
FROM v$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT name target,MAX(SEQUENCE#) standby_log,MAX(COMPLETION_TIME) standby_TIME
FROM v$ARCHIVED_LOG WHERE DEST_ID in (select dest_id from v$archive_dest where destination is not null and dest_id!=1) AND APPLIED='YES' group by name
)
where standby_TIME>sysdate-30
/

spool off

spool 3_otherinfo.log append

col spfile for a65
col last_DB_bkp for a20
col last_arc_bkp for a20
col MMON for a44

select
(select instance_name from v$instance) name,(select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and STATUS='COMPLETED' and OBJECT_TYPE='DB FULL' or OBJECT_TYPE='DB INCR') last_DB_bkp, (select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and OBJECT_TYPE='ARCHIVELOG' and STATUS='COMPLETED') last_arc_bkp,(select DISPLAY_VALUE from v$parameter where name='spfile') spfile 
from dual;

spool off

spool 4_outstandingalerts.log append

col reason for a70
col creation_time for a35
col suggested_action for a70
select name Database,creation_time,message_type,reason,suggested_action from dba_outstanding_alerts,v$database;

spool off

spool 5_tablespace.log append

select name Database, tablespace_name,round(((used_space*8192)/1024/1024/1024),1) "USED_GB",round(((tablespace_size*8192)/1024/1024/1024),1) "TOTAL_GB",round(USED_PERCENT,1) Percentage_USED FROM dba_tablespace_usage_metrics,v$database where USED_PERCENT>80;

spool off

spool 6_recoveryarea.log append

col RECOVERY_AREA_USAGE for a30

select i.instance_name as DB_NAME,flashback_on, (select count(*) from v$restore_point) RESTORE_POINTS, round((f.space_used/1024/1024/1024),0)||'/'||(f.space_limit/1024/1024/1024)||' ('||round((f.space_used/space_limit*100),1)||'%)' "RECOVERY_AREA_USAGE" from v$database d, v$instance i,v$recovery_file_dest f;

spool off

exit 

Find Resource Limit

select * from gv$resource_limit 
where RESOURCE_NAME in ( 'processes','sessions') ;

 

Toad hangs up during login

There is a setting you'll have to change once you have 10.6.

Go to Options -> Oracle -> Optimizer Hints. You'll have to add a RULE hint for
DBA_SYNONYMS on Oracle 11Gr2.

Speeding Up Queries with Semi-Joins and Anti-Joins: How Oracle Evaluates EXISTS, NOT EXISTS, IN, and NOT IN

by Roger Schrag
Database Specialists, Inc.

From http://www.dbspecialists.com/files/presentations/semijoins.html

Introduction
Optimizing SQL usually gives the most significant results when DBAs are called upon to “make the system run faster.” Using tools like Statspack or Enterprise Manager, it is often easy to find the slow SQL. But how do you make the queries run faster? That is the challenge! In this paper we will discuss the semi-join and the anti-join, two powerful SQL constructs Oracle offers for use in your quest for faster queries. In particular, we will define these two terms, discuss when and why you might want to use the [NOT] EXISTS or [NOT] IN constructs, and demonstrate how you can use optimizer hints and make minor query changes in order to enable Oracle to use some very powerful and efficient access paths.

How To Understand AWR Report / Statspack Report ?

How To Understand AWR Report / Statspack Report
==============================================
(From  http://halimdba.blogspot.ca/2011/07/how-to-understand-awr-report-statspack.html )

script is here $ORACLE_HOME\RDBMS\ADMIN\
awrrpt.sql
awrrpti.sql

execute like below

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jul 10 14:37:04 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> CONN sys@stlbas105 as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> @G:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN\awrrpt.sql

How to check the capacity of your instance - 10g and higher

SELECT 'Max Sessions - '||highwater "Resource"

  FROM DBA_HIGH_WATER_MARK_STATISTICS

WHERE name = 'SESSIONS'

UNION

SELECT 'Processes - '||value

  FROM v$parameter

WHERE name = 'processes'

UNION

SELECT 'Sessions - '||value

  FROM v$parameter

WHERE name = 'sessions';

Resource
----------------------------
Max Sessions - 127
Processes - 300
Sessions - 335

Statspack Performance

Tip: Collecting statistics (e.g. with DBMS_STATS.GATHER_SCHEMA_STATS) on perfstat schema before purging, it may save you a lot of waiting time.

SQL>  CONNECT perfstat/my_perfstat_password

SQL>  DEFINE losnapid=1

SQL>  DEFINE hisnapid=2

SQL>  @?/rdbms/admin/sppurge

When SPPURGE.SQL is run, it does not prompt for the information provided by the variables.

What to define before huge table load?

ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;
ALTER SESSION SET SORT_AREA_SIZE=600000000;
ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 128;

GATHER_FIXED_OBJECT_STATS and GATHER_DICTIONARY_STATS

GATHER_FIXED_OBJECT_STATS and GATHER_DICTIONARY_STATS

May 25, 2009 Mohammed Mehraj Hussain Leave a comment Go to comments

 

There are two procedures in the DBMS_STATS package for gathering statistics on Oracle native objects:
GATHER_FIXED_OBJECT_STATS
GATHER_DICTIONARY_STATS
GATHER_FIXED_OBJECT_STATS collects statistics for fixed X$ and K$ objects. It needs to be run whenever any init.ora parameter is changed. Fixed objects are the magic tables that are not tables – they are not “dictionary” tables. The x$ tables would change size in response to init.ora setting changes generally. Setting processes higher will add rows to various x$ views for example.
So, they could be considered a one time thing unless you make a big change to your parameter
settings.
GATHER_DICTIONARY_STATS collects statistics for SYS schema. It needs to be run whenever you do “big things” to the dictionary (loaded a schema – not put data into the schema, but rather did things in the schema that affect the dictionary like creating and dropping objects…) – you would consider gathering statistics.
Look at last_analyzed for the sys tables to see when the dictionary was gathered against. Metalink Note 281793.1 states that the DBA_OPTSTAT_OPERATIONS view may be used to determine the start and end time of all DBMS_STATS operations executed at the schema and database level.

Importance Of SQL Design

Importance Of SQL Design

July 16, 2009 Mohammed Mehraj Hussain

 

If the sql statement  is designed poorly, nothing much can be done by optimizer or indexes

Few are the well known rules

-Enabling indexes to eliminate the need for full table scans

-Avoid Cartesian joins

–Use UNION ALL instead of UNION – if possible

–Use EXIST clause instead of IN – (Wherever appropiate)

–Use order by when you really require it – Its very costly

–When joining 2 views that themselves select from other views, check that the 2 views that you are using do not join the same tables!

–Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from using indexes. Use where amount > 0 instead of where amount != 0

- Avoid writing where is not null. nulls can prevent the optimizer from using an index

- Avoid calculations on indexed columns. Write WHERE amount > 26000/3 instead of WHERE approved_amt/3 > 26000

Example of function-based index

Tom Kyte Blog - asktom.oracle.com

My point is, the optimizer thinks that very few rows will be returned by the FBI, that will be the index it really wants to use to access this table then. 0 or 1 regardless. 0 would be even better than 1. This is actually an example I use in my seminars when talking about indexing actually. ops$tkyte%ORA11GR1> create table t as 2 select 'Y' processed_flag, a.* from stage a; Table created.

How does one tune the Redo Log Buffer?

The size of the Redo log buffer is determined by the LOG_BUFFER parameter in your SPFILE/INIT.ORA file. The default setting is normally 512 KB or (128 KB * CPU_COUNT), whichever is greater. This is a static parameter and its size cannot be modified after instance startup.

SQL> show parameters log_buffer
NAME                                 TYPE        value
------------------------------------ ----------- ------------------------------
log_buffer                           integer     262144

Database appears hanging but generates huge amount of redo and archived redo logs

Problem Description
——————-

Your database is generating huge amount of redo logs with the archive processes  creating new archived logs very often (for example, the 50MB size redo logs are fullfiled and archived every 10 sec),  but  no one  is doing anything on the  database.

The queries run  against the  database  are not completing,  jobs seem to be hanging, although simple queries against the data dictionary can be run ok.

Shutdown immediate hangs,   after shutdown  abort and  restart in normal or restricted mode,  the situation   is the same.   Rebooting the box does not make  any difference in what has been observed.

Parallel Rollback may hang database, Parallel query servers get 100% cpu

Problem Description
——————-

Sometimes Parallel Rollback of Large Transaction may become very slow. After killing a large running transaction (either by killing the shadow process or aborting the database) then database seems to hang, or smon and parallel queryservers taking all the available cpu.  After killing shadow process or aborting the database the v$transaction entry is lost, so you cannot estimate by
examining v$transaction.used_ublk how the rollback procedure proceeds.

Database Hangs Because SMON is taking 100% CPU doing transaction recovery

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7 to 10.2
Information in this document applies to any platform.

Goal
After killing a large running transaction, either by killing the process or aborting the database, the database may appear to hang
and/or the SMON background process is taking all the available cpu. Basically SMON is in the progress of rolling back this terminated
large transaction, which can be time consuming. As a result of this users often try to restart the database, but because a shutdown
immediate will hang, the database is often aborted. Please note that stopping the database will actually make things worse and will not ecrease the amount of work SMON needs to perfom to complete the rollback.

The message ‘Waiting for smon to disable tx recovery’ will posted in the alert log as well.

What To Do and Not To Do When 'shutdown immediate' Hangs

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7 to 10.2
Information in this document applies to any platform.

Goal
What to do when shutdown immediate appears to hang.
Sometimes, the message ‘Waiting for smon to disable tx recovery’ is posted in the alert log.
This note only addresses situations when the apparent hang occurs when the database is going from OPEN to MOUNT, which is actually the most common situation.
If the apparent hang occurs at a different step, then this note does not apply.