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.
by
CORE
·
Join the DZone community and get the full member experience.
Join For FreeWhy 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.
Solution
- Oracle stores all the SQL database in-page memory and indexes it by SQL ID in
gv$sqltext - 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.
Sample Query
SQL
x
111
1
SELECT DISTINCT obj.object_name
2
3
,program_line#
4
5
, cpu_time / 1000000 AS cpu_time_in_secs
6
7
, elapsed_time / 1000000 AS elapsed_time_in_secs
8
9
,buffer_gets
10
11
,disk_reads
12
13
,end_of_fetch_count AS rows_fetched_per_execution
14
15
,executions
16
17
,optimizer_cost
18
19
,vsql.sql_id
20
21
,NULL operation
22
23
,NULL options
24
25
,vsplan.plan_hash_value
26
27
,sql_text
28
29
, ( SUBSTR ( DBMS_LOB.SUBSTR ( sql_fulltext
30
31
,4000
32
33
,1
34
35
)
36
37
, INSTR ( DBMS_LOB.SUBSTR ( sql_fulltext
38
39
,4000
40
41
,1
42
43
), '/*' ) + 2
44
45
, INSTR ( DBMS_LOB.SUBSTR ( sql_fulltext
46
47
,4000
48
49
,1
50
51
), '*/' ) - INSTR ( DBMS_LOB.SUBSTR ( sql_fulltext
52
53
,4000
54
55
,1
56
57
), '/*' ) - 2
58
59
)
60
61
) release_string
62
63
,NULL error_flag
64
65
,NULL error_message
66
67
,loads
68
69
,first_load_time
70
71
,user_io_wait_time
72
73
,rows_processed
74
75
,last_load_time
76
77
,vsql.module
78
79
,fnd_global.user_id created_by
80
81
,SYSDATE creation_date
82
83
,fnd_global.user_id last_updated_by
84
85
,SYSDATE last_update_date
86
87
,'-1' last_update_login
88
89
FROM gv$sql vsql
90
91
,gv$sql_plan vsplan
92
93
,all_objects obj
94
95
WHERE 1 = 1
96
97
AND vsql.sql_id = vsplan.sql_id
98
99
AND vsql.sql_fulltext NOT LIKE '%sql_text%'
100
101
AND vsql.program_id = obj.object_id(+)
102
103
AND vsql.sql_fulltext LIKE lv_pattern
104
105
AND TO_DATE ( last_load_time, 'YYYY-MM-DD/HH24:MI:SS' ) >= ( SYSDATE - p_hours_from / 24 )
106
107
AND nvl(obj.object_name, '-') = NVL(p_program,nvl(obj.object_name, '-'))
108
109
AND nvl(vsql.module, '-') = NVL(p_module,nvl(vsql.module, '-'))
110
111
ORDER BY last_load_time DESC;
fnd_file.put_line(fnd_file.output, 'String..') – This can be used to print the SQL plan in a concurrent program output file.
Sample Output
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.
sql
Database
Opinions expressed by DZone contributors are their own.
Comments