Oracle性能优化方法

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);
EXPLAIN PLAN SQL,SQL. ,ORACLE,().
,. EXPLAIN PLAN, , ,.
TOADORACLEOMSSQL AnalyzeEXPLAIN PLAN..
5 使用TKPROF 工具来查询SQL性能状态
SQL trace SQL. ,.,CPU..
partition PART3 values lessthan (200512319999)
TABLENAME
2003Data
2005Data
2004Data
Oracle
7 访问Table的方式
ORACLE :
()
SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’;
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) ,.
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
6 表分区的应用
create table TABLENAME(<field list>)
partition by range (PutOutNo)
(partition PART1 values lessthan (200312319999)
partition PART2 values lessthan (200412319999)
2.
select pin , name from people where pin = :blk1.ot_ind;
select pin , name from people where pin = :blk1.ov_ind;
9 选择最有效率的表名顺序
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30));
1 综述
ORACLEl l I/O
l l l l 2 如何分析SQL语句
/ORACLE_HOME/rdbms/admin/utlxplan.sqlSQL>plan_tableSQL create table PLAN_TABLE (
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
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).
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
(CBO, Cost-Based Optimizer) , analyze ,(object statistics).
(CHOOSE),analyze. tableanalyze, CBO , ,RULE.
,ORACLECHOOSE, (full table scan) , CHOOSE,.
4 用EXPLAIN PLAN 分析SQL语句
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 ,.
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
options varchar2(30),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
init.ora,,,.
ORACLE SQL,ORACLE.,ORACLE,,SQL(,).
:
l :
.:
SELECT * FROM EMP;
SELECT * from EMP;
Select * From Emp;
SELECT * FROM EMP;
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
l :
:
Jack sal_limit private synonym
Work_city public synonym
Plant_detail public synonym
Jill sal_limit private synonym
Work_city public synonym
l SQL
(bind variables)
SQL(),(,)
1.
select pin , name from people where pin = :blk1.pin;
select pin , name from people where pin = :blk1.pin;
SQL TRACE:
ALTER SEStistics
ALTER SESSION SET TIMED_STATISTICS=TRUE;
tkprof
trace,TKPROFtkprof tracefile output=outtracefile.out
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%'';