EXPLAIN PLAN USING SQL_ID

 We can extract the explain plan using sqlid

command
=======

select * from TABLE(dbms_xplan.display_awr('0srj720xy3mx7'));

PLAN_TABLE_OUTPUT


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID favqakzqswx1q

--------------------

SELECT cd.Id, cd.TypeNum AS CxnType, od.Id, od.GUID, od.Version,

oda.ShortPlainString, od.TypeNum, od.GroupId, od.RefGlobalId,

od.SubTypeNum, od.DefSymbolNum, od.SymbolGuid FROM e151_cxndef cd JOIN

e151_objdef od ON cd.FromObjDefId = od.Id LEFT OUTER JOIN

e151_objdefattr oda ON (od.Id = oda.ParentItemId AND oda.AttrTypeNum =

1 AND oda.LocaleId = :1 ) WHERE cd.ToObjDefId = :2 AND EXISTS (SELECT

'X' FROM e151_visibleobjdef v WHERE v.ObjDefId = od.Id AND

v.UserGroupId = :3 AND v.Filter = :4 )





PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1195989455



-------------------------------------------------------------------------------------------------------


Id
Operation
Name
Rows
Bytes
Cost (%CPU)
Time


-------------------------------------------------------------------------------------------------------


0
SELECT STATEMENT



14 (100)




1
NESTED LOOPS OUTER

1
243
14 (0)
00:00:01



2
NESTED LOOPS SEMI

1
198
11 (0)
00:00:01



3
NESTED LOOPS

3
411
8 (0)
00:00:01



4
TABLE ACCESS BY INDEX ROWID
E151_CXNDEF
3
120
5 (0)
00:00:01



5
INDEX RANGE SCAN
E151_CXNDEF_FK_2I
3

3 (0)
00:00:01




PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


6
TABLE ACCESS BY INDEX ROWID
E151_OBJDEF
1
97
1 (0)
00:00:01



7
INDEX UNIQUE SCAN
SYS_C00336210
1

0 (0)




8
INDEX UNIQUE SCAN
E151_VISIBLEOBJDEF_1
10028
597K
1 (0)
00:00:01



9
TABLE ACCESS BY INDEX ROWID
E151_OBJDEFATTR
1
45
3 (0)
00:00:01



10
INDEX RANGE SCAN
E151_OBJDEFATTR_1
1

2 (0)
00:00:01


-------------------------------------------------------------------------------------------------------

configuration guidelines for SGA sizing on the database instance.ASM

Aggregate the values from the following queries to obtain the current database storage size that is either on Oracle ASM or stored in Oracle ASM. Next, determine the redundancy type and calculate the SHARED_POOL_SIZE using the aggregated value as input.



SELECT SUM(bytes)/(1024*1024*1024) FROM V$DATAFILE;


SELECT SUM(bytes)/(1024*1024*1024) FROM V$LOGFILE a, V$LOG b
WHERE a.group#=b.group#;


SELECT SUM(bytes)/(1024*1024*1024) FROM V$TEMPFILE
WHERE status='ONLINE';


◦For disk groups using external redundancy, every 100 GB of space needs 1 MB of extra shared pool plus 2 MB
◦For disk groups using normal redundancy, every 50 GB of space needs 1 MB of extra shared pool plus 4 MB
◦For disk groups using high redundancy, every 33 GB of space needs 1 MB of extra shared pool plus 6 MB

Error: ORA 1594

To see how much undo information has been written in the past hour.


select begin_time, end_time, undoblks, txncount, maxconcurrency as maxcon,nospaceerrcnt from V$UNDOSTAT where begin_time > sysdate-(4/24);



BEGIN_TIME END_TIME UNDOBLKS TXNCOUNT MAXCON NOSPACEERRCNT

-------------- -------------- ---------- ---------- ---------- -------------

01-04-30 14:07 01-04-30 14:14 1883 7 3 0

01-04-30 13:57 01-04-30 14:07 488 34 3 0

01-04-30 13:47 01-04-30 13:57 0 2 1 0

01-04-30 13:37 01-04-30 13:47 0 2 1 0

01-04-30 13:27 01-04-30 13:37 0 2 1 0

01-04-30 13:17 01-04-30 13:27 0 2 1 0



This query shows how much redo has been used over the last hour. With the last 20 minutes being the only time that has used undo space. Here we can see that there have been 34 transactions from 13:57 – 14:07 using 488 undo blocks and none of them received any errors due to space management. And from 14:07 until present there are 7 transactions in total using 1883 undo blocks and so far no space problems.



If space problems occur so a user receives a segment out of space error then the column NOSPACEERRCNT will increase. If this happens then the undo tablespace is now to small and will need to be increased via enlarging a datafile or adding another data file to the tablespace.





To find the time of the longest query in the instance:



select begin_time, MAXQUERYLEN, undoblks from V$UNDOSTAT where begin_time > sysdate-(4/24);



BEGIN_TIME MAXQUERYLEN UNDOBLKS

-------------- ----------- ----------

01-04-30 15:27 15 482

01-04-30 15:17 14 1895

01-04-30 15:07 0 1

01-04-30 14:57 0 0

01-04-30 14:47 0 0

01-04-30 14:37 0 1

01-04-30 14:27 738 3426 <--- 738 seconds

01-04-30 14:17 377 2564

01-04-30 14:07 236 2277

01-04-30 13:57 21 488

01-04-30 13:47 0 0

01-04-30 13:37 0 0



This shows that the longest query that has been run was 738 seconds long. To ensure this query never gets ORA-1555 set undo_retention to at least 750 seconds. If undo_retention is set to high the UNDO tablespace will need to be very large. If to low ORA-1555 may occur to often.


SQL> select begin_time, end_time, undoblks, txncount, maxconcurrency as maxcon,nospaceerrcnt from V$UNDOSTAT where begin_time > sysdate-(4/24);



BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT MAXCON NOSPACEERRCNT

--------- --------- ---------- ---------- ---------- -------------

08-AUG-08 08-AUG-08 0 0 0 0

08-AUG-08 08-AUG-08 1 269 1 0

08-AUG-08 08-AUG-08 0 0 0 0

08-AUG-08 08-AUG-08 0 239 0 0

08-AUG-08 08-AUG-08 0 226 0 0

08-AUG-08 08-AUG-08 0 161 0 0

08-AUG-08 08-AUG-08 2 135 1 0

08-AUG-08 08-AUG-08 2 29 1 0

08-AUG-08 08-AUG-08 5 1 1 0

9 rows selected.

Temp utilization..How much?

Sometimes we need to monitor the Temp utilization as we use to get a request that Temp Table is going out of space even if that is pretty BIG enough 40 to 60 GB..we have to catch the utilization and monitor who is ueing and how much. So i used this for that ...and then further investigation for the Root cause of using so much of Temp and Why?

--Execute the following query to determine who is using a TEMP Segment:


set line 200

column sid_serial format a20



SELECT b.tablespace,

ROUND(((b.blocks*p.value)/1024/1024),2)

'M' "SIZE",

a.sid

','

a.serial# SID_SERIAL,

a.username,

a.program

FROM sys.v_$session a,

sys.v_$sort_usage b,

sys.v_$parameter p

WHERE p.name = 'db_block_size'

AND a.saddr = b.session_addr

ORDER BY b.tablespace, b.blocks;





TABLESPACE SIZE SID_SERIAL USERNAME PROGRAM

---------- ------- ---------- -------- ------------------------------

TEMP 24M 260,7 SCOTT sqlplus@localhost.localdomain

(TNS V1-V3)

distributed transactions awaiting recovery

Somtimes in our environment we see that after restart of the database there are some traces of distributed transaction in Logs. so to catch them in database and kill thee i use this procedure


column local_tran_id new_value TRANS_ID


select local_tran_id from dba_2pc_pending;

rollback force '&TRANS_ID';

exec dbms_transaction.purge_lost_db_entry(&TRANS_ID);

Reactive Performance report Script...

spool tfsrstat.lst


set echo off

set feedback off



prompt****************************************************

prompt Hit Ratio Section

prompt****************************************************

prompt

prompt =========================

prompt BUFFER HIT RATIO

prompt =========================

prompt (should be > 70, else increase db_block_buffers in init.ora)



select trunc((1-(sum(decode(name,'physical reads',value,0))/

(sum(decode(name,'db block gets', value,0))+

(sum(decode(name,'consistent gets',value,0)))))

)* 100) "Buffer Hit Ratio"

from v$sysstat;



column "logical_reads" format 99,999,999,999

column "phys_reads" format 999,999,999

column "phy_writes" format 999,999,999

select a.value + b.value "logical_reads",

c.value "phys_reads",

d.value "phy_writes",

round(100 * ((a.value+b.value)-c.value) /

(a.value+b.value))

"BUFFER HIT RATIO"

from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d

where

a.name = 'db block gets'

and

b.name = 'consistent gets'

and

c.name = 'physical reads'

and

d.name = 'physical writes';



prompt

prompt

prompt =========================

prompt DATA DICT HIT RATIO

prompt =========================

prompt (should be higher than 90 else increase shared_pool_size in init.ora)

prompt



column "Data Dict. Gets" format 999,999,999

column "Data Dict. cache misses" format 999,999,999

select sum(gets) "Data Dict. Gets",

sum(getmisses) "Data Dict. cache misses",

trunc((1-(sum(getmisses)/sum(gets)))*100)

"DATA DICT CACHE HIT RATIO"

from v$rowcache;



prompt

prompt =========================

prompt LIBRARY CACHE MISS RATIO

prompt =========================

prompt (If > .1, i.e., more than 1% of the pins resulted in reloads, then

prompt increase the shared_pool_size in init.ora)

prompt

column "LIBRARY CACHE MISS RATIO" format 99.9999

column "executions" format 999,999,999

column "Cache misses while executing" format 999,999,999

select sum(pins) "executions", sum(reloads) "Cache misses while executing",

(((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"

from v$librarycache;



prompt

prompt =========================

prompt Library Cache Section

prompt =========================

prompt hit ratio should be > 70, and pin ratio > 70 ...

prompt



column "reloads" format 999,999,999

select namespace, trunc(gethitratio * 100) "Hit ratio",

trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"

from v$librarycache;

prompt

prompt

prompt =========================

prompt REDO LOG BUFFER

prompt =========================

prompt

set heading off

column value format 999,999,999

select substr(name,1,30),

value

from v$sysstat where name = 'redo log space requests';



set heading on

prompt

prompt

prompt****************************************************

prompt Lock Section

prompt****************************************************

prompt

prompt =========================

prompt SYSTEM-WIDE LOCKS - all requests for locks or latches

prompt =========================

prompt

prompt Processing Locks and Latches, please standby...



select /*+ ordered */ substr(username,1,12) "User",

substr(lock_type,1,18) "Lock Type",

substr(mode_held,1,18) "Mode Held"

from v$session b ,sys.dba_lock a

where lock_type not in ('Media Recovery','Redo Thread')

and a.session_id = b.sid;

prompt

prompt =========================

prompt DDL LOCKS - These are usually triggers or other DDL

prompt =========================

prompt

select substr(username,1,12) "User",

substr(owner,1,8) "Owner",

substr(name,1,15) "Name",

substr(a.type,1,20) "Type",

substr(mode_held,1,11) "Mode held"

from sys.dba_ddl_locks a, v$session b

where a.session_id = b.sid;



prompt

prompt =========================

prompt DML LOCKS - These are table and row locks...

prompt =========================

prompt

select substr(username,1,12) "User",

substr(owner,1,8) "Owner",

substr(name,1,20) "Name",

substr(mode_held,1,21) "Mode held"

from sys.dba_dml_locks a, v$session b

where a.session_id = b.sid;



prompt

prompt

prompt****************************************************

prompt Latch Section

prompt****************************************************

prompt if miss_ratio or immediate_miss_ratio > 1 then latch

prompt contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora

prompt

column "miss_ratio" format 999.99

column "immediate_miss_ratio" format 999.99

select substr(l.name,1,30) name,

(misses/(gets+.001))*100 "miss_ratio",

(immediate_misses/(immediate_gets+.001))*100

"immediate_miss_ratio"

from v$latch l, v$latchname ln

where l.latch# = ln.latch#

and (

(misses/(gets+.001))*100 > .2

or

(immediate_misses/(immediate_gets+.001))*100 > .2

)

order by l.name;



prompt

prompt

prompt****************************************************

prompt Rollback Segment Section

prompt****************************************************

prompt if any count below is > 1% of the total number of requests for data

prompt then more rollback segments are needed



--column count format 999,999,999

select class, count

from v$waitstat

where class in ('free list','system undo header','system undo block',

'undo header','undo block')

group by class,count;



column "Tot # of Requests for Data" format 999,999,999



select sum(value) "Tot # of Requests for Data" from v$sysstat where

name in ('db block gets', 'consistent gets');





prompt

prompt =========================

prompt ROLLBACK SEGMENT CONTENTION

prompt =========================

prompt

prompt If any ratio is > .01 then more rollback segments are needed



column "Ratio" format 99.99999

select name, waits, gets, waits/gets "Ratio"

from v$rollstat a, v$rollname b

where a.usn = b.usn;



column "total_waits" format 999,999,999

column "total_timeouts" format 999,999,999

prompt

prompt

set feedback on;

prompt****************************************************

prompt Session Event Section

prompt****************************************************

prompt if average-wait > 20 then contention might exists

prompt

select substr(event,1,30) event,

total_waits, total_timeouts, average_wait

from v$session_event

where average_wait > 0

and sid not in (select s.sid from v$session s

where s.paddr in (select p.addr from v$process p

where p.background='1'))

and event not like '%SQL%from%'

and event not like '%rdbms%'

and event not like '%wake%'

/

prompt

prompt****************************************************

prompt file i/o should be evenly distributed across drives.

prompt



select

substr(a.file#,1,2) "#",

substr(a.name,1,30) "Name",

a.status,

a.bytes,

b.phyrds,

b.phywrts

from v$datafile a, v$filestat b

where a.file# = b.file#;



column value format 999,999,999,999

select substr(name,1,55) system_statistic, value

from v$sysstat

order by name;



spool off;

===== For more pls check Meta Link Note:1019592.6

archive logs required to recovery

set echo on feedback on time on pagesize 20000

set linesize 300

set pause off

set serveroutput on

set feedback on

set echo on

set numformat 999999999999999

Spool recover.lst

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select * from v$version;

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as current_date from dual;

column name format a10

select dbid, name,

to_char(created, 'DD-MON-YYYY HH24:MI:SS') created,

open_mode, log_mode,

to_char(checkpoint_change#, '999999999999999') as checkpoint_change#,

controlfile_type,

to_char(controlfile_change#, '999999999999999') as controlfile_change#,

to_char(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time,

to_char(resetlogs_change#, '999999999999999') as resetlogs_change#,

to_char(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') resetlogs_time

from v$database;

select * from v$instance;

archive log list;

select file#,substr(name, 1, 50), status,enabled from v$datafile;

select distinct to_char(checkpoint_change#, '9999999999999999')

from v$datafile;

select status, distinct checkpoint_change#, to_char(checkpoint_time,'DD-MON-YYYY HH24:MI:SS') as checkpoint_time

from v$datafile_header;

select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

select * from v$backup;

select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;

select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#

from v$log;

select GROUP#,substr(member,1,60) from v$logfile;

select * from v$recover_file;

select * from v$recovery_log;

select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,

HXERR Validity,FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,

FHRBA_SEQ Sequence

from X$KCVFH;

spool off

Shrink Database Size...

Usefull doc i read written by Naresh




This database procedure can be used to shrink tablespaces by passing two values:



1. Tablespace_name



2. Freespace_keep_pct



This database procedure can be used to shrink tablespaces by passing two values:



1. Tablespace_name



2. Freespace_keep_pct



It's specially useful if you are refreshing a dev instance from prod and you don't want to occupy the same size RBS, TEMP, and tablespace as prodution. Just run this procedure as a last step of your refresh process and reclaim all the freespace that can be used for some other purpose.





/*

Procedure : Shrink_tbsp

Owner : SYS

Author : Naresh Awasthi



Note : Set serveroutput on before running this procedure.





*/



create or replace procedure SHRINK_TBSP (p_tablespace_name in varchar2,

p_keep_size_pct number) AUTHID CURRENT_USER is

FILE_SIZE number;

FREE_BYTES number;

FILEID number;



LAST_BLOCKID_USED number;

FREE_BLOCKID number;

FILE_NAME varchar2(2000);

STMT varchar2(2000);

SHRINK_TO number;



cursor FREE_SPACE_CUROR is

select file_id FILEID,max(block_id) FREE_BLOCKID from dba_free_space

where tablespace_name=p_tablespace_name group by file_id,bytes;





BEGIN

FOR fsc_row in FREE_SPACE_CUROR LOOP

select bytes into FREE_BYTES from dba_Free_space where file_id=fsc_row.FILEID and block_id=fsc_row.FREE_BLOCKID;

select bytes,file_name into FILE_SIZE,FILE_NAME from dba_data_files where file_id=fsc_row.FILEID;

select nvl(max(block_id),0) into LAST_BLOCKID_USED from dba_extents where file_id=fsc_row.FILEID;

if LAST_BLOCKID_USED < fsc_row.FREE_BLOCKID THEN

select round((FILE_SIZE - round(FREE_BYTES * (100 - p_keep_size_pct) / 100))/1024/1024) into SHRINK_TO from dual;

STMT := 'alter database datafile '

''''

FILE_NAME

''''

' resize '

SHRINK_TO

'M';

EXECUTE IMMEDIATE STMT;



dbms_output.put_line ('Shrunk '

FILE_NAME

' from

'

FILE_SIZE

' to '

SHRINK_TO

' MB.');

end if;

END LOOP;



EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line('Done.');

WHEN OTHERS THEN

dbms_output.put_line('Unhandled Error : '

sqlerrm);

END;
/