220 likes | 242 Vues
Optimizing SQL Queries. 26 January 2009 Balys Šulmanas , Vilnius University. Applications. CMS Dashboard (CMS offline DB) CMS PVSS (CMS online DB) Other applications on CMS online DB. Typical workflow. Look for the highest I/O (sometimes CPU) query on the DB Analyze execution plan
E N D
Optimizing SQL Queries 26 January 2009 Balys Šulmanas, Vilnius University
Applications • CMS Dashboard (CMS offline DB) • CMS PVSS (CMS online DB) • Other applications on CMS online DB DM technical meeting - 2
Typical workflow • Look for the highest I/O (sometimes CPU) query on the DB • Analyze execution plan • Make improvements: • Create or modify the indexes (most often) • Redesign the query (or the views the query selects) • Sometimes add some optimizer hints DM technical meeting - 3
Tools used • Oracle Enterprise Manager for DB performance monitoring • Oracle Enterprise Manager and Benthic for optimization DM technical meeting - 4
Query execution plan • An execution plan defines how Oracle finds or writes the data • It consists of steps • Oracle optimizer estimates the metric called cost for each step of the execution plan and for the whole query • Typically the lowest cost execution should be the fastest • You can reduce the cost with proper indexing and proper queries DM technical meeting - 5
Execution plan graph (Oracle Enterprise Manager) Access predicate: "DPE"."SYS_ID"= "ELEMENTS"."SYS_ID" AND "DPE"."DPE_ID"= "ELEMENTS"."DPE_ID" AND "DPE"."DP_ID"= "ELEMENTS"."DP_ID" DM technical meeting - 6
Execution plan table (Oracle Enterprise manager) DM technical meeting - 7
Main execution plan steps • Data can be retrieved from tables and indexes • TABLE ACCESS BY INDEX vs. TABLE ACCESS FULL • NESTED LOOPS vs. HASH JOIN • Access predicates DM technical meeting - 8
TABLE ACCESS BY INDEX vs. TABLE ACCESS FULL • To find a row by index is much faster then to scan all the rows to find the one you need • Full table scan means that either the columns that you filter are not indexed or too many rows • Sometimes Oracle optimizer overestimates the number of rows DM technical meeting - 9
NESTED LOOPS vs. HASH JOIN • 2 most often used methods to join tables • NESTED loops should be faster if proper indexes are set and there are not too many rows • Sometimes Oracle estimates this wrong DM technical meeting - 10
Typical index related problems • The columns used to filter data are not indexed • The columns used to filter data are indexed, but in a wrong order • Functions are being called on indexed columns DM technical meeting - 11
Recent no index example (CMS_RPC_PVSS_COND) • select count(*) from elements d where d.sys_id = :1 and d.dp_id = :2 • There is a foreign key set on ELEMENTS(DP_ID, SYS_ID), so the corresponding index should be created anyway, but there was no index • The cost of execution plan was 266 • It got 1 after the index was created DM technical meeting - 12
Columns indexed in wrong order (CMS PVSS case) • 15 schemas and few hundreds of tables • All those tables have DPID (which is a number foreign key) and CHANGE_DATE (Timestamp) columns • The index was created on [CHANGE_DATE, DPID] (this means foreign key was not indexed) • The most often used query provides a particular DPID and the range of CHANGE_DATE • I dropped [CHANGE_DATE, DPID] indexes and created [DPID, CHANGE_DATE] ones • As a result, the users started selecting week-long ranges instead of 3 hours-long DM technical meeting - 13
Functions on indexed columns • Typically functions are called to round Timestamp (to days, hours, seconds etc.) • If the column has an index, Oracle will not be able to use it • To truncate a Timestamp means the same as to take an interval • TS >= :1 and TS < :1 + INTERVAL '1' DAY is better than to_timestamp(TO_CHAR(TS, 'DD-MON-YY'))=:1 if TS column has an index DM technical meeting - 14
Typical SQL related problems • Not using features, which would allow to increase performance, like Oracle analytic functions • Oracle analytic functions have many nice features, but the most simple (and the most often to be used) are RANK()/DENSE_RANK() and KEEP FIRST/LAST functions • Bugs, which result in huge result sets and high load on DB DM technical meeting - 15
No Oracle analytic functions example (CMS Dashboard) • select "ServiceName","DetailedAv” from ( select a."ServiceId", "DetailedAv" from SAM_SERVICE_AVL_H1 a, (selectmax("TimeStamp") as t, "ServiceId" from SAM_SERVICE_AVL_H1 groupby "ServiceId“) g where g."ServiceId"=a."ServiceId" andg.t=a."TimeStamp“ ) b, SAM_SERVICES s , SITE where b."ServiceId"=s."ServiceId" and "IgnoreFlag"=0 and "SiteId"="ServiceSiteId" and ( "VOName" = :site0 ) DM technical meeting - 16
Oracle analytic functions example (CMS Dashboard) • select MIN("ServiceName") KEEP (DENSE_RANK LAST ORDER BY "TimeStamp") , MIN("DetailedAv") KEEP (DENSE_RANK LAST ORDER BY "TimeStamp") from SAM_SERVICE_AVL_H1 H1, SAM_SERVICES s, SITE where H1."ServiceId"=s."ServiceId" and "IgnoreFlag"=0 and "SiteId"="ServiceSiteId" and VOName" = :site0 group by H1."ServiceId" DM technical meeting - 17
Oracle hints • Oracle hints are directives to the optimizer • i.e. you tell Oracle SQL engine what to do • Oracle was choosing FULL SCANS and HASH JOINS instead of ACCESS BY INDEX in NESTED LOOPS • Sometimes Oracle overestimates the number of rows in the execution plan and then switches to FULL SCANS and HASH JOINS • So I just used to add /*+USE_NL*/ hints DM technical meeting - 18
The results for CMS Dashboard Before: The scale is 0-4400. SYS$USERS are Oracle jobs from CMS Dashboard 2000 The scale is 0-1500 After: 500 DM technical meeting - 19
The results • “It got X times faster” makes no sense, because it depends on amount of data • A simple query with an index should be kind of logarithmic complexity and without it should be linear DM technical meeting - 20
Some notes • "You should almost always index foreign keys“ (Oracle Database Concepts manual) • ~70% of the issues were because of ignoring this simple recommendation DM technical meeting- 21
Conclusions • Applications can profit from SQL tuning • Oracle optimizer is good for most cases • Particular queries still require manual work/Oracle expertise • CMS Dashboard improved 4 times at that moment DM technical meeting- 22