SQL Tuning Proven Methodologies

(1)

SQL Tuning Proven

Methodologies

V.Hariharaputhran

(2)

V.Hariharaputhran

(3)

Our 60 minutes Agenda

•

Objective

•

Lifecycle of a SQL

•

Cursors – Why and What

•

Get the right Execution Plan

•

Case Studies

–

Push predicate vs. No-Push

–

Join Elimination

–

Correlated sub-query

(4)

Until Yesterday it was running Fine

What happened now ?

(5)

Typical Day

•

Options we see

1. Why not Cache Table

2. There is Memory Problem / No partition hence you

are bound to run slow

3. I learnt new features and desperate to implement

them

4. Rewrite the SQL / Add Index /

5. SQL running slow – Found the init parameter causing

it, Let me change it.

6. I see no Histograms / must be a stats problem

7. I came across a tool, you just have to provide the

(6)

Out of the BOX

•

Recreate the Table

•

Avoid writing the SQL / Change the Design

(7)

SQL Lifecycle

•

Parse

•

Bind

•

Execute

(8)

Some more Parse

PARSE

So what is before the syntax check

Adding VPD Predicates to SQL

(9)

VPD

(10)

(11)

More about Parsing

•

Syntax Check - Keywords

•

Semantic Check - Objects Accessible

•

View Merging/Subquery Unnesting

•

Views to Base tables / Inline Views

•

Query Transformation

•

Transitivity

•

Optimization

Soft Parse

(12)

Cursor

SELECT * FROM EMP

ENV

STATS

BIND

VARIABLES

SQL TEXT

(13)

(14)

(15)

(16)

(17)

declare

v_ename emp.ename%type; begin

For x in 1..10000 loop select /* NORMAL */ ename into v_ename from emp where empno =

x

; end loop; end; / declare v_ename emp.ename%type; v_sql varchar2(200); begin For x in 1..10000 loop

execute immediate 'select /* bind */ ename from emp where empno =

:v_empno

' into v_ename using x ;

(18)

(19)

(20)

(21)

(22)

Autotrace

(23)

Explain Plan

•

Not the Real Plan, Just an estimate

•

Treat all Bind Variable as Varchar2 Datatype

(24)

DBMS_XPLAN

•

DBMS_XPLAN.display - Estimated

•

DBMS_XPLAN.display_cursor –

Real from

Memory

(25)

(26)

SQL> select * from tblchk tbl1 where exists

2 (select 1 from tblchk tbl2 where tbl1.id=tbl2.id and tbl2.id_val='AIOUG');

no rows selected

SQL> select * from table(dbms_xplan.display_cursor());

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --- --- | 0 | SELECT STATEMENT | | | | 1 (100)| | |* 1 | FILTER | | | | | | |* 2 | HASH JOIN RIGHT SEMI| | 190K | 2226K| 207 (2) | 00:00:01 | |* 3 | TABLE ACCESS FULL | TBLCHK | 36663| 214K | 103 (1)| 00:00:01 | | 4 | TABLE ACCESS FULL | TBLCHK | 220K | 1289K | 103 (1)| 00:00:01 | ---

Predicate Information (identified by operation id): ---

(27)

God – 10046+TKprof

call count cpu elapsed disk query current rows --- --- --- --- --- --- --- --- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.02 0.12 483 487 0 0 --- --- --- --- --- --- --- --- total 3 0.03 0.14 483 487 0 0

Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS

Parsing user id: 143

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation --- --- --- ---

0 0 0 HASH JOIN RIGHT SEMI (cr=487 pr=483 pw=0 time=128029 us cost=207 size=2280096 card=190008) 0 0 0 TABLE ACCESS FULL TBLCHK (cr=487 pr=483 pw=0 time=127573 us cost=103 size=219978 card=36663) 0 0 0 TABLE ACCESS FULL TBLCHK (cr=0 pr=0 pw=0 time=0 us cost=103 size=1320000 card=220000)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited --- Waited --- --- SQL*Net message to client 1 0.00 0.00

(28)

Add the Check Constraint

alter table tblchk add CONSTRAINT check_idval CHECK

(id_val in ('A','B','C'));

SQL> select distinct id_val from tblchk;

ID_VAL --- A

B C

(29)

After Constraint 10046+TKprof

call count cpu elapsed disk query current rows --- --- --- --- --- --- --- --- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 0 0 0 --- --- --- --- --- --- --- --- total 3 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS Parsing user id: 143

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation --- --- --- --- 0 0 0 FILTER (cr=0 pr=0 pw=0 time=7 us)

0 0 0 HASH JOIN RIGHT SEMI (cr=0 pr=0 pw=0 time=0 us cost=276 size=3094080 card=257840) 0 0 0 TABLE ACCESS FULL TBLCHK (cr=0 pr=0 pw=0 time=0 us cost=138 size=279972 card=46662) 0 0 0 TABLE ACCESS FULL TBLCHK (cr=0 pr=0 pw=0 time=0 us cost=137 size=1680000 card=280000)

(30)

Rows = Num_Rows /

No of Distinct records in ID_VAL

DBMS_XPLAN – ALLSTATS LAST

(31)

Explain Plan

•

Access Method – Table Scan / Index

•

Join Method – NL/Hash/Sort Merge

•

Join Order – Order in which tables are joined

•

Join Type – Anti / Semi / Outer

•

Partition Pruning – What partitions are used

•

Parallel Execution

•

Predicate / Column Projections

(32)

Full table Scan

•

Majority Rows + Columns

•

Scans up to HWM

•

Multi block Reads

(33)

(34)

Index Fast Full Scan

(35)

•

Not Going to Table

(36)

(37)

Transform SQL to semantically

equivalent SQL - WHY

•

Processed more efficiently

•

Better Plan.

•

I wrote this way because it easy for me, I can

develop the application Faster

(38)

(39)

View Merging

CREATE VIEW V_EMP_SAL AS

SELECT * FROM EMP

WHERE SAL>100;

SELECT EMP_ID FROM

V_EMP_SAL

WHERE

DEPTNO = 10;

SELECT * FROM EMP

WHERE SAL>100

AND DEPTNO = 10;

•

ROWNUM PSEUDOCOLUMN

•

SET OPERATORS –

UNION

UNION ALL

INTERSECT

MINUS

•

CONNECT BY CLAUSE

(40)

(41)

Transitivity

SELECT T1.COL1, T2.COL2

FROM TBL1 T1, TBL2 T2

WHERE T1.COL1 = T2.COL1

AND T1.COL1 = 100;

SELECT T1.COL1, T2.COL2

FROM TBL1 T1, TBL2 T2

WHERE T1.COL1 = T2.COL1

AND T1.COL1 = 100

(42)

(43)

SELECT E.EMPNO, E.DEPTNO

FROM EMP E

WHERE E.DEPTNO

IN

(SELECT D.DEPTNO

FROM DEPT D

WHERE D.LOC = 'CHICAGO')

SELECT E.EMPNO EMPNO,E.DEPTNO

DEPTNO

FROM HARI.EMP E

WHERE E.DEPTNO

=ANY

(SELECT D.DEPTNO

DEPTNO

FROM HARI.DEPT D WHERE

D.LOC='CHICAGO')

SELECT E.EMPNO EMPNO,E.DEPTNO DEPTNO

FROM HARI.DEPT D,HARI.EMP E

WHERE E.DEPTNO=D.DEPTNO

E.DEPTNO=D.DEPTNO

(44)

(45)

SELECT D.DNAME, D.DEPTNO,VW.DEPT_AVG_SAL

FROM DEPT D,

VW_AVG_SAL

VW

WHERE D.DEPTNO =

VW

.DEPTNO

AND D.LOC = 'CHICAGO';

SELECT D.DNAME DNAME,D.DEPTNO DEPTNO, _{VW.DEPT_AVG_SAL DEPT_AVG_SAL}

FROM HARI.DEPT D, (

SELECT EMP.DEPTNO DEPTNO,

AVG(EMP.SAL) DEPT_AVG_SAL FROM HARI.EMP EMP

GROUP BY EMP.DEPTNO

) VW

WHERE D.DEPTNO=VW.DEPTNO AND D.LOC='CHICAGO'

SELECT D.DNAME,D.DEPTNO,

AVG(EMP.SAL) DEPT_AVG_SAL

FROM HARI.DEPT D,HARI.EMP EMP

WHERE D.DEPTNO=EMP.DEPTNO

(46)

(47)

CREATE OR REPLACE VIEW V_EMP_UNION AS SELECT ENAME, EMPNO

FROM EMP E1

WHERE DEPTNO <20 UNION

SELECT ENAME, EMPNO FROM EMP E2 WHERE DEPTNO >30;

SELECT * FROM

VW_EMP_UNION

WHERE

EMPNO > 7788

SELECT ENAME, EMPNO FROM EMP E1

WHERE DEPTNO <20 UNION

SELECT ENAME, EMPNO FROM EMP E2

WHERE DEPTNO >30;

SELECT * FROM (

SELECT ENAME, EMPNO FROM EMP E1

WHERE DEPTNO <20 UNION

SELECT ENAME, EMPNO FROM EMP E2 WHERE DEPTNO >30 ) AND EMPNO > 7788 AND EMPNO > 7788 ;

(48)

Do You Know (DYK)

•

How many row source can be joined together

at a time ?

•

In a Full Table Scan up to what point oracle

scans all the blocks in a table ?

•

Hash or Sort-Merge which is more efficient in

general ?

TWO

HWM

(49)

(50)

(51)

(52)

Join Type

•

Nested Loop

–

Retrieves row from one source and find its

corresponding one from another source

•

Hash Join

–

Build Hash Table in memory for smaller row

source –

–

Go through the other source and uses Hash

function on Join Columns

(53)

Do You Know (DYK)

•

select empno from emp

where empno like '11';

•

select * from dept where loc

like '%CHENNAI';

•

This ______ operator can be

used instead OR clause.

•

____,Even after having

DISTINCT clause in the query,

SORT UNIQUE is not done by

How will my predicate info look like ?

(54)

(55)

In Action

•

Correlated Subquery

SELECT *

FROM TAB1

WHERE EXISTS

(SELECT 1

FROM TAB2

(56)

(57)

(58)

(59)

(60)

(61)

(62)

Approach of Datatype Change now

- Impacts the Existing Code

Don’t Forget the Learning

TO_Number Function

(63)

Correlated Subquery is

the problem, do you

know how it works ?

You should not use Correlated subquery,

rewrite it with NOT IN … OK ?

(64)

(65)

(66)

(67)

(68)

NOT EXISTS

(69)

NOT IN

(70)

NOT EXISTS

NOT IN

ORDERS ********

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for ORDERS[O]

Table: ORDERS Alias: O

Access Path: TableScan

Cost: 1074.74

Access Path: index (FullScan) Index: PK_ORDER

Cost: 2096.64

Best:: AccessPath: TableScan Cost: 1074.74

ORDERS ********

Single Table Cardinality Estimation for ORDERS[O]

Table: ORDERS Alias: O Access Path: TableScan Cost: 1075.36

Access Path: index (FFS) Cost: 777.95

index filter:"O"."BOOK_ID" IS NOT NULL

Best:: AccessPath: IndexFFS Index: IDX_ORDERS1

(71)

(72)

Back to Basics

•

multiple blocks from disk in a

single I/O

•

Cost = No of blocks to be scanned

+ MBRC value

•

Reads the index blocks in no

particular order

•

Accesses the data in the index

itself, without accessing the table

Fast Full Index Scan

(73)

Fast Full Index Scan

Accesses the data in the index itself,

without accessing the table

(74)

(75)

Can I do it even better

•

Enforce referential Integrity

•

Mark Columns as NOT NULL

•

Index the Foreign Key Column

Yes, Why not at the

Design Stage

(76)

I have a CTD

(77)

(78)

Recap

•

View Merging/Subquery Unnesting

•

Views to Base tables / Inline Views

•

Query Transformation

•

Transitivity

•

Optimization

(79)

Until Yesterday it was running Fine

What happened now ?

DBA

Developer

Start Comparing Old Plan and New Plan

Analyze the Data and understand what has

changed

(80)

References

•

www.asktom.oracle.com ( NOT IN with NULL

Values)

•

Gaja Krishna Vaidyanatha – CTD,TKPROF

Options - Exadata Performance Mgmt –

AIOUG Chennai Chapter - 2015

(81)

:

www.asktom.oracle.com