All reports in one script

Its like a doctor script. My suggestion is to don’t directly run all the scripts in prod it will make some performance issue. Use the specified query and execute step by step. Its easy to figure out your issues.

"
------------------------------------------------------------------------------------------------------------------------------------------------------
---
--- one script to Identify all performace issue related to sql query
------
--
--
------------------------------------------------------------------------------------------------------------------------------------------------------

Set echo off
set trimspool on
set define on

column filename new_val filename
select to_char(sysdate, 'yyyymmdd-hh-mi-ss' ) filename from dual;
column dbname new_value dbname noprint
select name dbname from v$pdbs;
spool &dbname-&filename..txt

PROMPT ==================
PROMPT DB INFO
PROMPT ==================

set lines 750 pages 9999
select name CDB_NAME,(select name from v$pdbs) PDB_NAME,database_role from v$database;
select INSTANCE_NAME,HOST_NAME,logins,VERSION from v$instance;

PROMPT ==================
PROMPT TOTAL Connections
PROMPT ==================

set lines 750 pages 9999
break on report
compute SUM of tot on report
compute SUM of active on report
compute SUM of inactive on report
col username for a50
select DECODE(username,NULL,'INTERNAL',USERNAME) Username,
count(*) TOT,
COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE,
COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE
from gv$session
where status in ('ACTIVE','INACTIVE')
group by username;

PROMPT ================
PROMPT Session Details
PROMPT ================

set linesize 750 pages 9999
column box format a30
col serial# for 999999
column spid format a10
column username format a30
column program format a30
column os_user format a20
col LOGON_TIME for a20

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
substr(b.username,1,30) username,
substr(b.osuser,1,20) os_user,
substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id
from gv$session b,gv$process a
where b.paddr = a.addr
and a.inst_id = b.inst_id
and type='USER'
order by b.inst_id,b.sid;

PROMPT ================
PROMPT Sql Details
PROMPT ================

column sid format 9999
column username format a15
column PARSING_SCHEMA_NAME format a15
column sql_text format a50
column module format a35
select a.inst_id,a.sid,a.username,b.PARSING_SCHEMA_NAME,a.module,a.sql_id,a.sql_child_number child,b.hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,(sysdate-sql_exec_start)*24*60*60 SECS,b.rows_processed,a.status,substr(b.sql_text,1,50) sql_text
from gv$session a,gv$sqlarea b
where a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.module not like '%emagent%'
and a.module not like '%oraagent.bin%'
and a.username is not null
order by a.status;

PROMPT =========================
PROMPT Sql Monitor - REPORT
PROMPT =========================

column text_line format a1000
set lines 750 pages 9999
set long 20000 longchunksize 20000
select dbms_sqltune.report_sql_monitor_list() text_line from dual;

PROMPT =========================
PROMPT Sql Monitor - Executing
PROMPT =========================

set lines 1000 pages 9999
column sid format 9999
column serial for 999999
column status format a15
column username format a10
column sql_text format a80
column module format a30
col program for a30
col SQL_EXEC_START for a20

SELECT * FROM
(SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE,
program,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000) AS "CPU (s)",
substr(sql_text,1,30) sql_text
FROM gv$sql_monitor where status='EXECUTING' and module not like '%emagent%'
ORDER BY sql_exec_start desc
);

PROMPT ================
PROMPT Blocking Session
PROMPT ================

set lines 750 pages 9999
col blocking_status for a100
select s1.inst_id,s2.inst_id,s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s1.inst_id;

PROMPT ==============================================================================
PROMPT More Details
PROMPT ==============================================================================

accept sid default '' -
prompt 'Please provide the sid: '

accept inst_id default '' -
prompt 'Please provide the inst_id: '

PROMPT ================
PROMPT SID Details
PROMPT ================

column box format a30
column spid format a10
column username format a20
column program format a30
column os_user format a20
col LOGON_TIME for a20

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
substr(b.username,1,20) username,
substr(b.osuser,1,20) os_user,
substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id
from gv$session b,gv$process a
where b.paddr = a.addr
and a.inst_id = b.inst_id
and type='USER' and b.sid=&sid
and b.inst_id=&inst_id
order by logon_time;




column sid format 9999
column username format a15
column PARSING_SCHEMA_NAME format a15
column sql_text format a50
column module format a35
select a.inst_id,a.sid,a.username,b.PARSING_SCHEMA_NAME,a.module,a.sql_id,a.sql_child_number child,b.hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,(sysdate-sql_exec_start)*24*60*60 SECS,b.rows_processed,a.status,substr(b.sql_text,1,50) sql_text
from gv$session a,gv$sqlarea b
where a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.sid=&sid
and a.inst_id=&inst_id
and a.module not like '%emagent%'
and a.module not like '%oraagent.bin%'
and a.username is not null
order by a.status;

accept sql_id default '' -
prompt 'Please provide the sql_id: '

PROMPT ================
PROMPT SID Waiting on
PROMPT ================

COLUMN username FORMAT A20
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 999999
COLUMN event FORMAT A40

SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
se.event,
se.total_waits,
se.total_timeouts,
se.time_waited,
se.average_wait,
se.max_wait,
se.time_waited_micro
FROM gv$session_event se,
gv$session s
WHERE s.sid = se.sid
AND s.sid = &sid
and s.inst_id=se.inst_id
and s.inst_id=&inst_id
ORDER BY se.time_waited DESC
/

col WAIT_CLASS for a10
SELECT sw.inst_id,NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM gv$session_wait sw,
gv$session s
WHERE s.sid = sw.sid and s.inst_id=sw.inst_id and s.sid=&sid
and s.inst_id=&inst_id
ORDER BY sw.seconds_in_wait DESC;

PROMPT ================
PROMPT Session LongOps
PROMPT ================

SET VERIFY OFF

SELECT a.sid,RPAD(a.opname,30),a.sofar,a.totalwork,a.ELAPSED_SECONDS,ROUND(((a.sofar)*100)/a.totalwork,3) "%_COMPLETED",time_remaining,
RPAD(a.username,10) username,a.SQL_HASH_VALUE,B.STATUS
FROM GV$SESSION_LONGOPS a, gv$session b
WHERE a.sid=&sid
and b.inst_id=&inst_id
AND a.sofar<> a.totalwork
/

PROMPT ==============================
PROMPT RealTime Monitoring For Sid
PROMPT ===============================

SELECT *
FROM
(SELECT status,
--username,
sql_id,
sql_exec_id,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000) AS "CPU (s)",
buffer_gets,
ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)",
ROUND(physical_write_bytes/(1024*1024)) AS "Phys writes (MB)"
FROM gv$sql_monitor where sid=&sid and inst_id=&inst_id
ORDER BY elapsed_time DESC
)
WHERE rownum<=20;

SELECT ROUND(elapsed_time /1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000,3) AS "CPU (s)",
ROUND(queuing_time /1000000,3) AS "Queuing (s)",
ROUND(user_io_wait_time /1000000,3) AS "I/O wait (s)",
ROUND(application_wait_time/1000000,3) AS "Appli wait (s)",
ROUND(concurrency_wait_time/1000000,3) AS "Concurrency wait (s)",
ROUND(cluster_wait_time /1000000,3) AS "Cluster wait (s)",
ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)",
ROUND(physical_write_bytes /(1024*1024)) AS "Phys writes (MB)",
buffer_gets AS "Buffer gets",
ROUND(plsql_exec_time/1000000,3) AS "Plsql exec (s)",
ROUND(java_exec_time /1000000,3) AS "Java exec (s)"
FROM gv$sql_monitor
WHERE sid=&sid and inst_id=&inst_id;

PROMPT ================================
PROMPT SQL_MONITOR REPORT
PROMPT ================================

set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000
select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id=>'&sql_id',
report_level=>'ALL',
type=>'TEXT')
from dual;

PROMPT ================================
PROMPT Full sql statement
PROMPT ================================

set lines 1000 pages 9999
set long 20000
col sql_text for a500
select sql_text from dba_hist_sqltext where sql_id = '&sql_id';

PROMPT ================
PROMPT Bind Variables
PROMPT ================

col VALUE_STRING for a50
SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM gv$sql_bind_capture WHERE sql_id='&sql_id' and inst_id=&inst_id;

PROMPT ================
PROMPT SQL History
PROMPT ================

set lines 1000 pages 9999
COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';
SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,
TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions_total,
TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
FROM dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE h.sql_id = '&sql_id'
AND h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
UNION ALL
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions,
TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,
TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,
TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,
TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,
TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,
TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,
TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,
TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,
TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec
FROM gv$sqlarea_plan_hash h
WHERE h.sql_id = '&sql_id'
and h.inst_id=&inst_id
AND h.executions > 0
order by source ;

PROMPT ================================
PROMPT Sql_id waiting on ?
PROMPT ================================

select
sql_id,event,
time_waited "time_waited(s)",
case when time_waited = 0 then
0
else
round(time_waited*100 / sum(time_waited) Over(), 2)
end "percentage"
from
(
select sql_id,event, sum(time_waited) time_waited
from gv$active_session_history
where sql_id = '&sql_id' and
inst_id=&inst_id
group by sql_id,event
)
order by
time_waited desc;

PROMPT ================================
PROMPT Object Statistics
PROMPT ================================

col table_name for a40
col owner for a30
select distinct owner, table_name, STALE_STATS, last_analyzed, stattype_locked
from dba_tab_statistics
where (owner, table_name) in
(select distinct owner, table_name
from dba_tables
where ( table_name)
in ( select object_name
from gv$sql_plan
where upper(sql_id) = upper('&sql_id') and inst_id=&inst_id and object_name is not null))
--and STALE_STATS='YES'
/

col index_name for a50
SELECT owner, index_name, table_name,last_analyzed, sample_size, num_rows, partitioned, global_stats
FROM dba_indexes
WHERE index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
ORDER BY owner, table_name, index_name
/

PROMPT ================================
PROMPT Explain Plan from Memory
PROMPT ================================

col PLAN FOR a150
SELECT
RPAD('(' || p.plan_line_ID || ' ' || NVL(p.plan_parent_id,'0') || ')',8) || '|' ||
RPAD(LPAD (' ', 2*p.plan_DEPTH) || p.plan_operation || ' ' || p.plan_options,60,'.') ||
NVL2(p.plan_object_owner||p.plan_object_name, '(' || p.plan_object_owner|| '.' || p.plan_object_name || ') ', '') ||
NVL2(p.plan_COST,'Cost:' || p.plan_COST,'') || ' ' ||
NVL2(p.plan_bytes||p.plan_CARDINALITY,'(' || p.plan_bytes || ' bytes, ' || p.plan_CARDINALITY || ' rows)','') || ' ' ||
NVL2(p.plan_partition_start || p.plan_partition_stop,' PStart:' || p.plan_partition_start || ' PStop:' || p.plan_partition_stop,'') ||
NVL2(p.plan_time, p.plan_time || '(s)','') AS PLAN
FROM gv$sql_plan_monitor p
WHERE sid=&sid
and p.inst_id=&inst_id
ORDER BY p.plan_line_id, p.plan_parent_id;

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

PROMPT ================================
PROMPT Explain Plan from AWR
PROMPT ================================

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

PROMPT ================================
PROMPT Sql Profiles
PROMPT ================================

set lines 1000 pages 9999
col name for a30
col task_exec_name for a16
col category for a10
col created for a30
col sql_text for a150
col signature for 9999999999999999999999999

select sql.sql_id,sql.child_number as child , prof.name, prof.category, prof.created,prof.task_exec_name,prof.FORCE_MATCHING,prof.status,prof.SIGNATURE
from
dba_sql_profiles prof,
gv$sql sql
where sql.sql_id in ('&sql_id')
-- and sql.child_number=child_number
-- and sql.force_matching_signature=prof.SIGNATURE
order by
created;

PROMPT ================
PROMPT Sql Baselines
PROMPT ================

col SQL_HANDLE for a30
col origin for a16
col last_modified for a30
col last_verified for a30

select sql_handle, plan_name, origin, created, last_modified, last_verified,ENABLED,ACCEPTED,FIXED,REPRODUCED
from dba_sql_plan_baselines
where signature in (select force_matching_signature
from gv$sql
where sql_id='&sql_id'
and inst_id=&inst_id);

undef sid
undef sql_id
undef inst_id
spool off;

"

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

SRVCTL command in RAC in 19c Database

Oracle RAC Administration and Monitoring Scripts -2

There are lots of options used with SRVCTL command, i mentioned them below.

-d Database Name
-i Instance Name
-s Service Name
-n Node Name
-r Preferred list
-a Available list
-p TAF (Transparent application failover policy)
-v Verbose

Status and Config Database

We can check all instance status of database as follows.

srvctl status database -d DB_NAME

[oracle@RAMdbrac01 ~]$ srvctl status database -d RAM
Instance RAM1 is running on node RAMdbrac01
Instance RAM2 is running on node RAMdbadm02
Instance RAM3 is running on node RAMdbadm03
Instance RAM4 is running on node RAMdbadm04
[oracle@RAMdbrac01 ~]$

We can display the configurations (instances name, nodes and oracle home) of any RAC Database as follows.

srvctl config database -d DB_NAME

srvctl config database -d RAM

Shutdown RAC Database

We can shutdown all instances of any database as follows.

srvctl stop database -d db_name [-o stop_options]
srvctl stop database -d DB_NAME
srvctl stop database -d DB_NAME -o normal
srvctl stop database -d DB_NAME -o immediate
srvctl stop database -d DB_NAME -o transactional
srvctl stop database -d DB_NAME -o abort

[oracle@RAMdbrac01 ~]$ srvctl stop database -d RAM

Startup RAC Database

We can start all instances of any database as follows.

srvctl start database -d db_name [-o start_options]

srvctl start database -d DB_NAME

srvctl start database -d DB_NAME -o nomount
srvctl start database -d DB_NAME -o mount
srvctl start database -d DB_NAME -o open

[oracle@RAMdbrac01 ~]$ srvctl start database -d RAM

Shutdown An Instance

We can shutdown only specific Instance of RAC database as follows.

srvctl stop instance -d db_unique_name [-i "instance_name_list"]} [-o stop_options] [-f]

srvctl stop instance -d DB_NAME -i INSTANCE_NAME

[oracle@RAMdbrac01 ~]$ srvctl stop instance -d RAM -i RAM1

Startup and Status An Instance

We can startup only specific Instance(s) of RAC database as follows.

srvctl start instance -d db_unique_name [-i "instance_name_list"} [-o start_options]

srvctl start instance -d DB_NAME -i INSTANCE_NAME

[oracle@RAMdbrac01 ~]$ srvctl start instance -d RAM -i RAM1

We can display any instance of RAC database as follows.

srvctl status instance -d DB_NAME -i INSTANCE_NAME

[oracle@RAMdbrac01 ~]$ srvctl status instance -d RAM -i RAM1

Add and Remove Database from CRS

We can add new DB to CRS as follows.

srvctl add database -d db_unique_name -o ORACLE_HOME [-p spfile]

srvctl add database -d RAM -o /u01/app/product/12.1.0.2/dbhome_1 -p +DATAC1/RAM/parameterfile/spfileRAM.ora

We can remove existing DB from CRS as follows.

srvctl remove database -d db_unique_name [-f] [-y] [-v]

srvctl remove database -d DB_NAME -f -y

Stop, Start and Check ASM via SRVCTL

We can shutdown an ASM instance as follows.

srvctl stop asm -n NODE_NAME -o immediate

srvctl stop asm -n RAMdbrac01 -o immediate

We can startup an ASM instance as follows.

srvctl start asm -n NODE_NAME

srvctl start asm -n RAMdbrac01

We can display configuration and status an ASM instance as follows.

srvctl status asm -n NODE_NAME
srvctl config asm -n NODE_NAME

[oracle@RAMdbrac01 ~]$ srvctl status asm -n RAMdbrac01
ASM is running on RAMdbrac01
[oracle@RAMdbrac01 ~]$
[oracle@RAMdbrac01 ~]$
[oracle@RAMdbrac01 ~]$ srvctl config asm -n RAMdbrac01
PRKO-2207 : Warning:-node option has been deprecated and will be ignored.
ASM home: <CRS home>
Password file: +DBFS_DG/orapwASM
ASM listener: LISTENER
[oracle@RAMdbrac01 ~]$

Stop, Start and Check Service via SRVCTL

We can start, stop and check any service on Database as follows.

srvctl status service -d db_name
srvctl config service -d db_name
srvctl start service -d dbname -s service_name
srvctl stop service -d dbname -s service_name

srvctl add service -d {DB_NAME} -s {SERVICE_NAME} -r {"preferred_list"} -a {"available_list"} [-P {BASIC | NONE | PRECONNECT}]

srvctl relocate service -d db_name -s service_name -i instance_name -t new_instance_name [-f]

We can remove any service as follows.

srvctl remove service -d {DB_NAME} -s {SERVICE_NAME}

srvctl remove service -d RAM -s RAM_SALES

Enable/disable Autorestart of Instance and Database

We can enable and disable auto restart of the database as follows.

srvctl enable database -d DB_UNIQUE_NAME
srvctl disable database -d DB_UNIQUE_NAME

We can enable and disable auto restart of the instance

srvctl enable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME
srvctl disable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME

Start, Stop and Check status,configurations of NodeApps

srvctl status nodeapps -n nodename
srvctl config nodeapps -n nodename
srvctl start nodeapps -n nodename
srvctl stop nodeapps -n nodename

History of executed queries

The view v$sql contains almost of queries which are executed in your Oracle Database. I attached few sql queries which will be useful to check history of SQL.

1. Get latest query

select sql_txt from v$sql where first_load_time=(select max(1st_load_time) from v$sql);

2. Sort executed queries by load time

select sql_text, first_load_time from v$sql order by first_load_time desc;

3. Get executed queries in a schema which have special text and sort by load time

select * from v$sql where parsing_schema_name like 'YOUR_SCHEMA_DETAILS' and 
sql_text like '%YOUR_SQLTEXT%' order by first_load_time desc;

4. Get 50 last executed queries

select sql_fulltext from (select * from v$sql where parsing_schema_name 
like 'ORAAGENT' order by first_load_time desc) where rownum < 51;

5. Get 50 executed UPDATE or DELETE queries in a specific time period and sort by load time

select sql_text,sql_fulltext, first_load_time, parsing_schema_name from
(
  select * from v$sql   where parsing_schema_name like 'YOUR_SCHEMA'
    and (sql_text like '%UPDATE %' or sql_text like '%INSERT %') 
and to_timestamp(first_load_time, 'YYYY-MM-DD/HH24:MI:SS') > 
to_timestamp('2021-01-24/09:06:00', 'YYYY-MM-DD/HH24:MI:SS')
  order by first_load_time desc
)
where rownum < 51

You can create your own queries to find out what queries you need to check. Remember this view v$sql doesn’t store prepared statements.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

History of executed queries

The view v$sql contains almost of queries which are executed in your Oracle Database. I attached few sql queries which will be useful to check history of SQL.

1. Get latest query

select sql_txt from v$sql where first_load_time=(select max(1st_load_time) from v$sql);

2. Sort executed queries by load time

select sql_text, first_load_time from v$sql order by first_load_time desc;

3. Get executed queries in a schema which have special text and sort by load time

select * from v$sql where parsing_schema_name like 'YOUR_SCHEMA_DETAILS' and 
sql_text like '%YOUR_SQLTEXT%' order by first_load_time desc;

4. Get 50 last executed queries

select sql_fulltext from (select * from v$sql where parsing_schema_name 
like 'ORAAGENT' order by first_load_time desc) where rownum < 51;

5. Get 50 executed UPDATE or DELETE queries in a specific time period and sort by load time

select sql_text,sql_fulltext, first_load_time, parsing_schema_name from
(
  select * from v$sql   where parsing_schema_name like 'YOUR_SCHEMA'
    and (sql_text like '%UPDATE %' or sql_text like '%INSERT %') 
and to_timestamp(first_load_time, 'YYYY-MM-DD/HH24:MI:SS') > 
to_timestamp('2021-01-24/09:06:00', 'YYYY-MM-DD/HH24:MI:SS')
  order by first_load_time desc
)
where rownum < 51

You can create your own queries to find out what queries you need to check. Remember this view v$sql doesn’t store prepared statements.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Find sql history and statistics

SQL History:

All in one:

set lines 1000 pages 9999 COL instance_number FOR 9999 HEA 'Inst'; COL end_time HEA 'End Time'; COL plan_hash_value HEA 'Plan|Hash Value'; COL executions_total FOR 999,999 HEA 'Execs|Total'; COL rows_per_exec HEA 'Rows Per Exec'; COL et_secs_per_exec HEA 'Elap Secs|Per Exec'; COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec'; COL io_secs_per_exec HEA 'IO Secs|Per Exec'; COL cl_secs_per_exec HEA 'Clus Secs|Per Exec'; COL ap_secs_per_exec HEA 'App Secs|Per Exec'; COL cc_secs_per_exec HEA 'Conc Secs|Per Exec'; COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec'; COL ja_secs_per_exec HEA 'Java Secs|Per Exec'; SELECT 'gv$dba_hist_sqlstat' source,h.instance_number, TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time, TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time, h.sql_id, h.plan_hash_value, h.executions_total, TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec, TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec, TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec, TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec, TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec, TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec, TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec, TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec, TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec FROM dba_hist_sqlstat h, dba_hist_snapshot s WHERE h.sql_id = '&sql_id' AND h.executions_total > 0 AND s.snap_id = h.snap_id AND s.dbid = h.dbid AND s.instance_number = h.instance_number UNION ALL SELECT 'gv$sqlarea_plan_hash' source,h.inst_id, TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time, TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time, h.sql_id, h.plan_hash_value, h.executions, TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec, TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec, TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec, TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec, TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec, TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec, TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec, TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec, TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec FROM gv$sqlarea_plan_hash h WHERE h.sql_id = '&&sql_id' AND h.executions > 0 order by source ;

AWR DATA:

AWR-LIO:

col execs for 999,999,999 col avg_etime for 999,999 col avg_lio for 999,999,999 col avg_pio for 999,999,999 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, ROUND(disk_reads_delta/DECODE(executions_delta,0,1, executions_delta),1) avg_pio, ROUND(rows_processed_delta/DECODE(executions_delta,0, 1, executions_delta), 1) avg_rows, round(px_servers_execs_delta/decode(executions_delta,0,1, executions_delta), 1) avg_px from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = nvl('&sql_id','4dqs2k5tynk61') and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3 /

To find which is using Current memory:

set linesize 999 col avg_et_secs justify right format 9999999.99 col cost justify right format 9999999999 col timestamp justify center format a25 col parsing_schema_name justify center format a30 col inst_id format 999999999 alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss'; select 'gv$sqlarea_plan_hash' source, INST_ID, SQL_ID, PLAN_HASH_VALUE, round(elapsed_time/decode(nvl(executions,0),0,1,executions)/1e6/ decode(px_servers_executions,0,1,px_servers_executions)/decode(nvl(executions,0),0,1,executions),2) avg_et_secs, px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px, optimizer_cost cost, LAST_LOAD_TIME timestamp, parsing_schema_name --FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SQL_PROFILE from gv$sqlarea_plan_hash where sql_id = nvl(trim('&sql_id'),sql_id) UNION SELECT 'dba_hist_sql_plan' source, null INST_ID, t1.sql_id sql_id, t1.plan_hash_value plan_hash_value, t2.avg_et_secs avg_et_secs, t2.avg_px, t1.cost cost, t1.timestamp timestamp, NULL parsing_schema_name FROM dba_hist_sql_plan t1, ( SELECT sql_id, plan_hash_value, --round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6,2) avg_et_secs round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6/ decode(SUM(px_servers_execs_total),0,1,SUM(px_servers_execs_total))/decode(SUM(executions_total),0,1,SUM(executions_total)),2) avg_et_secs, SUM(px_servers_execs_total)/decode(SUM(executions_total),0,1,SUM(executions_total)) avg_px FROM dba_hist_sqlstat WHERE executions_total > 0 GROUP BY sql_id, plan_hash_value ) t2 WHERE t1.sql_id = nvl(TRIM('&sql_id'), t1.sql_id) AND t1.depth = 0 AND t1.sql_id = t2.sql_id(+) AND t1.plan_hash_value = t2.plan_hash_value(+) order by avg_et_secs, cost /

Identify the Culprit: SQL , SQL TEXT , BIND VARIABLE

col exact_matching_signature for 99999999999999999999999999 col sql_text for a50 select sql_id, exact_matching_signature, SQL_TEXT from v$sqlarea where UPPER(sql_text) like '%DUMMY%' order by UPPER(sql_text);

set long 20000 set lines 750 pages 9999 select sql_text from dba_hist_sqltext where sql_id = '&SQL_ID';

set long 20000 set lines 750 pages 9999 select sql_text from gv$sqlarea where sql_id = '&SQL_ID';

col VALUE_STRING for a50 SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM gv$sql_bind_capture WHERE sql_id='&sql_id';

statistics of objects of a specific sql id:

set lines 300 set pages 300 col table_name for a40 col owner for a30 select distinct owner, table_name, STALE_STATS, last_analyzed, stattype_locked from dba_tab_statistics where (owner, table_name) in (select distinct owner, table_name from dba_tables where ( table_name) in ( select object_name from gv$sql_plan where upper(sql_id) = upper('&sql_id') and object_name is not null)) --and STALE_STATS='YES' /

col index_name for a50 SELECT owner, index_name, table_name,last_analyzed, sample_size, num_rows, partitioned, global_stats FROM dba_indexes WHERE index_name IN ( select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ') from ( SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', null, 'BASIC')) UNION ALL SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null, 'ALL')) ) where plan_table_output like '%INDEX%' ) ORDER BY owner, table_name, index_name /

Sql id current rows, I/O , read/wrire details:

select SQL_ID,PLAN_HASH_VALUE,ELAPSED_TIME,PHYSICAL_READ_BYTES,PHYSICAL_WRITE_BYTES,DISK_READS,DIRECT_WRITES,BUFFER_GETS,ROWS_PROCESSED from v$sqlstats where sql_id='&sql_id';

select sql_text,rows_processed from v$sql where USERS_EXECUTING>0;

select s.sid, s.serial#, p.spid, s.username, s.program, t.xidusn, t.used_ublk, t.used_urec, sa.sql_text from v$process p,v$session s, v$sqlarea sa, v$transaction t where s.paddr=p.addr and s.taddr=t.addr and s.sql_address=sa.address(+) and s.sql_hash_value=sa.hash_value(+) order by s.sid /

exec dbms_stats.flush_database_monitoring_info; select inserts,updates,deletes from user_tab_modifications where table_name = '&table_name';

Check the progress of DML statements:

col sql_text for a60 SELECT rows_processed "Total Rows Processed", ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)", TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min", TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec" , SUBSTR(sql_text, 1, 60) sql_text FROM gv$sqlarea WHERE SQL_ID='&SQL_ID' AND open_versions > 0 AND rows_processed > 0;

Detailed history of SQL_ID:

select instance_number inst_id,SESSION_ID,USER_ID,PROGRAM,sql_id,SQL_CHILD_NUMBER,sql_plan_hash_value,to_char (sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start from dba_hist_active_sess_history where sql_id='&sql_id';

select sql_id, starting_time, end_time, (EXTRACT(HOUR FROM run_time) * 3600 + EXTRACT(MINUTE FROM run_time) * 60 + EXTRACT(SECOND FROM run_time))/60 run_time_MIN, READ_IO_BYTES, PGA_ALLOCATED PGA_ALLOCATED_BYTES, TEMP_ALLOCATED TEMP_ALLOCATED_BYTES from ( select sql_id, max(sample_time - sql_exec_start) run_time, max(sample_time) end_time, sql_exec_start starting_time, sum(DELTA_READ_IO_BYTES) READ_IO_BYTES, sum(DELTA_PGA) PGA_ALLOCATED, sum(DELTA_TEMP) TEMP_ALLOCATED from ( select sql_id, sample_time, sql_exec_start, DELTA_READ_IO_BYTES, sql_exec_id, greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_PGA, greatest(TEMP_SPACE_ALLOCATED - first_value(TEMP_SPACE_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_TEMP from dba_hist_active_sess_history where sample_time >= trunc(sysdate) --and sample_time < to_date ('2013/04/16 03:10:00','YYYY/MM/DD HH24:MI:SS') and sql_exec_start is not null and IS_SQLID_CURRENT='Y' ) group by sql_id,SQL_EXEC_ID,sql_exec_start order by sql_id ) where sql_id = '&sql_id' order by sql_id, run_time_MIN desc;

To find Sql Hanging or not:

select sess_io.inst_id, sess_io.sid, sesion.sql_id, sess_io.block_gets, sess_io.consistent_gets, sess_io.physical_reads, sess_io.block_changes, sess_io.consistent_changes from gv$sess_io sess_io, gv$session sesion where sesion.sid = sess_io.sid and sess_io.inst_id = sesion.inst_id and sesion.sql_id='&sql_id' and sesion.username is not null ;

select sess_io.inst_id, sess_io.sid, sess_io.block_gets, sess_io.consistent_gets, sess_io.physical_reads, sess_io.block_changes, sess_io.consistent_changes from gv$sess_io sess_io, gv$session sesion where sesion.sid = sess_io.sid and sess_io.inst_id = sesion.inst_id and sesion.username is not null ;

SQL to show the full SQL executing for active sessions:

select sesion.sid,sql_text from v$sqltext sqltext, v$session sesion where sesion.sql_hash_value = sqltext.hash_value and sesion.sql_address = sqltext.address and sesion.username is not null order by sqltext.piece;

select a.sid,b.sql_fulltext from V$Session a, V$SQLAREA b where a.sql_id=b.sql_id and a.status='ACTIVE';

Wait events AWR,ASH Time model scripts

Wait event for a SID:

To find currently waiting:

col WAIT_CLASS for a10 SELECT sw.inst_id,NVL(s.username, '(oracle)') AS username, s.sid, s.serial#, sw.event, sw.wait_class, sw.wait_time, sw.seconds_in_wait, sw.state FROM gv$session_wait sw, gv$session s WHERE s.sid = sw.sid and s.inst_id=sw.inst_id and s.sid=&sid ORDER BY sw.seconds_in_wait DESC;

Overall waits:

COLUMN username FORMAT A20 COLUMN sid FORMAT 9999 COLUMN serial# FORMAT 9999 COLUMN event FORMAT A40 SELECT NVL(s.username, '(oracle)') AS username, s.sid,s.serial#,se.event,se.total_waits,se.total_timeouts,se.time_waited, se.average_wait,se.max_wait,se.time_waited_micro FROM v$session_event se,v$session s WHERE s.sid = se.sid AND s.sid = &Session_ID ORDER BY se.time_waited DESC /

Time Model:

select stat_name, value from V$SESS_TIME_MODEL where sid = &sid order by value desc;

Stats:

select vsn.name, vst.value from v$sesstat vst, v$statname vsn where vsn.statistic# = vst.statistic# and vst.value != 0 and vst.sid = &sid order by vst.value;

RealTime Monitoring for sid:

Elapsed/CPU/Read/Write MB:

SELECT * FROM (SELECT status, --username, sql_id, sql_exec_id, TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start, ROUND(elapsed_time/1000000) AS "Elapsed (s)", ROUND(cpu_time /1000000) AS "CPU (s)", buffer_gets, ROUND(physical_read_bytes /(10241024)) AS "Phys reads (MB)", ROUND(physical_write_bytes/(10241024)) AS "Phys writes (MB)" FROM gv$sql_monitor where sid=&sid and inst_id=&inst_id ORDER BY elapsed_time DESC ) WHERE rownum<=20;

To find Each layer Time spend:

SELECT ROUND(elapsed_time /1000000) AS "Elapsed (s)", ROUND(cpu_time /1000000,3) AS "CPU (s)", ROUND(queuing_time /1000000,3) AS "Queuing (s)", ROUND(user_io_wait_time /1000000,3) AS "I/O wait (s)", ROUND(application_wait_time/1000000,3) AS "Appli wait (s)", ROUND(concurrency_wait_time/1000000,3) AS "Concurrency wait (s)", ROUND(cluster_wait_time /1000000,3) AS "Cluster wait (s)", ROUND(physical_read_bytes /(10241024)) AS "Phys reads (MB)", ROUND(physical_write_bytes /(10241024)) AS "Phys writes (MB)", buffer_gets AS "Buffer gets", ROUND(plsql_exec_time/1000000,3) AS "Plsql exec (s)", ROUND(java_exec_time /1000000,3) AS "Java exec (s)" FROM gv$sql_monitor WHERE sid=&sid and inst_id=&inst_id;

To Find Explain plan waiting steps:

col PLAN for a150 SELECT RPAD('(' || p.plan_line_ID || ' ' || NVL(p.plan_parent_id,'0') || ')',8) || '|' || RPAD(LPAD (' ', 2*p.plan_DEPTH) || p.plan_operation || ' ' || p.plan_options,60,'.') || NVL2(p.plan_object_owner||p.plan_object_name, '(' || p.plan_object_owner|| '.' || p.plan_object_name || ') ', '') || NVL2(p.plan_COST,'Cost:' || p.plan_COST,'') || ' ' || NVL2(p.plan_bytes||p.plan_CARDINALITY,'(' || p.plan_bytes || ' bytes, ' || p.plan_CARDINALITY || ' rows)','') || ' ' || NVL2(p.plan_partition_start || p.plan_partition_stop,' PStart:' || p.plan_partition_start || ' PStop:' || p.plan_partition_stop,'') || NVL2(p.plan_time, p.plan_time || '(s)','') AS PLAN FROM gv$sql_plan_monitor p WHERE sid=&sid ORDER BY p.plan_line_id, p.plan_parent_id;

To find list the top 20 SQL having the longest elapsed time:

SELECT * FROM (SELECT status, --username, sql_id, sql_exec_id, TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start, ROUND(elapsed_time/1000000) AS "Elapsed (s)", ROUND(cpu_time /1000000) AS "CPU (s)", buffer_gets, ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)", ROUND(physical_write_bytes/(1024*1024)) AS "Phys writes (MB)" FROM v$sql_monitor ORDER BY elapsed_time DESC ) WHERE rownum<=20;

Currently running SQL query’s:

v$sqlarea/v$sql:

set lines 1500 pages 9999 column sid format 9999 column username format a15 column PARSING_SCHEMA_NAME format a15 column SQL_EXEC_START for a21 column sql_text format a50 column module format a35 select a.inst_id,a.sid,a.username,b.PARSING_SCHEMA_NAME,a.module,a.sql_id,a.sql_child_number child,b.plan_hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,(sysdate-sql_exec_start)2460*60 SECS,b.rows_processed,a.status,substr(b.sql_text,1,50) sql_text from gv$session a,gv$sqlarea b where a.sql_hash_value = b.hash_value and a.sql_address = b.address and a.module not like '%emagent%' and a.module not like '%oraagent.bin%' and sql_text not like '%b.PARSING_SCHEMA_NAME%' and a.username is not null order by a.status;

ASH:

column my_sid format 999 column my_ser format 99999 column my_state format a30 column my_blkr format 999 select to_char(a.sample_time, 'HH24:MI:SS') MY_TIME,a.session_id MY_SID,a.session_serial# MY_SER, DECODE(a.session_state, 'WAITING' ,a.event, a.session_state) MY_STATE,a.xid, a.sql_id, a.blocking_session MY_BLKR from gv$active_session_history a, dba_users u where u.user_id = a.user_id and a.sql_id = '&sql_id' and a.sample_time > SYSTIMESTAMP-(2/1440);

AWR:

set lines 1000 pages 9999 SELECT s.snap_id,TO_CHAR(s.begin_interval_time, 'DD-MON HH24:MI') snap_time,ss.sql_id,ss.plan_hash_value, ss.ROWS_PROCESSED_TOTAL, ss.executions_delta execs, (ss.elapsed_time_delta/1000000)/DECODE(ss.executions_delta,0,1,ss.executions_delta) ela_per_exec, (ss.cpu_time_delta /1000000)/DECODE(ss.executions_delta,0,1,ss.executions_delta) cpu_per_exec, ss.buffer_gets_delta /DECODE(ss.executions_delta,0,1,ss.executions_delta) lio_per_exec, ss.disk_reads_delta /DECODE(ss.executions_delta,0,1,ss.executions_delta) pio_per_exec FROM dba_hist_snapshot s, dba_hist_sqlstat ss WHERE ss.dbid = s.dbid AND ss.instance_number = s.instance_number AND ss.snap_id = s.snap_id AND ss.sql_id = nvl('&sql_id','4dqs2k5tynk61') /* and ss.executions_delta > 0 / / check executions_delta for 1 , if it is 0 just consider only rows proceesed and calculate total execution time = sum ( executions_delta 1 + executions_delta 0 ) */ ORDER BY s.snap_id;

TIME BASED REPORT:

select s.sql_id, sum(case when begin_interval_time = to_date('14-nov-2017 1100','dd-mon-yyyy hh24mi') then s.executions_total else 0 end) sum_after, (sum(case when begin_interval_time >= to_date('14-nov-2020 1100','dd-mon-yyyy hh24mi') then s.executions_total else 0 end) - sum(case when begin_interval_time < to_date('14-nov-2020 1100','dd-mon-yyyy hh24mi') then s.executions_total else 0 end)) difference from dba_hist_sqlstat s, dba_hist_snapshot sn where sn.begin_interval_time between to_date('05-nov-2020 0001','dd-mon-yyyy hh24mi') and to_date('05-nov-2020 2359','dd-mon-yyyy hh24mi') and sn.snap_id=s.snap_id group by s.sql_id order by difference desc;

select * from ( select sql_id,sql_plan_hash_value,event,sql_exec_id,sql_exec_start,current_obj#,sql_plan_line_id,sql_plan_operation,sql_plan_options,SUM (delta_read_io_requests) lio_read ,SUM (delta_read_io_bytes) pio_read ,count(*) count_1 from dba_hist_active_sess_history where sql_id='&sql_id' group by sql_id, sql_plan_hash_value, event,sql_exec_id, sql_exec_start, current_obj#, sql_plan_line_id, sql_plan_operation, sql_plan_options ) order by count_1 desc;

RealTime Monitoring for sql_id:

Sql monitor report:

set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000 select DBMS_SQLTUNE.REPORT_SQL_MONITOR( sql_id=>'&sql_id', report_level=>'ALL', type=>'TEXT') from dual;

Elapsed/CPU/Read/Write MB:

SELECT * FROM (SELECT status, --username, sql_id, sql_exec_id, TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start, ROUND(elapsed_time/1000000) AS "Elapsed (s)", ROUND(cpu_time /1000000) AS "CPU (s)", buffer_gets, ROUND(physical_read_bytes /(10241024)) AS "Phys reads (MB)", ROUND(physical_write_bytes/(10241024)) AS "Phys writes (MB)" FROM gv$sql_monitor where sql_id='&sql_id' and inst_id=&inst_id ORDER BY elapsed_time DESC ) WHERE rownum<=20;

You can also verify To find Each layer Time spend & To Find Explain plan waiting steps above scripts attaches in this same blog

Monitoring SQL’S

To Find ONLY EXECUTING sql :

set lines 1000 pages 9999 column sid format 9999 column serial for 999999 column status format a15 column username format a10 column sql_text format a80 column module format a30 col program for a30 col SQL_EXEC_START for a20 SELECT * FROM (SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE, MODULE,program, TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start, ROUND(elapsed_time/1000000) AS "Elapsed (s)", ROUND(cpu_time /1000000) AS "CPU (s)", substr(sql_text,1,30) sql_text FROM gv$sql_monitor where status='EXECUTING' and module not like '%emagent%' ORDER BY sql_exec_start desc );

OVERALL EXECUTING SQL’s:

set lines 1000 pages 9999 column sid format 9999 column serial for 999999 column status format a15 column username format a10 column sql_text format a80 column module format a30 col program for a30 col SQL_EXEC_START for a20 SELECT * FROM (SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE, MODULE,program, TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start, ROUND(elapsed_time/1000000) AS "Elapsed (s)", ROUND(cpu_time /1000000) AS "CPU (s)", substr(sql_text,1,30) sql_text FROM gv$sql_monitor where module not like '%emagent%' ORDER BY sql_exec_start desc ) WHERE rownum<=20;

Sql-Monitor report for a sql_id ( Like OEM report):

column text_line format a254 set lines 750 pages 9999 set long 20000 longchunksize 20000 select dbms_sqltune.report_sql_monitor_list() text_line from dual; select dbms_sqltune.report_sql_monitor() text_line from dual;

Historical SQL Monitor reports:

SELECT report_id,EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_id') sql_id,EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_exec_id') sql_exec_id, EXTRACTVALUE(XMLType(report_summary),'/report_repository_summary/sql/@sql_exec_start') sql_exec_start FROM dba_hist_reports WHERE component_name = 'sqlmonitor';

To get text from above Report id :

set long 10000000 longchunksize 10000000 pages 0 SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => 1022, TYPE => 'text') FROM dual;

Sid Level Debug

gv$session:

set linesize 750 pages 9999 column box format a30 column spid format a10 column username format a20 column program format a30 column os_user format a20 col LOGON_TIME for a20 select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time, substr(b.username,1,20) username, substr(b.osuser,1,20) os_user, substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from gv$session b,gv$process a where b.paddr = a.addr and a.inst_id = b.inst_id and type='USER' and b.sid=&sid order by logon_time;

gv$sql_monitor:

set lines 1000 pages 9999 column sid format 9999 column serial for 999999 column status format a15 column username format a20 column sql_text format a80 col program for a33 col SQL_EXEC_START for a20 SELECT * FROM (SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE,program, TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start, ROUND(elapsed_time/1000000) AS "Elapsed (s)", ROUND(cpu_time /1000000) AS "CPU (s)", substr(sql_text,1,30) sql_text FROM gv$sql_monitor where module not like '%emagent%' and sid=&sid ORDER BY sql_exec_start desc );

set pages 50000 lines 32767 col OPNAME for a10 col SID form 9999 col SERIAL form 9999999 col PROGRAM for a10 col USERNAME for a10 col SQL_TEXT for a40 col START_TIME for a10 col LAST_UPDATE_TIME for a10 col TARGET for a25 col MESSAGE for a25 alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS'; SELECT inst_id,sid, serial#, sql_id, opname, username, target, sofar, totalwork, start_time,last_update_time,round(time_remaining/60,2) "REMAIN MINS", round(elapsed_seconds/60,2) "ELAPSED MINS", round((time_remaining+elapsed_seconds)/60,2) "TOTAL MINS", ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE", message FROM gv$session_longops WHERE OPNAME NOT LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND sofar<>totalwork AND time_remaining > 0 /

COLUMN sid FORMAT 99999 COLUMN serial# FORMAT 9999999 COLUMN machine FORMAT A30 COLUMN progress_pct FORMAT 99999999.00 COLUMN elapsed FORMAT A10 COLUMN remaining FORMAT A10 SELECT s.sid, s.serial#, s.machine, ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed, ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining, ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct FROM gv$session s, gv$session_longops sl WHERE s.sid = sl.sid AND s.serial# = sl.serial#;

SELECT SID, SERIAL#, OPNAME, TARGET, SOFAR, TOTALWORK, UNITS, TO_CHAR(START_TIME,'DD/MON/YYYY HH24:MI:SS') START_TIME, TO_CHAR(LAST_UPDATE_TIME,'DD/MON/YYYY HH24:MI:SS') LAST_UPDATE_TIME, TIME_REMAINING, ELAPSED_SECONDS, MESSAGE, USERNAME FROM V$SESSION_LONGOPS WHERE TIME_REMAINING != 0;

col username for a20 Select s.username, s.Sid, s.serial#, S.Sql_Id, round((Sysdate-Sql_Exec_Start)246060/60,0) MINUTES, Sql_Text From V$Session S, V$Sqltext T Where S.Sql_Id = T.Sql_Id And Sql_Exec_Start Is Not Null And Piece = 0 And (Sysdate-Sql_Exec_Start)24*60 > 1 order by MINUTES desc;

Long Running query – long ops:

SELECT a.sid,RPAD(a.opname,30),a.sofar,a.totalwork,a.ELAPSED_SECONDS,ROUND(((a.sofar)*100)/a.totalwork,3) "%_COMPLETED", RPAD(a.username,10) username,a.SQL_HASH_VALUE,B.STATUS FROM gV$SESSION_LONGOPS a, gv$session b WHERE a.sid=b.sid and a.sid=&sid --AND b.status='ACTIVE' AND a.sofar<> a.totalwork /

column username format a20 Select s.username, s.Sid, s.serial#, S.Sql_Id, round((Sysdate-Sql_Exec_Start)246060/60,0) MINUTES, Sql_Text From V$Session S, V$Sqltext T Where S.Sql_Id = T.Sql_Id And Sql_Exec_Start Is Not Null And Piece = 0 And (Sysdate-Sql_Exec_Start)24*60 > 1 order by MINUTES desc ;

Tag Archives: oracle agent shrink

Oracle RAC Administration and Monitoring Scripts -2

Status and Config Database

Shutdown RAC Database

Startup RAC Database

Shutdown An Instance

Startup and Status An Instance

Add and Remove Database from CRS

Stop, Start and Check ASM via SRVCTL

Stop, Start and Check Service via SRVCTL

Enable/disable Autorestart of Instance and Database

Start, Stop and Check status,configurations of NodeApps

History of executed queries

History of executed queries

SQL History:

Identify the Culprit: SQL , SQL TEXT , BIND VARIABLE

statistics of objects of a specific sql id:

Sql id current rows, I/O , read/wrire details:

Check the progress of DML statements:

Detailed history of SQL_ID:

To find Sql Hanging or not:

SQL to show the full SQL executing for active sessions:

Wait event for a SID:

RealTime Monitoring for sid:

Currently running SQL query’s:

RealTime Monitoring for sql_id:

Historical SQL Monitor reports:

Sid Level Debug

Long Running query – long ops: