OraFAQ Forum: Performance Tuning » SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools

Home » RDBMS Server » Performance Tuning » SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools (Oracle, 11.2, SQLDeveloper)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601314]

Tue, 19 November 2013 10:02

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.

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601315 is a reply to message #601314]

Tue, 19 November 2013 10:05

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/

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601317 is a reply to message #601315]

Tue, 19 November 2013 10:18

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.

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601318 is a reply to message #601317]

Tue, 19 November 2013 10:23

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

you wouldn't get odds on that in vegas

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601325 is a reply to message #601314]

Tue, 19 November 2013 12:43

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.

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601355 is a reply to message #601325]

Tue, 19 November 2013 23:36

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.

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601357 is a reply to message #601355]

Tue, 19 November 2013 23:42

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.

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601365 is a reply to message #601357]

Wed, 20 November 2013 02:13

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.

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601368 is a reply to message #601365]

Wed, 20 November 2013 03:01

cookiemonster
Messages: 13966
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.

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601370 is a reply to message #601365]

Wed, 20 November 2013 03:05

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"

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601373 is a reply to message #601370]

Wed, 20 November 2013 04:21

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.

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601381 is a reply to message #601370]

Wed, 20 November 2013 05:36

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.

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601433 is a reply to message #601381]

Wed, 20 November 2013 21:33

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.

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601434 is a reply to message #601433]

Wed, 20 November 2013 21:40

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

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601438 is a reply to message #601434]

Wed, 20 November 2013 23:20

royalyogi
Messages: 22
Registered: November 2013
Location: India

Junior Member

BlackSwan: above link show Invalid link. please check and let me know

Report message to a moderator

Re: SELECT works, INSERT+ SELECT hangs in SQLDeveloper Tools [message #601447 is a reply to message #601438]

Thu, 21 November 2013 02:38

cookiemonster
Messages: 13966
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?

Report message to a moderator

Previous Topic:	Need advice on index to apply
Next Topic:	Locking In application code.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 06 07:30:18 CST 2025