Thoughts of Oracle !: 2012

http://www.oracle-class.com/

rmanbackup.com

orafaq.com

http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/OTOC44/Default.aspx

How to trace :
================

1)

set serveroutput on size 1000000 for wra
declare
paramname varchar2(256);
integerval binary_integer;
stringval varchar2(256);
paramtype binary_integer;
begin
paramtype:=dbms_utility.get_parameter_value('user_dump_dest',integerval,stringval);
if paramtype=1 then
dbms_output.put_line(stringval);
else
dbms_output.put_line(integerval);
end if;
end;
/

2)

select name,value
from v$parameter
where name='user_dump_dest';

3)
set lines 1000 pages 1000

select s.sid,s.serial#,s.username,s.osuser
from v$session s,v$process p
where s.paddr=p.addr;

SYSTEM : SID: 61 SERIAL : 325

4)

exec dbms_system.set_sql_trace_in_session(61,325,true);

exec dbms_system.set_sql_trace_in_session(61,325,false);

select * from v$session_longops where time_remaining>0 order by time_remaining desc
select event,seconds_in_wait,status from v$session where program like '%DW%';

The followng trace files are created :

-rw-r----- 1 oracle oinstall 1018 Jan 24 17:00 RAC2_j001_12799.trc
-rw-r----- 1 oracle oinstall 1946 Jan 24 17:03 RAC2_lmd0_4111.trc
-rw-r----- 1 oracle oinstall 3053 Jan 24 17:04 RAC2_pz97_12966.trc
-rw-r----- 1 oracle oinstall 4801 Jan 24 17:07 RAC2_dw00_12972.trc
-rw-r----- 1 oracle oinstall 12187 Jan 24 17:07 RAC2_pz98_12958.trc
-rw-r----- 1 oracle oinstall 5049 Jan 24 17:07 RAC2_dm00_12893.trc
-rw-r----- 1 oracle oinstall 81029 Jan 24 17:07 RAC2_pz99_12750.trc
-rw-r----- 1 oracle oinstall 649892 Jan 24 17:07 RAC2_ora_12789.trc

But in above files, only "RAC2_ora_12789.trc" is increasing , so that I update this file only .

DECLARE
hand NUMBER;
BEGIN
hand := Dbms_DataPump.Open(operation => 'EXPORT',
job_mode => 'FULL',
job_name => 'FULLEXPJOB',
version => 'COMPATIBLE');
Dbms_DataPump.Add_File(handle => hand,
filename => 'expdp_plsql.log',
directory => 'DMPDIR',
filetype => 3);
Dbms_DataPump.Add_File(handle => hand,
filename => 'expdp_plsql.dmp',
directory => 'DMPDIR',
filetype => 1);
-- Dbms_DataPump.Set_Parameter(handle => hand,
-- name => 'ESTIMATE',
-- value => 'STATISTICS');
Dbms_DataPump.Start_Job(hand);
END;
/

By DBMS_MONITOR :
=================

DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
plan_stat IN VARCHAR2 DEFAULT NULL);

DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN 67,
serial_num IN 413,
waits IN TRUE,
binds IN FALSE,
plan_stat IN NULL);

EXECUTE dbms_monitor.session_trace_enable (2827, 64);

EXECUTE dbms_monitor.session_trace_disable (2827, 64);

Can one monitor how fast a table is imported?
==============================================

If you need to monitor how fast rows are imported from a running import job, try one of the following methods:
Method 1:

select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;

For this to work one needs to be on Oracle 7.3 or higher (7.2 might also be OK).
If the import has more than one table, this statement will only show information about the current
table being imported.

mystat :
========

select distinct * from v$sysstat order by class

select * from v$mystat

All Alerts :
=============

>dba_outstanding_alerts : DBA_OUTSTANDING_ALERTS describes alerts which the server considers to be
outstanding.

>dba_alert_history : DBA_ALERT_HISTORY describes a time-limited history of alerts which are no longer
outstanding.

>v$alert_types : V$ALERT_TYPES displays information about server alert types.

>user_advisor_findings
dba_advisor_objects
dba_advisor_actions
dba_advisor_rationale
----------------------------------------------------------------------

select username,sid,serial#,logon_time,status from v$session where sid=375

select * from dba_outstanding_alerts order by time_suggested desc

select * from dba_alert_history order by time_suggested desc

select * from user_advisor_findings

select * from dba_advisor_objects where owner='ABABIL'

select * from dba_advisor_actions where owner='ABABIL'

select * from dba_advisor_rationale where owner='ABABIL'

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

----------------------------------------------------------------------------------------------------------------
|session_wait shows what is each session waiting at at the instance you issue select * from v$session_wait
|
|session_event would be the sum of session_wait in each session and each event
|
|system_event would be the sum of all waits in session_event
|
|normally I use session_wait to check for enqueue or those transactions that is taking long time (and see what they are doing/waiting)
|
|I query session_event when I am tracing an user and see how many waits, wait time etc his session has been facing
|------------------------------------------------------------------------------------------------------------

select event,count(*) from v$session_wait group by event

select event,count(*) from v$session_event group by event

select sid,p1,p2,p3 from v$session_wait where event='latch: cache buffers chains'

select * from v$latchname where latch#=122

select 'kill -9 '||spid from v$process where addr in(
select paddr--sid,username,program,sql_address,logon_time,status,machine
from v$session where event='latch: cache buffers chains' and program like '%java%'
and username is not null and logon_time
select sql_address,count(*) from v$session where event='latch: cache buffers chains' group by sql_address

select sql_fulltext,address,sql_id from v$sqlarea where sql_id ='1grbab9syxsx9'

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

SELECT /*+ FIRST_rows */ * FROM VW_DAILY_VOUCHER
WHERE GLOBALTXNNO IN (
SELECT DISTINCT GLOBALTXNNO FROM VW_DAILY_VOUCHER WHERE TXNDATE = : P_TXNDATE AND GLOBALTXNNO <> 0
AND NVL ( BATCHNO , '%' ) LIKE NVL ( : P_BATCHNO , '%' ) AND NVL ( GLCODE , '%' ) LIKE NVL ( : P_GLCODE , '%' )
AND NVL ( TO_CHAR ( GLOBALTXNNO ) , '%' ) LIKE NVL ( : P_GLOBALTXNNO , '%' )
AND NVL ( ENTRYUSER , '%' ) LIKE NVL ( : P_ENTRYUSER , '%' ) AND NVL ( POSTINGUSER , '%' ) LIKE NVL ( : P_VERIFYUSER , '%' )
AND NVL ( OWNERBRANCHID , '%' ) LIKE NVL ( : P_OWNERBRANCH , '%' )
AND NVL ( INITIATORBRANCHID , '%' ) LIKE NVL ( : P_INITBRANCH , '%' ) )
ORDER BY 3 ASC,19 ASC,20 ASC,16 ASC,17 ASC,1 ASC,2 ASC,18 ASC , to_number ( TRACERNO ) , 17 , 3 , 4

select sum(pga_alloc_mem) from v$process

SELECT vv.TXNDATE, vv.VALUEDATE, vv.GLOBALTXNNO, vv.TRACERN
O, vv.GLCODE, vv.GLTITTLE, vv.PARTICULARS, --vv.GLDT
CLIENTACCNO, vv.GLCURRENCY, --vv.BATCHNO, vv.LCYDEBIT,
vv.LCYCREDIT, vv.OWNERBRANCHID, vv.ENTRYUSER FROM VW_

select * from user_views where view_name like 'VV%'

select sid,username,program,sql_address,logon_time,event from v$session where status='ACTIVE' order by logon_time desc

select sql_text from v$sqlarea where address='070000058D49EE90'

select sql_address,prev_sql_addr from v$session where program like '%toad%'

select * from user_objects order by last_ddl_time desc

select event,count(*) from v$session_wait group by event

--CPU TIme :
===========

SELECT
SST.VALUE CPU, S.USERNAME, S.SID, S.SERIAL#, SQL_TEXT
FROM
V$SESSION S,
V$SQLTEXT_WITH_NEWLINES T,
V$SESSTAT SST
WHERE S.SQL_ID IS NOT NULL
AND S.SQL_ID = T.SQL_ID
AND SST.SID = S.SID
AND SST.STATISTIC# = (SELECT STATISTIC# FROM V$STATNAME SN WHERE SN.NAME = 'CPU used by this session')
ORDER BY 1 DESC, S.SID,T.PIECE;

==CPU TIME 3:
==============

a) ps -eaf -o pcpu,pid,user,tty,comm | grep ora | grep -v \/sh | grep -v ora_ | sort -r | head -20

b) SELECT a.username, b.sql_text
FROM v$session a, v$sqlarea b, v$process c
WHERE (c.spid = '&PID' OR a.process = '&PID')
AND a.paddr = c.addr
AND a.sql_address = b.address

==CPU TIME 3:
===============

select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
order by cpu_time desc

--Longes Time Query :
========================

select sql_text,elapsed_time,sql_id,hash_value,plan_hash_value,program_id,module,action
from v$sql where module like 'TOAD%' order by elapsed_time desc

select sql_text,elapsed_time,sql_id,hash_value,plan_hash_value,program_id,module,action
from v$sql where module like 'frmweb%' order by elapsed_time desc

select sesion.sid,
sesion.serial#,
sesion.username,
sesion.sql_id,
sesion.sql_child_number,
optimizer_mode,
hash_value,
address,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null

--Session Query :
===============

SELECT p.spid,
s.process,
s.status,
s.machine,
s.username,
TO_CHAR (s.logon_time, 'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et / 60 TIME_IN_MIN,
s.action,
s.module,
s.program program,
s.sid sid,
s.serial# serial,
s.osuser,
'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
sql_string
FROM V$SESSION s,V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND UPPER(s.OSUSER) = 'ORACLE'
AND (UPPER(s.PROGRAM) LIKE 'FRMWEB%' OR UPPER(s.PROGRAM) LIKE 'JAVA%'
)
AND s.status = 'INACTIVE'
--and (Last_Call_ET/60)>(select TO_NUMBER(APCVALUE) from APPCONFIGURATIONwhere TRIM(UPPER(APCITEM))='APP_IDLE_TIME_IN_MINUTES')
ORDER BY p.spid

SELECT p.spid,
s.process,
s.status,
s.machine,
s.username,
TO_CHAR (s.logon_time, 'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et / 60 TIME_IN_MIN,
s.action,
s.module,
s.program program,
s.sid sid,
s.serial# serial,
s.osuser,
'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
sql_string
FROM V$SESSION s,V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND UPPER(s.OSUSER) = 'ORACLE'
AND (UPPER(s.PROGRAM) LIKE 'FRMWEB%' OR UPPER(s.PROGRAM) LIKE 'JAVA%'
)
AND s.status = 'INACTIVE'
--and (Last_Call_ET/60)>(select TO_NUMBER(APCVALUE) from APPCONFIGURATIONwhere TRIM(UPPER(APCITEM))='APP_IDLE_TIME_IN_MINUTES')
ORDER BY p.spid

Oracle with OS user :
=====================

select s.username "Oracle User",s.osuser "OS User",i.consistent_gets "Consistent Gets",
i.physical_reads "Physical Reads",s.status "Status",s.sid "SID",s.serial# "Serial#",
s.machine "Machine",s.program "Program",to_char(logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time",
w.seconds_in_wait "Idle Time", P.SPID "PROC",
name "Stat CPU", value
from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and 'SQL*Net message from client' = w.event(+)
and s.osuser is not null
and s.username is not null
and s.paddr=p.addr
and n.statistic# = t.statistic#
and n.name like '%cpu%'
and t.SID = s.sid
and status='ACTIVE'
order by 6 asc, 3 desc, 4 desc

Lock Object :
===============

--Find Database Lock:
====================

select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

--Details :
===============

SELECT
DISTINCT BLOCKER,
SID_SERIAL,
COUNT(*)
FROM
(
select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' || (select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' || (select serial# from v$session where sid=b.sid) sid_serial_V
from
v$lock a,
v$lock b
where
a.block = 1
and
b.request > 0
and
a.id1 = b.id1
and
a.id2 = b.id2
)
GROUP BY BLOCKER, SID_SERIAL
ORDER BY COUNT(*) DESC

select sql_hash_value from v$session where sid = &sid

select sql_Text from v$sql where hash_value = &sql_hash_value

SELECT * FROM V$OPEN_CURSOR WHERE SID = 767

--How to Get Detail of Locks with Object Locked:
==============================================

SELECT VLO.OS_USER_NAME "OS USERNAME", VLO.ORACLE_USERNAME "DB USER",
VP.SPID "SPID", AO.OWNER "OWNER", AO.OBJECT_NAME "OBJECT LOCKED",AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, 'NO LOCK',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCL',
6, 'EXCLUSIVE',
NULL
) "MODE OF LOCK",
VS.STATUS "CURRENT STATUS"
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> 'KILLED'
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;

--Find Last SQL query :
========================

select S.USERNAME||'('||s.sid||')-'||s.osuser UNAM
-- ,s.program||'-'||s.terminal||'('||s.machine||')' PROG
,s.sid||'/'||s.serial# sid
,s.status "Status",p.spid
,sql_text sqltext
from v$sqltext_with_newlines t,V$SESSION s , v$process p
where t.address =s.sql_address
and p.addr=s.paddr(+)
and t.hash_value = s.sql_hash_value
order by s.sid,t.piece
/

--cpu load :
==========

select * from v$osstat

select * from v$sysmetric_history

--io/cpu usage query :
========================

select
sid,
username, terminal,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username, b.terminal terminal,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by
b.sid, nvl(b.username,p.name) , b.terminal),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by
4 desc

select * from v$filestat

--Find SPID:
=============

select a.username,a.sid,b.spid from v$session a, v$process b where b.spid = '1638634'

select a.username,a.sid,b.spid from v$session a, v$process b where a.serial# = b.serial#

select s.username,p.spid from v$session s, v$process p
where s.paddr=p.addr AND p.background is null;

Tablespace Free Space :
=======================

SELECT a.tablespace_name "TableSpace",
ROUND(SUM(a.Ebytes),3) "Ext. Space(GB)",
ROUND(SUM(a.bytes),3) "Alo. Space(GB)",
(ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3)) "Used Space(GB)",
ROUND(SUM(a.Ebytes),3) - ((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "Free Space(GB)",
ROUND(SUM(a.bytes),3)-((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "ABs Free Space(GB)"
FROM (SELECT tablespace_name tablespace_name,SUM(DECODE(maxbytes,0,bytes,maxbytes)/1024/1024/1024) Ebytes,
SUM((bytes)/1024/1024/1024) bytes
FROM DBA_DATA_FILES
GROUP BY tablespace_name ) a,
(SELECT tablespace_name tablespace_name, SUM(bytes/1024/1024/1024) bytes
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) c
WHERE a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name
UNION
SELECT 'XXXTOTAL:',
ROUND(SUM(a.Ebytes),3) "Ext. Space(GB)",
ROUND(SUM(a.bytes),3) "Alo. Space(GB)",
(ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3)) "Used Space(GB)",
ROUND(SUM(a.Ebytes),3) - ((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "Free Space(GB)",
ROUND(SUM(a.bytes),3)-((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "ABs Free Space(GB)"
FROM (SELECT tablespace_name tablespace_name,SUM(DECODE(maxbytes,0,bytes,maxbytes)/1024/1024/1024) Ebytes,
SUM((bytes)/1024/1024/1024) bytes
FROM DBA_DATA_FILES
GROUP BY tablespace_name ) a,
(SELECT tablespace_name tablespace_name, SUM(bytes/1024/1024/1024) bytes
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) c
WHERE a.tablespace_name = c.tablespace_name

-----To check imp0rt performence
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;

logical/physical read :
=======================

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('session logical reads','physical reads',
'physical reads direct','physical reads direct (lob)');

To check import performence :
==============================

select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;

To find Longops :
=================

select * from v$session_longops where time_remaining>0 order by time_remaining desc

select username,sid,serial#,logon_time,status,machine,sql_id,prev_sql_id from v$session

select * from v$sqlarea where sql_id ='8wk7fbjqgf1ap'

FRA space :
============
SELECT space_limit/1024/1024/1024 "TOTAL_SPACE_GB",space_used/1024/1024/1024 "SPACE_USED_GB" FROM V$RECOVERY_FILE_DEST;
select * from v$recovery_file_dest
select * from v$flash_recovery_area_usage

Archive History :
=================
select to_char(first_time,'DD/MON') day,
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'000')"01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'000')"02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'000')"03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'000')"04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'000')"05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'000')"06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'000')"07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'000')"08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'000')"09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'000')"10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'000')"11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'000')"12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'000')"13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'000')"14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'000')"15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'000')"16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'000')"17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'000')"18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'000')"19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'000')"20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'000')"21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'000')"22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'000')"23",
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'000') "00",
count(*) Total
from v$log_history
WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) - 7
group by to_char(first_time,'DD/MON') order by 1 desc

Logminer :
==========

select * from V$LOGMNR_CONTENTS

Analyze Table :
===============
select * from table(dbms_xplan.display_cursor('4u1s5ns3nuwbz',0))

From Banaful :
==============

select * from v$session_wait

select sid,p1,p2,p3,p2text from v$session_wait where event like 'SQL*Net%'

select segment_name from dba_extents where file_id=1 and (:p2 value) between block_id and block_id+1

select count(*) from obj

select count(*) from sys.obj$

PGA MEMORY USED :
=================

The following query can be used to find the total amount of memory allocated to the PGA areas of all sessions:

SQL> select sum(value)/1024/1024 Mb
from
v$sesstat s, v$statname n
where
n.STATISTIC# = s.STATISTIC# and
name = 'session pga memory';

Which process is requesting too much memory? :
===============================================

Some operations will require lots of process memory like huge PL/SQL tables or big sort operations. In these cases,
the processes will run for a certain period of time before getting the ora-4030 error, hopefully allowing us to
find out where and why the memory is allocated. You can use the following query to find out oracle's idea of
PGA and UGA size for the oracle processes.

select
sid,name,value
from
v$statname n,v$sesstat s
where
n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%'
order by 3 asc; This query will show the most hungry process last in the list.

select sql_text from v$sqlarea a, v$session s where a.address = s.sql_address and s.sid = 1024 ;

Resource Plan :
================

alter system set RESOURCE_MANAGER_PLAN='system_plan'

To check : select * from V$RSRC_PLAN

To set null : alter system set RESOURCE_MANAGER_PLAN=''

OS SPID with SQL_TEXT :
========================

SELECT a.username,
a.machine,
a.program,
a.sid,
a.serial#,
a.status,
c.piece,
c.sql_text
FROM v$session a,
v$process b,
v$sqltext c
WHERE b.spid in (1323346)
AND b.addr = a.paddr
AND a.sql_address = c.address (+)
ORDER BY a.sid, c.piece;

[oracle@apps1 ~]$ rwdiag.sh -findall

Broadcast mechanism used to locate servers
------------------------------------------
Channel address = 228.5.6.7
Channel port = 14021

(1) Name = rep_svr4 : Type = server : Host = apps1.aibl.com
(2) Name = rep_svr3 : Type = server : Host = apps1.aibl.com
(3) Name = rep_svr2 : Type = server : Host = apps1.aibl.com
(4) Name = rep_svr1 : Type = server : Host = apps1.aibl.com
(5) Name = rep_apps1_as_middle_home : Type = server : Host = apps1.aibl.com
[oracle@apps1 ~]$

opmnctl startproc ias-component=LogLoader

Thoughts of Oracle !

Sunday, March 4, 2012

How to kill impdp job when unavailable !

Sunday, February 12, 2012

How to trace datapump job !

Sunday, January 22, 2012

All Oracle Command !

Friday, January 20, 2012

Special Query

About Me

Blog Archive