Skip to main content

SQL query to get the sessions causing high CPU usage on database (DB) server

If you are experiencing the high cpu usage on database (DB) server, you can use the below queries to track the session and sql details which are consuming high cpu resources in oracle database server.

 sessions based on cpu usage 1:
************************************
set pages 1000
set lines 1000
col OSPID for a06
col SID for 99999
col SERIAL# for 999999
col SQL_ID for a14
col USERNAME for a15
col PROGRAM for a23
col MODULE for a18
col OSUSER for a10
col MACHINE for a25
select * from (
select p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.MACHINE,ss.status,ss.BLOCKING_SESSION blk_sid,ss.BLOCKING_SESSION_STATUS blk_status,
se.VALUE/100 cpu_usage_sec,ss.event
from
v$session ss,
v$sesstat se,
v$statname sn,
v$process p
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and ss.username !='SYS' and
ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc);

 ********************************************************************************************************

 This one shows SQL that is currently "ACTIVE":
******************************************************
set pages 10000
set lines 1000
col SID for a05
col SERIAL for a06
col Program for a15
col event for a28
col Username for a15
col SQL_TEXT for a65
select substr(v2.sid,1,5) "SID",substr(v2.serial#,1,5) "SERIAL",substr(v2.username,1,15) "Username"
,substr(program,1,45) "Program",v2.event event,v1.sql_id,sql_text from sys.v_$sqlarea v1,
sys.v_$session v2 where v1.address= v2.sql_address and v1.hash_value = v2.sql_hash_value order by 1 asc;

 ********************************************************************************************************

 This one shows SQL that is currently "ACTIVE" for the specific user:
*****************************************************************************
set pages 10000
set lines 1000
col SID for a05
col SERIAL for a06
col Program for a45
col Username for a15
col event for a28
col SQL_TEXT for a30
select substr(v2.sid,1,5) "SID",substr(v2.serial#,1,5) "SERIAL",substr(v2.username,1,15) "Username"
,substr(program,1,45) "Program",v2.status,v2.event,v1.sql_id,sql_text from sys.v_$sqlarea v1,
sys.v_$session v2 where v1.address= v2.sql_address and
v1.hash_value = v2.sql_hash_value and v2.username='&user_name' order by 1 asc;

 ********************************************************************************************************

 If you have OS Process id
******************************

 set pages 100
set lines 1000
col SPID heading 'PID' for a06
col SID for 99999
col SERIAL# heading 'serial' for 999999
col SQL_ID for a14
col USERNAME for a10
col PROGRAM for a20
col MODULE for a18
col OSUSER for a07
col MACHINE for a20
select p.spid,s.sid,s.serial#,s.sql_id,s.username,s.status,s.program,s.module,s.osuser,s.machine,s.event
from v$session s, v$process p where s.paddr=p.addr and p.spid=&spid;
finding long operations:
**************************
This is a good one for finding long operations (e.g. full table scans). If it is because of lots
of short operations, nothing will show up.

 set lines 1000
COLUMN percent FORMAT 999.99
col sid for 9999
col start_time for a20
col message for a45
SELECT sid,SERIAL#,SQL_ID,to_char(start_time,'dd-mm-yy hh24:mi:ss') start_time,
ELAPSED_SECONDS/60 Elapsed_Mins,TIME_REMAINING/60 "TIME_REMAINING_Mins" ,message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1 and totalwork!=0
/
To check the full SQL statement for a sql_id
**************************************************

 set pages 50000
col SQL_TEXT for a75
select sql_text from v$sqltext where sql_id ='&sql_id' order by piece;

Comments

Post a Comment

Popular posts from this blog

Pinning execution plan for a sql_id using 'SQL Profile' to consistently reproduce a good plan

Deal all, Below post content is not my own. It is taken from MOSC document but the example shown below is taken from our environment. I got to know the below topic when we had experienced the bad SQL query performance in one of our production environment during daily batch running time. Hence we had implemented the same mentioned in the title "Pinning execution plan for a sql_id using 'SQL Profile' to consistently reproduce a good plan". Introduction The "cost-based optimizer (CBO or just optimizer) is the part of database software that determines the most efficient means of accessing the specified data (or at least what it "calculates" as the most efficient). The optimizer determines which execution plan is most efficient by considering several sources of information, including query conditions, available access paths, statistics gathered for the system, and hints. Some changes in those conditions may affect the calculations of the plan...
9 comments
Read more

Export and import multiple schema using expdp/impdp (Data Pump utility)

Use the below sql query to export and import multiple schema: expdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp exclude=statistics logfile=expdpschemas120514.log impdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp logfile=impdpschemas120514.log sql query to export and import a schema: expdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp exclude=statistics logfile=expdpschema120514.log impdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp logfile=expdpschema120514.log Parameter STATISTICS=NONE can either be used in export or import. No need to use the parameter in both. To export meta data only to get ddl of the schemas: expdp schemas=schema1,schema2,schema3 directory=TEST_DIR dumpfile=content.dat content=METADATA_ONLY exclude=statistics To get the DDL in a text file: impdp directory=TEST_DIR sqlfile=sql.dat logfile=sql.log dumpfil...
3 comments
Read more

Cloud Control EM13c - Loader Throughput (rows per second) for Loader_D crossed the critical threshold and Total Loader Runtime in the Last Hour (seconds) for Loader_D crossed the critical threshold

Hello Guys, Here is an another post related to Cloud Control EM13c - Real-time Scenario. Suddenly We were receiving the following warning and critical alerts from EM13c Cloud Control during the time slot between 12 AM and 04:00 AM daily. 1)  Message= Loader Throughput (rows per second) for Loader_D crossed the critical threshold (xx). Current value: xx.xx 2)  Message= Total Loader Runtime in the Last Hour (seconds) for Loader_D crossed the critical threshold (x,xxx). Current value: xxxx.xx 3)  ORA-error stack (3,136) and ORA-error stack (609) were also logged in alert log 4)  Message= Incident (BEA-310003 [LowMemory]) detected in $OMS_HOME/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/adr/diag/ofm/GCDomain/EMGC_OMS1/alert/log.xml at time/line number: When we analyzed the AWR reports of EM PDB repository database, we found there were few PL/SQL statements given below causing this issue with wait event  SQL*Net Break/reset To Client...
Post a Comment
Read more