Home » SQL & PL/SQL » SQL & PL/SQL » same query taking different time when executed thru diff dbs (Oracle 10g)
same query taking different time when executed thru diff dbs [message #577619] Tue, 19 February 2013 02:56 Go to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Hi All,

I have a query which is executing fast in dev env,but very long time in qa env.What is the criteria when this behaviour occurs.Though qa is having more data than dev.But still it is taking long time for 1 rows also.When I am using the query rownum<=1.So What to check for this.Even explain plan is also having same problem.

Regards,
Nathan
Re: same query taking different time when executed thru diff dbs [message #577620 is a reply to message #577619] Tue, 19 February 2013 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fix the problem and it will be OK.

Regards
Michel
Re: same query taking different time when executed thru diff dbs [message #577622 is a reply to message #577620] Tue, 19 February 2013 03:07 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Hi Michel,

Is any other area needs to have a look apart from query.Because query is as simple as one.Like gathering statistics etc.
To make the update statement look likes below I have modified like as follows to fasten the update statment.

update (select e.ename emp_ename,d.ename dept_ename from e1 e,dept1 d
where d.deptno=e.deptno) set dept_ename=emp_ename;

create table e1 as select * from emp 
where rowid in(select max(rowid) from emp group  by deptno);

alter table e1 add constraint id_must_be_unique unique(deptno);

select * from e1;

create table dept1 as select * from dept;


Have a look on this URL also.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:288016031632

So It's behaving differently .In real scenario dept and emp having records 40000 and 400000 records.

Regards,
Nathan

[Updated on: Tue, 19 February 2013 03:14]

Report message to a moderator

Re: same query taking different time when executed thru diff dbs [message #577627 is a reply to message #577622] Tue, 19 February 2013 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We have NO idea of what you have.

Regards
Michel
Re: same query taking different time when executed thru diff dbs [message #577631 is a reply to message #577627] Tue, 19 February 2013 04:23 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Hi Michel,

The row's been locked so even single record it is not updating.So can you please tell How to find out what are the table's been locked and how to realese the lock.Because I have tried so many times with giving rollback. Still it's in lock mode.

Regards,
Nathan
Re: same query taking different time when executed thru diff dbs [message #577632 is a reply to message #577631] Tue, 19 February 2013 04:34 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Hi All,

Thanks All for valuable time,now I got the solution .Please find it below.

select a.object_name, b.oracle_username, b.os_user_name,c.sid, c.serial#,c.terminal, d.sql_text
from sys.dba_objects a,
v$locked_object b,
v$session c,
v$sqltext d
where a.object_id = b.object_id
and c.sid = b.session_id
and c.sql_hash_value = d.hash_value

alter system kill session 'c.sid, c.serial#';


Regards,
Nathan
Re: same query taking different time when executed thru diff dbs [message #577633 is a reply to message #577631] Tue, 19 February 2013 04:36 Go to previous message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ "How to find out what are the table's been locked "
If someone is waiting on it, you can query v$lock
If no one is waiting on it, you can search on the web, several have posted a way to do it (basically you try to get the row)

2/ "how to realese the lock"
Only the one that locks the row can released it.
The alternative is to kill the holder session.

Regards
Michel
Previous Topic: Mutating trigger while updating original table - Tried using temporary table but still :-(
Next Topic: Updates by Week
Goto Forum:
  


Current Time: Fri Sep 19 08:52:48 CDT 2014

Total time taken to generate the page: 0.14262 seconds