1. To get SQL Performance Detail for a given SQL ID, which is currently running or is just completed. (The SQL ID should be a available in the memory (Shared Pool), else the SQL will not return any result):
SELECT
ROUND(a.elapsed_time/(1000*1000),2) elapsed_time,
ROUND((a.elapsed_time/(1000*1000))/a.executions,2) elapsed_time_per_exec,
a.sql_id,
SUBSTR(a.sql_text, 1,40) sql_text,
a.executions,a.users_executing,
a.first_load_time,
a.rows_processed,
a.parsing_schema_name,
a.plan_hash_value,
a.module,
a.last_load_time
FROM v$sqlarea a
WHERE a.executions > 0
and sql_id='g306ta1yzcfuq';
1a. To get all the SQLs from your database, which are currently running or is just completed. (The SQL ID should be a available in the memory (Shared Pool), else the SQL will not return any result. If it the SQL is not available in the v$sqlarea, check in DBA_HIST_SQLSTAT view)
SELECT to_char(last_active_time, 'dd-mon-yyyy hh24:mi:ss') last_active_time,
ROUND(a.elapsed_time/(1000*1000),2) elapsed_time,
ROUND((a.elapsed_time/(1000*1000))/a.executions,2) elapsed_time_per_exec,
a.sql_id,
SUBSTR(a.sql_text, 1,40) sql_text,
a.executions,a.users_executing,
a.first_load_time,
a.rows_processed,
a.parsing_schema_name,
a.plan_hash_value,
a.module,
a.last_load_time
FROM v$sqlarea a
WHERE a.executions > 0
and parsing_schema_name NOT IN ('SYS','SYSTEM','DBSNMP')
order by last_active_time;
2. To get all the expensive SQLs from your database, which are currently running or is just completed. (The SQL ID should be a available in the memory (Shared Pool), else the SQL will not return any result)
select
ROUND(a.elapsed_time/(1000*1000),2) elapsed_time,
ROUND((a.elapsed_time/(1000*1000))/a.executions,2) elapsed_time_per_exec,
a.sql_id,
SUBSTR(a.sql_text, 1,40) sql_text,
a.executions,a.users_executing,
a.first_load_time,
a.rows_processed,
a.parsing_schema_name,
a.plan_hash_value,
a.module,
a.last_load_time , a.*
FROM v$sqlarea a
WHERE a.executions > 100
-- and ( LOWER(SUBSTR(a.sql_text, 1,4)) <> 'call' AND LOWER(SUBSTR(a.sql_text, 1,5)) <> 'begin')
and ROUND((a.elapsed_time/(1000*1000))/a.executions,2) > 0
order by ROUND((a.elapsed_time/(1000*1000))/a.executions,2) desc , a.executions desc;
3. Generate kill session command for a single user.
set lines 300
select 'alter system kill session '||''''||s.sid||','||s.serial#||''' immediate;' kill_command, s.sid, s.serial#, s.status, p.spid
from v$session s, v$process p
where s.username = '&username'
and p.addr (+) = s.paddr;
4. If you have multiple Archive destination (Local or Standby), you can use the below command to check the status of each destination and the error message, if any of the destination is not receiving the archive log files.
set lines 300
col dest_name format A20
select dest_id, dest_name, error from v$archive_dest_status;
5. Get the database name, Mode, role etc. from v$database.
select current_scn, name, open_mode, database_role, flashback_on from v$database;
6. Get the Redo Generation Rate Per Hour.
select to_char(completion_time, 'DD-MON-YYYY HH24') DAY, Thread#,
count(*) "Archive File Count", round(sum(blocks*block_size)/1024/1024/1024) GB
from v$archived_log
where completion_time between sysdate-1 and sysdate
group by to_char(completion_time, 'DD-MON-YYYY HH24'), thread# order by 1,2;
7. Get the Redo Generation Rate Per Day.
select to_char(completion_time, 'DD-MON-YYYY') DAY, Thread#,
count(*) "Archive File Count", round(sum(blocks*block_size)/1024/1024/1024) GB
from v$archived_log
where completion_time > (sysdate - 7)
group by to_char(completion_time, 'DD-MON-YYYY'), thread# order by 1,2;
8. Get the list of Child Tables for a given Table
SET LINES 300
col parent_owner format A15
col child_owner format A15
col parent_table format A30
col child_table format A30
col parent_cons_name format A30
col child_cons_name format A30
SELECT
p.owner parent_owner,
c.owner child_owner,
p.table_name Parent_table,
p.constraint_name parent_cons_name,
c.table_name Child_table,
c.constraint_name child_cons_name
FROM
dba_constraints p, dba_constraints c
where p.owner='&parent_Owner'
and p.table_name='&Parent_Table_Name'
and p.constraint_type IN ('P','U')
and c.constraint_type='R'
and p.owner=c.r_owner
and p.constraint_name=c.r_constraint_name
order by 4;
9. Get the List of Parent Table for a given Child Table.
SET LINES 300
col parent_owner format A15
col child_owner format A15
col parent_table format A30
col child_table format A30
col parent_cons_name format A30
col child_cons_name format A30
SELECT
p.owner parent_owner,
c.owner child_owner,
p.table_name Parent_table,
p.constraint_name parent_cons_name,
c.table_name Child_table,
c.constraint_name child_cons_name
FROM
dba_constraints p, dba_constraints c
where c.owner='&child_Owner'
and c.table_name='&Child_Table_Name'
and p.constraint_type IN ('P','U')
and c.constraint_type='R'
and p.owner=c.r_owner
and p.constraint_name=c.r_constraint_name
order by 4;
10. Get the Instance Detail.
set lines 300
col host_name format A25
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select instance_number, instance_name, host_name, startup_time, status from v$instance;
11. Get Your Session Detail.
col Database Format A10
col Username Format A10
col current_schema format A10
SELECT
sysdate,
sys_context('USERENV','SERVER_HOST') server_host,
sys_context('USERENV','SERVICE_NAME') service_name,
sys_context('USERENV','INSTANCE_NAME') instance_name,
sys_context('USERENV','DB_UNIQUE_NAME') DB_UNIQUE_NAME,
sys_context('USERENV','DB_NAME') DB_NAME,
sys_context('USERENV','DB_DOMAIN') DB_DOMAIN,
sys_context('USERENV','SESSION_USER') Username,
sys_context('USERENV','CURRENT_SCHEMA') current_schema
FROM Dual;
12. Find out if there is any plan change and all of its detail for a SQL ID.
(** The change in plan_hash_value column will indicate a change in plan)
select
sn.begin_interval_time,
sn.end_interval_time,
sq.sql_id,
sq.plan_hash_value,
sq.executions_total,
sq.executions_delta,
round(sq.elapsed_time_total/(1000*1000),3) elapsed_time_total_in_sec,
round(sq.elapsed_time_delta/(1000*1000),3) elapsed_time_delta_in_sec,
sq.module,
sq.action,
round(sq.cpu_time_total/(1000*1000),3) cpu_time_total_in_sec,
round(sq.cpu_time_delta/(1000*1000),3) cpu_time_delta_in_sec
from dba_hist_sqlstat sq, dba_hist_snapshot sn
where sq.sql_id='gsd2mfhfcynwg'
and sq.snap_id = sn.snap_id
order by sn.end_interval_time;
13. Get the actual values for Bind Variables for a SQL ID
select * from v$sql_bind_capture where sql_id='gsd2mfhfcynwg' ;
14. Get the Detail of Plan History for a SQL ID
select * from dba_hist_sql_plan sq where sq.sql_id='gsd2mfhfcynwg';
15. Get the list SQL Baslines for your Database.
select * from dba_sql_plan_baselines;
16. Find the SQL executed by a OS Process ID
prompt "Please Enter The UNIX Process ID"
set pagesize 50000
set linesize 30000
set long 500000
set head off
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;
16. List of Sesions based on CPU Usage
sessions based on cpu usage :
-----------------------------
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,
se.VALUE/100 cpu_usage_sec
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.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr
--and value > 0
order by se.VALUE desc);
17. Session Detail
select
a.event,
a.sid,
a.username,
a.blocking_session,
a.final_blocking_session,
a.sql_id,
a.status,
a.osuser,
a.machine,
a.program,
a.last_call_et,
a.blocking_session_status,
a.final_blocking_session_status,
a.wait_class,
a.seconds_in_wait,
a.state
FROM
v$session a where username is not null
and event not in ('SQL*Net message from client','SQL*Net message to client');
17. SQL Detail from V$SQL
select
sql_id,
plan_hash_value,
first_load_time,
executions,
round(elapsed_time/(1000*1000),3) elapsed_time_total_in_sec
from V$sql
where sql_id IN ('fsy3ubymdz500','g306ta1yzcfuq');
SELECT
ROUND(a.elapsed_time/(1000*1000),2) elapsed_time,
ROUND((a.elapsed_time/(1000*1000))/a.executions,2) elapsed_time_per_exec,
a.sql_id,
SUBSTR(a.sql_text, 1,40) sql_text,
a.executions,a.users_executing,
a.first_load_time,
a.rows_processed,
a.parsing_schema_name,
a.plan_hash_value,
a.module,
a.last_load_time
FROM v$sqlarea a
WHERE a.executions > 0
and sql_id='g306ta1yzcfuq';
1a. To get all the SQLs from your database, which are currently running or is just completed. (The SQL ID should be a available in the memory (Shared Pool), else the SQL will not return any result. If it the SQL is not available in the v$sqlarea, check in DBA_HIST_SQLSTAT view)
SELECT to_char(last_active_time, 'dd-mon-yyyy hh24:mi:ss') last_active_time,
ROUND(a.elapsed_time/(1000*1000),2) elapsed_time,
ROUND((a.elapsed_time/(1000*1000))/a.executions,2) elapsed_time_per_exec,
a.sql_id,
SUBSTR(a.sql_text, 1,40) sql_text,
a.executions,a.users_executing,
a.first_load_time,
a.rows_processed,
a.parsing_schema_name,
a.plan_hash_value,
a.module,
a.last_load_time
FROM v$sqlarea a
WHERE a.executions > 0
and parsing_schema_name NOT IN ('SYS','SYSTEM','DBSNMP')
order by last_active_time;
2. To get all the expensive SQLs from your database, which are currently running or is just completed. (The SQL ID should be a available in the memory (Shared Pool), else the SQL will not return any result)
select
ROUND(a.elapsed_time/(1000*1000),2) elapsed_time,
ROUND((a.elapsed_time/(1000*1000))/a.executions,2) elapsed_time_per_exec,
a.sql_id,
SUBSTR(a.sql_text, 1,40) sql_text,
a.executions,a.users_executing,
a.first_load_time,
a.rows_processed,
a.parsing_schema_name,
a.plan_hash_value,
a.module,
a.last_load_time , a.*
FROM v$sqlarea a
WHERE a.executions > 100
-- and ( LOWER(SUBSTR(a.sql_text, 1,4)) <> 'call' AND LOWER(SUBSTR(a.sql_text, 1,5)) <> 'begin')
and ROUND((a.elapsed_time/(1000*1000))/a.executions,2) > 0
order by ROUND((a.elapsed_time/(1000*1000))/a.executions,2) desc , a.executions desc;
3. Generate kill session command for a single user.
set lines 300
select 'alter system kill session '||''''||s.sid||','||s.serial#||''' immediate;' kill_command, s.sid, s.serial#, s.status, p.spid
from v$session s, v$process p
where s.username = '&username'
and p.addr (+) = s.paddr;
4. If you have multiple Archive destination (Local or Standby), you can use the below command to check the status of each destination and the error message, if any of the destination is not receiving the archive log files.
set lines 300
col dest_name format A20
select dest_id, dest_name, error from v$archive_dest_status;
5. Get the database name, Mode, role etc. from v$database.
select current_scn, name, open_mode, database_role, flashback_on from v$database;
6. Get the Redo Generation Rate Per Hour.
select to_char(completion_time, 'DD-MON-YYYY HH24') DAY, Thread#,
count(*) "Archive File Count", round(sum(blocks*block_size)/1024/1024/1024) GB
from v$archived_log
where completion_time between sysdate-1 and sysdate
group by to_char(completion_time, 'DD-MON-YYYY HH24'), thread# order by 1,2;
7. Get the Redo Generation Rate Per Day.
select to_char(completion_time, 'DD-MON-YYYY') DAY, Thread#,
count(*) "Archive File Count", round(sum(blocks*block_size)/1024/1024/1024) GB
from v$archived_log
where completion_time > (sysdate - 7)
group by to_char(completion_time, 'DD-MON-YYYY'), thread# order by 1,2;
8. Get the list of Child Tables for a given Table
SET LINES 300
col parent_owner format A15
col child_owner format A15
col parent_table format A30
col child_table format A30
col parent_cons_name format A30
col child_cons_name format A30
SELECT
p.owner parent_owner,
c.owner child_owner,
p.table_name Parent_table,
p.constraint_name parent_cons_name,
c.table_name Child_table,
c.constraint_name child_cons_name
FROM
dba_constraints p, dba_constraints c
where p.owner='&parent_Owner'
and p.table_name='&Parent_Table_Name'
and p.constraint_type IN ('P','U')
and c.constraint_type='R'
and p.owner=c.r_owner
and p.constraint_name=c.r_constraint_name
order by 4;
9. Get the List of Parent Table for a given Child Table.
SET LINES 300
col parent_owner format A15
col child_owner format A15
col parent_table format A30
col child_table format A30
col parent_cons_name format A30
col child_cons_name format A30
SELECT
p.owner parent_owner,
c.owner child_owner,
p.table_name Parent_table,
p.constraint_name parent_cons_name,
c.table_name Child_table,
c.constraint_name child_cons_name
FROM
dba_constraints p, dba_constraints c
where c.owner='&child_Owner'
and c.table_name='&Child_Table_Name'
and p.constraint_type IN ('P','U')
and c.constraint_type='R'
and p.owner=c.r_owner
and p.constraint_name=c.r_constraint_name
order by 4;
10. Get the Instance Detail.
set lines 300
col host_name format A25
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select instance_number, instance_name, host_name, startup_time, status from v$instance;
11. Get Your Session Detail.
col Database Format A10
col Username Format A10
col current_schema format A10
SELECT
sysdate,
sys_context('USERENV','SERVER_HOST') server_host,
sys_context('USERENV','SERVICE_NAME') service_name,
sys_context('USERENV','INSTANCE_NAME') instance_name,
sys_context('USERENV','DB_UNIQUE_NAME') DB_UNIQUE_NAME,
sys_context('USERENV','DB_NAME') DB_NAME,
sys_context('USERENV','DB_DOMAIN') DB_DOMAIN,
sys_context('USERENV','SESSION_USER') Username,
sys_context('USERENV','CURRENT_SCHEMA') current_schema
FROM Dual;
12. Find out if there is any plan change and all of its detail for a SQL ID.
(** The change in plan_hash_value column will indicate a change in plan)
select
sn.begin_interval_time,
sn.end_interval_time,
sq.sql_id,
sq.plan_hash_value,
sq.executions_total,
sq.executions_delta,
round(sq.elapsed_time_total/(1000*1000),3) elapsed_time_total_in_sec,
round(sq.elapsed_time_delta/(1000*1000),3) elapsed_time_delta_in_sec,
sq.module,
sq.action,
round(sq.cpu_time_total/(1000*1000),3) cpu_time_total_in_sec,
round(sq.cpu_time_delta/(1000*1000),3) cpu_time_delta_in_sec
from dba_hist_sqlstat sq, dba_hist_snapshot sn
where sq.sql_id='gsd2mfhfcynwg'
and sq.snap_id = sn.snap_id
order by sn.end_interval_time;
13. Get the actual values for Bind Variables for a SQL ID
select * from v$sql_bind_capture where sql_id='gsd2mfhfcynwg' ;
14. Get the Detail of Plan History for a SQL ID
select * from dba_hist_sql_plan sq where sq.sql_id='gsd2mfhfcynwg';
15. Get the list SQL Baslines for your Database.
select * from dba_sql_plan_baselines;
16. Find the SQL executed by a OS Process ID
prompt "Please Enter The UNIX Process ID"
set pagesize 50000
set linesize 30000
set long 500000
set head off
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;
16. List of Sesions based on CPU Usage
sessions based on cpu usage :
-----------------------------
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,
se.VALUE/100 cpu_usage_sec
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.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr
--and value > 0
order by se.VALUE desc);
17. Session Detail
select
a.event,
a.sid,
a.username,
a.blocking_session,
a.final_blocking_session,
a.sql_id,
a.status,
a.osuser,
a.machine,
a.program,
a.last_call_et,
a.blocking_session_status,
a.final_blocking_session_status,
a.wait_class,
a.seconds_in_wait,
a.state
FROM
v$session a where username is not null
and event not in ('SQL*Net message from client','SQL*Net message to client');
17. SQL Detail from V$SQL
select
sql_id,
plan_hash_value,
first_load_time,
executions,
round(elapsed_time/(1000*1000),3) elapsed_time_total_in_sec
from V$sql
where sql_id IN ('fsy3ubymdz500','g306ta1yzcfuq');
No comments:
Post a Comment