Oracle SQL Performance Plan Review Automation
A developer and SysAdmin gives a tutorial on how to perform a SQL performance review and how to automate this process as much as possible.
Join the DZone community and get the full member experience.Join For Free
Why Do We Need a SQL Performance Review?
- The current code review process is manual and doesn’t capture the Explain Plan for all modified queries.
- Currently, lead devs, along with developers, run Explain Plans manually in Toad/SQL Developer.
- To build an automated tool to capture problematic queries from an Explain Plan perspective and reduce manual oversight.
- To provide performance audits with data points.
- Oracle stores all the SQL database in-page memory and indexes it by SQL ID in
- During development (PLSQL/Java/OA Framework/XML Publisher), tag all the desired SQL queries with a code comment (Release Name/User Story Number).
- Develop a PLSQL program with below features:
- Analyze the Execution Plan of queries executed by a concurrent program/Java program/OAF code/forms/reports/BI publisher reports.
- Generate a report with queries which could impact performance. For example, queries with FULL TABLE SCAN, MERGE CARTESIAN JOIN, FULL INDEX SCAN.
- Capability to categorize queries at User Story, Sprint, Release, and Scrum Team levels based on program input parameters.
- Capability to analyze queries executed by a concurrent program/package/Java modules.
- Capture data of relevant queries analyzed in a table for future analysis and dashboards.
- Store the generated Explain Plan in a database table for audit purposes.
- In the Oracle E-Business Suite world, this program can be registered as an executable of concurrent programs and assigned to a request group.
- Before a release migration, the SysAdmin can put together a business process to execute this concurrent program to review the Explain Plan for the SQL queries for that release and catch any trouble making queries well in advance.
fnd_file.put_line(fnd_file.output, 'String..') – This can be used to print the SQL plan in a concurrent program output file.
As one can see, we can further enhance this tool and use it for flagging incorrect queries and also enforce SQL standards during the development cycle. This tool is used by distributed teams to maintain code consistency as well.
I hope this is a helpful article for your work.
Opinions expressed by DZone contributors are their own.