| Query takes too much time [message #305689] |
Tue, 11 March 2008 09:52  |
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  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
