Home » SQL & PL/SQL » SQL & PL/SQL » Regarding Hints (Oracle 11g, Windows)
Regarding Hints [message #659326] Thu, 12 January 2017 21:35 Go to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
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
Re: Regarding Hints [message #659327 is a reply to message #659326] Thu, 12 January 2017 21:50 Go to previous messageGo to next message
BlackSwan
Messages: 25793
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
Re: Regarding Hints [message #659328 is a reply to message #659327] Thu, 12 January 2017 22:39 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
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.
Re: Regarding Hints [message #659329 is a reply to message #659328] Thu, 12 January 2017 23:17 Go to previous messageGo to next message
BlackSwan
Messages: 25793
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.
Re: Regarding Hints [message #659330 is a reply to message #659329] Thu, 12 January 2017 23:43 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
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.
Re: Regarding Hints [message #659331 is a reply to message #659330] Thu, 12 January 2017 23:50 Go to previous messageGo to next message
BlackSwan
Messages: 25793
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.
Re: Regarding Hints [message #659361 is a reply to message #659326] Fri, 13 January 2017 07:53 Go to previous messageGo to next message
joy_division
Messages: 4817
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.
Re: Regarding Hints [message #659363 is a reply to message #659331] Fri, 13 January 2017 08:05 Go to previous messageGo to next message
John Watson
Messages: 7212
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.
Re: Regarding Hints [message #659365 is a reply to message #659363] Fri, 13 January 2017 08:07 Go to previous messageGo to next message
cookiemonster
Messages: 12988
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm pretty sure that's not true John. select for update does, but nothing else.
Re: Regarding Hints [message #659371 is a reply to message #659365] Fri, 13 January 2017 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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?).

Re: Regarding Hints [message #659410 is a reply to message #659371] Mon, 16 January 2017 03:44 Go to previous message
cookiemonster
Messages: 12988
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.
Previous Topic: PL/SQL table :: ORA-00902: invalid datatype
Next Topic: AUDSID temporary not found
Goto Forum:
  


Current Time: Tue Jan 16 20:16:38 CST 2018

Total time taken to generate the page: 0.01702 seconds