+14 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
Posts Tagged ‘Oracle FAQ’
delete statement performance impact
June 6th, 2015 
rishi 
Posted in Uncategorized 
Tags: delete, delete response time, logging, nologging, oracle, Oracle FAQ, trigger 
No Comments »Increase Redo Log File Size in Oracle 10g
June 25th, 2012 rishi +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
Posted in Uncategorized Tags: 10g, logfile, oracle, oracle 10g, Oracle FAQ, redo log file, sql No Comments »Query to get rolled back statement
July 23rd, 2011 rishi +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
July 23rd, 2011 rishi +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
July 23rd, 2011 rishi +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
April 24th, 2011 rishi +7 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
February 23rd, 2011 rishi +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 […]
