Author JP Vijaykumar Oracle DBA
Date June 17th 2017

I had the opportunity of tuning a long running query, taking 13 hrs to complete.

Unfortunately, I can not share the details of these original /modified queries,
as it violates proprietary/confidential/sensitive information laws.

Without the scripts' details, this article describes the challenges in tuning code.
As an Oracle dba, if you were given to tune a query, you try the normal
track and tune it. When all your avenues were exhausted, you are left
with left with no alternative but to tune the query by hook or by crook.
I am going the describe a similar scenario to tune a behemoth sql query.

01) About 80% of sql queris can be tuned by gathering stats on the base tables.
02) About 10% of sql queries can be tuned using hints & tweaking config parameters.
03) About 5% of sql queries can be tuned by pinning sql_plans/using profiles
03) Last 5% of sql queries pose the real challenge. Hard nut to crack.

Being an Oracle dba, there are chances, that one may face such a situation.
Wherein all your preliminary tuning options were exhausted with no luck.
What next?

This article deals with tuning a sql query, which falls into the last 5% of queries.

Though it is a tough task, it's challenging and the results are quite amazing.

01) The Original query is taking almost 13 hrs to complete.
02) This query is accessing, two views and one table, with following details.
I had provided the base objects names in parenthesis, accessed by each view:

"TEMP_JP_VIEW_1" H1, --(TEMP_JP_VIEW_3 ( TEMP_JP_TBL_2 20 million rows, TEMP_JP_TBL_3 1 billion rows), TEMP_JP_TBL_4 1.6 billion rows)
"TEMP_JP_VIEW_2" H2, --(TEMP_JP_VIEW_3 ( TEMP_JP_TBL_2 20 million rows, TEMP_JP_TBL_3 1 billion rows), TEMP_JP_TBL_5 102 million rows, TEMP_JP_TBL_6 3.6 billion rows)
"TEMP_JP_TBL_1" I3 --318 million rows

03) Since the query is accessing two views, which are accessing another view +
few other behemoth tables, I can not use hints(to use any specific indexes
or base tables' join order) to improve performance.
04) AS all the base tables accessed by the views and other tables joined in the
query are very large, some of the test query modifications failed due to
space crunch in temp tablespace.

ERROR at line 15:
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP1

Modified the query with better filtering options.

05) while re-writing the query, the query failed with the following error:

ERROR at line 33:
ORA-00918: column ambiguously defined

I used column aliases to circumwent the error, wherever necessary.

I have to re-write all the three views TEMP_JP_VIEW_1,TEMP_JP_VIEW_2,TEMP_JP_VIEW_3
using inline views.

Used index/leading/parallel hints, in the query wherever appropriate.
The query completed under 3 mts.

oracle33_dba:PROD1> cat tmp.sql|wc -l
oracle33_dba:PROD1> cat tmp1.sql|wc -l

The original query was written in 48 lines of code.
The modified query ran upto 341 lines of code.

Tested/verified the resultant rows' count from the original/modified queries.

dba@oracle33:~/scripts/jp> cat PROD_SCOTT_tmp.csv
Fri Jun 16 22:08:38 CDT 2017


Elapsed: 12:55:53.95

Pls note, after each query's execution, flushed the buffer_cache & shared_pool.
There was a gap of 24 hrs between modified query & original quereis' execution.

dba@oracle33:~/scripts/jp> cat PROD_SCOTT_tmp1.csv
Sat Jun 17 11:05:39 CDT 2017


Elapsed: 00:03:13.24

The elapsed time of the modified query, is extremely impressive.


01) Developers use readily available views for their reporting purposes.
View upon view is constructed, while developping code for some of the complex
reports. Choosing short cuts, by using views, further complicate sql queries.
02) A person tuning such complex queries, need to understand the business logic
first, access the base tables and fine tune the code.
03) Re-writing code will require resources and time.
Presenting 341 lines of modified code, masking sensitive info, is tough.
After editing/masking the sensitive code, query may look like garbage.
04) When the code is accessing, views created on other views (cyclical views),
the code may not work efficiently as expected.
05) Accessing the base tables will provide the opportunity of forcing the use
of appropriate indexes, choosing better join order of tables.
06) As with views, you can only apply the filtering condition on the resultant data
from the base tables. while re-writing the code, I filtered the data from base
tables, with inline views and used the resultant (less number of rows) data to
join with other tables. This had reduced the amount of logical IO


"Appreciate your help in fixing the issue on query performance which was performing
very bad. Post fix reports are being generated in ~3 mins from ~13 hrs."

"That’s amazing. 13 hrs to 4 mins. Well done!!!"

Happy scripting.

Tough tuning tasks! welcome :-) jp[ _ ]vijaykumar[@]yahoo[dot]com