第一章 性能调整简介
====重要的基准点统计数据：
*客户PC CPU和内存利用率
*网络通信效率
*服务器CPU与内存利用率
*服务器磁盘I/O和控制器活动率
*ORACLE实例内存利用率
*ORACLE数据库I/O活动率
====常见的调整问题：
*编写拙劣的应用SQL
*效率差的SQL执行计划
*大小不合适的SGA内存结构
*过度的文件I/O
*访问数据资源的等待
效率差SQL会导致执行低效率的执行计划；
低效率的执行计划会导致过度的I/O；
过度的I/O会降低各SGA内存结构的效率;
进而导致过度的I/O和其他对数据库资源的激烈争用。
---------------------------------------------------------------------------
第二章 调整信息的来源
====alter 日志
*由background_dump_dest参数指定
====后台进程跟踪文件
*由background_dump_dest参数指定
====事件跟踪文件
*由background_dump_dest参数指定
*数据库事件就是数据库中发生的一个具体动作或操作
*默认大部分事件不会引起跟踪文件创建，但是可以在init.ora中設置，例如：
EVENT="10046 trace name context forever, level 12"
每个事件项由两个组件组成：
-- 要跟踪的事件，由一个号码(10046)来指定
-- 执行跟踪的级别，由号码(12)来指定
====用户跟踪文件
*由USER_DUMP_DEST指定
====激活用户跟踪
当用户的server process中发生错误处理时，ORACLE时常会创建一个用户跟踪文件。
*实例级跟踪
--设置SQL_TRACE=TRUE,所有进程讲创建他们自己的跟踪文件，开销很大影响性能，默认是FALSE
*用户级自跟踪
--在用户自己的会话中打开或关闭SQL跟踪
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET SQL_TRACE=FALSE;
*用户级DBA跟踪
--使用SET_SQL_TRACE_IN_SESSION
--实例:
1. 通過v$session 获取此会话SID和SERIAL#：
select username,sid,serial# from v$session where username='&name';
2.使用DBMS_SYSTEM PL/SQL程序包，激活次会话跟踪：
exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,TRUE);
3.跟踪结束，可以使用以下命令禁止：
exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,FALSE);
*翻译用户跟踪文件输出结果
-- 使用TKPROF
*管理跟踪文件
--如果跟踪持续时间长，或导致文件非常大，可能会填满整个磁盘位置；
--指定MAX_DUMP_FILE_SIZE限制跟踪文件大小
MAX_DUMP_FILE_SIZE=10000 10000个OS数据块
MAX_DUMP_FILE_SIZE=500k 500000字节
MAX_DUMP_FILE_SIZE=10M 10M字节
MAX_DUMP_FILE_SIZE=unlimited 无限制
==== 性能调整有关的V$视图
V$SGASTAT
V$EVENT_NAME
V$SYSTEM_EVENT
V$SESSION_EVENT
V$SESSION_WAIT
V$STATNAME
V$SYSSTAT
V$SESSTAT
V$SESSION
V$WAITSTAT
==== 性能调整有关的DBA视图
DBA_TABLES 表存储、行和块信息
DBA_INDEXS 索引存储、行和块信息
INDEX_STATS 索引深度和分散信息
DBA_DATA_FILES 数据文件位置、命名和长度信息
DBA_SEGMENTS 数据库中段空间使用情况的常规信息
DBA_HISTOGRAMS 表与索引直方图定义信息
--> DBA视图中的有些列只有在使用ANALYZE TABLE或ANALYZE INDEX分析了视图所引用的表或索引后才会含有数据
====配置STATSPACK
*安裝STATSPACK
--$ORACLE_HOME/rdbms/admin/spcreate.sql
執行期間會提示輸入分配給PERFSTAT用戶的默认和临时表空间名称，为了更好的管理，应该新建一个只用来保存PERFSTAT段的250MB的表空间；
note:如果在执行期间出现故障，可以使用SPDROP.SQL删除已经创建的PERFSTAT模式，然后再执行SPCREATE.SQL
*手工执行STATSPACK(先conn perfstat/perfstat)
execute statspack.snap
*自動執行STATSPACK
---使用SPAUTO.SQL(默认每小时运行一次)設置快照自动收集的时间。
-->note:參數job_queue_processes必须设置为非零
---如果需要修改运行频率，而已修改SPAUTO.SQL脚本再重新执行：
*生成STATSPACK报告
--@$ORACLE_HOME/rdbms/admin/spreport.sql
---------------------------------------------------------------------------
第三章 SQL应用调整与设计
====测量SQL性能
5个最常用的工具：TKPROF,TOP SQL,EXPLAIN PLAN,AUTOTRACE,STATSPACK
*TKPROF
-->用来格式化用户会话生成的用户跟踪文件。
-->用户跟踪文件包含用户在跟踪处于活动期间所发布的全部SQL，和每条SQL花费的时间及占用资源数量。
使用方法： tkprof ora_1253.trc trace.txt
$ tkprof azdb2_ora_8345.trc trace.txt3 print=2 record=sql.txt
-->格式化跟踪文件azdb2_ora_8345.trc，在trace.txt3中打印前2条SQL语句的信息，所有的SQL都存储在SQL.TXT文件中。
$ tkprof azdb2_ora_8345.trc tarce.txt sort=(fchrow) sys=no
--> 格式化后的SQL按照fchrow(该语句所取出的行数)排序
SYS=NO，不显示递归语句(针对数据目录发布的)，建议使用。以便只保留用户发布的SQL
输出结果描述：
********************************************************************************
select pid
from
v$process
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 60 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 0 0 41
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 60 0 41
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
41 FIXED TABLE FULL X$KSUPR (cr=0 pr=0 pw=0 time=14 us)
********************************************************************************
count:parse,execute,fetch阶段所发生的次数；
cpu:cpu处理该语句中的全部parse,execute,fetch调用所花费的总秒数；
elapsed:处理该语句中全部PARSE,EXECUTE,FETCH调用所花费的总秒数；
disk:在该语句中的PARSE,EXECUTE,FETCH调用期间从磁盘上读取的数据块的总数量；
query:在该语句中的PARSE,EXECUTE,FETCH调用期间从SGA中读取的回退块(保证一致性中的块)數量；
current:在该语句中的PARSE,EXECUTE,FETCH调用期间从SGA中读取的回退块(当前模式中的块)數量；
rows:影响的行数；
找出可能需要调整的语句
-->占用过多CPU资源的语句
-->PARSE,EXECUTE和FETCH阶段中花费太长时间的语句
-->从磁盘上读取太多的数据块，而从SGA中读取太少的数据块的语句
-->访问许多数据块，但只返回几行的语句；
*EXPLAIN PLAN FOR 生成EXPLAIN PLAN
找出一条执行效率差的SQL语句之后，就可以使用EXPLAIN PLAN FOR命令来生成该语句的一个EXPLAIN PLAN。
该命令本身不执行，只有它的执行计划得到生成。
步驟：
1、创建PLAN_TABLE表：
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
2、使用EXPLAIN PLAN FOR命令給PLAN_TABLE表填充SQL语句的执行计划。
SQL> explain plan for
2 select nvl(max(al.recid),0) into :b1
3 from v$archived_log al
4 where ((al.status in ('X','A') and al.is_recovery_dest_file='YES') and al.creator='RMAN');
Explained.
3、显示执行计划
SQL> select * from table(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 157323055
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 52 | | |
|* 2 | FIXED TABLE FULL| X$KCCAL | 1 | 52 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("INST_ID"=USERENV('INSTANCE') AND
DECODE(BITAND("ALFL2",64),0,'NO','YES')='YES' AND
DECODE(BITAND("ALFLG",496),16,'ARCH',32,'FGRD',64,'RMAN',128,'SRMN',256,
'LGWR','UNKNOWN')='RMAN' AND (DECODE(BITAND("ALFLG",6145),0,'A',1,'D',20
48,'X',4096,'U','?')='X' OR DECODE(BITAND("ALFLG",6145),0,'A',1,'D',2048
,'X',4096,'U','?')='A'))
19 rows selected.
*AUTOTRACE
使用AUTOTRACE在生成一个执行计划之前就先实际执行正在被检查的那条语句
*STATSPACK
--sql ordered by gets
按具有最高GET數據量的SQL排序(在SGA中查找一条SQL高速缓存叫GET)
--sql ordered by reads
按引起最高读操作个数的SQL排序
--sql ordered by executions
按执行最频繁的SQL排序
--sql executions ordered by parse calls
按被语法分析最频繁的SQL排序
*RBO
*CBO
CBO主要依赖数据库中表和索引的相关统计数据，这些数据存储在数据目录中，而数据目录包含以下内容：
每个表或索引的大小；
每个表或索引所含有的行数；
每个表或索引所使用的数据库块数量；
每个表行的长度；
索引列中列数据的基数(高基数：可选值多，低基数:可选值少)
分析单个表和索引：
sql> analyze index employee_last_name_idx compute statistics; -->收集employee_last_name_idx 索引的统计数据
sql> analyze table employee compute statistics;-->收集employee的统计数据及所关联的所有索引的统计数据
sql> analyze table employee estimate statistics;-->大表可以使用estimate 代替compute
按照OWNER來收集：
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('JOE','EMPLOYEE');
GATHER_INDEX_STATS 用來收集指定索引的统计数据；
GATHER_SCHEMA_STATS 用来收集指定模式的统计数据；
GATHER_DATABASE_STATS 用来收集整个数据库的统计数据；
GATHER_SYSTEM_STATS 用来收集服务器的I/O和CPU性能统计数据；
查看表和索引相关数据：
select table_name,num_rows,blocks,empty_blocks,avg_row_len,chain_cnt
from dba_tables
where owner='AITSFIS1';
exec dbms_stats.gather_schema_stats('RMADP');
====索引
B树索引
压缩B树索引
位图索引
基于函数索引
Revers key index(RKI,反向键索引)
Index Organized Table(IOT,索引组织表)
*B树索引：
最常见，按升序对表列数据进行排序；
每行保存被排序的列数据和ROWID(ROWID指出该行其他数据的位置)；
建议查询经常返回表中5%不到的行时，应建立索引策略;
B树索引适合高基数的表列;
随着表INSERT和DELETE，表所关联的索引会产生许多级，会降低B树索引效率;
查看索引有多深(多少級)
SQL> select index_name,blevel
from dba_indexes
where blevel >=4;
note:上述SQL显示根块到最深页之间有4或更多级的所有索引
重建索引：
优先考虑：1.达到级别4以上(4以上会降低性能)
2.该索引中被删除项占了总项的20%以上(这种情况索引占的空间比他存储的空间还要多，加大了检索某一个项所须访问的块数)
查找索引浪費的空間：
--VALIDATE STRUCTURE选项给INDEX_STATS数据目录视图填充值：
SQL> ANALYZE INDEX RMADP.IDX_R_WIP_ID VALIDATE STRUCTURE;
--查看该索引被浪费的空间：
SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 "Wasted Space"
FROM index_stats
where name='IDX_R_WIP_ID';
方法：
1.删除然后重新建立该索引； -->最耗资源
2.使用ALTER INDEX ... REBUILD -->创建的时候新旧索引都在，所以索引在创建过程中能使用，这种方法需要有额外的磁盘空间供临时用，该命令还能将索引转至新的表空间；
SQL> ALTER INDEX indexname REBUILD ONLINE;
3.使用ALTER INDEX ... COALESCE
SQL> ALTER INDEX indexname COALESCE; -->COALESCE不要求額外的磁盤空間，只是将处于同一个索引分支内的叶块合并，但是不能转移索引到新的表空间；
*压缩B树索引：
基于大表
基于数据仓库,决策支持系统
最大限度介绍某类B树索引所使用的存储空间
被索引列值的重复值被消除
创建：
1： SQL> CREATE INDEX employee_hist_last_name_idx
ON employee_history (last_name)
TABLESPACE APP_IDX
COMPRESS;
2:将未压缩修改为压缩：
SQL> ALTER INDEX employee_hist_last_name_idx REBUILD COMPRESS;
note:不能在含有唯一性值的单个表列上创建压缩B树索引，因为没有两个索引值是相同的；
*位图索引
适用于低基数的列；
不适合在OLTP和RAC环境使用；
不应该用在频繁INSERT,UPDATE,DELETE操作的表上；
适合数据仓库和决策支持系统；
使用位图索引，考虑增加
SORT_AREA_SIZE:已排序位图列和行ID信息在成批提交前就存储在该缓冲区
PGA_AGGREGATE_TARGET:按照索引范围扫描来管理已分配给位图索引创建和位图合并操作的内存量
*基于函数的索引
B树和位图索引都能被创建为基于函数的索引；
SQL> CREATE INDEX employee_first_name_upper_idx
ON employee (UPPER(first_name));
SQL> CREATE INDEX sales_total_sale_idx
ON sales (price*units)
TABLESPACE appl_idx;
note:query_rewrite_enabled=TRUE才能使用函数索引
*反向键索引(RKI,Reverse Key Index)
在基于含有序数的列时非常有用
SQL> CREATE INDEX employee_emp_id_idx
ON employee (emp_id)
REVERSE
TABLESPACE APPL_IDX;
或：ALTER INDEX employee_emp_id_idx REBUILD REVERSE;
note:RKI只适合相等和不相等搜索，如果使用BETWEEN、>、<的查询无法使用该索引
*索引组织表(Index Organized Table IOT)
什么叫heap table堆表？
适合按顺序存储的表列
IOT不是存储一个指向行数据的ID指针，而是把全部数据存储在索引本身内，这样的好处：
1.表行按索引顺序存储，如果用表的主键访问表，IOT比传统表更快返回行；
2.使用B数索引时先读取索引后读取表所使用的额外I/O操作得到消除；
SQL> CREATE TABLE employee_history
(employee_id number primary key,
last_name varchar2(20),
first_name varchar2(20),
title varchar2(30),
hire_date date,
departure_date date)
ORGANIZATION INDEX TABLESPACE appl_idx -->ORGANIZATION INDEX指定该表是一个IOT
PCTTHRESHOLD 25 -->在索引表块中指定百分比用来存储主键的行数据，必须在0-50间(默認50)
INCLUDING first_name -->指定行长度超过PCTTHRESHOLD设置大小时按哪一列截成两段
OVERFLOW TABLESPACE appl_of -->指定被截断的另一半存储的表空间
MAPPING TABLE; --> 致使在创建IOT上的位图索引时所必须的一个关联映像表的创建
nots:IOT在将要做索引基础的那一列上必须有一个主键约束。IOT不能含有唯一性约束或被聚簇
位图索引能以heap table或IOT为基础表。
--以heap table为基础表，索引除了存储位图，还存储在heap table中定位行的物理行ID。
--以IOT为基础表，位图索引必须利用一个MAPPING TABLE来定位被索引的IOT行。
详见<> 105页
--时间长后，位图索引可能不能准确反映被索引IOT中逻辑与物理ID的真正映像，降低索引效力
通过以下SQL查看映像表与位图索引不一致的范围：
SQL> SELECT index_name,pct_direct_access
FROM dba_indexes
WHERE pct_direct_access IS NOT NULL;
IOT表的pct_direct_access 必须是非空。其值超出30%的任一索引都应该让它的位图索引重建；
？：
--查询表是否IOT
SQL> select OWNER,TABLE_NAME,IOT_NAME,IOT_TYPE from dba_tables where IOT_TYPE='IOT';
=====找出未使用过的索引
--激活对索引的监视:
ALTER INDEX RMADP.WO_TYPE_ID MONITORING USAGE;
--禁用索引监视:
ALTER INDEX RMADP.WO_TYPE_ID NOMONITORING USAGE;
--查看索引使用情況：
select index_name,used from v$object_usage; used列yes表示监视过程中至少一次访问，no表示未被访问过，可能需要删除,需要使用owner用戶才能查詢，如果是DBA用戶使用下面语句：
col INDXNAME format a40;
col TABNAME format a20;
col name format a20;
col MONITORING format a10;
col USED format a10;
select z.name||'.'||io.name as "INDXNAME",t.name as "TABNAME",
decode(bitand(i.flags, 65536), 0, 'NO', 'YES') as "MONITORING",
decode(bitand(ou.flags, 1), 0, 'NO', 'YES') as "USED",
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou ,sys.user$ z
Where i.obj# = ou.obj#
and io.obj# = ou.obj# And io.owner#=z.user#
and t.obj# = i.bo#
Order By 4 Desc,2 Desc;
=== 分区
类型：
范围分区
列表分区
散列分区
组合分区
示例student_history：
student_id 学生标识号
degree 学位的3字符缩写：BS.BA.BBA.BFA.MA.MBA.MFA.MS.PHD
graduation_date 学生毕业日期
final_gpa 学生毕业时的最终平均等级分
====按表类型来比较物理存储
堆表：数据按照表块的填充顺序进行存储。随着表块进出表的free list,行的存储顺序本质上变成随机的。
聚簇索引：相关行按相关分组的形式，围绕着聚簇键值进行同时存储。在分组之间，数据顺序无任何保证。
索引组织表：数据严格地按索引顺序进行存储。
==== 调整OLTP系统
OLTP主要关心的是吞吐量：定购某个货物、删除一件物品或安排一个约会所花费的总时间量。
OLTP需要足够的B树和反向键索引来满足性能目标，但太多也会影响性能。
位图索引不适合OLTP，因为它会引起加锁问题。
CBO，应定期收集表和索引统计数据。
OLTP索引的表中的数据经常修改，它们也应该重建。
==== 调整DSS系统
DSS关心的是响应时间：他们的查询中获取结果所花费的时间。
DSS频繁利用全表扫描，因此索引和散列聚簇的使用非常重要。
IOT对大型DSSl也很重要。
位图索引在列值具有低基数但经常用作查询基础的地方可以考虑。
直方图的使用可以帮助改进DSS响应时间。
阅读(4534) | 评论(0) | 转发(0) |
