Home » RDBMS Server » Performance Tuning » SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools (Oracle, 11.2, SQLDeveloper)
SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601314] Tue, 19 November 2013 10:02 Go to next message
royalyogi
Messages: 22
Registered: November 2013
Location: India
Junior Member


The strange think is,
that the select without insert completes in just a few seconds but the insert+select query would hang without output in sqldeveloper.

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601315 is a reply to message #601314] Tue, 19 November 2013 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601317 is a reply to message #601315] Tue, 19 November 2013 10:18 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Five internet dollars says that you're not returning all the rows in sql developers preview pane.
Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601318 is a reply to message #601317] Tue, 19 November 2013 10:23 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
you wouldn't get odds on that in vegas
Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601325 is a reply to message #601314] Tue, 19 November 2013 12:43 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
royalyogi wrote on Tue, 19 November 2013 21:32

The strange think is,
that the select without insert completes in just a few seconds but the insert+select query would hang without output in sqldeveloper.



But, what are you actually doing? A select statement might be a projection or selection. And an insert statement is a DML transaction. So please post what exactly you are doing. The sticky on top of performance tuning forum would help you on how to post regarding performance tuning questions.
Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601355 is a reply to message #601325] Tue, 19 November 2013 23:36 Go to previous messageGo to next message
royalyogi
Messages: 22
Registered: November 2013
Location: India
Junior Member

Update:
First It's select the rows then It insert rows in a table.So here select query do not have any issue but while insert inot a table it's like look hangs.

Same query is running fine for prod databases using SQLDevelpoer tools.
Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601357 is a reply to message #601355] Tue, 19 November 2013 23:42 Go to previous messageGo to next message
royalyogi
Messages: 22
Registered: November 2013
Location: India
Junior Member

Roachcoach : you're not returning all the rows in sql developers preview pane.

But same query is running fine in production database and production database is also returing same number of rows. So I don;t think returning number of rows is problem.

I have compared prod and nonprod database and only difference is nls_character set.

Really I need help here.
Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601365 is a reply to message #601357] Wed, 20 November 2013 02:13 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
royalyogi wrote on Wed, 20 November 2013 06:42
But same query is running fine in production database and production database is also returing same number of rows. So I don;t think returning number of rows is problem.

Just for completeness: about how many rows are you talking?

If there is a primary key/unique constraint on the table to which the rows are inserted, then INSERT may wait until another session INSERTing/UPDATing to the same PK/UQ values commits its changes.

But, of course, without providing a test case - CREATE TABLE statements for table structure, INSERT statements for sample data and the steps reproducing the issue - it is imposible to spot the reason, we may just make (more or less) qualified guesses as I did.
Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601368 is a reply to message #601365] Wed, 20 November 2013 03:01 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post the select statement, the insert statement, the explain plan for both (or better tkprof) and the number of rows selected.
Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601370 is a reply to message #601365] Wed, 20 November 2013 03:05 Go to previous messageGo to next message
royalyogi
Messages: 22
Registered: November 2013
Location: India
Junior Member

same query run fine in production database and checked table structre,parameters and memory are same in both the databases.
But script run fine and not run in nonproduction database using SQLDeveloper tool.

Number of Rows:15890

Table structure:
CREATE TABLE "I$USER_WUS"
( "DOWNLOAD_DATE" DATE,
"ROW_STATUS" VARCHAR2(Cool,
"PROCESS_FLAG" CHAR(2),
"RECORD_NO" NUMBER(5,0),
"USER_ID" VARCHAR2(64),
"REF_COUNTRY" CHAR(2),
"PROFILE_CODE" VARCHAR2(64),
"USER_TYPE" VARCHAR2(64),
"WEB_VISUALISATION" CHAR(1),
"ADDRESS_LINE_3" VARCHAR2(128),
"CITY" VARCHAR2(64),
"POSTAL_CODE" VARCHAR2(50),
"COUNTRY_NAME" VARCHAR2(128),
"PHONE" VARCHAR2(60),
"MOBILE_PHONE" VARCHAR2(60),
"FAX_NUMBER" VARCHAR2(60),
"E_MAIL" VARCHAR2(250),
"MIFID" NUMBER(1,0),
"PROPOSITION" NUMBER(1,0),
"REPORTING" NUMBER(1,0),
"IND_UPDATE" CHAR(1)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
TABLESPACE "ODATA"
Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601373 is a reply to message #601370] Wed, 20 November 2013 04:21 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Do you really think it contains anything useful for reproducing your problem? No note about the use of table (source or destination one?), other points (DML for sample data, query) missing at all.
With the details you posted I may state this explanation: there is somewhere something wrong.

Good luck in detecting it.
Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601381 is a reply to message #601370] Wed, 20 November 2013 05:36 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
royalyogi wrote on Wed, 20 November 2013 14:35
same query run fine in production database and checked table structre,parameters and memory are same in both the databases.
But script run fine and not run in nonproduction database using SQLDeveloper tool.




cookiemonster wrote on Wed, 20 November 2013 14:31
Post the select statement, the insert statement, the explain plan for both (or better tkprof) and the number of rows selected.


You have not yet mentioned that what exactly are you doing? Is it a plain SQL that you are using for the task? Or is it PL/SQL? Please post the things as suggested by cookiemonster.
Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601433 is a reply to message #601381] Wed, 20 November 2013 21:33 Go to previous messageGo to next message
royalyogi
Messages: 22
Registered: November 2013
Location: India
Junior Member

first I says thanks to everyone. Now client said, insert query is also running fine in nonprod database. He said, the table are not analysed that why with insert script was very slow.

But I am not agree with him bacause of below points
1)No issue was in select query. it always complete within minute. We tried to insert data in newly created table I$USER_WUS.
How analysed the table will help to resolve this issue????????????????????
Since we know insert will more faster than select.

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601434 is a reply to message #601433] Wed, 20 November 2013 21:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601438 is a reply to message #601434] Wed, 20 November 2013 23:20 Go to previous messageGo to next message
royalyogi
Messages: 22
Registered: November 2013
Location: India
Junior Member

BlackSwan: above link show Invalid link. please check and let me know
Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601447 is a reply to message #601438] Thu, 21 November 2013 02:38 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It should be the sticky from the top of the performance tuning forum: http://www.orafaq.com/forum/t/84315/

However we've already said what we need, repeatedly, what do you not understand?
Previous Topic: Need advice on index to apply
Next Topic: Locking In application code.
Goto Forum:
  


Current Time: Thu Mar 28 05:41:07 CDT 2024