Posts Tagged ‘Oracle FAQ’

delete statement performance impact

+12 Delete Statement Response Time  in Oracle: Below is a quick observation on my test machine. ———————————–Playing with Delete statement with enabled trigger,Logging/Nologging Below Analysis Result on below System Configuration: Two Node RAC 11gR2  created on Virtual Box CPU: 1(2.00 GHz) each node RAM: 2gb each node

Increase Redo Log File Size in Oracle 10g

+5  How to Increase Redo Log File Size :   Steps to Increase Redo Log File Size : SQL> select * from v$log; GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS  FIRST_CHANGE# FIRST_TIM

Query to get rolled back statement

+5 Query to get rolled back statement: SELECT x.SQL_TEXTFROM V$SQL x,v$session_longops y,v$session z where x.SQL_ID=y.SQL_ID and z.SQL_ADDRESS=y.SQL_ADDRESS and z.SQL_HASH_VALUE=y.SQL_HASH_VALUE;

Query to get Memory Usage for Each User Session

+5 Query to get Memory Usage for Each User Session: SELECT a.username, b.value “Memory in bytes per session”FROM v$session a, v$sesstat b, v$statname cWHERE a.sid = b.sid AND b.statistic# = c.statistic# AND c.name = ‘session uga memory’;

Query to get uncommitted statements in the current session

+5 Query to get uncommitted statements(INSERT, UPDATE or DELETE statements) in the current session: SQL> SELECT COUNT(*) FROM v$transaction t, v$session s, v$mystat m WHERE t.ses_addr = s.saddr AND s.sid = m.sid AND ROWNUM = 1;   COUNT(*) ———- 0   IF: count>0 SQL> SELECT COUNT(*) FROM v$transaction t, v$session s, v$mystat m WHERE t.ses_addr […]

Status Of Open Cursors

+6 SQL To Get Status Of Open Cursors: select max(a.value) as highest_open_cur, p.value as max_open_curfrom v$sesstat a, v$statname b, v$parameter pwhere a.statistic# = b.statistic# and b.name = ‘opened cursors current’and p.name= ‘open_cursors’group by p.value; OR select o.sid, osuser, machine, count(*) num_cursfrom v$open_cursor o, v$session swhere user_name = ” and o.sid=s.sidgroup by o.sid, osuser, machineorder by […]

Query to get deadlocked table and fired query during deadlock

+5 Query to get deadlocked table: select a.session_id,a.oracle_username, a.os_user_name, b.owner, b.object_name,b.object_type,a.locked_mode from (select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a, (select object_id, owner, object_name,object_type from dba_objects) bwhere a.object_id=b.object_id and a.oracle_username=’STDUIAPR29A’; Query to get fired query during deadlock: step1: get sid for deadlocked session:select sid from v$lock where block>0; step2: use above sid in followning […]