1 / 18

Module 4 Database Tuning

Module 4 Database Tuning. Section 3 Application Performance. Overview of Application Performance. Application Performance – Developer and DBA Shared Responsibilities Tuning and optimizing SQL statement to maximize an application’s performance

evette
Download Presentation

Module 4 Database Tuning

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. Module 4 Database Tuning ITEC 450 Section 3 Application Performance

  2. Overview of Application Performance Application Performance – Developer and DBA Shared Responsibilities • Tuning and optimizing SQL statement to maximize an application’s performance • Ensuring the application interacts with the DBMS appropriately and efficiently. ITEC 450

  3. Designing Applications for Database For relational database, application design should be for relational access. • Type of SQL – planned or not, dynamic or static, embedded or stand-alone • Programming language – any features can be explored for database access performance • Transaction design and processing • Locking strategy • Commit strategy • Batch processing or Online processing ITEC 450

  4. Overview of Optimizer • The optimizer is the heart of a DBMS, and is an inference engine responsible for determining the most efficient means of accessing the specified data. • Each DBMS also provides techniques that you can use to influence the optimizer perform its job better. • The query optimizer performs the following operations for each SQL statement: • Evaluation of expressions and conditions • Statement transformation • Choice of optimizer goals – batch for best throughput, online for best response time • Choice of access paths • Choice of join orders ITEC 450

  5. Optimizer Influence Factors • CPU and I/O costs • Database statistics – one of DBA’s main responsibilities. You can use ANALYZE TABLE in Oracle, or utilities. • Query analysis – involved objects and conditions • Joins – how to combine the outputs from each table in the most efficient manner • Access path choices • Full table scans – read all rows and filters out those that do not meet the selection criteria • Indexed access – retrieve by traversing the index • Rowid scans – fastest way to retrieve a single row, as rowid specifies the datafile, data block and the location of the row in that block • Hashed access – access based on a hash value, similar to indexed access ITEC 450

  6. SQL Tuning Tips SQL tuning is a complicated task that requires a full-length book of its own. • KISS principle: Keep It Short and Simple. • Retrieve only what is needed • Judicious use of LIKE – avoid leading wild-card (%) • Beware of code generators – automatically created query statements from a tool can be a nightmare to DBA’s • Goals for tuning: • Reduce the workload – for example to create or use an index • Balance the workload – adjust query running time to avoid peak usage • Parallelize the workload – for large amounts of data in data warehouse queries ITEC 450

  7. Module 4 Database Tuning ITEC 450 Section 4 Oracle SQL Query Optimization

  8. Optimizing Oracle Query Processing Query processing is the transformation of your SQL statement into an efficient execution plan to return the requested data from the database. • Parsing – checking the syntax and semantics of the SQL statements • Optimization – using a cost-based optimizer (CBO) to choose the best access method for retrieving data for the tables and indexes referred to in the query • Query rewrite – converting into an abstract logical query plan • Execution plan generation phase – permutation of various operations, orders, algorithms, etc. • Query execution – executing the physical query plan ITEC 450

  9. Oracle Cost Based Optimizer ITEC 450

  10. Understanding Statistics Optimizer statistics are a collection of data: • Table statistics • Number of rows • Number of blocks • Average row length • Column statistics • Number of distinct values in column • Number of nulls in column • Data distribution (histogram) • Extended statistics • Index statistics • Number of leaf blocks • Levels • Clustering factor • System statistics • I/O performance and utilization • CPU performance and utilization ITEC 450

  11. Providing Statistics to the Optimizer The recommended approach is to allow Oracle database to automatically collect the statistics. • The job to collect statistics can be found SQL> select owner, job_name, enabled, state, comments from dba_scheduler_jobs; • To check that the statistics are indeed collected SQL> select table_name , last_analyzed, num_rows from dba_tables where owner = 'OE' ; • Oracle also collects the statistics on columns SQL> select column_name, num_distinct from dba_tab_col_statistics where owner = 'OE' and table_name = 'PRODUCT_DESCRIPTIONS'; ITEC 450

  12. Manually Gathering Statistics Because the automatic optimizer statistics collection runs during maintenance windows, the statistics on tables which are significantly modified throughout the day may become stale. • Volatile tables that are being deleted and rebuilt • Objects with large bulk loads Manual Statistics Gathering • Using the dbms_stats utility, for example: SQL> exec dbms_stats.gather_schema_stats( - ownname => 'SCOTT', - options => 'GATHER AUTO', - estimate_percent => dbms_stats.auto_sample_size, - method_opt => 'for all columns size repeat', - degree => 34 ) • Old-fashion for backward compatibility – Analyze table ITEC 450

  13. Execution Plan (Explain Plan) A statement’s execution plan is the sequence of operations Oracle performs to run the statement. The row source tree is the core of the execution plan. It shows the following information: • An ordering of the tables referenced by the statement • An access method for each table mentioned in the statement • A join method for tables affected by join operations in the statement • Data operations like filter, sort, or aggregation ITEC 450

  14. Running Explain Plan Using autotrace utilities • To generate the execution plan only and doesn’t execute the query itself SQL> set autotrace on explain • To show only the execution statistics for the SQL statement SQL> set autotrace on statistics • To show both the execution plan and execution statistics SQL> set autotrace on Using PLAN_TABLE • To explain a SQL statement: SQL> EXPLAIN PLAN FOR SELECT last_name FROM employees; • This explains the plan into the PLAN_TABLE table. You can then select the execution plan from PLAN_TABLE. ITEC 450

  15. Running Explain Plan Using Tools Using Oracle SQL Developer ITEC 450 Using Other Tools • TOAD • Many Others

  16. Example of Execution Plan • An example of SQL statement SQL> select i.product_id, i.product_name, d.translated_name from oe.product_informationi, oe.product_descriptions d where i.product_id = d.product_id and rownum < 5; • The execution plan is 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=4 Bytes=228) 1 0 COUNT (STOPKEY) 2 1 NESTED LOOPS (Cost=6 Card=4 Bytes=228) 3 2 TABLE ACCESS (FULL) OF 'PRODUCT_DESCRIPTIONS' (TABLE)(Cost=2 Card=4 Bytes=148) 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT_INFORMATION’ (TABLE) (Cost=1 Card=1 Bytes=20) 5 4 INDEX (UNIQUE SCAN) OF 'PRODUCT_INFORMATION_PK' (INDEX (UNIQUE)) (Cost=0 Card=1) ITEC 450

  17. Execution Plan Using SQL Developer ITEC 450

  18. Wrap Up • Assignment 3-1-4: Lab4: Query Optimization • Creation of execution plans • Review and interpretation of execution • Analysis of the differences between the two execution plans • Clearly presented "Lessons Learned" section ITEC 450

More Related