This lecture discusses selected database performance issues from the developer's point of view: connection overhead, bind variables and SQL injection, making most of the optimizer with up-to-date statistics, reading execution plans. Prior knowledge of SQL is expected

Kwiatek, Michal

CERN Document Server

Basic database performance tuning - developer’s perspectiveMichal KwiatekCERN, Geneva, SwitzerlandAbstractThis lecture discusses selected database performance issues from the devel-oper’s point of view: connection overhead, bind variables and SQL injec-tion, making most of the optimizer with up-to-date statistics, reading executionplans. Prior knowledge of SQL is expected.1 IntroductionApplication developers tend to perceive the problem of database performance as the responsibility ofthe database administrators (DBAs). This is a misconception, because with a badly designed applicationthere is little a DBA can do.It is true that the DBA often has the intimate knowledge of the database. However, the developerhas the intimate knowledge of the application which allows him or her to design and implement theapplication so that it fulls the requirements in the fastest possible way.This lecture explains selected elements of the Relational Database Management System (RDBMS).Oracle RDBMS is used in the examples, but similar mechanisms exist in other database systems. Under-standing these mechanisms will enable the developers to increase the efciency of their applications.Indexes, which are of paramount importance to database performance, are not discussed in thislecture. The reader should therefore turn to other sources to learn how to effectively use database indexes.2 Connection overheadProblems with database performance can occur as soon as one connects to the database. In the mostcommon scenario, when a client rst connects to an Oracle database, a dedicated server process is createdto serve this and all the following requests for this client.Fig. 1: Oracle dedicated server architecture, based on [1]Creating a new process is a time consuming operation. Indeed, the time needed to open a connec-tion is the matter of seconds, not milliseconds. This is not a problem for a single-threaded applicationwhere the connection is opened once and then used for many subsequent queries. However, in a webapplication serving hundreds of requests per minute where each request needs only one short query tocomplete, opening a separate connection for every request can create a non-negligible overhead.To solve his problem, using connection pooling should be considered. Create a pool of connectionsthat are open only once and shared among the threads of the application according to needs. When a87database connection is needed, it is taken from the pool. When it is no longer needed, it is returnedto the pool. When the number of threads that need a connection is larger than the number of availableconnections some threads just wait.This is a standard solution and no new code has to be written for this purpose: ready-to-useimplementations exist for most of today’s programming languages.3 Performance cost of riskOnce connected to the database, one probably wants to query some data. Let’s have a quick look at whathappens when an SQL query is run.Fig. 2: Running a SQL queryThe query rst has to be checked, both syntactically and semantically. It is done by a parser. Thenit is transformed into row-level operations that will actually be run. This is called an execution plan. Fora given SQL query, there are many semantically correct execution plans - many different ways to achievethe same result. The optimizer analyses the query to create the best execution plan possible. But theoptimization itself is time consuming, so every time an execution plan is created, it is put into a specialmemory structure, called Library Cache, together with the digest of the query. Next time the same queryis executed, optimization is skipped. This is called a soft parse, as opposed to the hard parse, whichincludes the optimization.Let us now consider the example listing in Fig. 3Fig. 3: SQL query with a hardcoded literal valueIt shows an SQL query invoked from the Java programming language. When line 3 is executed, the query"select sal from emp where ename = ’Kwiatek’" is submitted to the database. If such a query is run for2M. KWIATEK88many different employees, and a different name is submitted every time - will there be a hard or a softparse every time this query is run?From the point of view of the database, the query "select sal from emp where ename = ’Kwiatek’"is different from the query "select sal from emp where ename = ’Smith’", because their digests aredifferent. So every time such a query is run, it will be hard- parsed. In consequence, the database will beunnecessarily loaded. This can be observed when a large number of such queries are run in parallel.But the code shown on the listing in Fig. 3 has a more important problem. Imagine variablemyName is assigned a value of "O’Brian". In this case, the query, as constructed in line 2, will be:""select sal from emp where ename = ’O’Brian’". This query will fail: it is syntactically incorrect,because it contains 3 single-quotes characters. So, using the code as presented in Fig. 3 , one runs therisk of having errors in the application!Very often the data, used as parameters to queries, is actually taken from user input. So dependingon user input, the application will work ne or crash. This is very bad programming style. What isworse, control over the query is lost: a malevolent user could try to hack it by submitting a value thatcloses the apostrophe, adds an unauthorised clause (for example a union with a table the user should notbe allowed to see) and the second apostrophe to make such crafted query syntactically correct. In short,the application becomes vulnerable to SQL injection. This is insecure.One could of course change the code in such a way, that all special characters in user input arereplaced, as shown on the listing in Fig. 4. This will protect the application from SQL injection, providedthat all special characters are effectively encoded. But it will not help against a hard parse every timesuch a query is executed.Fig. 4: Manually encoding special characters in an SQL queryThere is an easy solution to both these issues: bind variables. Using placeholders for values,instead of the literal values in the SQL query, makes the query secure. What is more, the digest of such aquery is the same no matter what values are submitted, so the existing query plan can be retrieved fromthe library pool and reused. The listing in Fig. 5 shows the syntax for using bind variables in the Javalanguage. Again, similar syntax exists in most of today’s computer languages.Fig. 5: SQL query with bind variables4 Can a blindfolded optimizer be effective?Looking again at Fig. 2, two types of optimizer can be seen: the Rule Based Optimizer (RBO) and theCost Based Optimizer (CBO).3BASIC DATABASE PERFORMANCE TUNING – DEVELOPER’S PERSPECTIVE89RBO prepares the execution plan based on a predened set of rules. No matter what the dataactually is, RBO will always come up with the same execution plan for the given query. This is bad,because the distribution of the data has important impact on the time needed to query it.CBO, on the other hand, uses table and index statistics to estimate the cost of running the query.This is much more accurate but recent statistics are needed.The listing in Fig. 6 shows an Oracle PL/SQL procedure call that will gather statistics for allobjects in the current schema. See [2] for an explanation of parameters used. Whenever an investigationof query performance is needed, it is recommended to start by running this procedure to make sure thatthe optimizer is at its best.Fig. 6: Gathering statistics for all objects in the current schemaCBO is so much more effective that in Oracle 10g RBO disappears altogether. Statistics areautomatically gathered on regular basis. Still, in case of performance problems, it is worth refreshing thestatistics.5 The black box syndromeSQL is a declarative language: its syntax lets us specify what results we want but not how to get them.This is very convenient, because the complexity of the database operation is hidden from us as if thedatabase was a black box that produces the results without revealing how.It works very well in most cases: RDBMS takes care of the tedious technical details. However,when performance problems arise, it is a signal that the automatic operation of the database is not aseffective as it is supposed to be. Clearly, a human investigation is needed. The rst step is to understandwhat is actually going on within the black box.Oracle provides mechanisms that let us investigate with different levels of complexity. Somemechanisms, like tkprof (see [3] to learn how to use tkprof at CERN), require shell access to the databaseserver, so are reserved to the DBAs. Other, like the EXPLAIN PLAN command, can be very well usedby the developers.The EXPLAIN PLAN command lets us read the execution plan of our query. To use it, we rstneed to create the PLAN_TABLE where the plan will be stored for us to see. We just need to run the$ ORACLE_HOME/rdbms/admin/utlxplan.sql script (it can be found on the Internet) and we can startusing EXPLAIN PLAN for our queries. After the query has been explained, we can see it using thedbms_xplan.display function, as shown on the listing in Fig. 7.The table on the listing shown in 7 contains the tree of operations that constitute the executionplan. Next to each operation we can see the number of rows, the number of bytes and the relative CPUcost of this operation. To have a complete picture, we need to add the predicate information presentedbelow the table. The complete tree is presented in Fig. 8.The last piece of information we need is the meaning of the access paths and join methods used inthe execution plan (see [4] for a complete list). The nested loops predicate at the root of the tree meansthat for every row returned by the rst (upper) branch, the second (lower) branch is executed and theirresults are joined. In our example, the table EMP will be searched row-by-row (this is the meaning of4M. KWIATEK90Fig. 7: Reading the execution plan of your queryFig. 8: The execution plan presented in a treethe "table access full" access path) but only the rows which satisfy the lter criteria will be returned.For each such row, the lower branch will be executed: rst index EMP_EMPNO_PK will be scannedwith the unique condition to nd the ROWID needed to get the full row by ROWID in the second step.ROWIDs in Oracle are basically physical addresses of data (see also [5]). In a semi-formal imperativelanguage, it would look like the listing in Fig. 9.Fig. 9: The execution plan in a semi-formal imperative language5BASIC DATABASE PERFORMANCE TUNING – DEVELOPER’S PERSPECTIVE91In conclusion, Oracle, as well as other RDBMS vendors, gives us the tool to look inside the blackbox and understand the execution plan of SQL queries. This is the rst thing to do for a developer whoinvestigates performance problems. See also [6] for a wider explanation of how to read execution plans.6 Summary: the importance of being earnestTo sum up, the developer of a database application has even more potential to tune for performance thanthe DBA. This is because the developer knows the application: its structure, its use cases and its potentialperformance bottlenecks.Achieving higher performance requires following best practices (some of which are discussed inthis lecture) and thinking about performance before problems actually arise. To investigate performanceissues, one needs to earnestly try to understand what the application does on the database side. It takessome work to get this understanding, but this work is most easily done by the developer.Understanding certain points about the RDBMS is required, and although this lecture touchessome areas of RDBMS, it is far from being exhaustive. In particular, indexes, which are substantial todatabase performance, are not discussed in this lecture at all. The reader should therefore turn to othersources to further enhance his or her knowledge of the database. A very good source of information is"AskTom", the question and answer service hosted by Oracle (see [7]).References[1] Oracle9i Net Services Administrator’s Guide, Dedicated Server Processes.http://download-uk.oracle.com/docs/cd/B10501_01/network.920/a96580/architec.htm#1048705[2] Oracle9i Supplied PL/SQL Packages and Types Reference, GATHER_DATABASE_STATSProcedure.http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats2.htm[3] Michal Kwiatek, Performance Optimization and Tuning, iCSC 2005.http://indico.cern.ch/getFile.py/access?contribId=s5t4&sessionId=5&resId=1&materialId=0&confId=a05859[4] Oracle9i Database Performance Tuning Guide and Reference, Understanding Access Paths forCBO.http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#35891[5] Thomas Kyte, Effective Oracle by Design, Oracle Press, ISBN 0-07-223065-7, p. 454-455.[6] Ibid. p. 84-87.[7] Ask Tom Oracle, the Question and Answer service edited by Thomas Kyte.http://asktom.oracle.com/6M. KWIATEK92

Basic database performance tuning - developer's perspective

Abstract

Similar works

Full text

Available Versions

CERN Document Server