Home » RDBMS Server » Performance Tuning » Query takes too much time (10.1.0.5.0/HPUX)
Query takes too much time [message #305689] Tue, 11 March 2008 09:52 Go to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Hello Everybody,

I have fired a query which is running from more than 45 min wthout results.

Query is ...

CREATE TABLE verification.total_nov_CALLINGPRTYNO NOLOGGING AS
SELECT DISTINCT CALLINGPRTYNO,CALLINGSUBSIMEI FROM verification.AS_VERIFICATION_0711
WHERE SUBSTR(callingprtyno,-10) LIKE '9%'
AND DTSTRTCHRG >= TO_DATE ('25-nov-07', 'dd-mon-yy');


While querying v$session_longops i do not get anything.

Please advice what could be the bottle neck and how to solve that.

Thanks in advance for your valuable time.
Re: Query takes too much time [message #305691 is a reply to message #305689] Tue, 11 March 2008 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
number of rows, io, cpu, workload, concurrent sessions, concurrent processes, lack of index, swapping, wrong cache size...

Regards
Michel
Re: Query takes too much time [message #305696 is a reply to message #305689] Tue, 11 March 2008 10:15 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Thanks a lot for your response.

Below is the result from top command.

CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 1.34 35.3% 23.5% 15.7% 25.5% 0.0% 0.0% 0.0% 0.0%
1 1.77 33.3% 24.5% 20.6% 21.6% 0.0% 0.0% 0.0% 0.0%
2 1.65 36.3% 13.7% 21.6% 28.4% 0.0% 0.0% 0.0% 0.0%
3 1.55 39.2% 23.5% 21.6% 15.7% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 1.58 35.9% 21.4% 19.4% 23.3% 0.0% 0.0% 0.0% 0.0%


SQL> select count(1) from v$session where status ='ACTIVE';

COUNT(1)
----------
24


SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ -------------------------------
__db_cache_size big integer 6432M
db_cache_advice string ON
db_cache_size big integer 32M

we do have indexes on (CALLINGPRTYNO,CALLINGSUBSIMEI) columns used in the query.

please advice.

Re: Query takes too much time [message #305758 is a reply to message #305696] Tue, 11 March 2008 21:58 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post the Explain Plan.
How many rows are there WITHOUT the DISTINCT?

Ross Leishman
Previous Topic: Statspack
Next Topic: need help! how to intercept sql command of update
Goto Forum:
  


Current Time: Wed Dec 07 20:25:32 CST 2016

Total time taken to generate the page: 0.12297 seconds