OraFAQ Forum: Performance Tuning » Sql Running Slowly

Home » RDBMS Server » Performance Tuning » Sql Running Slowly (9i)

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

Sql Running Slowly [message #573549]

Mon, 31 December 2012 05:09

snehal123
Messages: 4
Registered: December 2012
Location: india

Junior Member

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,

Report message to a moderator

Re: Sql Running Slowly [message #573550 is a reply to message #573549]

Mon, 31 December 2012 05:13

John Watson
Messages: 8928
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

snehal123
Messages: 4
Registered: December 2012
Location: india

Junior Member

Hi,

I Agree but Any alternate for this.

Thanks,
Snehal

Report message to a moderator

Re: Sql Running Slowly [message #573553 is a reply to message #573551]

Mon, 31 December 2012 05:18

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) ;

Report message to a moderator

Re: Sql Running Slowly [message #573555 is a reply to message #573553]

Mon, 31 December 2012 05:22

John Watson
Messages: 8928
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.

Report message to a moderator

Re: Sql Running Slowly [message #573556 is a reply to message #573553]

Mon, 31 December 2012 05:24

Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You didn't read the link John provided, did you?

Regards
Michel

Report message to a moderator

Re: Sql Running Slowly [message #573559 is a reply to message #573555]

Mon, 31 December 2012 05:33

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
********************************************************************************

Report message to a moderator

Re: Sql Running Slowly [message #573561 is a reply to message #573559]

Mon, 31 December 2012 05:34

John Watson
Messages: 8928
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.

Report message to a moderator

Re: Sql Running Slowly [message #573569 is a reply to message #573561]

Mon, 31 December 2012 05:56

Littlefoot
Messages: 21807
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 /forum/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).

Report message to a moderator

Re: Sql Running Slowly [message #573571 is a reply to message #573569]

Mon, 31 December 2012 06:03

John Watson
Messages: 8928
Registered: January 2010
Location: Global Village

Senior Member

I've just looked at the "asterisks". How bizarre.

Report message to a moderator

Re: Sql Running Slowly [message #573578 is a reply to message #573571]

Mon, 31 December 2012 06:53

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

Senior Member

Orafaq engine does reverse engineer asterisks. We discussed this a while ago.

Report message to a moderator

Re: Sql Running Slowly [message #573705 is a reply to message #573559]

Wed, 02 January 2013 09:55

LNossov
Messages: 318
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

Report message to a moderator

Previous Topic:	Tuning the sql query
Next Topic:	OCI client result cache

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 06:36:55 CDT 2024