Database performance tuning and query optimization

 Chapter 11  Database Performance Tuning and Query Optimization ISBN-13: 978-1337627900 ISBN-10: 1337627909 Buy Book Amazon.com URL Modified by: Usman Tariq, PhD Associate Professor, PSAU Office ☎ 00966 11 588 8386

Learning Objectives  In this chapter, the student will learn:  Basic database performance-tuning concepts  How a DBMS processes SQL queries  About the importance of indexes in query processing  About the types of decisions the query optimizer has to make  Some common practices used to write efficient SQL code  How to formulate queries and tune the DBMS for optimal performance 2

Database Performance-Tuning Concepts  Goal of database performance is to execute queries as fast as possible  Database performance tuning: Set of activities and procedures that reduce response time of database system  Fine-tuning the performance of a system requires that all factors must operate at optimum level with minimal bottlenecks 3

Table 11.1 - General Guidelines for Better System Performance 4

Performance Tuning: Client and Server  Client side  SQL performance tuning: Generates SQL query that returns correct answer in least amount of time  Using minimum amount of resources at server  Server side  DBMS performance tuning: DBMS environment configured to respond to clients’ requests as fast as possible  Optimum use of existing resources 5

“Low-end” Server Specifications 6

“Enterprise” Server Specifications 7

DATABASE STATISTICS MEASUREMENTS 8

Example: to generate statistics for the VENDOR table, you would use: 1. In Oracle:  ANALYZE TABLE VENDOR COMPUTE STATISTICS; 2. In MySQL:  ANALYZE TABLE VENDOR; 3. In SQL Server: UPDATE STATISTICS VENDOR; 9 It is common to periodically regenerate the statistics for database objects, especially database objects that are subject to frequent change.

Database analysis 10 Growth trend of the database in total, the data files, the transaction logs, and the filestream data, for the selected period

Select more fields to avoid SELECT DISTINCT  Inefficient:  SELECT * FROM Customers  Efficient:  SELECT FirstName, LastName, Address, City, State, Zip FROM Customers  Inefficient and inaccurate:  SELECT DISTINCT FirstName, LastName, State FROM Customers 11

SELECT DISTINCT statement in MySQL 12 Will take at least 10 minutes to furnish results

Query Processing [1/2]  Parsing. The DBMS parses the SQL query and chooses the most efficient access/execution plan.  Execution. The DBMS executes the SQL query using the chosen execution plan.  Fetching. The DBMS fetches the data and sends the result set back to the client. 13 SQL query is: 1. Validated for syntax compliance 2. Validated against the data dictionary to ensure that table names and column names are correct 3. Validated against the data dictionary to ensure that the user has proper access rights 4. Analyzed and decomposed into more atomic components

SQL Parsing Phase  Query is broken down into smaller units  Original SQL query is transformed into slightly different version of the original SQL code which is fully equivalent and more efficient  Query optimizer: Analyzes SQL query and finds most efficient way to access data  Access plans: DBMS-specific and translate client’s SQL query into a series of complex I/O operations 15

SQL Parsing  Syntax Check  Oracle Database must check each SQL statement for syntactic validity.  A statement that breaks a rule for well-formed SQL syntax fails the check. For example, the following statement fails because the keyword FROM is misspelled as FORM: 16 external

SQL Parsing  Semantic Check  A semantic check determines whether a statement is meaningful, for example, whether the objects and columns in the statement exist.  A syntactically correct statement can fail a semantic check, as shown in the following example of a query of a nonexistent table: 17 external

SQL Parsing  Shared Pool Check  Shared Pool is a portion of the System Global Area (SGA) that contains shared memory constructs such as shared SQL areas.  During the parse, the database performs a shared pool check to determine whether it can skip resource-intensive steps of statement processing. 18 external

SQL Parsing Phase  If Query Access Plan already exists for query in SQL cache, DBMS reuses it  If not, optimizer evaluates various plans and chooses one to be placed in SQL cache for use 19 ParallelQueryAccessPlan

Create joins with INNER JOIN rather than WHERE  SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate FROM Customers, Sales WHERE Customers.CustomerID = Sales.CustomerID 20 SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate FROM Customers INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID The database would only generate the #### desired records where CustomerID is equal

Use WHERE instead of HAVING to define filters [1/2]  SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID) FROM Customers INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID GROUP BY Customers.CustomerID, Customers.Name HAVING Sales.LastSaleDate BETWEEN #1/1/2019# AND #12/31/2019# 21 SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID) FROM Customers INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID WHERE Sales.LastSaleDate BETWEEN #10/10/2019# AND #17/10/2019# GROUP BY Customers.CustomerID, Customers.Name

Use WHERE instead of HAVING to define filters [2/2] 22 SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID) FROM Customers INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID WHERE Sales.LastSaleDate BETWEEN #10/10/2019# AND #17/10/2019# GROUP BY Customers.CustomerID, Customers.Name HAVING Count(Sales.SalesID) > 5 HAVING should only be used when filtering on an aggregated field. In the query above, we could additionally filter for customers with greater than 5 sales using a HAVING statement.

23 1. Proper indexing 2. Retrieve the relevant data only 3. Getting rid of correlated subqueries 4. Using or avoiding temporary tables according to requirement 5. Avoid coding loops OPTIMIZATION 1. Proper indexing 2. Retrieve the relevant data only 3. Getting rid of correlated subqueries 4. Using or avoiding temporary tables according to requirement 5. Avoid coding loops MONITORING PERFORMANCE 1. Monitor Availability and Resource Consumption 2. Measure and Compare Throughput 3. Monitor Expensive Queries 4. Track Database Changes 5. Monitor Logs A data mart is a subset of a data warehouse oriented to a specific business line. Data marts contain repositories of summarized data collected for analysis on a specific section or unit within an organization, for example, the sales department. The Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates. Partitioning is the database process where very large tables are divided into multiple smaller parts. In computing, a materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function. external

Full Table Scan: Example 24 The following statement queries monthly salaries over SAR 4000

Indexes and Query Optimization  Indexes are crucial in speeding up data access because they facilitate searching, sorting, and using aggregate functions and even join operations. 26

Index Range Scan: Example 27 SELECT * FROM employees WHERE department_id = 20 AND salary > 1000; If indexes are so important, why not index every column in every table? Indexing every column in every table overtaxes the DBMS in terms of index- maintenance processing, especially if the table has many attributes and rows, or requires many inserts, updates, and deletes.

Database Query Optimization Modes  Algorithms proposed for query optimization are based on: 1. Selection of the optimum order to achieve the fastest execution time 2. Selection of sites to be accessed to minimize communication costs  Evaluated on the basis of: 1. Operation mode 2. Timing of its optimization 28

Query Blocks  Each SELECT block in the original SQL statement is represented internally by a query block. A query block can be a top-level statement, subquery, or unmerged view. 29 external

Query Optimizer Operations 30 The optimizer contains three components: the transformer, estimator, and plan generator. external

Join Method Example Adaptive Query Plans SELECT product_name FROM order_items o, prod_info p WHERE o.unit_price = 15 AND quantity > 1 AND p.product_id = o.product_id 32 external Hybrid Hash Distribution EXPLAIN PLAN FOR SELECT /*+ parallel(4) full(e) full(d) */ department_name, sum(salary) FROM employees e, departments d WHERE d.department_id=e.department_id GROUP BY department_name;

Using Hints to Affect Optimizer Choices 33

DBMS tuning hierarchical level 34 external

Adaptive Query Plans 35 Statistics Feedback SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id, product_name FROM order_items o, product_information p WHERE p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v WHERE o.order_id = v.order_id external

Classification of Operation Modes  Automatic query optimization: DBMS finds the most cost-effective access path without user intervention  Manual query optimization: Requires that the optimization be selected and scheduled by the end user or programmer 36

Classification Based on Timing of Optimization  Static query optimization: best optimization strategy is selected when the query is compiled by the DBMS  Takes place at compilation time  Dynamic query optimization: Access strategy is dynamically determined by the DBMS at run time, using the most up-to-date information about the database  Takes place at execution time 37

Classification Based on Type of Information Used to Optimize the Query  Statistically based query optimization algorithm: Statistics are used by the DBMS to determine the best access strategy  Statistical information is generated by DBMS through:  Dynamic statistical generation mode  Manual statistical generation mode  Rule-based query optimization algorithm: based on a set of user-defined rules to determine the best query access strategy 38

DBMS Architecture  All data in a database are stored in data files  Data files automatically expand in predefined increments known as extends  Data files are grouped in file groups or table spaces  Table space or file group: Logical grouping of several data files that store data with similar characteristics  Data cache or buffer cache: Shared, reserved memory area  Stores most recently accessed data blocks in RAM 39

DBMS Architecture  SQL cache or procedure cache: Stores most recently executed SQL statements or PL/SQL procedures  DBMS retrieves data from permanent storage and places them in RAM  Input/output request: Low-level data access operation that reads or writes data to and from computer devices  Data cache is faster than working with data files  Majority of performance-tuning activities focus on minimizing I/O operations 40

Find Current Location of Data and Log File of All the Database 41 SELECT name, physical_name AS current_file_location FROM sys.master_files

42 DBMS - File Structure external

Figure 11.1 - Basic DBMS Architecture 43

SQL Execution Phase  All I/O operations indicated in the access plan are executed  Locks are acquired  Data are retrieved and placed in data cache  Transaction management commands are processed 44

SQL Execution Phase 45 Using SQL Access Advisor external

SQL Fetching Phase  Rows of resulting query result set are returned to client  DBMS may use temporary table space to store temporary data  Database server coordinates the movement of the result set rows from the server cache to the client cache 46

Indexes and Query Optimization  Indexes  Help speed up data access  Facilitate searching, sorting, using aggregate functions, and join operations  Ordered set of values that contain the index key and pointers  More efficient than a full table scan 47 SELECT * FROM Table WITH(INDEX(Index_Name)) external

Indexes and Query Optimization  Data sparsity: Number of different values a column could have  Data structures used to implement indexes:  Hash indexes  B-tree indexes  Bitmap indexes  DBMSs determine best type of index to use 48

Creating a Named b-Tree Composite 49 external

Optimizer Choices  Rule-based optimizer: Uses preset rules and points to determine the best approach to execute a query  Cost-based optimizer: Uses algorithms based on statistics about objects being accessed to determine the best approach to execute a query 50

Using Hints to Affect Optimizer Choices  Optimizer might not choose the best execution plan  Makes decisions based on existing statistics, which might be old  Might choose less-efficient decisions  Optimizer hints: Special instructions for the optimizer, embedded in the SQL command text 51

Optimizer Hints  A hint is embedded within a SQL comment.  The hint comment must immediately follow the first keyword of a SQL statement block. You can use either style of comment: a slash-star (/*) or pair of dashes (--). The plus-sign (+) hint delimiter must immediately follow the comment delimiter, with no space permitted before the plus sign, as in the following fragment: 52 external

Table 11.5 - Optimizer Hints 53

SQL Performance Tuning  Evaluated from client perspective  Most current relational DBMSs perform automatic query optimization at the server end  Most SQL performance optimization techniques are DBMS-specific and thus rarely portable  Majority of performance problems are related to poorly written SQL code 54

Index Selectivity  Measure of the likelihood that an index will be used in query processing  Indexes are used when a subset of rows from a large table is to be selected based on a given condition  Index cannot always be used to improve performance  Function-based index: Based on a specific SQL function or expression 55

Conditional Expressions  Expressed within WHERE or HAVING clauses of a SQL statement  Restricts the output of a query to only rows matching conditional criteria  Guidelines to write efficient conditional expressions in SQL code  Use simple columns or literals as operands  Numeric field comparisons are faster than character, date, and NULL comparisons 56

SQL Expressions  Oracle Database does not accept all forms of expressions in all parts of all SQL statements. Information URL 57 external

Conditional Expressions  Equality comparisons are faster than inequality comparisons  Transform conditional expressions to use literals  Write equality conditions first when using multiple conditional expressions  When using multiple AND conditions, write the condition most likely to be false first  When using multiple OR conditions, put the condition most likely to be true first  Avoid the use of NOT logical operator 58

Query Formulation  Identify what columns and computations are required  Identify source tables  Determine how to join tables  Determine what selection criteria are needed  Determine the order in which to display the output 59

DBMS Performance Tuning  Managing DBMS processes in primary memory and the structures in physical storage  DBMS performance tuning at server end focuses on setting parameters used for: 1. Data cache 2. SQL cache 3. Sort cache 4. Optimizer mode  In-memory database: Store large portions of the database in primary storage 60

DBMS Performance Tuning  Recommendations for physical storage of databases:  Use RAID (Redundant Array of Independent Disks) to provide a balance between performance improvement and fault tolerance  Minimize disk contention  Put high-usage tables in their own table spaces  Assign separate data files in separate storage volumes for indexes, system, and high-usage tables 61

DBMS Performance Tuning  Take advantage of the various table storage organizations in the database  Index-organized table or clustered index table: Stores the end-user data and the index data in consecutive locations in permanent storage  Partition tables based on usage  Use denormalized tables where appropriate  Store computed and aggregate attributes in tables 62

63 Databases, tablespaces, and datafiles are closely related, but they have important differences: • An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data. • Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running. • A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles). external

CREATE TABLESPACE  Create a Shadow Tablespace: Example 64 The following example creates a 10 MB undo tablespace undots1:

Creating a Temporary Tablespace: Example  This statement shows how the temporary tablespace that serves as the default temporary tablespace for database users in the sample database was created: 65

Enabling Autoextend for a Tablespace: Example 66 This statement creates a tablespace named tbs_02 with one data file. When more space is required, 500 kilobyte extents will be added up to a maximum size of 100 megabytes:

Using Multiple Tablespaces  You can use multiple tablespaces to perform the following tasks: 1. Control disk space allocation for database data 2. Assign specific space quotas for database users 3. Control availability of data by taking individual tablespaces online or offline 4. Perform partial database backup or recovery operations 5. Allocate data storage across devices to improve performance 67 external

Using Tablespaces  A database administrator can use tablespaces to do the following actions: 1. Create new tablespaces 2. Add datafiles to tablespaces 3. Set and alter default segment storage settings for segments created in a tablespace 4. Make a tablespace read only or read/write 5. Make a tablespace temporary or permanent 6. Rename tablespaces 7. Drop tablespaces 68 external

Create TABLESPCE: Example 69 CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; In this example, a 128K extent size is specified. Each 128K extent (which, if the tablespace block size is 2K, is equivalent to 64 Oracle blocks) is represented by a bit in the extent bitmap for this file.

Make a tablespace read only 70 ALTER TABLESPACE sales_1 READ ONLY; ALTER TABLESPACE LMS_Student READ ONLY;

Creating Oracle Managed Files: Examples 71 CREATE TABLESPACE omf_ts2 DATAFILE AUTOEXTEND OFF; The following example creates a tablespace with an Oracle-managed data file of 100M that is not autoextensible: ALTER SYSTEM SET DB_CREATE_FILE_DEST = '$ORACLE_HOME/rdbms/dbs'; CREATE TABLESPACE omf_ts1; The following example sets the default location for data file creation and creates a tablespace with a data file in the default location. The data file is 100M and is autoextensible with an unlimited maximum size:

Database performance tuning and query optimization

In this document