场景5 Performance Management

性能调优（不能重启数据库）

索引

资源管理器

性能优化

统计分析

SQL性能分析

SPM (SQL执行计划管理)

堆表：数据存储无序

位图索引：适合字段重复值高的，数据仓库环境下（适合OLAP/DSS环境），存储占用空间少, DML成本高, WHERE中与或操作效率高

eg :

select count(*) from customers where m_status = married and region in (‘central’, ‘west’);

eg :

su -oracle

sqlplus / as sysdba

set linesize 120

set pagesize 20

line on

timing on

sqlprompt ‘_user@_connect_identifier’

start up

alter user scott identified by tiger unlock;

conn scott/tiger

create table t1 (id number, color varchar2, pty number);

begin for i in 1..50000 loop insert into t1 values(1, blue’, 1000); end loop; commt; end; /

select count(*) from t1;

update t1 set color=‘red’ where id < 20001;

update t1 set color=‘green’ where id > 40000;

commit;

select distinct color from t1;

!clear

create tablespace indx datafile ‘/u01/app/oracle/prod/indx01.dbf’ size 100m;

create indext1_color_ind on t1(color) tablespace indx;

analyze table t1 compute statistics; (表分析)

analyze index t1_color_ind compute statistics;

select a.table_name, a.index_name, a.index_type, a.leef_blocks, b.height from user_indexes a.index_stats b where a.index_mane=b.index_name and a.index_name=’T1_COLOR_IND’;

col table_name for a10

col index_name for a10

@?sqlplus/

conn scott/tiger

set autrace trace

select count(*) from t1 where color=‘blue’;

set auto trace off

select * from t1 where id<10;

drop index t1_color_indx;

create bitmap index t1_color_indx on t1(color) tablespace indx;

analyze index t1_color_indx compute statistics;

analyze index t1_color_indx valudate structure;

select a.table_name, a.index_name, a.index_type, a.leef_blocks, b.height from user_indexes a.index_stats b where a.index_mane=b.index_name and a.index_name=’T1_COLOR_IND’;

set autostrade trace

select count(*) from t1 where color=‘red’;

set auto trace off

select * from t1 where id < 10;

update t1 set color = ‘blue’ where id = 1;

update t1 set color = ‘green’ where id = 2;(打开另一个会话)

rollback;

set autotrace trace

select count(*) from t1 where color=‘red’ or color = ‘blue’;

su - oracle

|sql

e.g. : select rowid, ename, sal from emp；

rowid : 6 ：object_id, 3 ：file#， 6 ：block, 3 : row#

复合压缩索引：

全局hash索引：

反向索引：解决热块问题，不适合范围查询

平衡树索引（普通索引）：适合字段基数大，重复值少，DML代价比较低，适合OLTP环境

经常用于select语句中，where语句中，排序的字段适合建立索引

表级共享锁 share lock

行级排他锁 exclusive lock

段级位图锁

eg :

show parameter cpu

show parameter resoure

eg 资源管理器的创建：

官方文档：administrator

SQL 语句执行的三个过程：

parse解析 : 建立执行计划

execute执行 :

fetch取值从buffer cache里获取数据 :

硬解析：三步

软解析：

1. 在lib cache读取执行计划

2. execute plan

3. 从buffer cache读取数据块

LRU : 最近最少使用

eg :

alter system set result_cache_max_size=0;

show parameter result

alter system set result_cache_max_size=1536;(从0改为非0后，必须重启才能生效)

eg :

conn scott/tiger

select /*+ result_cache */ count(*) from emp;

set autotrace trace

show parameter result

alter system set result_cache_mode = force;

select /*+ result_cache */ count(*) from emp;

exec dbms_result_cache.flush;

select dbms_result_cache.status from dual;

shutdown immediate;

startup

select dbms_result_cache.status from dual;

show parameter result

alter system set result_cache_mode=manual;

conn scott/tiger

set autotrace trace

select count(*) from emp where empno=20;

select /*+ result_cache */ count(*) from emp where deptno=20;

exec dbms_result_cache.memory_report;

set serverout on

exec dbms_result_cache.memory_report;

eg :

select * from emp where deptno=:n;

结果缓存（官方文档 Performance Tuning Guide 7.6 Managing the server and client query cache)

统计分析（官方文档 PL/SQL Package and Type Reference 或

SPA (SQL Performance Analyzer)

SQL性能优化分析

SPM(SQL Plan Management)

SQL执行计划管理

store outline 存储纲要

sql profile

hints 提示

稳定执行计划，根据访问对象的变化，实时调整新的计划

spm : sql执行计划管理

sql plan baseline : 执行计划基线(执行计划的集合)

eg :

show parameter optimizer

eg :

select * from tab;

create table emp1 select * from emp;

select * from emp1;

set autotrace on

select * from emp1 where empno=7788;

select sql_text, sql_id from v$sql where sql_text like ‘select * from emp%’; (查询sql id)

set serveroutput on

declare

`1_plans_loaded pls_integer；

begin

1_p;ans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id =>…);

DBMS_OUTPUT.put_line(‘Plans Loaded : || 1_plan_loaded);

end;

select sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines where sql_text like ‘%emp1%’ and sql_id not like ‘%dba%’ ;

create index

set autotrace on

select * from emp1 where empno = 7788;