0

I am logged into my application from system. I perform refresh from one user and copy paste from the other. refresh has mainly a set of select queries and copy paste is having more of insert queries. refresh as such takes one minute or less to perform but when copy-paste is being done from other system it takes a lot of time or waits for the copy paste to complete and only then it completes.

I am using oracle 10g database.I have been using oracle sql develepor (monitor session) to see the real time queries but not have been able to use it effectively.

Can you please tell me:

  1. How to see conflicting queries if at all.
  2. How to see locks acquired by variuos queries.
  3. how long it takes to complete one query.
  4. Any other suggestion or any other approach or tool that i may use .
Casper Leon Nielsen
  • 2,528
  • 1
  • 28
  • 37
jetty
  • 859
  • 2
  • 17
  • 37

1 Answers1

0

How to see conflicting queries

In Enterprise Edition, you can use the Enterprise Manager to track the bloking sessions, and the participating queries. (Enterprise Manager for 10g documentation)

You can also write SQL queries for this, like detailed in this article: Tracking Oracle blocking sessions

SQL from the article (listing blocking sessions):

select blocking_session, sid, serial#, wait_class, seconds_in_wait
from  v$session
where blocking_session is not NULL
order by blocking_session;

Listing the active queries (from Ask Anantha):

SELECT a.USERNAME, a.STATUS, b.sql_text
FROM V$SESSION a 
INNER JOIN V$SQLAREA b ON a.SQL_ADDRESS= b.ADDRESS;

How to see locks acquired by variuos queries.

This query will tell you the session IDs (From Oracle forum):

set linesize 150; set head on; col sid_serial form a13 col ora_user for a15; col object_name for a35; col object_type for a10; col lock_mode for a15; col last_ddl for a8; col status for a10;

break on sid_serial;

SELECT l.session_id||','||v.serial# sid_serial,
   l.ORACLE_USERNAME ora_user,
   o.object_name, 
   o.object_type, 
   DECODE(l.locked_mode,
      0, 'None',
      1, 'Null',
      2, 'Row-S (SS)',
      3, 'Row-X (SX)',
      4, 'Share',
      5, 'S/Row-X (SSX)',
      6, 'Exclusive', 
      TO_CHAR(l.locked_mode)
   ) lock_mode,
   o.status, 
   to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
FROM dba_objects o, gv$locked_object l, v$session v
WHERE o.object_id = l.object_id
  and l.SESSION_ID=v.sid
order by 2,3;

How long it takes to complete one query

You can track it with this SQL from SearchOracle

SELECT * 
FROM 
    (select 
        username,opname,sid,serial#,context,sofar,totalwork ,round(sofar/totalwork*100,2) "% Complete" 
    from v$session_longops) 
WHERE "% Complete" != 100

Any other suggestion or any other approach or tool that i may use

Well, Google comes to mind...

ppeterka
  • 20,583
  • 6
  • 63
  • 78
  • The query is returning no records. But refresh is taking so much of time only when multiple users are logged in , and someones performing copy + paste. Can this be happening because of any other reason than locks and blocking sessions.? – jetty Jan 10 '13 at 09:55
  • i am seeing the query in real time but they seem to take more time when copy paste is also done simultaneously. The query for blocking session is still returning no records. Can i be missing anything here? – jetty Jan 10 '13 at 10:03
  • Well, any performance bottleneck (as the name implies...) can cause slowness too. Check for I/O and memory contention, and badly sized logfiles, meaning too quick log rotation. – ppeterka Jan 10 '13 at 10:03