1. Check
Tablespace USAGES
alter session set "_hash_join_enabled"=true;
set pagesize 100
set lines 200
COLUMN free_space_mb format 99999990.00
COLUMN allocated_mb format 99999990.00
COLUMN used_mb format 99999990.00
COLUMN percent_used format 00.00
SELECT
SUBSTR(df.tablespace_name,1,20) tablespace_name,
(df.bytes) / 1024 / 1024
allocated_mb,((df.bytes)-nvl(dfs.bytes,0))/1024/1024 used_mb,
NVL ((dfs.bytes) / 1024 / 1024, 0) free_space_mb,round( ((df.bytes-nvl(dfs.bytes,0))/df.bytes *
100),2) percent_used
FROM ( select sum(bytes)
bytes,tablespace_name from dba_data_files group by tablespace_name) df,
( select sum(bytes) bytes ,
tablespace_name from dba_free_space group by tablespace_name) dfs
WHERE df.tablespace_name =
dfs.tablespace_name
and df.tablespace_name='CUSTOM'
ORDER BY percent_used ;
If necessary, add datafile(s)
to the tablespace:
For ASM
ALTER TABLESPACE
CUSTOM
ADD DATAFILE '+EASYDATA_DG2/'
size 30g;
For File System
ALTER TABLESPACE
CUSTOM
ADD DATAFILE '/EASYFILE_bc/EASY/CUSTOM_e3_EASY.dbf'
size 30g;
2. Check
TEMPORARY Tablespace:
SET LINESIZE 200
COL TABLESPACE_NAME
FORMAT A20
SELECT TABLESPACE_NAME,SUM(BYTES_USED/1024/1024)
"USED_MB",SUM(BYTES_FREE/1024/1024) "FREE_MB",
SUM(BLOCKS_FREE)
"FREE_BLOCKS"
FROM
V$TEMP_SPACE_HEADER
WHERE
TABLESPACE_NAME='SORT_TBLSPC' GROUP BY TABLESPACE_NAME;
Or
select sysdate
dtstamp,
s.tablespace_name,
d.tbspc_mb,
s.total_blocks*8192/1024/1024
temp_tot_mb,
s.used_blocks*8192/1024/1024
temp_used_mb,
s.free_blocks*8192/1024/1024
temp_free_mb
from v$sort_segment
s,
(select
tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_data_files
group by
tablespace_name
union
select
tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_temp_files
group by
tablespace_name) d
where
s.tablespace_name=d.tablespace_name;
Add TEMP data file
if necessary:
column file_name
format a50
select file_name,TABLESPACE_NAME,bytes/1024/1024
from dba_temp_files where TABLESPACE_NAME='TEMP' order by 1 ;
alter database
tempfile '/ora/TESTDB/temp/oradata/TESTDB/tivoli_temp_01.dbf' resize 4000m
OR
ALTER TABLESPACE
SORT_TBLSPC
ADD TEMPFILE '/EASYFILE_bc/EASY/SORT_TBLSPC_t1.dbf'
size 4g;
FOR ASM
ALTER TABLESPACE
SORT_TBLSPC
ADD TEMPFILE 'EASYTEMP_DGNEW'
size 4g;
3.Check Session and Table Lock on Database
To check Session Lock
SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE
request > 0)
ORDER BY id1, request;
Object level
lock
select a.sid,
a.serial#,OBJECT_NAME
from v$session a,
v$locked_object b, dba_objects c =
where b.object_id =
c.object_id
and a.sid =
b.session_id;
or
Select * from (
Select
c.owner,c.object_name,
c.object_type,b.sid,
b.serial#,b.status,
b.osuser,b.machine
from v$locked_object
a ,v$session b, dba_objects c
where b.sid =
a.session_id
and a.object_id =
c.object_id);
select
p.spid,s.sid,s.serial#,s.username,to_char(s.logon_time,'dd-mon:hh24:mi'),machine,module
from v$session s, v$process p
where p.addr=s.paddr and s.sid =’&sid’
ALTER SESSION SET "_HASH_JOIN_ENABLED"=TRUE;
select
a.sid,a.serial#,b.lmode, ROUND(b.ctime/60) "L Time" from v$session a,
v$lock b,v$locked_object c,dba_objects d
where b.sid=a.sid
and b.SID=c.SESSION_ID and c.OBJECT_ID=d.object_id and d.object_name =(select
table_name from
dba_synonyms where
lower(synonym_name)='&SYN_NAME');
COL PROGRAM FORMAT
A40
COL MACHINE FORMAT
A20
COL USERNAME FORMAT
A10
SELECT PROGRAM,
MACHINE, USERNAME FROM V$SESSION WHERE SID=&SID;
col pid format
999999999
SELECT
S.SID,S.SERIAL#,L.PID,L.CURRENT_OPTION_ID,L.HOST_NAME
FROM V$SESSION S,LGI
L WHERE
TO_NUMBER(L.PID)=TO_NUMBER(S.PROCESS)AND L.HOST_NAME=S.MACHINE
AND S.SID=&SID;
SELECT SERIAL#, SID
FROM V$SESSION WHERE SID=&SID;
ALTER SYSTEM KILL
SESSION '&SID,&SERIAL';
4.Check Long running Query/ Full Table Scan Checking:
alter session set
"_hash_join_enabled"=true;
set linesize 200
col sid format
999999
col serial# format
9999
col opname format
a25
col units format a8
col start format a8
col target format
a30
col In_Minutes
format 999
col % format 999
select
sid,opname,target,to_char(start_time,'HH24:MI:SS')
"START",ELAPSED_SECONDS/60
"In_Minutes",totalwork,sofar,units,round((sofar/totalwork)*100)
"%",sql_id
from
v$session_longops where (sofar/totalwork)*100<100 order by start_time ;
5.Check
Problematic Events on Database
set pagesize 30
select
event,count(event) from v$session group by event order by 2;
set linesize 200
col sid format 99999
col SL# format 99999
col PROC FORMAT a10
col MACHINE format
a10
col UNAME format a10
col OSUSER format a8
col TERMINAL format
a8
col PROGRAM format
a32
col operation format
a16
col options format
a15
col CN format 99999
select
a.SID,a.SERIAL# "SL#",a.PROCESS
"PROC",a.SQL_ID,b.CHILD_NUMBER "CN" ,a.username
"UNAME",a.OSUSER,a.PROGRAM,
b.OPERATION,b.OPTIONS,sum(b.CPU_COST)
"CPU_COST",sum(b.IO_COST) "IO_COST"
from v$session a,
v$sql_plan b
where
a.SQL_ID=b.SQL_ID
and
a.STATUS='ACTIVE'
and
a.event='&event_name'
and b.options is not
null
group by
a.SID,a.SERIAL#,a.PROCESS,a.SQL_ID,a.username,a.OSUSER,a.PROGRAM,b.OPERATION,b.OPTIONS,b.CHILD_NUMBER
order by
sum(b.CPU_COST);
6. Resource
Utilization:
SET LINESIZE 200
set pagesize 200
select
RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION,LIMIT_VALUE
,(INITIAL_ALLOCATION-CURRENT_UTILIZATION)
"IN_POOL"
from
v$resource_limit
where
CURRENT_UTILIZATION>0
and resource_name in
('sessions','processes','parallel_max_servers')
group by
RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION,LIMIT_VALUE
order by
CURRENT_UTILIZATION desc;
select * from
v$resource_limit where CURRENT_UTILIZATION>0;
7) To check Archive log gap and
applied status on standby Server:
alter session set "_hash_join_enabled"=true;
select STATUS,PROCESS,SEQUENCE#,BLOCK#,BLOCKS from
v$managed_standby;
select max(sequence#)"Last Sequence" from
v$loghist;
select
max(SEQUENCE#)"Applied Sequence" from v$archived_log where
APPLIED='YES' and dest_id=2;
set linesize 200
col INST_ID format 99
col process format 99
COL LOGSEQ FORMAT 9999999
col state format a8
col error format a10
col DEST_NAME format a20
col RECOVERY_MODE format a10
col process format 99
col ArcGap format 99
select
ap.INST_ID,ad.DEST_NAME,ap.PROCESS,ap.STATUS,ap.LOG_SEQUENCE
"LOGSEQ",ap.STATE,ad.STATUS,ad.ARCHIVED_SEQ#,
ad.APPLIED_SEQ#,(select max(sequence#) from V$loghist)-
(select APPLIED_SEQ# from GV$ARCHIVE_DEST_STATUS where
APPLIED_SEQ#>0)"ArcGap",ad.ERROR
from GV$ARCHIVE_DEST_STATUS ad, GV$ARCHIVE_PROCESSES
ap,gv$log a
where ad.ARCHIVED_SEQ#=a.SEQUENCE# and
ap.LOG_SEQUENCE=ad.ARCHIVED_SEQ# and ap.LOG_SEQUENCE>0 order by
ad.DEST_NAME;
8) Archival Generation History
set linesize
200
column LOGDATE format
a12
col MidN format
9999
col 1AM format
99999
col 2AM format
99999
col 3AM format
99999
col 4AM format
99999
col 5AM format
99999
col 6AM format
99999
col 7AM format
99999
col 8AM format
99999
col 9AM format
99999
col 10AM format
9999
col 11AM format
9999
col Noon format
9999
col 1PM format
99999
col 2PM format
99999
col 3PM format
99999
col 4PM format
99999
col 5PM format
99999
col 6PM format
99999
col 7PM format
99999
col 8PM format
99999
col 9PM format
99999
col 10PM format
9999
col 11PM format
9999
select
to_char(first_time,'mm-dd-yyyy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0))
"MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0))
"1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0))
"2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0))
"3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0))
"4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0))
"5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0))
"6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0))
"7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0))
"8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0))
"9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0))
"10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0))
"11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0))
"Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0))
"1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0))
"2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0))
"3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0))
"4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0))
"5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0))
"6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0))
"7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0))
"8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0))
"9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0))
"10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0))
"11PM"
from
v$log_history
group by
to_char(first_time,'mm-dd-yyyy')
order by 1;
9.Performance Troubleshooting Related
Query
Execution Plan from cursor
select * from
table ( dbms_xplan.display_cursor('&sql_id'))
Execution Plan from AWR
Check execution
plan from the AWR using below query
-------------------------------------------------------
select * from
table ( dbms_xplan.display_awr('&sql_id'))
To check child
number for sql_id
-------------------------------
SQL_ID and child
number of the SQL are known
A SQL_ID can have
multiple children with different characteristics. You can identify the child
number by selecting from V$SQL as follows:
select sql_id,
child_number, sql_text from v$sql where sql_id='&sql';
or
SELECT sql_id,
hash_value, child_number, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text
LIKE '%&Some_Identifiable_String%'
/
The first child
for a cursor will have a CHILD_NUMBER of zero.
Once you have this
information, you can use dbms_xplan.display_cursor as follows:
set linesize 150
set pagesize 2000
select * from
TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD));
To check number
of Executions
-----------------------------
select
sql_id,plan_hash_value,executions from gv$sql where sql_id='&SQL_ID'
To find out
change in SQL plan for SQL_ID
------------------------------------
The below script
will help you find the SQL plan ID for previous executions and check if there
is any change in SQL plan ID.
planchange.sql
-------------
col
begin_interval_time for a30
break on
plan_hash_value on startup_time skip 1
select ss.snap_id,
ss.instance_number node, begin_interval_time, s.sql_id, s.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, version_count
from
DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where s.sql_id =
nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id =
S.snap_id
and
ss.instance_number = S.instance_number
and
executions_delta > 0 --and plan_hash_value=3099046451
order by 1, 2, 3
select s.sql_text,
s.sql_id, s.plan_hash_value from v$sql s where s.plan_hash_value =
'&plan_hash_value';
select
sql_id,LAST_ACTIVE_TIME,PLAN_HASH_VALUE,ROWS_PROCESSED,EXECUTIONS,END_OF_FETCH_COUNT,LOADS,ELAPSED_TIME/power(10,6)/60,SORTS,
PX_SERVERS_EXECUTIONS
from V$SQLSTATS
where sql_id='&SQLID';
--Look at the
V$SQLSTATS view, which gives information about currently or recently run SQL
statements. If you wanted
to get the five
recent SQL statements that performed the most disk I/O, you could issue the
following query:
The V$SQLSTATS
view is meant to help more quickly find information on resource-consuming SQL
statements. V$SQLSTATS
has the same
information as the V$SQL and V$SQLAREA views, but V$SQLSTATS has only a subset
of columns of the other
views. However,
data is held within the V$SQLSTATS longer than either V$SQL or V$SQLAREA
SELECT sql_text,
disk_reads FROM
(SELECT sql_text, buffer_gets, disk_reads,
sorts,
cpu_time/1000000 cpu, rows_processed,
elapsed_time
FROM v$sqlstats
ORDER BY disk_reads DESC)
WHERE rownum <=
5;
You can use the
V$SQL_MONITOR view to see real-time statistics of currently running SQL and see
the resource consumption
used for a given
query based on such statistics as CPU usage, buffer gets, disk reads, and
elapsed time of the query. Let’s
first find a
current executing query within our database:
SELECT sid,
sql_text FROM v$sql_monitor
WHERE status =
'EXECUTING'
SELECT sid,
buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds FROM
v$sql_monitor WHERE SID=100
AND status =
'EXECUTING';
SELECT * FROM (
SELECT sql_id,
to_char(sql_exec_start,'yyyy-mm-dd:hh24:mi:ss') sql_exec_start,
sql_exec_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads,
round(sum(cpu_time/1000000),1) cpu_secs
FROM v$sql_monitor
WHERE sql_id = '21z86kt10h3rp'
GROUP BY sql_id, sql_exec_start, sql_exec_id
ORDER BY 6 desc)
WHERE rownum <=
5
Keep in mind that
if a statement is running in parallel, one row will appear for each parallel
thread for the query,
including one for
the query coordinator. However, they will share the same SQL_ID,
SQL_EXEC_START, and
SQL_EXEC_ID
values. In this case, you could perform an aggregation on a particular
statistic, if desired. See the
following example
for a parallelized query, along with parallel slave information denoted by the
PX_SERVER# column:
SELECT sql_id,
sql_exec_start, sql_exec_id, px_server# px#, disk_reads,
cpu_time/1000000 cpu_secs, buffer_gets
FROM v$sql_monitor
WHERE status =
'EXECUTING'
ORDER BY
px_server#;
Then, to perform a
simple aggregation for a given query, in this case, our parallelized query, the
aggregation is
done on the three
key columns that make up a single execution of a given SQL statement:
SELECT
sql_id,sql_exec_start, sql_exec_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads,
round(sum(cpu_time/1000000),1) cpu_seconds
FROM v$sql_monitor
WHERE sql_id = '&SQL_ID'
GROUP BY sql_id,
sql_exec_start, sql_exec_id;
If you wanted to
perform an aggregation for one SQL statement, regardless of the number of times
is has been
executed, simply
run the aggregate query only on the SQL_ID column, as shown here:
SELECT sql_id,
sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads,
round(sum(cpu_time/1000000),1) cpu_seconds
FROM v$sql_monitor
WHERE sql_id = '&SQL_ID'
GROUP BY sql_id;
=========
if SQL is no
longer available in v$sql, you can find it in the AWR history views: DBA_HIST_SQLTEXT
and DBA_HIST_SQLSTAT
SELECT s.sql_id,
s.plan_hash_value,t.sql_text,s.snap_id FROM dba_hist_sqlstat s,
dba_hist_sqltext t
WHERE s.dbid =
t.dbid
AND s.sql_id = t.sql_id
AND sql_text LIKE 'SELECT /* TARGET SQL */%'
ORDER BY s.sql_id
Analyze a particular SQL ID and see the trends for the past day.
select
s.snap_id,
to_char(s.begin_interval_time,'HH24:MI') c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.cpu_time_delta c6,
sql.elapsed_time_delta c7
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and s.begin_interval_time
> sysdate -5
and
sql.sql_id='&sqlid'
order by c7
No comments:
Post a Comment