SQL Tuning Case --- A Quick Tuning by Applying SQL Profile

This is a real SQL tuning case using this tool

Problem

The customer was complaining they cannot get a report long time after they submitted the job. To reproduce the problem, we submitted the same job again, then we can see the long-running SQL of this report.

Obviously, to solve the problem, we need to tune this SQL. 

Troubleshooting

Drill into this SQL, we got the SQL text, execution plan, statistics data and everything required for troubleshooting immediately!

Check its performance statistics data roughly,

We can see the Buffer Gets and Disk Reads are very high. Then let's see what happening on this SQL from its execution plan.

check its excution plan, we can see 90% cost is on an Index Scan operation on an index. Check it further, this Index/Table is the driven table, while the driving table is a Cartesian Join of two system created temporary tables, which is more interesting for us. Those temporary tables were created internally by Oracle for those "materialized" views in the WITH clause. Look at the cardinality of those objects in the Cartesian Join, their actualy cardinality is much more than the estimated cardinality. That means oracle optimizer under-estimated the cardinality of those temporary tables. 

Go back to the Cartesian Join. Oracle optimizer will choose cartesian join in two scinarios.

1. There is no join condition between to table;
2. The cardinality of two joining tables are very small, cartesian join will have less cost than any other type of join

We were hitting the second scenario because we can see the join condition in the SQL, and the calculated cardinalities are small.

Click the cardinality operation, we can quickly spot the related outline entries in the outline table.

Solution

From the troubleshooting, we can get a conclusion is that Oracle optimizer calculated the cardinality of the temporary tables incorrectly, consequently, chose wrong (Cartesian) join to produce the result of the Join. We can add hint in the SQL to force the optimizer choose other Join method to solve this problem. And we can make it via a more efficient way, solve the problem immediately.

Click "Edit" button, popup the outline editor window, find the target Cartesian Join outline, USE_MERGE_CARTESIAN(...), change it to NO_CARTESIAN(...), replace everything else with this NO_CARTESIAN outline,

Click "Apply", a SQL profile was generated and applied to this SQL. Then we killed the running session, submitted the job again, the report was generated in less than 1 minute!