OSWatcher – a very nice Oracle Utility

You can download this utility from Oracle’s metalink.  This utility is a good way of monitoring the CPU, RAM and Network load.  This is especially useful in RAC based deployments.  You can configure the data collection frequency – default being 30 seconds.  What it collects is the the output of famous Unix/Linux utilities: iostat, netstat, traceroute, vmstat, top (depending upon the platform: Solaris, HP/Ux, Linux).  Metalink note: 301137.1 has a nice users guide on the features of this utility and the different examples on how to configure and run it.  We recommend running it as a background job to collect data at periodic intervals.  This can be used to establish a performance baseline as well.

This along with Statspack or AWR (10g) information provides a good set of information for troubleshooting and diagnosing issues.

Dynamic Management Views and Functions in SQL Server 2005

Dynamic management views & functions in SQL Server 2005 is such a nice addition – Oracle has had these since a long time.  These return the server state information and are very useful for troubleshooting purposes.  There are server scoped and database scoped dmv’s.  Here is a breakdown of the high level categories:

Server Level (the xxx denote the specific views under that category):

1. dm_exec_xxx : Execution of user code and their associated connections.
2. dm_os_xxx : Memory, locks and scheduling.
3. dm_tran_xxx : For transactions, locks, snapshots.
4. dm_io_xxx : For IO.
5. dm_db_xxx : For databases and their related objects.

Component Level (the xxx denote the specific views under that category):

1. dm_repl_xxx : Replication related.
2. dm_broker_xxx : Service Broker related.
3. dm_fts_xxx : Full Text Search related.
4. dm_qn_xxx : Notification Services related.
5. dm_clr_xxx : Common Language Runtime (CLR) related.

In one of the future blogs, we will start drilling down into the important ones and will start putting out the scripts that we use for troubleshooting performance issues and diagnosing issues in an environment.  Some of the scripts were posted before as well in our blog posts dealing with locks.

Some Useful day to day Oracle SQL Scripts

Show Column:  Input will be the column name

select table_name,
column_name,
substr(data_type,1,10) data_type,
CASE WHEN DATA_TYPE IN (‘CHAR’,'VARCHAR2′) THEN char_length ELSE data_length END length,
data_precision,
data_scale
FROM   user_Tab_columns
WHERE  column_name like upper(‘%&column_name%’)
ORDER BY table_name
/

Show constraints:  Input is the table name

select   substr(a.constraint_name,1,30),substr(a.column_name,1,30),a.position
from     user_cons_columns a, user_constraints b
where    a.table_name = upper(‘&table’)
and    b.table_name = a.table_name
and    b.constraint_name = a.constraint_name
and    b.constraint_type in (‘P’, ‘R’, ‘U’)
order by a.constraint_name,a.position
/

select /*+ rule */  b.table_name child_table,
b.constraint_name fk_constraint
from   user_constraints a,
user_constraints b
where  a.table_name      = Upper(‘&table’)
and    a.constraint_type = ‘P’
and    a.constraint_name = b.r_constraint_name (+)
/

Show Default: Table and Column Names are the two inputs

select data_Default from user_tab_columns
where table_name = upper(‘&table_name’)
and column_name = upper(‘&column_name’)
/

Show Index:  Table Name is the input

select   substr(index_name,1,30),substr(column_name,1,30),column_position
from     user_ind_columns
where    table_name = upper(‘&table’)
order by index_name,column_position
/

Show Invalid: Shows invalid status objects and generates the SQLs to COMPILE them (please note that this is just one of the ways to recompile the invalid objects – there are many other simple ways as well that we will cover in a future blog)

select ‘ALTER ‘ || decode(object_type,’PACKAGE BODY’, ‘PACKAGE’,object_Type) ||’ ‘||object_name || ‘ COMPILE;’
from user_objects
where status = ‘INVALID’
and object_name not like ‘BIN$%’
/

Show Space: For tablespace

SELECT owner,SUM(bytes+blocks)/1024/1024 FROM dba_segments
WHERE tablespace_name = UPPER(‘&tablespace_name’)
GROUP BY owner
ORDER BY 2 DESC
/

Show memory usage:

SELECT to_number(decode(SID,65535,NULL,SID)) SID,
operation_Type OPERATION,
trunc(work_area_size/1024) WSIZE,
trunc(expected_size/1024) ESIZE,
trunc(actual_mem_used/1024) MEM,
trunc(max_mem_used/1024) “MAX_MEM”
FROM v$sql_workarea_active
ORDER BY 1,2
/

More DB2 LUW SQL Scripts

You can use this SQL to generate the SQL statements for truncating the tables in a schema:

SELECT ‘ALTER TABLE ‘ || TABNAME || ‘ ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;’
FROM SYSCAT.TABLES A
WHERE A.TABSCHEMA = CURRENT SERVER
AND A.TYPE = ‘T’

You can use this SQL to generate the RUNSTATS commands for all tables or sub-set of the tables – you can make modifications as needed for the criteria for the RUNSTATS command:

SELECT ‘RUNSTATS ON TABLE ‘ || ‘MYSCH’ || ‘.’ || TABNAME ||  ‘ WITH DISTRIBUTION AND DETAILED INDEXES ALL;’
FROM SYSCAT.TABLES
WHERE TABSCHEMA = ‘MYSCH’
AND TYPE = ‘T’;

For generating the enable/disable for the tables:

– enable_constraint

select ‘alter table ‘ || current server || ‘.’ || tabname ||  ‘ alter foreign key ‘ || constname || ‘ ENFORCED;’
from syscat.references WHERE TABSCHEMA = CURRENT SERVER;

– Disable constraint

select ‘alter table ‘ || current server || ‘.’ || tabname ||  ‘ alter foreign key ‘ || constname || ‘ NOT ENFORCED;’
from syscat.references WHERE TABSCHEMA = CURRENT SERVER;

For Identity Resets:

SELECT ‘SELECT ”ALTER TABLE ‘ || A.TABNAME || ‘ ALTER ‘ || A.COLNAME || ‘ RESTART WITH ” ||  ‘ || ‘RTRIM(CHAR(MAX(‘ || A.COLNAME || ‘)+1)) ‘ ||
‘ FROM ‘ || A.TABNAME ||’;’
FROM SYSCAT.COLUMNS A
WHERE A.TABSCHEMA = CURRENT SCHEMA
AND A.IDENTITY = ‘Y’
ORDER BY A.TABNAME;

Will post more SQLs in subsequent posts – will help in working through the meta-data to make life a bit easier in Oracle, SQL Server and DB2 LUW.