Scripts to Check Library Cache Lock Contention

One of the best sites for scripts related to checking library cache locks…

http://allappsdba.blogspot.qa/2012/04/to-check-library-cache-lock-contention.html

Script to Check Total No. of Blocks in Buffer Cache for each User Object

COLUMN object_name FORMAT A40

COLUMN number_of_blocks FORMAT 999,999,999,999

SELECT o.object_name, COUNT(*) number_of_blocks

FROM DBA_OBJECTS o, V$BH bh

WHERE o.data_object_id = bh.OBJD

AND o.owner != ‘SYS’

GROUP BY o.object_Name

ORDER BY COUNT(*);

ASH Script for Finding out Top Wait Events in Oracle

prompt ************************************
prompt **** ASH OVERALL WAIT PROFILE
prompt ************************************
set lines 999

SELECT MIN(sample_time) min_ash_available,sysdate-MIN(sample_time) available duration FROM v$active_session_history;

select * from (
select NVL(event,’CPU’) event,count(*),
round((ratio_to_report(sum(1)) over ()*100),1) rr
from gv$active_session_history
WHERE user_id0
AND sample_timetrunc(sysdate-1)
group by event
order by 2 desc
) where rownum<10;

ASH and AWR Scripts for Checking Wait Events and Top Consuming SQL’s

http://gavinsoorma.com/2012/11/ash-and-awr-performance-tuning-scripts/

ksvcreate: Process(m000) creation failed. Error in Alert log due to DNS ip change.

Today on one of our 10g (10.2.0.1, don’t ask !) production databases  running on AIX 5.3 we saw the following message in the alert log.

ksvcreate: Process(m000) creation failed

The database slowed down to a crawl. We couldn't login even SQLPLUS, even sqlplus -prelim couldn't bring up the instance. On starting the instance we got the below error.

ORA-00445: background process "PMON" did not start after 120 seconds

After googling around, we figured out it could be related to a DNS change. Our old DNS was commissioned and the new ones were not added to the resolv.conf file. After adding the new name servers. The instance started immediately without any issues. I'm still wondering what is the relation between the Oracle instance startup and the DNS ip's. Could this be a bug specific to 10.2.0.1 or is it something more generic.

Running SQL Script for Multiple Databases SQL*PLUS | Mutiple Servers SQL*PLUS in Shell Script | BASH Script Looping SQL*PLUS commands

For running a single SQL Command via sqlplus on multiple servers, we have to first do a few pre-requisites

1. Oracle client should be installed on the Unix/Linux Server

2. Create Local TNS entry in the tnsnames.ora file for the databases where you will run the commands

3. All databases should have one common user with a common password (this method is not the safest method
since the password will be in plain text in your Shell script)
Now let us proceed to first create a text file called dbnodes.txt which will have the TNSNAMES for the
database we will connect to..

boston
chicago
newyork

Above is the entries in the dbnodes.txt file, these are the databases where we will loop the sql commands

Create a sql script with the commands you have to run on all the databases, lets call the file script.sql

set echo on
set linesize 200
set pages 0
select sysdate from dual;
select user from dual;
select instance_name,host_name from gv$instance;
alter system set cpu_count=8 scope=both sid='*';
show parameter cpu_count;
alter session set nls_date_format='DD/MM/YYYY';
exit;

I do this to dynamically to cap the cpu_count on some of our databases to contain any resouce hogging.

Now the final shell script which will call the dbnodes.txt and script.sql to loop the above sql commands
through all databases. The shell script is called dbloop.sh

#!/bin/bash
cat dbnodes.txt | while read line
do
sqlplus -s user/user123@$line @/u03/scripts/script.sql
done

Copy all 3 files dbnodes.txt, script.sql and dbloop.sh to one directiry and run it like below; to collect the log of the SQL commands.

$--> dbloop.sh > dbrun.log

This will spool the output to a logfile.

So there you see it, one of the easiest ,method to run a set ofcommon sql commands on hundreds of server. I use this script to do basics like checking a certain parameters on all our production databases, to check dataguard status for multiple production DB’s etc. The use cases of this script is unlimited. Hope you enjoyed it 🙂 Keep it Easy and Keep It Oracle !

Active Session History 11g

Even when sessions are inactive they can be executing SQL’s in the background. You have to identify the SQL’s which are executing.

Run this query as sysdba user. If you are using RAC then replace v$active_session_history with gv$active_session_history

select
ash.SQL_ID ,
sum(decode(ash.session_state,’ON CPU’,1,0)) “CPU”,
sum(decode(ash.session_state,’WAITING’,1,0)) –
sum(decode(ash.session_state,’WAITING’, decode(en.wait_class, ‘User I/O’,1,0),0)) “WAIT” ,
sum(decode(ash.session_state,’WAITING’, decode(en.wait_class, ‘User I/O’,1,0),0)) “IO” ,
sum(decode(ash.session_state,’ON CPU’,1,1)) “TOTAL”
from v$active_session_history ash,v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
group by sql_id ;

You will see something like

SQL_ID               CPU       WAIT         IO      TOTAL
————- ———- ———- ———- ———-
8tfvwyvfm5cjn          0          1          0          1
1xpfbutrrzkph          0          1          0          1
aykvshm7zsabd          0          1          0          1
92f47aa2q2rmd          0          1          0          1
92b382ka0qgdt          0          4          0          4
1h50ks4ncswfn          0          3          1          4
f99a23s9aba0z          0          0          1          1
f6cz4n8y72xdc          0          0          1          1
cvn54b7yz0s8u          0          0          1          1
3s58mgk0uy2ws          0          1          0          1
fnk7155mk2jq6          0          1          0          1

SQL_ID               CPU       WAIT         IO      TOTAL
————- ———- ———- ———- ———-
c2p32r5mzv8hb          0          1          0          1
5p6a1yss527ap          0          1          0          1
6gvch1xu9ca3g          0          1          0          1

Now see which statement shows maximum value in Total column. Take the SQL ID  and run below query

SELECT sql_text FROM v$sqlarea WHERE sql_id = ‘1h50ks4ncswfn’;

This will give you an SQL statement. See which table is it running on and what is it doing. Maybe it is a poorly written SQL which is locking the table or looping on the table. Do an explain plan for this statement.

SELECT * FROM table(dbms_xplan.display_awr(‘1h50ks4ncswfn’));

This way you can identify the top consuming SQL’s running inside your Oracle database. This procedure was run on a 11gR2 database but is also valid for 10gR2.

Monitor User Processes and Send Email Shell Script Solaris10

Sometimes on a system it is important to know the maximum user processes does not increase so much that it consumes all shared memory. Recently we had an incident in our ORganization where a rogue application bug caused user processes to spike upto 20,000 and it consumed all memory and swap and the entire server crashed.  If we pro-actively monitored the system for increasing user processes we could have averted this disaster. Below is script to monitor user processes and send email.

swx – is name process name

500- threshold for permitted processes

#!/bin/ksh
VAL=`ps -ef | grep -c swx | awk ‘{if ( $1 > 500 ) print $1 }’`

if [ “$VAL” -ge “500” ]; then
echo “$VAL user processes found running with SWX user. Exceeded the Threshold,
Please take immediate action.” | /usr/bin/mailx -s “Alert !! $VAL SWX Applicati
on processes” email@test.com
fi

exit 0

You can add this to crontab like below:

* * * * * /u02/scripts/userprocmon.sh >/dev/null 2>&1

Scripts related to Tables/Indexes Oracle

Here are some scripts related to Tables/Indexes .

Tabs w/ Questionable Inds

TABLES WITH QUESTIONABLE INDEX(ES) NOTES:

  • Owner – Owner of the table
  • Table Name – Name of the table
  • Column – Name of the column in question
  • The above query shows all tables that have more than one index with the same leading column. These indexes can cause queries to use an inappropriate indexes; in other words, Oracle will use the index that was created most recently if two indexes are of equal ranking. This can cause different indexes to be used from one environment to the next (e.g., from DEV to TEST to PROD).
  • The information does not automatically indicate that an index is incorrect; however, you may need to justify the existence of each of the indexes above.
    select 	TABLE_OWNER,
     TABLE_NAME,
     COLUMN_NAME
    from  	dba_ind_columns
    where  	COLUMN_POSITION=1
    and  	TABLE_OWNER not in ('SYS','SYSTEM')
    group  	by TABLE_OWNER, TABLE_NAME, COLUMN_NAME
    having  count(*) > 1

    Tabs With More Than 5 Inds

    TABLES WITH MORE THAN 5 INDEXES NOTES:

  • Owner – Owner of the table
  • Table Name – Name of the table
  • Index Count – Number of indexes
    select 	OWNER,
    	TABLE_NAME,
    	COUNT(*) index_count
    from  	dba_indexes
    where  	OWNER not in ('SYS','SYSTEM')
    group  	by OWNER, TABLE_NAME
    having  COUNT(*) > 5
    order 	by COUNT(*) desc, OWNER, TABLE_NAME

    Tables With No Indexes

    TABLES WITHOUT INDEXES NOTES:

  • Owner – Owner of the table
  • Table Name – Name of the table
    select 	OWNER,
    	TABLE_NAME
    from
    (
    select 	OWNER,
    	TABLE_NAME
    from 	dba_tables
    minus
    select 	TABLE_OWNER,
    	TABLE_NAME
    from 	dba_indexes
    )
    orasnap_noindex
    where	OWNER not in ('SYS','SYSTEM')
    order 	by OWNER,TABLE_NAME

    Tables With No PK

    NO PRIMARY KEY NOTES:

  • Table Owner – Owner of the table
  • Table Name – Name of the table
    select  OWNER,
    	TABLE_NAME
    from    dba_tables dt
    where   not exists (
            select  'TRUE'
            from    dba_constraints dc
            where   dc.TABLE_NAME = dt.TABLE_NAME
            and     dc.CONSTRAINT_TYPE='P')
    and 	OWNER not in ('SYS','SYSTEM')
    order	by OWNER, TABLE_NAME

    Disabled Constraints

    DISABLED CONSTRAINT NOTES:

  • Owner – Owner of the table
  • Table Name – Name of the table
  • Constraint Name – Name of the constraint
  • Constraint Type – Type of constraint
  • Status – Current status of the constraint
    select  OWNER,
            TABLE_NAME,
            CONSTRAINT_NAME,
            decode(CONSTRAINT_TYPE, 'C','Check',
                                    'P','Primary Key',
                                    'U','Unique',
                                    'R','Foreign Key',
                                    'V','With Check Option') type,
            STATUS
    from 	dba_constraints
    where 	STATUS = 'DISABLED'
    order 	by OWNER, TABLE_NAME, CONSTRAINT_NAME

    FK Constraints

    FOREIGN KEY CONSTRAINTS NOTES:

  • Table Owner – Owner of the table
  • Table Name – Name of the table
  • Constraint Name – Name of the constraint
  • Column Name – Name of the column
  • Referenced Table – Name of the referenced table
  • Reference Column – Name of the referenced column
  • Position – Position of the column
    select 	c.OWNER,
    	c.TABLE_NAME,
    	c.CONSTRAINT_NAME,
    	cc.COLUMN_NAME,
    	r.TABLE_NAME,
    	rc.COLUMN_NAME,
    	cc.POSITION
    from 	dba_constraints c,
    	dba_constraints r,
    	dba_cons_columns cc,
    	dba_cons_columns rc
    where 	c.CONSTRAINT_TYPE = 'R'
    and 	c.OWNER not in ('SYS','SYSTEM')
    and 	c.R_OWNER = r.OWNER
    and 	c.R_CONSTRAINT_NAME = r.CONSTRAINT_NAME
    and 	c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
    and 	c.OWNER = cc.OWNER
    and 	r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
    and 	r.OWNER = rc.OWNER
    and 	cc.POSITION = rc.POSITION
    order 	by c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME, cc.POSITION

    FK Index Problems

    FK CONSTRAINTS WITHOUT INDEX ON CHILD TABLE NOTES:

  • Owner – Owner of the table
  • Constraint Name – Name of the constraint
  • Column Name – Name of the column
  • Position – Position of the index
  • Problem – Nature of the problem
  • It is highly recommended that an index be created if the Foreign Key column is used in joining, or often used in a WHERE clause. Otherwise a table level lock will be placed on the parent table.
    select 	acc.OWNER,
    	acc.CONSTRAINT_NAME,
    	acc.COLUMN_NAME,
    	acc.POSITION,
    	'No Index' Problem
    from   	dba_cons_columns acc,
    	dba_constraints ac
    where  	ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME
    and   	ac.CONSTRAINT_TYPE = 'R'
    and     acc.OWNER not in ('SYS','SYSTEM')
    and     not exists (
            select  'TRUE'
            from    dba_ind_columns b
            where   b.TABLE_OWNER = acc.OWNER
            and     b.TABLE_NAME = acc.TABLE_NAME
            and     b.COLUMN_NAME = acc.COLUMN_NAME
            and     b.COLUMN_POSITION = acc.POSITION)
    order   by acc.OWNER, acc.CONSTRAINT_NAME, acc.COLUMN_NAME, acc.POSITION

    Inconsistent Column Names

    INCONSISTENT COLUMN DATATYPE NOTES:

  • Owner – Owner of the table
  • Column – Name of the column
  • Table Name – Name of the table
  • Datatype – Datatype of the column
    select 	OWNER,
    	COLUMN_NAME,
    	TABLE_NAME,
    	decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH) datatype
    from 	dba_tab_columns
    where  	(COLUMN_NAME, OWNER) in
    		(select	COLUMN_NAME,
    			OWNER
    	 	 from 	dba_tab_columns
    	 	 group	by COLUMN_NAME, OWNER
    	  	 having	min(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) <
    		 	max(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) )
    and 	OWNER not in ('SYS', 'SYSTEM')
    order	by COLUMN_NAME,DATA_TYPE

    Object Extent Warning

    TABLES THAT CANNOT EXTEND NOTES:

  • Owner – Owner of the object
  • Object Name – Name of the object
  • Object Type – Type of object
  • Tablespace – Name of the tablespace
  • Next Extent – Size of next extent (bytes)
    select 	OWNER,
    	SEGMENT_NAME,
    	SEGMENT_TYPE,
    	TABLESPACE_NAME,
    	NEXT_EXTENT
    from (
    	select 	seg.OWNER,
    		seg.SEGMENT_NAME,
    			seg.SEGMENT_TYPE,
    		seg.TABLESPACE_NAME,
    			t.NEXT_EXTENT
    	from 	dba_segments seg,
    			dba_tables t
    	where 	(seg.SEGMENT_TYPE = 'TABLE'
    	and  	 seg.SEGMENT_NAME = t.TABLE_NAME
    	and  	 seg.owner = t.OWNER
    	and    NOT EXISTS (
    			select 	TABLESPACE_NAME
    				from 	dba_free_space free
    				where 	free.TABLESPACE_NAME = t.TABLESPACE_NAME
    				and 	BYTES >= t.NEXT_EXTENT))
    	union
    	select 	seg.OWNER,
    		seg.SEGMENT_NAME,
    			seg.SEGMENT_TYPE,
    		seg.TABLESPACE_NAME,
    			c.NEXT_EXTENT
    	from 	dba_segments seg,
    			dba_clusters c
    	where  	(seg.SEGMENT_TYPE = 'CLUSTER'
    	and    	 seg.SEGMENT_NAME = c.CLUSTER_NAME
    	and    	 seg.OWNER = c.OWNER
    	and    	NOT EXISTS (
    			select 	TABLESPACE_NAME
    			from 	dba_free_space free
    			where 	free.TABLESPACE_NAME = c.TABLESPACE_NAME
    			and 	BYTES >= c.NEXT_EXTENT))
    	union
    	select 	seg.OWNER,
    		seg.SEGMENT_NAME,
    			seg.SEGMENT_TYPE,
    		seg.TABLESPACE_NAME,
    			i.NEXT_EXTENT
    	from 	dba_segments seg,
    			dba_indexes  i
    	where  	(seg.SEGMENT_TYPE = 'INDEX'
    	and    	 seg.SEGMENT_NAME = i.INDEX_NAME
    	and    	 seg.OWNER        = i.OWNER
    	and    	 NOT EXISTS (
    			select 	TABLESPACE_NAME
    					from 	dba_free_space free
    					where 	free.TABLESPACE_NAME = i.TABLESPACE_NAME
    			and 	BYTES >= i.NEXT_EXTENT))
    	union
    	select 	seg.OWNER,
    		seg.SEGMENT_NAME,
    			seg.SEGMENT_TYPE,
    		seg.TABLESPACE_NAME,
    			r.NEXT_EXTENT
    	from 	dba_segments seg,
    			dba_rollback_segs r
    	where  	(seg.SEGMENT_TYPE = 'ROLLBACK'
    	and    	 seg.SEGMENT_NAME = r.SEGMENT_NAME
    	and    	 seg.OWNER        = r.OWNER
    	and    	 NOT EXISTS (
    			select	TABLESPACE_NAME
    					from 	dba_free_space free
    					where 	free.TABLESPACE_NAME = r.TABLESPACE_NAME
                    and 	BYTES >= r.NEXT_EXTENT))
    )
    orasnap_objext_warn
    order 	by OWNER,SEGMENT_NAME

    Segment Fragmentation

    OBJECTS WITH MORE THAN 50% OF MAXEXTENTS NOTES:

  • Owner – Owner of the object
  • Tablespace Name – Name of the tablespace
  • Segment Name – Name of the segment
  • Segment Type – Type of segment
  • Size – Size of the object (bytes)
  • Extents – Current number of extents
  • Max Extents – Maximum extents for the segment
  • Percentage – Percentage of extents in use
  • As of v7.3.4, you can set MAXEXTENTS=UNLIMITED to avoid ORA-01631: max # extents (%s) reached in table $s.%s.
  • To calculate the MAXEXTENTS value on versions < 7.3.4 use the following equation: DBBLOCKSIZE / 16 – 7
  • Here are the MAXEXTENTS for common blocksizes: 1K=57, 2K=121, 4K=249, 8K=505, and 16K=1017
  • Multiple extents in and of themselves aren’t bad. However, if you also have chained rows, this can hurt performance.
    select 	OWNER,
    	TABLESPACE_NAME,
    	SEGMENT_NAME,
    	SEGMENT_TYPE,
    	BYTES,
    	EXTENTS,
    	MAX_EXTENTS,
    	(EXTENTS/MAX_EXTENTS)*100 percentage
    from 	dba_segments
    where 	SEGMENT_TYPE in ('TABLE','INDEX')
    and 	EXTENTS > MAX_EXTENTS/2
    order 	by (EXTENTS/MAX_EXTENTS) desc

    Extents reaching maximum

    TABLES AND EXTENTS WITHIN 3 EXTENTS OF MAXIMUM :

  • Owner – Owner of the segment
  • Segment Name – Name of the segment
    select owner "Owner",
           segment_name "Segment Name",
           segment_type "Type",
           tablespace_name "Tablespace",
           extents "Ext",
           max_extents "Max"
    from dba_segments
    where ((max_extents - extents) <= 3)
    and owner not in ('SYS','SYSTEM')
    order by owner, segment_name

    Analyzed Tables

    ANALYZED TABLE NOTES:

  • Owner – Owner of the table
  • Analyzed – Number of analyzed tables
  • Not Analyzed – Number of tables that have not be analyzed
  • Total – Total number of tables owned by user
  • The ANALYZE statement allows you to validate and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient plan for retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You can choose the following operations: COMPUTER, ESTIMATE, and DELETE. Early version of Oracle7 produced unpredicatable results when the ESTIMATE operation was used. It is best to compute your statistics.
  • A COMPUTE will cause a table-level lock to be placed on the table during the operation.
    select	OWNER,
    	sum(decode(nvl(NUM_ROWS,9999), 9999,0,1)) analyzed,
    	sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) not_analyzed,
    	count(TABLE_NAME) total
    from 	dba_tables
    where 	OWNER not in ('SYS', 'SYSTEM')
    group 	by OWNER

    Recently Analyzed Tables

    LAST ANALYZED TABLE NOTES:

  • Owner – Owner of the table
  • Table Name – Name of the table
  • Last Analyzed – Last analyzed date/time
    select 	OWNER,
    	TABLE_NAME,
    	to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI:SS') last_analyzed
    from 	dba_tab_columns
    where 	OWNER not in ('SYS','SYSTEM')
    and 	LAST_ANALYZED is not null
    and	COLUMN_ID=1
    and 	(SYSDATE-LAST_ANALYZED) < 30
    order	by (SYSDATE-LAST_ANALYZED)

    Cached Tables

    CACHED TABLE NOTES:

  • Owner – Owner of the table
  • Table Name – Name of the table
  • Cache – Cached
  • select 	OWNER,
    	TABLE_NAME,
    	CACHE
    from dba_tables
    where OWNER not in ('SYS','SYSTEM')
    and CACHE like '%Y'
    order by OWNER,TABLE_NAME
  • How to Add Date and Time to a Filename with Windows Command Line.

    Many times it becomes necessary to append date stamp on your export dumps. In Unix systems, shell scripting is very liberal with variables you can define yourself eg:

    expdate=`date ‘+%d%m%Y’`
    dat=`date ‘+%m%d%y %H:%M:%S’`

    And then go onto define in your script as

    ./expdp system/system directory=export_dir dumpfile=exp_swx_$expdate.dmp logfile=exp_swx_$expdate.log schemas=swx

    But on Windows it can be done in a more easier way, using the Date and Time function. Just input the string below as part of the file name any time you want the current system date and/or time included as part of the file name.

    Date:  %date:~4,2%-%date:~7,2%-%date:~12,2%
    Time:  %time:~0,2%-%time:~3,2%-%time:~6,2%
    Example: copy c:\test.txt c:\test-%date:~4,2%-%date:~7,2%-%date:~12,2%.txt will output c:\test-mm-dd-yy.txt

    exp edate/edate file=edate%date:~4,2%-%date:~7,2%-%date:~12,2%.dmp log=edate%date:~4,2%-%date:~7,2%-%date:~12,2%.txt owner=edate statistics=none