Presented By
Akin S Walter-Johnson Ms

---

[attachment=10105]
SCOPE
• How data is accessed and reconstituted joins
• Inform the user on how identify problems with SQL
Repair of SQL
• Tuning can occur at 2 levels
– Server ( DBA)
– SQL level ( User)
IMPORTANCE OF TUNING
• Reduce response time for SQL processing
• To find a more efficient way to process workload
• Improve search time by using indexes
• Join data efficiently between 2 or more tables
HOW TO TUNE
• Review the access path, Join methods and index usage
• Test response through SQPLUS directly ( May mask performance )
• Test response through an Application front end ( Usually takes longer )
• Test response through a web interface
ROLE OF HARDWARE & DESIGN
• All the hardware in world will not save you
• Memory, Disk & CPU speed can improve performance
• Increased hardware does not always result into better performance
• Poor application design accounts for over 70% of performance issues
• Do Performance design review early in development
OVERVIEW OF SQL PROCESSING
• The Parser checks both syntax and semantic analysis of SQL statement
• Optimizer determines the most efficient way of producing the result of the query also known as the EXPLAIN PLAN. How best to get the data.
• Oracle Optimizer types ( Cost Based and Rule Based )
– CBO based Optimizer uses cost associated with each execution requires you to analyze objects for statistics
– RULE based Optimizer internal rules ( not encouraged by oracle)
• The SQL Execution Engine operates on the execution plan associated with a SQL statement and then produces the results of the query.
SETTING OPTIMIZER
• SERVER Level by DBA in parameter file (init.ora)
• CLIENT Level SQLPLUS command < alter session set optimizer_mode=choose>
• STATEMENT Level using hints
– a. select /*+RULE */ * from dual ;
– b. select /*+ CHOOSE */ * from dual ;
• Order of Precedence
SERVER->CLIENT->STATEMENT
• Users can set both client and statement
• To use CBO you need to analyze the tables (see Analyze objects)
OPTIMIZER OPERATIONS THAT AFFECT PERFORMANCE
• The Optimizer is the brain behind the process of returning data to user it needs to make the following choices.
• OPTIMIZER APPROACH
• ACCESS PATH
• JOIN ORDER
• JOIN METHOD
• Choice of optimizer approaches
• CBO or RULE
• Choice of Access Paths ( How data is Scanned )
• Use an index if not reading all records ( faster)
• Read or scan all records
• Choice of Join Orders
• Determine which table to join first when you have more than two tables in an SQL
• Choice of Join Methods
• Determine how to join the tables ( Merge, Sort, Hash )
• SQLPLUS ENVIRONMENT LAB
• Log on
• Set timing
• Auto Trace to see plan ( How SQL is processed )
• Set optimizer
• Review Plan
ANALYZE OBJECT STATISTICS
• Statistics describe physical attributes of an object such as
– Number of rows, average space, empty blocks
• All objects need to have statistics to use CBO
• Stored in user_tables and user_indexes
• Not update automatically use analyze
• Table Statistics
– Table Name
– Number of rows
– Average space
– Total number of blocks
– Empty blocks
• Index Statistics
– Index_Name
– Index_Type
– Table_Name
– Distinct_Keys
– Avg_Leaf_Blocks_Per_Key
– Avg_Data_Blocks_Per_Key
ANALYZE OBJECT STATISTICS LAB
• Create Table
• Create Index
• Review tables
• Review indexes
• TABLE TUNING (i)
• A Table in oracle store data
• Resides in a schema within a Table-space
• Contains actual data stored in oracle blocks
• An oracle block is a multiple of the OS block (Ask your DBA)
• Row Chaining (Performance killer)
– A row is too large to fit into on data block so oracle uses more than one chaining them
– Chaining occurs when you try to inset or update
• Row migration (Performance killer)
– There is not enough place in the BLOCK for UPDATES
– Oracle tries to find another Block with enough free space to hold the entire row.( Unnecessary scanning)
– If a free block is available Oracle moves the entire ROW to the NEW BLOCK.
– Oracle keeps the original Row piece of a Migrated row row to POINT to the NEW BLOCK
• Queries that select from chained or migrated rows must perform double read and write (I/O.
• To find Chained or Migrated table run
– SQL> ANALYZE TABLE SCHEMA_NAME.TABLE_NAME LIST CHAINED ROWS;
– SQL> select CHAIN_CNT from user_tables ;
• TABLE TUNING (ii)
• Too many empty blocks
• Occurs after a massive delete then inserting few records
• Select statement takes a very long time with only one record in table
• Solution is to TRUNCATE the table and copy to new table
• TABLE TUNNING LAB
• WHY USE AN INDEX
• What is an Index
– A pointer or a hand that directs to something
– Similar to index at the end of a book
• Oracle Index
– Binary tree Structure with entries know as ROWID
– Left nodes contain key and rowid
– ROWID is internal and points to direct location of record on disk
– ROWID is fasted way to reach a record.
– SQL> Select rowid, id, name from mytable ;
OPTIMIZER ACCESS by ROWID
• ROWID SCAN
– The fastest way to get a row
– Based on the file and the data block where record is located
– Used also during an index scan
OPTIMIZER ACCESS by INDEX UNIQUE SCAN
• The scan returns only one row
• It requires an index (Primary key)on the Table
• Index is automatically created for primary key
• Used by Optimizer
– When an index exist on a column with a where clause
– When the optimizer is told to use an index (hint) Index hints are not really used.
• Reading Explain Plan
– Do a unique scan of the index and obtain ROWID
– Access the table by ROWID
OPTIMIZER ACCESS by INDEX RANGE SCAN
• The scan may return more than one row
• Used by optimizer when
– where clause has > or < sign
– where clause has between 10 and 20
– where clause has like * ( wild card)
OPTIMIZER ACCESS by MULTIPLE UNIQUE SCAN
• Optimizer will search for ROWID in the statement
• Concatenate all records into one row set
– Combining all rows selected by the unique scan into I row set
• Used by Optimizer when
– where clause has an in condition id IN ( 123, 456, 678 )
OPTIMIZER ACCESS by MULTIPLE UNIQUE SCAN
• Multiple Unique Scan
OPTIMIZER ACCESS by FULL TABLE SCAN
• Each record is read one by one
• A sequential search for data no index is used
• The slowest search
• Occurs when
– There is no index or index disabled
– When the Optimizer is hinted not to use the index
OPTIMIZER ACCESS by FAST FULL INDEX SCAN
• Alternative to a full table scan
• Used by optimizer when
– Index contains all the columns that are needed for the query
– If I want to display only your SSN, you don’t have to access the table if I have SSN as an index
• A fast full scan accesses the data in the index itself, without accessing the table
OPTIMIZER JOIN METHOD
• A query with more than one table requires to have a Join Order
• Join Order are steps taken to assemble rows of data from more than one table
• Select From A,B,C Where A.col1 = B.Col1 And B.Col2 = C.Col2
• NESTED LOOP
• SORT-MERGE
• HASH JOIN