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
|
|
|
|
|
|
|
|
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:35Also 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 |
John Watson
Messages: 8951 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 #659410 is a reply to message #659371] |
Mon, 16 January 2017 03:44 |
cookiemonster
Messages: 13943 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.
|
|
|