Oracle 优化-Sql优化

Oracle性能优化方法(SQL篇)

                                      

1         综述

ORACLEl         l         I/O

l         l         l         l         2         如何分析SQL语句

    /ORACLE_HOME/rdbms/admin/utlxplan.sqlSQL>plan_tableSQL           create table PLAN_TABLE (

                  statement_id    varchar2(30),

                  timestamp       date,

                  remarks         varchar2(80),

                  operation       varchar2(30),

                  options         varchar2(30),

                  object_node     varchar2(128),

                  object_owner    varchar2(30),

                  object_name     varchar2(30),

                  object_instance numeric,

                  object_type     varchar2(30),

                  optimizer       varchar2(255),

                  search_columns number,

                  id              numeric,

                  parent_id       numeric,

                  position        numeric,

                  cost            numeric,

                  cardinality     numeric,

                  bytes           numeric,

                  other_tag       varchar2(255),

                  partition_start varchar2(255),

                  partition_stop varchar2(255),

                  partition_id    numeric,

                  other           long,

                  distribution    varchar2(30));

    SQL/PLUS           set autotrace traceonly;         (SQL)

SQLSQL3         选用适合的ORACLE优化器

ORACLE3:

a. RULE () b. COST () c. CHOOSE ()

,init.oraOPTIMIZER_MODE,RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . SQL(session).

(CBO, Cost-Based Optimizer) , analyze ,(object statistics).

(CHOOSE),analyze. tableanalyze, CBO , ,RULE.

,ORACLECHOOSE, (full table scan) , CHOOSE,.

4         用EXPLAIN PLAN 分析SQL语句

EXPLAIN PLAN SQL,SQL. ,ORACLE,().

,. EXPLAIN PLAN, , ,.

TOADORACLEOMSSQL AnalyzeEXPLAIN PLAN..

5         使用TKPROF 工具来查询SQL性能状态

SQL trace SQL. ,.,CPU..

SQL TRACE:

ALTER SESSION SET SQL_TRACE TRUE

timed_statisticsALTER SESSION SET TIMED_STATISTICS=TRUE;

tkprof

trace,TKPROFtkprof tracefile output=outtracefile.out

6         表分区的应用

create table TABLENAME(<field list>)

partition by range (PutOutNo)

(partition PART1 values lessthan (200312319999)

partition PART2 values lessthan (200412319999)

partition PART3 values lessthan (200512319999)

TABLENAME

2003Data

2005Data

2004Data

 

 

 

 

 

 

 

 

 

 

 

 

Oracle

 

 

7         访问Table的方式

ORACLE :

l        

. ORACLE(database block).

l         ROWID

ROWID,, , ROWID..ORACLE(INDEX)(ROWID). ROWID,.

8         共享SQL语句

SQL,, ORACLESQL.SGA(system global area)(shared buffer pool). ,SQL(),, ORACLE. ORACLESQL.ORACLE(cache buffering) ,.

init.ora,,,.

ORACLE SQL,ORACLE.,ORACLE,,SQL(,).

:

l         :

.:

SELECT * FROM EMP;

SELECT * from EMP;

Select * From Emp;

SELECT * FROM EMP;

l         :

:

Jack sal_limit private synonym

Work_city public synonym

Plant_detail public synonym

Jill sal_limit private synonym

Work_city public synonym

Plant_detail table owner

SQL.

select max(sal_cap) from sal_limit;

private synonym - sal_limit ,

SQL.

select count(*) from work_city where sdesc like 'NEW%';

public synonym - work_city

SQL.

select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id

jack private synonymplant_detail jill ,.

l         SQL(bind variables)

SQL(),(,)

1.        

 

 

select pin , name from people where pin = :blk1.pin;

select pin , name from people where pin = :blk1.pin;

2.        

 

 

select pin , name from people where pin = :blk1.ot_ind;

select pin , name from people where pin = :blk1.ov_ind;

9         选择最有效率的表名顺序

ORACLEFROM,FROM( driving table). FROM,.ORACLE, .,(FROM),(FROM),.

: TAB1 16,384 , TAB2 1

TAB2 ()

select count(*) from tab1,tab2

TAB1 ()

select count(*) from tab2,tab1

3, (intersection table), .

: EMPLOCATIONCATEGORY.

SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN

SQL

SELECT * FROM EMP E , LOCATION L , CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000

10   WHERE子句中的连接顺序．

ORACLEWHERE,,WHERE, WHERE.

:

()

SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);

()

SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’;