USEFUL DB SCRIPTS

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

MAPPING OS SESSION TO DATABASE SESSION

check top 10 resource consuming OS Process

ps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head

-------------------------------------------------
Use top to check top consuming sessions then query from db


SELECT
'USERNAME : ' || s.username || CHR(10) ||
'SCHEMA : ' || s.schemaname || CHR(10) ||
'OSUSER : ' || s.osuser || CHR(10) ||
'PROGRAM : ' || s.program || CHR(10) ||
'SPID : ' || p.spid || CHR(10) ||
'SID : ' || s.sid || CHR(10) ||
'SERIAL# : ' || s.serial# || CHR(10) ||
'KILL STRING: ' || '''' || s.sid || ',' || s.serial# || '''' || CHR(10) ||
'MACHINE : ' || s.machine || CHR(10) ||
'TYPE : ' || s.type || CHR(10) ||
'TERMINAL : ' || s.terminal || CHR(10) ||
'SQL ID : ' || q.sql_id || CHR(10) ||
'SQL TEXT : ' || q.sql_text
FROM v$session s
,v$process p
,v$sql q
WHERE s.paddr = p.addr
AND p.spid = '&&PID_FROM_OS'
AND s.sql_id = q.sql_id(+);


=================================================
CHECK EXECUTION PLAN OF SQL STATEMENT 


SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('&&SQL_ID')));

=================================================
CHECK PROCESS using Linux top command and then through sqlplus use oradebug

SQL> oradebug setospid 7853;

SQL> oradebug current_sql;

SQL> oradebug tracefile_name;

SQL> alter system kill session '1177,38583';

$ kill -9

iostat -d 3
===================================================
QUERY SESSIONS WAITING FOR I/O RESOURCES


SELECT *
FROM
(SELECT
parsing_schema_name
,direct_writes
,SUBSTR(sql_text,1,75)
,disk_reads
FROM v$sql
ORDER BY disk_reads DESC)
WHERE rownum < 20;

----------------------------------------------
To determine which sessions are currently waiting for I/O resources, query V$SESSION:

SELECT
username
,program
,machine
,sql_id
FROM v$session
WHERE event LIKE 'db file%read
-----------------------------------------------------
SELECT
object_name
,object_type
,owner
FROM v$session a
,dba_objects b
WHERE a.event LIKE 'db file%read'
AND b.data_object_id = a.row_wait_obj#;

=================================================
LOCKED TABLES

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   gv$locked_object a ,
   gv$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;
 

===================================================
BIND VERIABLES VALUE OF SQL STATEMENTS

  SELECT
  sql_id,
  t.sql_text sql_text,
  b.name bind_name,
  b.value_string bind_value
FROM
  v$sql t
JOIN
  v$sql_bind_capture b  using (sql_id)
WHERE
  b.value_string is not null
AND
  sql_id='&sqlid'
/

===================================================
QUERY TO IDENTIFY (AND QUANTIFY) WAITS CAUSED BY LOCKING OF A TABLE’S ROWS


select wait_class, event, time_waited / 100 time_secs
from v$system_event e
where e.wait_class <> 'Idle' AND time_waited > 0
union
select 'Time Model', stat_name NAME,
 round ((value / 1000000), 2) time_secs
from v$sys_time_model
where stat_name NOT IN ('background elapsed time', 'background cpu time')
 order by 3 desc;

--------------------------------------------------
IDENTIFYING RECENTLY LOCKED SESSIONS


select to_char(h.sample_time, 'HH24:MI:SS') TIME,h.session_id,
decode(h.session_state, 'WAITING' ,h.event, h.session_state) STATE,
h.sql_id,
h.blocking_session BLOCKER
from v$active_session_history h, dba_users u
where u.user_id = h.user_id
and h.sample_time > SYSTIMESTAMP-(2/1440);

====================================================
IDENTIFY ALL UN-INDEXED FOREIGN KEY CONSTRAINTS

select * from (
select c.table_name, co.column_name, co.position column_position
from user_constraints c, user_cons_columns co
where c.constraint_name = co.constraint_name
and c.constraint_type = 'R'
minus
select ui.table_name, uic.column_name, uic.column_position
from user_indexes ui, user_ind_columns uic
where ui.index_name = uic.index_name
)
order by table_name, column_position;

=====================================================
QUICKLY CHECK WAIT EVENTS

select event, count(*) from Gv$session_wait
group by event
ORDER BY 2 DESC

====================================================
WAIT EVENTS BY CLASS

select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits)
sum_waits
from v$system_wait_class
group by wait_class
order by 3 desc;

=====================================================
SQL STATEMENTS WITH MOST WAITS

select ash.user_id,
u.username,
s.sql_text,
sum(ash.wait_time +
ash.time_waited) ttl_wait_time
from gv$active_session_history ash,
gv$sqlarea s,
dba_users u
where ash.sample_time between sysdate - 60/2880 and sysdate
and ash.sql_id = s.sql_id
and ash.user_id = u.user_id
group by ash.user_id,s.sql_text, u.username
order by ttl_wait_time desc

========================================================
DATABASE WAIT TIME vs DATABASE CPU TIME RATIO


CPU Time ration should be higher in normal database


select metric_name, value
from v$sysmetric
where metric_name in ('Database CPU Time Ratio',
'Database Wait Time Ratio') and
intsize_csec =
(select max(INTSIZE_CSEC) from V$SYSMETRIC);

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

Grant on Multiple Objects


select 'GRANT ' ||
  case
    when object_type = 'SEQUENCE' then 'SELECT'
    when object_type in ('PACKAGE','FUNCTION','PROCEDURE') then 'EXECUTE'
    END
   || ' ON ' ||OWNER||'.'||OBJECT_NAME|| ' TO BOB;' "PERMISSIONS"
    from dba_objects
WHERE OBJECT_TYPE IN('SEQUENCE','PACKAGE','FUNCTION','PROCEDURE')
AND OWNER='FARHAT'