Home » RDBMS Server » Performance Tuning » Long Running SQL - NO entry in Session_LongOps view?
Long Running SQL - NO entry in Session_LongOps view? [message #247449] Mon, 25 June 2007 23:08 Go to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Hi,



I have got a query running INSERT Statement and it inserting 
4 millions rows and i running for last 6 hours
but i cant see any reference in v$session_longops


I can see the SID, sql details in v$session and v$sql views.


Can you somebody let me know how i can check the current
status of this query? 



Thanks
Re: Long Running SQL - NO entry in Session_LongOps view? [message #247473 is a reply to message #247449] Tue, 26 June 2007 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I can see the SID, sql details in v$session and v$sql views.

Good as we can't.

Quote:
i can check the current status of this query

v$session, v$transaction

As you post it, it is not a performance problem. You don't seem to ask us to help you on this point.
Can we transfer it to another forum?

Regards
Michel
Re: Long Running SQL - NO entry in Session_LongOps view? [message #247512 is a reply to message #247473] Tue, 26 June 2007 03:06 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
A job that runs for a long time may not have any long operations if it using a lot of Nested Loops joins and Indexed lookups.

Long Operations are Full Table Scans, Hash Joins, Sorts and the like.

Ross Leishman
Re: Long Running SQL - NO entry in Session_LongOps view? [message #247541 is a reply to message #247473] Tue, 26 June 2007 05:43 Go to previous messageGo to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Michel




ok,obviously you can not see.. right..

SQL is like this

INSERT INTO TAB1
(SELECT /*+ parallel( t 4) */ C1,C2,C3 FROM TEMP t WHERE
CODETYPE ='p'
)

Now above SQL takes more than 14 hours, its has 4 millions records to insert, now i think its something with the performance, if you have any good reason , please let me help me to solve this long running sql.



thanks
Re: Long Running SQL - NO entry in Session_LongOps view? [message #247550 is a reply to message #247449] Tue, 26 June 2007 06:19 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. How many indexes you have on your target table?
2. Can you use CREATE TABLE ... NOLOGGING AS SELECT ...?

Michael
Re: Long Running SQL - NO entry in Session_LongOps view? [message #247552 is a reply to message #247550] Tue, 26 June 2007 06:23 Go to previous messageGo to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Thanks Michel


Target table has 2 non-unique indexes ( table has total 3 columns), no partitions, oracle 9.2.0.6 , sun solaris,unix OS

Also table has already millions of rows, so we are adding new data everyday into the target table, so we can not use CREATE TABLE ... AS SELECT..



Cheers
Re: Long Running SQL - NO entry in Session_LongOps view? [message #247582 is a reply to message #247449] Tue, 26 June 2007 07:41 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Run event 10046 and post TKPROF with wait information.

IMHO you need to implement partitioning and to use:
CREATE TABLE ... NOLOGGING AS SELECT ...
ALTER TABLE ... EXCHANGE PARTITION

Michael
Re: Long Running SQL - NO entry in Session_LongOps view? [message #247659 is a reply to message #247449] Tue, 26 June 2007 13:07 Go to previous message
gintsp
Messages: 118
Registered: February 2007
Senior Member
mymot wrote on Tue, 26 June 2007 07:08
Hi,
I have got a query running INSERT Statement and it inserting 
4 millions rows and i running for last 6 hours
but i cant see any reference in v$session_longops

Thanks

Not all operations appear in v$session_longops for some more details you can check my paper Overview of long running operations in Oracle at http://www.gplivna.eu/papers/v$session_longops.htm

Gints Plivna
http://www.gplivna.eu
Previous Topic: Fine Tune Insert Statement
Next Topic: cache hit ratio
Goto Forum:
  


Current Time: Thu Dec 08 00:37:18 CST 2016

Total time taken to generate the page: 0.08413 seconds