| SELECT works, INSERT SELECT hangs (merrged 2) [message #507635] |
Tue, 17 May 2011 07:29  |
 |
Unknown_Entity
Messages: 9
Registered: May 2011
|
Junior Member |
|
|
Hello, I tried to google this problem but was unable to find and solution. I have quite a long query in format of
INSERT INTO table
SELECT (...)
which in SQL Developer just "hangs", it never finishes, does nothing --- if I query the v$session I can identify its SID and just watch time_waited (or something like it) to increase.
The strange think though is, that the select without insert completes in just a few seconds. If I replace selected columns with static values (i.e. instead of select a, b ... I do select 'alpha', 'beta' ) and use same table joins and conditions as before it works as well (only change is that I add where rownum=1 to avoid dupes).
Could you please point me to how to debug this? I have full access to the server and its logs.
Things I ve tried are:
select * from dba_locks -> none shows as blocking when the query is running
select * from dba_waiters -> empty
Thank you for your help, I ve spend one full day on this and its getting really frustrating:/
|
|
|
|
|
|
|
|
|
|
| Re: SELECT works, INSERT SELECT hangs [message #507654 is a reply to message #507651] |
Tue, 17 May 2011 08:00  |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Adding a commit to the end will make no difference to the speed. It'll also make no difference to locks, since if your insert really was hanging then it'd the other session blocking it that would need to commit.
It probably appeared to start working due to caching - After the select has been run a few times the datablocks will all be loaded in the cache.
Subsequent queries against them will run faster is oracle no longer needs to go to disk to get the data.
|
|
|
|
|
|
|
|
|
|
| Re: SELECT works, INSERT SELECT hangs [message #507670 is a reply to message #507669] |
Tue, 17 May 2011 08:29 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Also, I have included this query in our "Helpdesk" tools. It pretty much has returned the problematic query every time someone complained that "the system hangs"
(Should work in 10.X and up, 9.X would need an additional join to v$session_event)
select DISTINCT s.sid,s. SERIAL#, event, p1text, p1, p2text, p2, p3text, p3,
wait_time, seconds_in_wait, state, osuser, program,client_info, s.MODULE, sql_text
from v$session s
join v$sql q ON q.hash_value = s.sql_hash_value
where event not in ('SQL*Net message from client', 'SQL*Net message to client', 'jobq slave wait')
AND event not like 'PX Deq Credit%';
|
|
|
|
|
|
|
|
| Re: SELECT works, INSERT SELECT hangs [message #507699 is a reply to message #507694] |
Tue, 17 May 2011 09:50 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Well, the "message to/from client" events are pretty much there every time the client isn't doing anything. (for example waiting for user input).
So the solution would be to tell people to "type faster" ;-P
The *Other* possibility of those waits could happen would be an actual network bottleneck, where the database or the client is just waiting for the data to get transferred. But I have never seen one of those, unless it was a very slow WAN connection.
|
|
|
|
|
|
|
|
|
|
| Re: SELECT works, INSERT SELECT hangs [message #507711 is a reply to message #507706] |
Tue, 17 May 2011 10:11 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
I use SQL developer a lot, but it can get...funky on some queries/script outputs. If you think the wait is suspect, first thing you should try is using SQL*Plus.
Start>Run>cmd
sqlplus USER/PASS@SID
set timing on
|
|
|
|
|
|
| Re: SELECT works, INSERT SELECT hangs [message #507715 is a reply to message #507702] |
Tue, 17 May 2011 10:17 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Unknown_Entity wrote on Tue, 17 May 2011 16:57what do you mean tell them to type faster?
I mean that especially the "message from client" wait event is just the client waiting for user input. So the only way to get rid of that wait event is to tell the user to type faster.
|
|
|
|
|
|
|
|
| Re: SELECT works, INSERT SELECT hangs [message #507721 is a reply to message #507716] |
Tue, 17 May 2011 10:24 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So you're actually comparing:
Select * from
(select * from T order by column)
where rownum <100
and
Select * from
(select * from T order by column)
In which case oracle can use a different approach for finding the data in each case. And that'd be a different approach again to what you initially said you were running. Posting accurate sql is necessary if you want accurate help.
You can do the trace.
You can also do an explain plan of each and post them here.
|
|
|
|
|
|
|
|
| Re: SELECT works, INSERT SELECT hangs [message #507728 is a reply to message #507720] |
Tue, 17 May 2011 10:46 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Unknown_Entity wrote on Tue, 17 May 2011 17:21ThomasG wrote on Tue, 17 May 2011 10:17
I mean that especially the "message from client" wait event is just the client waiting for user input. So the only way to get rid of that wait event is to tell the user to type faster.
I am not sure if you understand what is my problem;
1. I enter the SQL query
2. I run it
3. it never completes, hangs on waiting for client
so what use is it to type faster:) ?
My point is: When it still is "waiting for the client", then it is NOT really "running" it is still waiting for the client to start it. Which means you never really did point 2 "run it"
|
|
|
|
|
|
|
|
| Re: SELECT works, INSERT SELECT hangs [message #507752 is a reply to message #507714] |
Tue, 17 May 2011 12:34 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Unknown_Entity wrote on Tue, 17 May 2011 11:14
and I assume it returns first 100 rows from the output, provided that I use order by clause it should return the same rows each time.
Incorrect assumption. WHERE clause comes first, then it ORDERs the output.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
