Home » RDBMS Server » Performance Tuning » urgent please modify this query frenz sloww performance
urgent please modify this query frenz sloww performance [message #533884] Thu, 01 December 2011 09:46 Go to next message
arivazhagancsegmailcom
Messages: 8
Registered: December 2010
Location: chennai
Junior Member
CURSOR C1 IS
SELECT 'SELECT ''1'' FROM '||UCC.TABLE_NAME||' WHERE '|| UCC.COLUMN_NAME ||' = '''||P_COL_VAL||''' UNION ' COL, UCC.TABLE_NAME TAB
FROM USER_CONS_COLUMNS UCC,
USER_CONSTRAINTS UC
WHERE NVL(UC.CONSTRAINT_TYPE, 'X') = 'R'
and UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND EXISTS (SELECT CONSTRAINT_NAME
FROM USER_CONS_COLUMNS UCC1
WHERE TABLE_NAME = P_TABLE_NAME
AND COLUMN_NAME = P_COLUMN_NAME
AND CONSTRAINT_NAME = R_CONSTRAINT_NAME )
Re: urgent please modify this query frenz sloww performance [message #533885 is a reply to message #533884] Thu, 01 December 2011 09:47 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
From information given:

SELECT 'SELECT ''1'' FROM '||UCC.TABLE_NAME||' WHERE '|| UCC.COLUMN_NAME ||' = '''||P_COL_VAL||''' UNION ' COL, UCC.TABLE_NAME TAB
FROM USER_CONS_COLUMNS UCC,
USER_CONSTRAINTS UC
WHERE NVL(UC.CONSTRAINT_TYPE, 'X') = 'R'
and UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND EXISTS (SELECT CONSTRAINT_NAME
FROM USER_CONS_COLUMNS UCC1
WHERE TABLE_NAME = P_TABLE_NAME
AND COLUMN_NAME = P_COLUMN_NAME
AND CONSTRAINT_NAME = R_CONSTRAINT_NAME
and 1=2



hth.
Re: urgent please modify this query frenz sloww performance [message #533886 is a reply to message #533884] Thu, 01 December 2011 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>urgent please modify this query frenz sloww performance
Please explain & clarify why it is urgent for me to solve this problem for you.

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
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: urgent please modify this query frenz sloww performance [message #533887 is a reply to message #533886] Thu, 01 December 2011 09:50 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Come on everybody, stop whatever else you're doing and get working on this issue. This forum question is URGENT, just like his last question. Now hop to it and get this guy's work done for him. Chop Chop.
Re: urgent please modify this query frenz sloww performance [message #533888 is a reply to message #533887] Thu, 01 December 2011 09:51 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I provided a faster query already. Problem?

Razz

Edit: Crap. I forgot to add /*+ fast(true) */

Mea culpa, mea culpa.

[Updated on: Thu, 01 December 2011 09:53]

Report message to a moderator

Re: urgent please modify this query frenz sloww performance [message #533889 is a reply to message #533888] Thu, 01 December 2011 09:53 Go to previous messageGo to next message
arivazhagancsegmailcom
Messages: 8
Registered: December 2010
Location: chennai
Junior Member
when sir
Re: urgent please modify this query frenz sloww performance [message #533890 is a reply to message #533888] Thu, 01 December 2011 09:56 Go to previous messageGo to next message
arivazhagancsegmailcom
Messages: 8
Registered: December 2010
Location: chennai
Junior Member
tat query also takes a very long time sir...sorry for disturbance
Re: urgent please modify this query frenz sloww performance [message #533891 is a reply to message #533890] Thu, 01 December 2011 09:57 Go to previous messageGo to next message
arivazhagancsegmailcom
Messages: 8
Registered: December 2010
Location: chennai
Junior Member
i dono about performance tuuning sir...
Re: urgent please modify this query frenz sloww performance [message #533892 is a reply to message #533891] Thu, 01 December 2011 09:58 Go to previous messageGo to next message
arivazhagancsegmailcom
Messages: 8
Registered: December 2010
Location: chennai
Junior Member
where i can add it this /*+ fast(true) */.

Re: urgent please modify this query frenz sloww performance [message #533893 is a reply to message #533892] Thu, 01 December 2011 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Re: urgent please modify this query frenz sloww performance [message #533896 is a reply to message #533888] Thu, 01 December 2011 10:54 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I think this is probably bug 314159265, server process spins radially during circumferential join.
Re: urgent please modify this query frenz sloww performance [message #534010 is a reply to message #533884] Fri, 02 December 2011 02:43 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
As this post is stated to be urgent, I realized that I have more urgent things for me. So, I come now with just a few remarks:

As the posted query is based on static system views, it would be useful to check its structure in Reference book. It is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
Using TABLE_NAME column for joining them could help.

That NVL expression can be omitted: if CONSTRAINT_TYPE would be NULL, the query would not return that row anyway.
As you did not post, what is this query supposed to return, it is quite tricky to modify it without changing its result set. By the way, what are P_TABLE_NAME and P_COLUMN_NAME identifiers in the subquery condition? Maybe putting the subquery in EXISTS clause to the main SELECT statement could also help as it seems to be quite selective.

My last remark is: you posted the declaration cursor. But, as cursors are only pointers to the result set, they are not fast or slow. Their usage may be, but as you did not post it here, the only advice I may give you is: also check whether its call cannot be rewritten to be fast (by the way, how much time does "sloww" take?).
Re: urgent please modify this query frenz sloww performance [message #534119 is a reply to message #533884] Fri, 02 December 2011 16:07 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
You can try the following:

- gather optimizer stats for the data dictionary or
- set optimizer_mode=rule in your session or directly in sql by hint opt_param('optimizer_mode' 'rule')

[Updated on: Sat, 03 December 2011 00:12]

Report message to a moderator

Previous Topic: parallel_index
Next Topic: How to reduce cost of my query?
Goto Forum:
  


Current Time: Fri Mar 29 00:59:11 CDT 2024