How to check performance of oracle Database through sqlplus commands

Database performance issue check

 

1)   To Check server load

            topas - To know top consuming process and load on server

            nmon and press m to know memory and paging utilization

            vmstat 5 5

            lsps -s To know paging usages

 

Memory Utilization

free

free | grep Mem | awk '{print $3/$2 * 100.0}'

free | grep Mem | awk '{print $4/$2 * 100.0}'

CPU utilization

Top

topas

vmstat 5 5 

select (sga+pga)/1024/1024 as "sga_pga"

from

(select sum(value) sga from v$sga),

(select sum(pga_used_mem) pga from v$process);

 rollback segment

SELECT s.username,

       s.sid,

       s.serial#,

       t.used_ublk,

       t.used_urec,

       rs.segment_name,

       r.rssize,

       r.status

FROM   v$transaction t,

       v$session s,

       v$rollstat r,

       dba_rollback_segs rs

WHERE  s.saddr = t.ses_addr

AND    t.xidusn = r.usn

AND   rs.segment_id = t.xidusn

ORDER BY t.used_ublk DESC

/ 

2) Map  OS processes with database process as below

select s.sql_id,s.sql_hash_value,s.prev_hash_value,s.sql_id,s.sid,s.serial#,s.username,s.event,to_char(s.logon_time,'dd-mon:hh24:mi')  from v$session s, v$process p

where p.addr=s.paddr and p.spid in(1105,4452)

 

3) Take explain plan and check statistics of tables and indexes

 

select * from table(dbms_xplan.display_cursor('&sql_id',null,'AdVanced ALLSTATS LAST'))

select a.owner, a.table_name, a.num_rows, b.bytes/1048576 MB, a.last_analyzed from dba_tables a, dba_segments b 

where  a.owner=b.owner and a.table_name=b.segment_name and a.table_name='&table_name' order by b.bytes desc; 

select count(1),event from v$session group by event;

 

4) Check any long running SQL on database

select sid, serial#, context, sofar, totalwork,round(sofar/totalwork*100,2) "%_complete"  from v$session_longops where sofar <> totalwork;

 

5) Check session level and object level lock on database

Object level locking

------------------------

select a.sid, a.serial#,OBJECT_NAME

from v$session a, v$locked_object b, dba_objects c =

where b.object_id = c.object_id

and a.sid = b.session_id;

or

Select * from (

Select c.owner,c.object_name,

            c.object_type,b.sid,

            b.serial#,b.status,

            b.osuser,b.machine

from v$locked_object a ,v$session b, dba_objects c

where b.sid = a.session_id

and a.object_id = c.object_id);

select p.spid,s.sid,s.serial#,s.username,to_char(s.logon_time,'dd-mon:hh24:mi'),machine,module from v$session s, v$process p

where p.addr=s.paddr and s.sid =369

 

      To kill process at os level

          kill -9 <process>

           kill -9 5747

 

 To kill process at Database level

ALTER SYSTEM KILL SESSION '369,12849' immediate;

SELECT s.sid, s.serial#, s.username, s.osuser, s.status,LAST_CALL_ET,TO_CHAR(LOGON_TIME,'DD-MON-YY HH24:MI:SS')

FROM v$session s, v$process p WHERE s.paddr = p.addr AND p.spid in('10429','10393','10393','10392');

 

   To display the ancestors and children of process 12312,

including children of process 0, enter:

proctree -a 12312

The output of this command might look like this:

1    /etc/init                                  

   4954    /usr/sbin/srcmstr                    

      7224    /usr/sbin/inetd                   

         5958    telnetd -a                     

            13212    -sh                        

               14724    ./proctree -a 13212

 

 Top users, program,modules,machine,sql_id

 

SELECT * FROM (

            SELECT count(*) AS count,USERNAME program, module, machine, sql_id

            FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY, DBA_USERS

 WHERE  DBA_HIST_ACTIVE_SESS_HISTORY.USER_ID=DBA_USERS.USER_ID

            AND session_type='FOREGROUND' and snap_id between 96067 and 96073

 GROUP BY USERNAME, program, module, machine, sql_id

            ORDER BY count(*) DESC

)

WHERE rownum <= 20 

or

SELECT * FROM (

            SELECT count(*) AS count,USERNAME program, module, machine, sql_id

            FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY, DBA_USERS

            WHERE  DBA_HIST_ACTIVE_SESS_HISTORY.USER_ID=DBA_USERS.USER_ID

            AND session_type='FOREGROUND'

            AND  sample_time > sysdate-60/1440

            AND sample_time < sysdate

            --AND event = 'library cache: mutex X'

            GROUP BY USERNAME, program, module, machine, sql_id

            ORDER BY count(*) DESC

)

WHERE rownum <= 20

 

Top temp segments given than a threshold

SELECT * FROM (

            SELECT count(*) AS count,username, program, module, machine, sql_id,sum(temp_space_allocated)

            FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY,DBA_USERS

            WHERE DBA_HIST_ACTIVE_SESS_HISTORY.USER_ID=DBA_USERS.USER_ID

            AND sample_time > sysdate-60/1440

            AND sample_time < sysdate

            AND temp_space_allocated > 100*1024*1024

            GROUP BY USERNAME, program, module, machine, sql_id

  ORDER BY count(*) DESC

)

WHERE rownum <= 20

/

 

SET LINESIZE 500

SET PAGESIZE 1000

SET VERIFY OFF

SELECT b.username,b.sid,b.serial#,username,b.status,b.sql_id,to_char(b.logon_time,'dd-mon:hh24:mi'),b.machine,b.module

FROM   v$sqltext a, v$session b

WHERE  a.address = b.sql_address

AND    a.hash_value = b.sql_hash_value

AND    b.sql_id = 'dt7cud8uwzstp';

select sid,serial#,username,program,machine,module from v$session where sql_id='dt7cud8uwzstp';

select count(1),event from v$session  group by event; 

select s.sql_id,s.sid,s.serial#,s.username,s.event,to_char(s.logon_time,'dd-mon:hh24:mi')  from v$session s, v$process p

where p.addr=s.paddr and p.spid in(4260298,41418786,5570940,3146020,1507632,4587808,4915642,3080460,2425340); 

SELECT s.sid, s.serial#, s.username, s.osuser, s.status,LAST_CALL_ET,TO_CHAR(LOGON_TIME,'DD-MON-YY HH24:MI:SS')

FROM v$session s, v$process p WHERE s.paddr = p.addr AND p.spid in('33423802', '34799798','23789680');

SELECT    p.program,s.module, p.spid, s.saddr, s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.logon_time, s.status,LAST_CALL_ET,TO_CHAR(LOGON_TIME,'DD-MON-YY HH24:MI:SS')   FROM v$session s, v$process p WHERE s.paddr = p.addr AND p.spid in('18705', '18704','18699','18681');

SELECT s.sid, s.serial#, s.username, s.osuser, s.status,LAST_CALL_ET,TO_CHAR(LOGON_TIME,'DD-MON-YY HH24:MI:SS')

FROM v$session s, v$process p WHERE s.paddr = p.addr AND p.spid in('18705', '18704','18699','18681');

To generate explain plan 

            select * from table(dbms_xplan.display_awr('d7dpgqhquy6c4',NULL,NULL,'ALL'));

select * from table(dbms_xplan.display_awr('&sql_id',null,null,'advanced'));

select * from table(dbms_xplan.display_cursor('d7dpgqhquy6c4',null,'AdVanced ALLSTATS LAST'));

 

select * from table(dbms_xplan.display_cursor('&sql_id',null,'AdVanced ALLSTATS LAST'));

select * from table(dbms_xplan.display_awr('&sql_id',null,null,'advanced'));

select * from table(dbms_xplan.display_awr('4jgxkz2msmvgn',null,null,'advanced'));

            select * from table(dbms_xplan.display_awr('&SQL_ID',NULL,NULL,'ALL'));

 

select substr(value_string,1,40) from v$sql_bind_capture where sql_id='dup0ph9mdm3fc';

select substr(value_string,1,40) from DBA_HIST_SQLBIND  where sql_id='dup0ph9mdm3fc';

 

To check Bind variable

select substr(value_string,1,40) from DBA_HIST_SQLBIND  where sql_id='dup0ph9mdm3fc';

 

select sn.END_INTERVAL_TIME,sb.NAME,sb.VALUE_STRING from DBA_HIST_SQLBIND sb,DBA_HIST_SNAPSHOT sn where sb.sql_id='dup0ph9mdm3fc' and sb.WAS_CAPTURED='YES' and sn.snap_id=sb.snap_id order by  sb.snap_id,sb.NAME;

SELECT a.sql_text,b.name,b.position,b.datatype_string,b.value_string  FROM v$sql_bind_capture b,v$sqlarea  a WHERE b.sql_id = 'dup0ph9mdm3fc' AND b.sql_id = a.sql_id;

SELECT a.sql_text  FROM v$sqlarea  a WHERE a.sql_id = 'dup0ph9mdm3fc' ;

 

        Analyze a particular SQL ID and see the trends for the past day

 

select sql_text,a.sql_id,,sid,serial#,username from v$sqlarea a, v$session v where sid in ( ) and a.sql_id=v.sql_id

select sid,serial,status,machine from v$session

select s.sid,s.serial#,s.username,to_char(s.logon_time,'dd-mon:hh24:mi'),s.sql_hash_value,s.prev_hash_value  from v$session s, v$process p

where p.addr=s.paddr and p.spid in (17629,23767,27834);

select sql_text,address,hash_value,sid,serial#,username from v$sqlarea a, vv$session b where hash_value='555131536'  and a.address=b.sql_address;

 

    Analyze a particular SQL ID and see the trends for the past day

select  s.snap_id,

 to_char(s.begin_interval_time,'HH24:MI') c1,

 sql.executions_delta c2,

 sql.buffer_gets_delta c3,

 sql.disk_reads_delta c4,

 sql.iowait_delta c5,

sql.cpu_time_delta c6,

 sql.elapsed_time_delta c7

 from

 dba_hist_sqlstat sql,

 dba_hist_snapshot s

 where

 s.snap_id = sql.snap_id

 and s.begin_interval_time > sysdate -5

 and sql.sql_id='&sqlid'

 order by c7

 /

Do we have multiple plan hash values for the same SQL ID in that case may be changed plan is causing bad performance

 

select SQL_ID

, PLAN_HASH_VALUE

, sum(EXECUTIONS_DELTA) EXECUTIONS

, sum(ROWS_PROCESSED_DELTA) CROWS

, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS

, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS

from DBA_HIST_SQLSTAT

where  SQL_ID in (

'&sqlid')

group by SQL_ID , PLAN_HASH_VALUE

order by SQL_ID, CPU_MINS;