Home » RDBMS Server » Performance Tuning » Sql Running Slowly (9i)
Sql Running Slowly [message #573549] Mon, 31 December 2012 05:09 Go to next message
snehal123
Messages: 4
Registered: December 2012
Location: india
Junior Member
Smile Hi,

Please help me,

Sql running slowly.

Sql


SELECT ROWID
FROM
TABLE_A WHERE ROWID IN ( SELECT ROW_ID FROM TABLE_B )

TABLE_A have 1.7 millions row.


Please do the needful.

Thanks,

Re: Sql Running Slowly [message #573550 is a reply to message #573549] Mon, 31 December 2012 05:13 Go to previous messageGo to next message
John Watson
Messages: 4693
Registered: January 2010
Location: Global Village
Senior Member
What is "needful" is for you to understand that ROWIDs are unique. Throughout the entire database.
--
Update: sorry, I misread your query. You are comparing the ROWID from one table with a column called ROW_ID in another table. So it becomes a normal tuning question. You need to provide the usual information, formatted properly with [code] tags, as described here OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

[Updated on: Mon, 31 December 2012 05:16]

Report message to a moderator

Re: Sql Running Slowly [message #573551 is a reply to message #573550] Mon, 31 December 2012 05:16 Go to previous messageGo to next message
snehal123
Messages: 4
Registered: December 2012
Location: india
Junior Member
Hi,

I Agree but Any alternate for this.

Thanks,
Snehal
Re: Sql Running Slowly [message #573553 is a reply to message #573551] Mon, 31 December 2012 05:18 Go to previous messageGo to next message
snehal123
Messages: 4
Registered: December 2012
Location: india
Junior Member
Hi,

Sorry,

SELECT ROWID
FROM table_a
WHERE ROWID IN (SELECT row_id
FROM table_b) ;
Re: Sql Running Slowly [message #573555 is a reply to message #573553] Mon, 31 December 2012 05:22 Go to previous messageGo to next message
John Watson
Messages: 4693
Registered: January 2010
Location: Global Village
Senior Member
OK, you are new to the forum. So I'll repeat myself:Quote:
You need to provide the usual information, formatted properly with [code] tags, as described here OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Read that forum guide before you do anything else. Particularly the part about a test case. No-one can do anything without seeing your table and index structures and the execution plan.

Re: Sql Running Slowly [message #573556 is a reply to message #573553] Mon, 31 December 2012 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't read the link John provided, did you?

Regards
Michel
Re: Sql Running Slowly [message #573559 is a reply to message #573555] Mon, 31 December 2012 05:33 Go to previous messageGo to next message
snehal123
Messages: 4
Registered: December 2012
Location: india
Junior Member
Hi,

Please find execution plan.


********************************************************************************

SELECT ROWID
FROM
TABLE_A WHERE ROWID IN ( SELECT ROW_ID FROM TABLE_B )
AND ORGANIZATION_ID = :B1 AND RECEIPT_NUM IS NOT NULL FOR UPDATE OF
TRANSACTION_ID


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 66.28 1282.55 220855 289813 1 0
Fetch 1 68.56 1169.86 222145 289812 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 134.84 2452.42 443000 579625 1 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173 (APPS) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 FOR UPDATE
2 HASH JOIN SEMI
1699834 TABLE ACCESS BY INDEX ROWID TABLE_A
1699834 INDEX RANGE SCAN TABLE_A_N4 (object id 485543)
2 TABLE ACCESS FULL TABLE_B


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 FOR UPDATE
2 HASH JOIN (SEMI)
1699834 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'TABLE_A'
1699834 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'TABLE_A_N4' (NON-UNIQUE)
2 TABLE ACCESS (FULL) OF 'TABLE_B'


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 440360 3.14 2369.60
latch free 36 0.00 0.03
direct path write 369 0.10 1.38
direct path read 176 0.08 1.13
********************************************************************************
Re: Sql Running Slowly [message #573561 is a reply to message #573559] Mon, 31 December 2012 05:34 Go to previous messageGo to next message
John Watson
Messages: 4693
Registered: January 2010
Location: Global Village
Senior Member
Do you realize how rude it is to repeatedly ignore requests to follow guidelines? Use the [code] tags. Provide the DDL.

Goodbye.
Re: Sql Running Slowly [message #573569 is a reply to message #573561] Mon, 31 December 2012 05:56 Go to previous messageGo to next message
Littlefoot
Messages: 19699
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh well, if you check that message's source, you'll be even more surprised. These asterisks are not really asterisks ./fa/917/0/ (though, I think it is the OraFAQ forum engine that did the conversion FROM asterisk TO ... hm, something else, not vice versa).
Re: Sql Running Slowly [message #573571 is a reply to message #573569] Mon, 31 December 2012 06:03 Go to previous messageGo to next message
John Watson
Messages: 4693
Registered: January 2010
Location: Global Village
Senior Member
I've just looked at the "asterisks". How bizarre.
Re: Sql Running Slowly [message #573578 is a reply to message #573571] Mon, 31 December 2012 06:53 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
Orafaq engine does reverse engineer asterisks. We discussed this a while ago.
Re: Sql Running Slowly [message #573705 is a reply to message #573559] Wed, 02 January 2013 09:55 Go to previous message
LNossov
Messages: 289
Registered: July 2011
Location: Germany
Senior Member
Check your optimizer statistics. If they are up-to-date, you can try with the hint cardinality in subquery.

SELECT ROWID
FROM
TABLE_A WHERE ROWID IN ( SELECT /*+ cardinality(table_b 1) */ ROW_ID FROM TABLE_B )
AND ORGANIZATION_ID = :B1 AND RECEIPT_NUM IS NOT NULL FOR UPDATE OF
TRANSACTION_ID
Previous Topic: Tuning the sql query
Next Topic: OCI client result cache
Goto Forum:
  


Current Time: Sun Oct 26 04:32:07 CDT 2014

Total time taken to generate the page: 0.04840 seconds