OraFAQ Forum: SQL & PL/SQL » Regarding Hints

Home » SQL & PL/SQL » SQL & PL/SQL » Regarding Hints (Oracle 11g, Windows)

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

Regarding Hints [message #659326]

Thu, 12 January 2017 21:35

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Hi,

Developers have requested help to tune Database procedure which they have developed.
One thing caught my attention in the procedure is it have a Cursor statement.
The Select statement in the Cursor have PARALLEL hint in it and ORDER BY statement in the Select statement.

My understanding is that the PARALLEL hint serves no purpose here as the SELECT statement is not doing any DML (INSERT,UPDATE, DELETE) statement instead it is just selecting the table records to the cursor memory. So adding Parallel hint in a SELECT statement for the CURSOR can add performance problems than reducing it. Also ORDER by Clause in the SELECT statement of the CURSOR is reduntant and should be avoided to enhance performance.

Please let me know whether this observations makes sense or my understanding is flawed here.

Thanks,
Ninan

Report message to a moderator

Re: Regarding Hints [message #659327 is a reply to message #659326]

Thu, 12 January 2017 21:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post 2 EXPLAIN PLAN; one with the hint & the other without the hint
then compare the elapsed times & any other differences

Report message to a moderator

Re: Regarding Hints [message #659328 is a reply to message #659327]

Thu, 12 January 2017 22:39

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Hi Blackswan,

Thanks for your quick reply.

Yes I have compared the explain plans, but I do not have sufficient data in Development enivornment and we don't have access to Prod.

The Explain plan with Parallel hint in SELECT statement had 20 execution steps with a Bytes = 1000 Cost =3 (0) and Time = 00:00:01
After removing the Parallel hint the execution steps reduced to 16 with Bytes = 545 Cost = 9 (34) and Time = 00:00:01.

Here cost has increased but execution steps has reduced. Is this acceptable.

Question is , is it advisable to have hints in a SELECT statement of a CURSOR as it is just selecting rows to the memory.
And is it advisable to have Order by clause in SELECT statement of a CURSOR ?

Is it advisable to take Execution plans without much data .

Thanks,
Ninan.

Report message to a moderator

Re: Regarding Hints [message #659329 is a reply to message #659328]

Thu, 12 January 2017 23:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If you can't test with Production like data, you are wasting everyone's time & effort.

PLEASE note that I said to actually POST the EXPLAIN PLANS.
Forgive me but I do not trust your interpretation of how Oracle behaves.

FWIW, I do not allow any SQL with hints to exist in any of my Production databases.

Report message to a moderator

Re: Regarding Hints [message #659330 is a reply to message #659329]

Thu, 12 January 2017 23:43

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Hi Blackswan

My apologies.

Can you please give me clarity :-
Quote:

Forgive me but I do not trust your interpretation of how Oracle behaves.

Please correct me what is wrong in my understanding.

Thanks,
Nirmal.

Report message to a moderator

Re: Regarding Hints [message #659331 is a reply to message #659330]

Thu, 12 January 2017 23:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I don't know what you do.
I don't know what you see.

Folks here frequently do not accurately report reality.
Therefore I only trust what I see from CUT & PASTE of actual SQL & results.

Report message to a moderator

Re: Regarding Hints [message #659361 is a reply to message #659326]

Fri, 13 January 2017 07:53

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

ninan wrote on Thu, 12 January 2017 22:35

Also ORDER by Clause in the SELECT statement of the CURSOR is reduntant and should be avoided to enhance performance.

This is not true. In most of my cursors, I use an order by. Things have to get done in a certain order. For example, you need to loop through all employees in a department, sorted by hire date to determine who gets first dibs on vacation days.

Report message to a moderator

Re: Regarding Hints [message #659363 is a reply to message #659331]

Fri, 13 January 2017 08:05

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

Senior Member

Another reason for ORDER BY is to avoid ora-1555 snapshot too old errors if the code takes ages too loop through the rows. The ORDER BY forces your session to run the query to completion when it opens the cursor.

Report message to a moderator

Re: Regarding Hints [message #659365 is a reply to message #659363]

Fri, 13 January 2017 08:07

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

Senior Member

I'm pretty sure that's not true John. select for update does, but nothing else.

Report message to a moderator

Re: Regarding Hints [message #659371 is a reply to message #659365]

Fri, 13 January 2017 10:43

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

Senior Member
Account Moderator

The ORDER BY forces Oracle to run the query and put the result in temporary space (memory or tablespace) and so prevents the ORA-1555 errors due to PL/SQL loop (how could it know which first row to return if it has no already sort all the result set?).

Report message to a moderator

Re: Regarding Hints [message #659410 is a reply to message #659371]

Mon, 16 January 2017 03:44

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

Senior Member

True, but it doesn't always have to do that.
If you're querying an indexed column(s) and ordering that column(s), oracle can just read the index in order and skip the sort operation entirely.

Report message to a moderator

Previous Topic:	PL/SQL table :: ORA-00902: invalid datatype
Next Topic:	AUDSID temporary not found

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 14:56:31 CDT 2024