Performance slowness of database issues

1,  Check the load on the database server , identify the process which are taking high resources.

2,  check the application services are running preffered node or not

3,  Check for FRA usage on the database and ask for users are getting any error codes in the application logs.

4,  Check for locks and Blockings in the database.

5,  check for Active and Inactive count.

6,  Check for ongoing transactions in the database.

7,  Check for alert log file for any errors .

8,  Get more information from application for any new code deplyoment or any load in the database.

9,  Check for archive generation growth in the database and check for tablespace usage and also undo and Temp.

10, open the OEM for ongoing transcations and Locks and Blockings in the database.

11, check for queries which are taking long time check the plan hash value.

12, Prepare steps for apply the best plan hash value to sql_id

13, Apply the SQLT to get more recommendations.

14  CPU is normal.

15- No any wait events.

16. Check the stats gatherd date on database and tables involved in the query.

17  Indexes are in usable state.

18, Check the schema sizes whcih are involved in the query .

===========Startup time =============

select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time" from v$instance;

select name,instance_name,open_mode,version,startup_time,status from v$database,v$instance;

select count(*) from v$session where status='ACTIVE';

select count(*) from v$session where status='INACTIVE';

select count(*) from v$session where status='INACTIVE' and username='EDW';

lsnrctl status 

tnsping name 

select count(*) from v$session where status='INACTIVE';

SELECT 'alter system kill session '''|| SID || ',' || serial# ||',@'||inst_id||''' immediate ;' FROM gv$session WHERE Status='INACTIVE'; AND SchemaName like '%&schemaname%';

===============================================================================

Can one see how connections are distributed across the nodes?

===============================================================================

SELECT inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24') "Hour when connected", count(*) "DB Sessions" FROM gv$session WHERE type = 'USER' GROUP BY inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24') ORDER BY inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24');

check the db size.

========================================================================

SQL> select sum(bytes/1024/1024/1024) from dba_data_files;  

============================================================================

Check the used size 

==================================================================================

SQL> select sum(bytes/1024/1024/1024) from dba_segments;

===============================================================================

To identify Sessions which are consuming high CPU at Database Level.

===============================================================================

select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day  from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg where s.paddr=p.addr and ss.sid=s.sid and ss.statistic#=12 and si.sid=s.sid and bg.paddr(+)=p.addr  and round((ss.value/100),0) > 10  order by 8; 

To get session details

==============================================================================================

select SID,SERIAL#,USERNAME,STATUS,MACHINE from v$session where SID=&sid; 

select s.sid || ',' || s.serial# "SID/SERIAL", s.username, s.osuser from v$session s,v$process p Where s.paddr = p.addr and p.spid=&pid;

To get SQL of a particular session.

=============================================================================================================================================

SELECT SQL_TEXT FROM v$sqlarea WHERE hash_value IN (SELECT sql_hash_value FROM v$session WHERE sid=&sid); 

To check long running queries 

========================================================================================================================================

 select a.sid,b.status,rpad(a.opname,15),a.sofar,a.totalwork, to_char(a.START_TIME,'dd-MON-YY HH24:MI') START_TIME, a.TIME_REMAINING,a.ELAPSED_SECONDS,((a.sofar)*100)/a.totalwork "%_completed", rpad(a.username,10) username,a.SQL_HASH_VALUE, B.STATUS from V$SESSION_LONGOPS a, v$session b where a.sid = b.sid and  b.status='ACTIVE' AND a.sofar<> a.totalwork and a.totalwork>0 ;

 Sessions since last 5 hours

========================================================================================================================================

column dur format a20  

select sid, serial#, username,to_char(sysdate-last_call_et/24/60/60,'hh24:mi:ss') started,trunc(last_call_et/60) || ' mins, ' || mod(last_call_et,60) || ' secs' dur,(select sql_text from v$sql where address = sql_address ) sql_text from v$session where username is not null and last_call_et > 5*60 and status = 'ACTIVE';

========Archive log generation On daily basis:in gb and mb===========================================

SELECT TRUNC(completion_time) AS time, ROUND(SUM(blocks * block_size)/1024/1024,2) AS size_mb, ROUND(SUM(blocks * block_size)/1024/1024/1024,2) AS size_gb,count(*) Archives_Generated FROM v$archived_log GROUP BY TRUNC(completion_time) ORDER BY 1 DESC;

----Undo Usage

===============================================================================

select ( select sum(bytes)/1024/1024 from dba_data_files where tablespace_name like 'UND%') allocated,(select sum(bytes)/1024/1024 from dba_free_space where tablespace_name like 'UND%') free,(select sum(bytes)/1024/1024 from dba_undo_extents where tablespace_name like 'UND%') USed from dual 

/

=========================

----Undo Used by session

=========================================

select s.sid,s.username,t.used_urec,t.used_ublk from v$session s,v$transaction t where s.saddr=t.ses_addr and s.sid=107 order by t.used_ublk desc;

========sessions used memory.==============

Use this Query to find any inactive sessions used memory. If they used and u sure about the session is no longer needed then kill the session.

select s.sid, n.name, s.value from v$sesstat s,v$statname n,v$session sess where n.statistic# = s.statistic# and s.sid = sess.sid and sess.status = 'INACTIVE' and n.name like '%memory%' and n.name not like 'sorts (memory)' order by s.sid;

select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in ('processes','session','transactions');

Database startup_time

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

set pages 9999 lines 300

col OPEN_MODE for a10

col HOST_NAME for a15

col Instance_start_time for a20

col CURRENT_TIME for a20

col DATABASE_STATUS for a15

col DB_VERSION for a10

col logins for a10

col DATABASE_ROLE for a10

col INSTANCE_NAME for a10

select inst_id, name DB_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,version DB_VERSION,DATABASE_STATUS,DATABASE_ROLE,LOGINS,log_mode,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:mi:SS') "Instance_start_time", to_char(sysdate,'DD-MON-YYYY HH24:mi:SS') "CURRENT_TIME" from v$database,gv$instance;

18, Check the schema sizes whcih are involved in the query ,Query to check schema owner size in Oracle:

==========================================================

SELECT sum(bytes)/1024/1024/1024 as "Size in GB" from dba_segments WHERE owner = UPPER('&schema_name'); 

select owner,sum(bytes)/1024/1024/1024 as "Size in GB" from dba_segments group by owner;