Oracle database performance tuning

Oracle Database Performance Tuning Presented By- Rahul Gaikwad

What is Database Tuning?  Database tuning is a group of activities used to optimize the performance of a database.  Goal Of Database Tuning?  To maximize use of system resources  To perform task as efficiently  To work rapidly as possible www.yogijicreations.com

Why and when should one tune?  Slow Physical I/O -caused by poorly-configured disks -caused by unnecessary physical I/O -caused by poorly-tuned SQL.  Excessive CPU usage -It means that there is little idle CPU on the system -caused by an inadequately-sized system, -caused by untuned SQLstatements -caused inefficient application programs.  Latch Contention Rarely is latch contention tunable by reconfiguring the instance. Rather, latch contention usually is resolved through application changes. www.yogijicreations.com

Causes for low Performance  Bad Connection Management  Bad Use of Cursors and the Shared Pool  Bad SQL  Use of Nonstandard Initialization Parameters  Getting Database I/O Wrong  Redo Log Setup Problems  Long Full Table Scans  High Amounts of Recursive (SYS) SQL www.yogijicreations.com

Where should we do the tuning?  Database Design  Poor system performance usually results from a poor database design.  One should generally normalize to the 3NF.  Selective denormalization can provide valuable performance improvements..  Application Tuning:  Approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL.  Memory Tuning:  By Properly size your database buffers (shared pool, buffer cache, log buffer, etc)  By looking at your wait events, buffer hit ratios, system swapping and paging, etc.  Disk I/O Tuning:  Database files needs to be properly sized.  Also look for frequent disk sorts, full table scans, data fragmentation, etc.  Eliminate Database Contention:  Study database locks, latches and wait events carefully and eliminate where possible.  Tune the Operating System: www.yogijicreations.com  Monitor and tune operating system CPU, I/O and memory utilization.

Optimizing the optimizer Optimizer inputs Table and index Cardinality Structure Estimates DB parameters IO and CPU Object Statistics And config Estimates System Statistics Cost estimate www.yogijicreations.com

Database Statistics  Database statistics provide information on the type of load on the database, as well as the internal and external resources used by the database.

Performance Data Statistics Time Model SQL Wait Metrics Stats Sessions

Wait Events  Wait events are statistics that indicate that it have to wait for an event to complete before being able to continue the processing.  common examples of the waits-  Application: locks waits caused by row level locking  Commit: waits for redo log write confirmation after a commit  Idle: signify the session is inactive  Network: waits for data to be sent over the network  User I/O: wait for blocks to be read off a disk

Time Model Statistics  The V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views provide time model statistics  The most important of the time model statistics is DB time.  This statistics represents the total time spent in database calls and is a indicator of the total instance workload.  It is calculated by aggregating the CPU and wait times of all sessions  DB time is measured cumulatively from the time that the instance was started.  For example, a instance that has been running for 30 minutes could have four active user sessions whose cumulative DB time is approximately 120 minutes.

Active Session History (ASH)  The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance.  Active sessions are sampled every second and are stored in a circular buffer in SGA.  Active Session includes any session that was on the CPU at the time of sampling.

System and Session Statistics  A large number of cumulative database statistics are available on a system and session level through the V$SYSSTAT and V$SESSTAT views.  Operating System Statistics  Operating system statistics provide information on the usage and performance of the main hardware components of the system, as well as the performance of the operating system itself.  It is always best to consider operating system statistics as a diagnostic tool, similar to the way many doctors use body temperature, pulse rate, and patient pain when making a diagnosis..  Operating system statistics include the following:  CPU Statistics  Virtual Memory Statistics  Disk Statistics  Network Statistics

Automatic Workload Repository  The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes.  This data is stored both in memory and in the database.  AWR include: – Time model statistics i.e. V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views – Some of the system and session statistics collected in the V$SYSSTAT and V$SESSTAT views – Active Session History (ASH) statistics, representing the history of recent sessions activity  AWR automatically generates snapshots of the performance data once every hour and collects the statistics in the workload repository.

Metric  A metric is defined as the rate of change in some cumulative statistic.  That rate can be measured against time, transactions, or database calls.  For example, the number database calls per second is a metric.  A history of recent metric values is available through V$ views.

Tools or Utilities for PT  V$SQL_PLAN ◦ Find SQLs with high resource costs  EXPLAIN PLAN & DBMS_STAT ◦ Determine the execution plan  SQL Trace/Tkprof ◦ Best drilldown at the session level

V$SQL_PLAN  Used to display the execution plan of a SQL statement:  After the statement has executed, you can display the plan by querying the V$SQL_PLAN view.  The V$SQL_PLAN_STATISTICS view provides the actual execution statistics for every operation in the plan, such as the number of output rows and elapsed time. www.yogijicreations.com

EXPLAIN PLAN  The EXPLAIN PLAN statement displays execution plans for SELECT, UPDATE, INSERT, and DELETE statements.  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 :  ordering of the tables  access method for each table  join method for tables  Data operations like filter, sort, or aggregation  The plan table Also contains information :  Optimization, such as the cost and cardinality of each operation  Partitioning, such as the set of accessed partitions  Parallel execution, such as the distribution method of join inputs www.yogijicreations.com

PLAN_TABLE Output Table  The PLAN_TABLE is automatically created to hold the output of an EXPLAIN PLAN statement for all users.  PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans.  While a PLAN_TABLE table is automatically set up for each user, you can use the SQL script utlxplan.sql to manually create a local PLAN_TABLE in your schema. www.yogijicreations.com

uses EXPLAIN PLAN  To examine a SQL statement that Select employee_id, job_title, salary, and department_name for the employees whose IDs are less than 103.  Example Using EXPLAIN PLAN SELECT e.employee_id, j.job_title, e.salary, d.department_name FROM employees e, jobs j, departments d WHERE e.employee_id < 103 AND e.job_id = j.job_id AND e.department_id = d.department_id; www.yogijicreations.com

EXPLAIN PLAN Output ----------------------------------------------------------------------------------- | Id | Operation | Name |Rows |Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | |3 | 189 | 10 (10)| | 1 | NESTED LOOPS | |3 | 189 | 10 (10)| | 2 | NESTED LOOPS | |3 | 141 | 7 (15)| |* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)| | 4 | TABLE ACCESS BY INDEX ROWID | JOBS | 19 | 513 | 2 (50)| |* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | | | 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)| |* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("E"."EMPLOYEE_ID"<103) 5 - access("E"."JOB_ID"="J"."JOB_ID") 7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") www.yogijicreations.com

Steps of Execution Plan  Step 3 reads all rows of the employees table.  Step 5 looks up each job_id in JOB_ID_PK index and finds the rowids of the associated rows in the jobs table.  Step 4 retrieves the rows with rowids that were returned by Step 5 from the jobs table.  Step 7 looks up each department_id in DEPT_ID_PK index and finds the rowids of the associated rows in the departments table.  Step 6 retrieves the rows with rowids that were returned by Step 7 from the departments table.  The following steps in Example operate on rows returned by the previous row source:  Step 2 performs the nested loop operation on job_id in the jobs and employees tables, accepting row sources from Steps 3 and 4, joining each row from Step 3 source to its corresponding row in Step 4, and returning the resulting rows to Step 2.  Step 1 performs the nested loop operation, accepting row sources from Step2 and Step6, joining each row from Step 2 source to its corresponding row in Step 6, and returning the resulting rows to Step 1. www.yogijicreations.com

Full Table Scans  This type of scan reads all rows from a table and filters out those that do not meet the selection criteria.  During a full table scan, all blocks in the table that are under the high water mark are scanned.  The high water mark indicates the amount of used space, or space that had been formatted to receive data.  Each row is examined to determine whether it satisfies the statement's WHERE clause. www.yogijicreations.com

Rowid Scans  The rowid of a row specifies the data files and data block containing the row and the location of the row in that block.  Locating a row by specifying its rowid is the fastest way to retrieve a single row, because the exact location of the row in the database is specified.  To access a table by rowid, Oracle first obtains the rowids of the selected rows, either from the statement's WHERE clause or through an index scan of one or more of the table's indexes.  Oracle then locates each selected row in the table based on its rowid. www.yogijicreations.com

Index Scans  In this method, a row is retrieved by traversing the index, using the indexed column values specified by the statement.  An index scan retrieves data from an index based on the value of one or more columns in the index.  To perform an index scan, Oracle searches the index for the indexed column values accessed by the statement.  If the statement accesses only columns of the index, then Oracle reads the indexed column values directly from the index, rather than from the table.  The index contains not only the indexed value, but also the rowids of rows in the table having that value.  Therefore, if the statement accesses other columns in addition to the indexed columns, then Oracle can find the rows in the table by using either a table access by rowid or a cluster scan.  An index scan types:  Assessing I/O for Blocks, not Rows  Index Unique Scans  Index Range Scans  Index Range Scans Descending  Index Skip Scans  Full Scans  Fast Full Index Scans  Index Joins  Bitmap Indexes www.yogijicreations.com

SINGLE TABLE LOOKUP  Index or table scan?  Avoid accidental table scans  Optimize indexes  best combination of concatenated indexes  Optimize necessary table scans  Vertical/Horizontal partitioning

1000 Full Scan no caching Index sorted data, no caching Index unsorted, cached data Full Table scan, cached data 100 Elasped Time (s) 10 Break even points for index vs table scan 1 0 10 20 30 40 50 60 70 80 90 100 Pct of table accessed

Concatenated Index Effectiveness last,first,birthyear,id 3 SELECT cust_id FROM sh.customers c WHERE cust_first_name = 'Connor' AND cust_last_name = 'Bishop' last,first,BirthYear 4 AND cust_year_of_birth = 1976; last+first name 6 last name 63 None 1459 0 200 400 600 800 1000 1200 1400 1600 Logical IO

BITMAP INDEXES 10 Elapsed Time (s) 1 0.1 0.01 1 10 100 1000 10000 100000 1000000 Distinct values in table Bitmap index B*-Tree index Full table scan

Joins OPTIMIZING JOINS  Best join order  Eliminate rows as early as possible  Join Type:  Nested loops  Optimize the join index  Sort merge  Avoid, esp. if memory scarce  Hash join  Avoid multi-pass executions

NESTED LOOPS JOIN prod_id,channel_id,cust_id,time_id,promo_id 2.2 time_id 3.14 Indexing prod_id,channel_id 23.43 prod_id 48.36 546.55 No Index 0 100 200 300 400 500 600 Elapsed time (s)

SORT-MERGE AND HASH JOIN 250 200 Elapsed Time (s) 150 Disk Sort 100 In Memory 50 Multi pass disk sort Single pass disk sort 0 In Memory 1 10 100 1000 Workarea Memory (MB) Hash Join Sort Merge Join

BITMAP JOIN PERFORMANCE Full table scan 13,480 Access Path Bitmap index 1,524 Bitmap Join index 68 0 2000 4000 6000 8000 10000 12000 14000 Logical IO SELECT SUM (amount_sold) FROM customers JOIN sales s USING (cust_id) WHERE cust_email='flint.jeffreys@company2.com';

SORTING – WHAT WE EXPECT Multi-pass Disk Sort Time Single Pass Disk Sort Memory Sort PGA Memory available (MB) Table/Index IO CPU Time Temp Segment IO

DML DML TUNING - INDEXES 7 16,316 6 14,285 5 12,727 Number of indexes 4 10,719 3 8,691 2 6,671 1 (PK only) 1,191 0 2,000 4,000 6,000 8,000 10,000 12,000 14,000 16,000 18,000 Logical reads required

MULTI-TABLE INSERT Multi-table insert Insert US Insert EMEA Insert both Two Inserts 0 1 2 3 4 5 6 Elapsed time (s)

MERGE 3.32 Update Insert Merge INSERT + UPDATE 3.89 3.71 0 1 2 3 4 5 6 7 8 Elapsed Time (s)

Top 10 Oracle SQL tuning tips 1. Design and develop with performance in mind 2. Establish a tuning environment 3. Index wisely 4. Reduce parsing 5. Take advantage of Cost Based Optimizer 6. Avoid accidental table scans 7. Optimize necessary table scans 8. Optimize joins 9. Use array processing 10. Consider PL/SQL for “tricky” SQL www.yogijicreations.com

www.yogijicreations.com For queries: info@yogijicreations.com

Oracle database performance tuning

In this document