Sql Queries for Finding Bottleneck on your Oracle Database

SQL Queries for Finding Bottleneck on your Oracle Database

1. Stalestats

set long 10000
set serveroutput on

declare
  a dbms_stats.objecttab;
begin
   dbms_stats.gather_database_stats
     (OPTIONS=>'LIST STALE',OBJLIST=>a);

   for i in 1 .. a.count
   loop
           dbms_output.put_line( a(i).ownname );
           dbms_output.put_line( a(i).objType );
           dbms_output.put_line( a(i).objName );
           dbms_output.put_line( a(i).PartName );
           dbms_output.put_line( a(i).subPartName );
           dbms_output.put_line( a(i).Confidence );
           dbms_output.put_line
                 ( '-------------------------' );
   end loop;
end;
/

2. System Stats
select pname, pval1 from sys.aux_stats$;

select
   sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,
   sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
   (
      sum(a.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 scatterr,
   (
      sum(b.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 seqr,
  (
      sum(b.time_waited_micro) /
      sum(b.total_waits)) /
      (sum(a.time_waited_micro)/sum(a.total_waits)
   ) * 100 c5
from
   dba_hist_system_event a,
   dba_hist_system_event b
where
   a.snap_id = b.snap_id
and
   a.event_name = 'db file scattered read'
and
   b.event_name = 'db file sequential read';

3. Tail Indexes
select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3;

4.  Table Space Usuage

col tablespace_name format a15
col alloc_size format 999999.999
col pct_used format 9999999.999
col free_space format 9999999.999
col maxnext format 999999.999
col definitsz format 999999.999
col defnextsz format 9999999.999

SELECT a.tablespace_name, a.datafile_sz, b.alloc_size,
(b.alloc_size)/a.datafile_sz*100 PCT_USED,
(a.datafile_sz-b.alloc_size) FREE_SPACE,
b.next_extent/1024/1024 MAXNEXT,
a.initial_extent/1024/1024 DEFINITSZ,
a.next_extent/1024/1024 DEFNEXTSZ
FROM (
  SELECT a.tablespace_name, SUM (b.bytes)/1024/1024
  DATAFILE_SZ, a.initial_extent, a.next_extent
  FROM dba_tablespaces a, dba_data_files b
  WHERE a.tablespace_name = b.tablespace_name
  GROUP BY a.tablespace_name, a.initial_extent, a.next_extent) A,
    (
  SELECT a.tablespace_name, SUM (c.bytes)/1024/1024
  ALLOC_SIZE, MAX(c.next_extent) NEXT_EXTENT
  FROM dba_tablespaces a, dba_segments c
  WHERE a.tablespace_name = c.tablespace_name
  GROUP BY a.tablespace_name) B
WHERE a.tablespace_name = b.tablespace_name (+)
ORDER BY 1;

5.  Find Missing Foreign Keys
rem -----------------------------------------------------------------------
rem   Shows the foreign keys without appropiate index
rem -----------------------------------------------------------------------
rem
SET echo off
SET verify off
--
COLUMN OWNER noprint new_value own
COLUMN TABLE_NAME format a32 wrap heading "Table Name"
COLUMN CONSTRAINT_NAME format a32 wrap heading "Constraint Name"
COLUMN CONSTRAINT_TYPE format a3 heading "Typ"
COLUMN COLUMN_NAME format a32 wrap heading "Column"
BREAK ON OWNER skip page
--
SET TERMOUT ON
TTITLE  CENTER 'Unindexed Foreign Keys owned by Owner: ' own SKIP 2
PROMPT
PROMPT Please enter Owner Name and Table Name. Wildcards allowed (DEFAULT: %)
PROMPT
PROMPT eg.:  SCOTT, S% OR %
PROMPT eg.:  EMP, E% OR %
PROMPT
--
ACCEPT vOwner prompt "Owner  <%>: " DEFAULT %
ACCEPT vTable prompt "Tables <%>: " DEFAULT %
--
SELECT OWNER, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
  FROM DBA_CONS_COLUMNS c
  WHERE position=1 AND
   (OWNER, TABLE_NAME, COLUMN_NAME) IN
   (SELECT c.OWNER, c.TABLE_NAME,cc.COLUMN_NAME
      FROM DBA_CONSTRAINTS  c, DBA_CONS_COLUMNS cc
     WHERE c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
       AND c.TABLE_NAME      = cc.TABLE_NAME
       AND c.OWNER           = cc.OWNER
       AND c.CONSTRAINT_TYPE = 'R'
       AND cc.POSITION       = 1
       AND c.OWNER           LIKE UPPER('&vOwner')
       AND c.TABLE_NAME      LIKE UPPER('&vTable')
     MINUS
    SELECT table_owner, table_name, column_name
      FROM DBA_IND_COLUMNS
     WHERE COLUMN_POSITION = 1
       AND TABLE_OWNER LIKE UPPER('&vOwner')
       AND TABLE_NAME  LIKE UPPER('&vTable')
  )
  ORDER BY OWNER, TABLE_NAME, CONSTRAINT_NAME;
--
ttitle off
SET pause off
clear breaks

6.  Large Indexes

SELECT dbi.owner||'.'||dbi.index_name, dbi.owner||'.'||dbi.table_name, dbi.blevel BLVL, db1.blocks TABBLKS, db1.blocks*8192 TBLSZ, db2.blocks INDBLKS, db2.blocks*8192 INDSZ, db2.blocks/db1.blocks RATIO
  FROM dba_segments db1, dba_segments db2, dba_indexes dbi, dba_tables dbt
  WHERE dbt.table_name = dbi.table_name
  AND dbt.table_name = db1.segment_name
  AND dbi.index_name = db2.segment_name
  AND db1.blocks < db2.blocks
  AND ( (db2.blocks/db1.blocks > 64) or (db1.blocks > 10000))
ORDER BY 8;

7.  Redo Logs

col 00 for a4
col 01 for a4
col 02 for a4
col 03 for a4
col 04 for a4
col 05 for a4
col 06 for a4
col 07 for a4
col 08 for a4
col 09 for a4
col 10 for a4
col 11 for a4
col 12 for a4
col 13 for a4
col 14 for a4
col 15 for a4
col 16 for a4
col 17 for a4
col 18 for a4
col 19 for a4
col 20 for a4
col 21 for a4
col 22 for a4
col 23 for a4
col 24 for a4


set line 300
set pages 180


col day for a10
select * from (select to_char(first_time,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23"
from v$log_history
group by to_char(first_time,'YYYY-MM-DD')) order by 1
/

8. Index Fragmentation

select t.table_owner||'.'||t.table_name name,
a.num_rows,
sum(t.inserts) ins,
sum(t.updates) upd,
sum(t.deletes) del,
sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,'999999.99') per_del,
round(((sum(t.updates)+sum(t.inserts)+sum(t.deletes))/(decode(a.num_rows,0,1,a.num_rows)) *100.0),2) per_chg
from analyzedb.table_modifications t,
all_tables a
where t.timestamp >= to_date('&from_date','dd-mon-yyyy') and
t.table_owner = a.owner and t.table_owner not in ('SYS','SYSTEM') and
t.table_name=a.table_name
having (sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0 >=5
group by t.table_owner, t.table_name, a.num_rows
order by num_rows desc, t.table_owner, t.table_name;

9. Archive Daily Logs

select decode(grouping (trunc(COMPLETION_TIME)),1,'TOTAL',TRUNC(COMPLETION_TIME)) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by cube (trunc (COMPLETION_TIME)) order by 1;

10. Busy Buffers

col "Object" format a30
set numwidth 12
set lines 132
set pages 50
select * from(
select DECODE(GROUPING(a.object_name), 1, 'All Objects',
a.object_name) AS "Object",
sum(case when a.statistic_name = 'buffer busy waits'
then a.value else null end) "Buffer Busy Waits",
sum(case when a.statistic_name = 'physical reads'
then a.value else null end) "Physical_Reads",
sum(case when a.statistic_name = 'physical writes'
then a.value else null end) "Physical_writes",
sum(case when a.statistic_name = 'logical reads'
then a.value else null end) "Logical Reads"
from v$segment_statistics a
where a.owner like upper('%')
group by rollup(a.object_name)) b
where b."Buffer Busy Waits">0
order by 2
/
clear columns
ttitle off


11. Empty Stats

set long 10000
set serveroutput on

declare
  a dbms_stats.objecttab;
begin
   dbms_stats.gather_database_stats
     (OPTIONS=>'LIST EMPTY',OBJLIST=>a);

   for i in 1 .. a.count
   loop
           dbms_output.put_line( a(i).ownname );
           dbms_output.put_line( a(i).objType );
           dbms_output.put_line( a(i).objName );
           dbms_output.put_line( a(i).PartName );
           dbms_output.put_line( a(i).subPartName );
           dbms_output.put_line( a(i).Confidence );
           dbms_output.put_line
                 ( '-------------------------' );
   end loop;
end;
/

12.  File I/O

set pages 99
set lines 200

column name format a50

column sum_io1 new_value st1 noprint
column sum_io2 new_value st2 noprint
column sum_io new_value divide_by noprint
Select  nvl(sum(a.phyrds+a.phywrts),0) sum_io1 from     sys.v_$filestat a;
/
select  nvl(sum(b.phyrds+b.phywrts),0) sum_io2 From            sys.v_$tempstat b;
/
select &st1+&st2 sum_io from dual;
/
ttitle 'File IO Statistics Report'

spool fileio

select file#, name, PHYRDS "PhysReads",    PHYWRTS "PhysWrites",    round(PERCENT,2) "%ofIO",   PHYBLKRD "PhysBlockReads",  PHYBLKWRT "PhysBlockWrites",    round(BRRATIO,2) "Block Read Ratio",    round(BWRATIO,2) "Block Write Ratio" from (
select  a.file#,b.name, a.phyrds, a.phywrts,
        (100*(a.phyrds+a.phywrts)/&divide_by) Percent,
        a.phyblkrd, a.phyblkwrt, (a.phyblkrd/greatest(a.phyrds,1)) brratio,
      (a.phyblkwrt/greatest(a.phywrts,1)) bwratio
from    sys.v_$filestat a, sys.v_$dbfile b
where   a.file#=b.file#
union
select  c.file#,d.name, c.phyrds, c.phywrts,
        (100*(c.phyrds+c.phywrts)/&divide_by) Percent,
        c.phyblkrd, c.phyblkwrt,(c.phyblkrd/greatest(c.phyrds,1)) brratio,
      (c.phyblkwrt/greatest(c.phywrts,1)) bwratio
from    sys.v_$tempstat c, sys.v_$tempfile d
where   c.file#=d.file# )
order by        PERCENT desc ;

spool off

Sql query to locate and destroy Oracle Database Blocking Session

Sql query to  locate  and destroy Oracle Database Blocking Session

  
Locate SID from v$session.

select process,sid, blocking_session from v$session where blocking_session is not null;

Locate the serial number for the Blocking Session to kill using SID

SQL> select SERIAL# from v$session where SID=509;

SERIAL#

1799

Destroy the blocking session using SID and serial number

SQL> alter system kill session ’365,130′;

Changing Oracle EBS 11i Apps Password

Changing Oracle EBS 11i Apps Password
Follow the  below steps

1.Shutdown the environment.
2.Backup tables FND_USER and FND_ORACLE_USERID
3.Execute FNDCPASS to change the apps password.
    FNDCPASS 0 Y SYSTEM
    FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME1

4. Manually modify files such as appsweb.cfg and wdbsvr.app
5. Startup the environment.

Oracle query to find PID TO SID

 Oracle query to find PID TO SID

select sid,serial#,inst_id from gv$session;
select PID, p.PROGRAM
from gv$process p, gv$session s
where s.paddr=p.addr
and sid=;

Learn Oracle RAC with Netapp Simulator

Posting here as one of my lovely friend Felix asked about Oracle RAC .
If you want to learn how Oracle RAC works here is a great tutorial of setting it up posted by Sachin Garg.

Link

The tutorial Lack the netapp Filer installation you can proceed using the below
Link


Below are more links about Oracle RAC

Oracle RAC Introduction Link

If you Need more info the given link got a lot of links for RAC References:
Link

Zenoss Oracle Monitoring

Zenoss Oracle Monitoring

Hi guys i thought i will submit the Zenpack through which i am monitoring Oracle , i wanted to submit this to Zenoss ,but someone already came up with some other zenpack . i am working on getting Oracle Apps monitoring will release next month .


Here are some key Points for installation of this Oracle Zenpack .

Download the below files from these location .

Oracle Script ( Someone gave me the initial Script and i modified it as per my requirement .)

Oracle Zenpack



1. Install oracle Client on your zenoss server . The oracle client install should be with user zenoss so you don’t have issue with oracle library issue . ( use the latest client like 11g , I have seen some bugs on 10g with oci.dll which stop querying the database after 200 days of uptime )

2. Copy the attach script check_health_oracle to $ZENHOME/checkoracle/plugins-scripts and set the executable permission to it .

3. Ask your dba to create a user zenoss and password zenoss . I have set the template with same user and same password you can change it if you like

4. Ask him to grant the below roles

grant create session, connect, select on v_$sysstat, v_$session, v_$log, v_$instance, sys.dba_free_space, sys.dba_data_files to zenoss ;

5. When you define the tnsnames or oracle client set it as below . I have set the tnsnames as the devicename

=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = Oracle_SID)

)

)

6. Install the attach zenpack .

7. this will create a Organizer /device/server/oracle , if you need to attach the same to your Linux boxes just copy the template and copy to linux and attach multiple template to it .

Below things can be monitored using this zenpack .

connected-users

Connection-Time

Data_File_IO_Traffic

Invalid_Objects

PGA-IN-Memory

Redo_IO_Logs

SGA

SGA-cache-hit

SGA-latches-hit

Stale-statics

Tablespace-Usage

RMAN Recovery Methods

RMAN Recovery Methods

Process to follow if your control files are still accessible.

Restoring and Recovering All Datafiles

Assumed that your control files are still accessible. You have a backup, done using command

backup database plus archivelog;

Make sure that the target database is shut down:

#sqlplus “/ as SYSDBA”
SQL> shutdown abort;
ORACLE instance shut down.

You need to start up your target database in mount mode. RMAN cannot restore datafiles unless the database is at least in mount mode, because RMAN needs to be able to access the control file to determine which backup sets are necessary to recover the database. If the control file isn't available, you have to recover it first. Issue the STARTUP MOUNT command shown in the following example to mount the database:

SQL> startup mount;
Oracle instance started.

Database mounted.

Since backup set files are created in an RMAN-specific format, you must use RMAN to restore the datafiles. To use RMAN, connect to the target database:

#rman target / oracle oracle/oracle@test

When the restore command is executed, RMAN will automatically go to its last good backup set and restore the datafiles to the state they were in when that backup set was created.

When restoring database files, RMAN reads the datafile header and makes the determination as to whether the file needs to be restored. The recovery is done by allocating a channel for I/O and then issuing the RMAN restore database command.

There have been major change onwards Oracle9i and above version, you don't need to allocate a channel explicitly. Instead, you can use the default channel mode:

RMAN> restore database;
RMAN> recover database;
SQL> alter database open;

For Oracle 8i

RMAN> run {
allocate channel d1 type disk;
restore database;
recover database;
}
alter database open;

Restoring Specific Tablespaces

Take the tablespace that needs recovery offline, restore the tablespace, recover the tablespace, and bring the tablespace online. If you cannot take the tablespace offline, then shutdown abort the database and restore in mount mode.

First try to take the tablespace offline;

$ sqlplus "/ as sysdba"
SQL> alter tablespace test offline;

If this works, continue with the RMAN recovery:

$ rman target / oracle oracle/oracle@test
RMAN> restore tablespace test;
RMAN> recover tablespace test;
SQL> alter tablespace test online;

If taking the tablespace offline fails, follow these steps:

$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / oracle oracle/oracle@test
RMAN> restore tablespace test;
RMAN> recover tablespace test;
SQL> alter database open;

Restoring Specific Datafiles

Take the datafile that needs recovery offline, restore the datafile, recover the datafile, and bring the datafile online. If you cannot take the datafile offline, then shutdown abort the database and restore in mount mode.

Take the datafile offline:

SQL> alter database datafile '/u01/oracle/db/test/test/dbI1_tab.dbf' offline;

If this works, continue with the RMAN recovery:

$ rman target / oracle oracle/oracle@test

RMAN> restore datafile '/u01/oracle/db/test/test/dbI1_tab.dbf’
RMAN> recover datafile '/u01/oracle/db/test/test/dbI1_tab.dbf'

SQL> alter database datafile '/u01/oracle/db/test/test/dbI1_tab.dbf’ online;

If taking the datafile offline fails, follow these steps:

$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / oracle oracle/oracle@test
RMAN> restore datafile '/u01/oracle/db/test/test/dbI1_tab.dbf’
RMAN> recover datafile '/u01/oracle/db/test/test/dbI1_tab.dbf'
SQL> alter database open;

Restoring Control Files

To recover Control Files follow the below

$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / oracle oracle/oracle@test

Set the database id (DBID) with the following command. The number can be obtained from any previous RMAN backup log file.

RMAN> set dbid = xxxxxxxxxx;

RMAN> restore controlfile;
SQL> alter database mount;
SQL> alter database open;

If this fails with ...

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

... then you must perform a recover database:

SQL> shutdown abort;
SQL> startup mount;
$ rman target / oracle oracle/oracle@test
RMAN> recover database;
SQL> alter database open resetlogs;

RMAN> reset database;

Restoring Online Redologs

Will add latter

Oracle 10g Bug with system Uptime

Oracle 10g Bug with system Uptime

I encounter this issue twice today morning so thought to share with you guys .
Oracle 10g starts acting funny in unix systems making oracle client connection hang and leads to high CPU Utilization and server hung state due to OCI client spins when machine uptime >= 198 days in Linux machines. The user will not be able to connect to the databases.

As per oracle bug doc 4612267 its 248 Days but on RHEL5 (2.6.18-92.el5) i experienced the same in 198 days up-time .