1 / 16

Application Performance

Chapter 11. Application Performance. Mohd Shahizan Othman Jabatan Sistem Maklumat Fakulti Sains Komputer & Sistem Maklumat. Introduction.

sora
Télécharger la présentation

Application Performance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 11 Application Performance Mohd Shahizan Othman Jabatan Sistem Maklumat Fakulti Sains Komputer & Sistem Maklumat

  2. Introduction • Poorly constructed and formulated application code accounts for the majority of relational database performance problems—as many as 80% of performance problems can be traced back to the application. • When should you consider tuning your queries? • If the query runs slower than expected • The same query that used to be very responsive is giving you more time to return result. Query optimizer => to find good plan for the query • If the optimizer is not smart enough to find the best plan (using evaluation strategies supported by the DBMS), user can guide the choice of plan by providing hints to the optimizer. • Force the use of a particular index. • Choose the join order than join method.

  3. Designing Applications for Relational Access • Type of SQL. Is the correct type of SQL (planned or unplanned, dynamic or static, embedded or stand-alone) being used for this particular application? • Programming language. Is the programming language capable of achieving the required performance, and is the language environment optimized for database access? • Transaction design and processing. Are the transactions within the program properly designed to assure ACID properties, and does the program use the transaction processor of choice appropriately and efficiently? • Locking strategy. Does the application hold the wrong type of locks, or does it hold the correct type of locks for too long? • COMMIT strategy. Does each application program issue SQL COMMIT statements to minimize the impact of locking? • Batch processing. Are batch programs designed appropriately to take advantage of the sequential processing features of the DBMS?

  4. Relational Optimization

  5. What is an optimizer Optimizer => an inference engine responsible for determining the best possible database navigation strategy for any given SQL request. How does it work? To optimize SQL statement, the optimizer analyzes each SQL statement by parsing it to determine the tables and columns that must be accessed. It will also access statistics stored by the DBMS => Relational Optimization Relational Optimization => allows queries to adopt to a changing database environment Refer to Figure 12-1 (pg 321)

  6. Tuning Queries and Views • Sometimes, the DBMS may not be executing the plan you had in mind. Common areas of weakness: • Selections involving null values. • Selections involving arithmetic or string expressions. • Selections involving OR conditions. • Lack of evaluation features like index-only strategies or certain join methods or poor size estimation. • Unnecessary sorting (group by, order by, union) • Check the plan that is being used! Then adjust the choice of indexes or rewrite the query/view.

  7. Guidelines for Query Tuning • Guideline 1 - Conditions using the OR connective. Rewrite the query that will achieve acceptable results with existing indexes. Example 1: SELECT E.dno FROM Employees E WHERE E.hobby=‘Stamps’ OR E.age=10 SELECT E.dno FROM Employees E WHERE E.hobby=‘Stamps’ UNION SELECT E.dno FROM Employee E WHERE E.age=10 =>

  8. Guidelines for Query Tuning - 2 Example 2: Converting a SQL statement that uses OR to one that uses IN. vs. SELECT E.position, E.last_name, E.empno,D.manager FROM Employees E, Department D WHERE E.deptid = e.deptid AND position = ‘MANAGER’ OR position = ‘DIRECTOR’ OR position = ‘VICE PRESIDENT’ ORDER BY position; SELECT E.position, E.last_name, E.empno,D.manager FROM Employees E, Department D WHERE E.deptid = e.deptid AND position IN (‘MANAGER’, ‘DIRECTOR’, ‘VICE PRESIDENT’) ORDER BY position;

  9. Guidelines for Query Tuning - 3 • Guideline 2: Minimize the use of DISTINCT- don’t need it if duplicates are acceptable, or if answer contains a key. Ex. SELECT DISTINCT * FROM EMPLOYEE (this is expensive!) => SELECT * FROM EMPLOYEE • Guideline 3: Minimize the use of GROUP BY and HAVING- eliminate sort operation. SELECT MIN (E.age) FROM Employee E GROUP BY E.dno HAVING E.dno=102 SELECT MIN (E.age) FROM Employee E WHERE E.dno=102 =>

  10. Guidelines for Query Tuning - 4 • Guideline 4: Selection involve arithmetic or string expressions. Ex 1. Consider DBMS use of index when writing arithmetic expressions: E.age=2*D.agewill benefit from index on E.age, but might not benefit from index on D.age! Replacing the condition by E.age/2 = D.age would reverse the situation. Ex 2. 2*E.age < 20=> E.age < 10 =>

  11. Guidelines for Query Tuning - 5 SELECT * INTO Temp FROM Emp E, Dept D WHERE E.dno=D.dno AND D.mgrname=‘Joe’ • Guideline 5: Avoid using intermediate relations: SELECT E.dno, AVG(E.sal) FROM Emp E, Dept D WHERE E.dno=D.dno AND D.mgrname=‘Joe’ GROUP BY E.dno and vs. SELECT T.dno, AVG(T.sal) FROM Temp T GROUP BY T.dno • Does not materialize the intermediate relation Temp. • If there is a dense B+ tree index on <dno, sal>, an index-only plan can be used to avoid retrieving Emp tuples in the second query!

  12. Guidelines for Query Tuning - 6 • Guideline 6: Avoid nested queries SELECT S.sname FROM Sailors S WHEREEXIST (SELECT * FROM Reserve R WHERE R.bid = 103 AND S.sid = R.sid) SELECT S.sname FROM Sailors S, Reserve R WHERES.sid=R.sid AND R.bid=103 vs.

  13. Guidelines for Query Tuning - 7 • Guideline 7: Make use of the available indexes. There is an index available on e.deptno vs. SELECT d.dept_name, e.last_name, e.empno FROM employee e, department d WHERE e.deptno = d.deptno AND d.deptno = ‘808’; SELECT d.dept_name, e.last_name, e.empno FROM employee e, department d WHERE e.deptno = d.deptno AND e.deptno = ‘808’;

  14. Guidelines for Query Tuning - 8 • Guideline 8: Retrieve only what is needed. To minimize the amount of data returned by the SQL statements, be sure to specify the absolute minimum number of columns in the SELECT statement. Is there a need to specify the last_name column in the SELECT statement? SELECT position, last_name, empno FROM employee WHERE last_name = ‘SMITH’;

  15. Guidelines for Query Tuning - 9 • Guideline 9: Make use of prefix (table aliases) • Tell the Oracle server exactly where to find the columns • Help SQL code smaller by conserving memory * Prefix (table aliases) only valid for the current SQL statement SELECT Sailors.sname, Reserve.day FROM Sailors, Reserve WHERESailors.sid=R.sid AND Reserve.bid=103 SELECT S.sname, R.day FROM Sailors S, Reserve R WHERES.sid=R.sid AND R.bid=103 vs.

  16. Summary • Over time, indexes have to be fine-tuned (dropped, created, re-built, ...) for performance. • Should determine the plan used by the system, and adjust the choice of indexes appropriately. • System may still not find a good plan: • Null values, arithmetic conditions, string expressions, the use of ORs, etc. can confuse an optimizer. • So, may have to rewrite the query/view: • Avoid nested queries, temporary relations, complex conditions, and operations like DISTINCT and GROUP BY.

More Related