Your Performance Toolkit 读书笔记 -part1

Your Performance Toolkit
Effective Oracle By Design chapter2读书笔记
使用SQLPLUS的好处
1.AUTOTRACE功能;
2.作为脚本池;


glogin.sql script是sqlplus打开时，会先运行的脚本，用于设置sqlplus的环境。


范例：


set termout off
define _editor=vi
set serveroutput on size 1000000 format wrapped
column object_name format a30
column segment_name format a30
column file_name format a40
column name format a30
column file_name format a30
column what format a30 word_wrapped
column plan_plus_exp format a100
set trimspool on
set long 5000
set linesize 131
set pagesize 9999
set termout on


Use@CONNECT
当sqlplus打开以后，如果你reconnect，login.sql所改变的环境就失效了，比如：
sqlplus /
show serveroutput
显示serveroutput ON size 1000000 format WRAPPED
connect /
show serveroutput
显示serveroutput OFF


那怎么解决这个问题呢？用@CONNECT来代替CONNECT
@connect /
@CONNECT是一个再运行一次login.sql的简单脚本。
如果要用dba权限登录，则@connect "/ as sysdba"


Use SQLPATH
SQLPATH是一个环境变量，用来告诉sqlplus去哪找scripts。你可以先将诸如login.sql,connect.sql,其他


script放到一个单独的目录中，再将SQLPATH变量指向这个目录。SQLPLUS运行脚本的次序首先是当前目录，然


后才是到SQLPATH指定的目录去寻找。这个变量可以用在各种支持环境变量的平台，诸如


unix,linux,Macintosh,Windows)


EXPLAIN PLAN
Setup for EXPLAIN PLAN involves serveral scripts in $ORACLE_HOME/rdbms/admin:


utlxplan.sql:有创建plan_table的DDL语句在里面，plan_table是存放用explain plan命令后得到的执行计划


的地方。


utlxplp.sql:用于并行查询后（parallel-query)，展示plan_table的执行计划的脚本.


utlxpls.sql：串行查询后（serial-query)，展示plan_table中执行计划的脚本。
@?/rdbms/admin/utlxpls


9i后，有个叫DBMS_XPLAN的包，用于查询plan_table中得执行计划更好用。
select * from table(dbms_xplan.display);


explain plan的语法
explain plan
[set statement_id = 'text']
[into [owner.]table_name]
for SqlStatement;
The text in brackets is optional.statement_id允许你在plan_table中存储多个执行计划。


owner.table_name允许你用别的表来代替plan_table;


用EXPLAIN PLAN只能查询一个给定的SQL语句的执行计划，但是不能查询一个在实际环境当中运行的SQL语句的


执行计划。借助V$SQL_PLAN视图，就能捕捉到在实际运行过程中的执行计划，这要配合上打开tracing功能与用


到TKPROF工具。后面会介绍。


AUTOTRACE
SQL*PLUS的一个好用的特性。AUTOTRACE可以让所有开发人员都能用，而TKPROF要用到TRACE文件，这


未必在所有环境都能顺利使用。


Set Up AUTOTRACE
1.Issue cd $ORACLE_HOME/rdbms/admin.
2.Log in to SQL*Plus as someone with CREATE TABLE and CREATE PUBLIC SYNONYM privileges


(for example,as a DBA).
3.Make PLAN_TABLE universally available（普遍通用），as described earlier in the section 


about EXPLAIN PLAN.
4.Exit SQL*Plus and issue cd $ORACLE_HOME/sqlplus/admin.
5.Log in to SQL*Plus as SYSDBA(sqlplus "/as sysdba").
6.Run SQL>@plustrace.
7.Run SQL>grant plustrace to public.


这样，大家就都能用AUTOTRACE了。


Use AUTOTRACE
在DML语句后，如INSERT,DELETE,UPDATE,SELECT,MERGE后，会有报告出来。
SET AUTOTRACE OFF


SET AUTOTRACE ON EXPLAIN 查询照常执行，最后显示执行计划
SET AUTOTRACE ON STATISTICS 查询照常执行，最后显示统计信息
SET AUTOTRACE ON 查询照常执行，最后显示执行计划+统计信息

SET AUTOTRACE TRACEONLY EXPLAIN 结果不打印，只显示执行计划
SET AUTOTRACE TRACEONLY STATISTICS 结果不打印，只显示统计信息
SET AUTOTRACE TRACEONLY 结果不打印，最后显示执行计划+统计信息




在AUTOTRACE输出中读到的信息
Recursive callsNumber of SQL statements executed in order to execute your SQL 


statement.为了执行你的SQL语句，需要执行的SQL语句数。recursive是递归的意思，递归SQL数。
Db block getsTotal number of blocks read from the buffer cache in current 


mode.在buffer cache中读取的块数目。
Consistent getsNumber of times a consistent read was requested for a block in 


the buffer cache.Consistent reads may require read asides to the Undo(rollback) information,and 


these reads to the undo will be counted here as well.一致性读的次数。
Physical readsNumber of physical reads from the datafiles into the buffer 


cache.从数据文件中读取数据块到内存的次数，物理读。
Redo sizeTotal amount of redo generated in bytes during the execution of this 


statement.执行过程中产生的redo数量，以byte计量。
Bytes sent via SQL*Net to clientTotal number of bytes sent to the client from the 


server.
Bytes received via SQL*Net from clientTotal number of bytes received from the client.
SQL*Net roundtrips to/from clientTotal number of SQL*Net messages sent to and 


received from the client.This includes round-trips for fetches from a multiple-row result set.
Sorts(disk)Sorts that use the disk(temporary tablespace) because the sort exceeded 


the user's sort area size.
Rows processedRows processed by modifications or returned from a SELECT 


statement.



recursive calls高是有问题的，应尽量降低，因为这耗资源。如果一个SQL语句的recursive calls很


高，我会运行多一次，如果仍然第二次不高了，证明第一次是由于Hard Parses硬解析的原因。


alter system flush shared_pool;
set autotrace traceonly statistics;
select * from scott.emp;
看统计信息
select * from scott.emp;
看统计信息
可能会发现，第一次的recursive SQL很高，第二次就很低了。这是由于第一次要硬解析。


逻辑读Logical Reads= Consistent Gets + DB Block Gets，虽然Consistent Gets也是从undo 


segment读到buffer cache中，但DB Block Gets不包含Consistent Gets.


下面摘取一段原文介绍：
Db Block Gets and Consistent Gets
Blocks may be retrieved(检索) and used by Oracle in one of two ways:current or 


consistent.A current mode get is a retrieval of a block as it exists right now.You will see these 


most frequently during modification statements,which must update only the latest copy of the 


block.Consistent gets are the retrieval of blocks from the buffer cache in "read consistent"mode 


and may  include read asides to UNDO (rollback segments).A query will generally 


perform"consistent gets."
These are the most important parts of the AUTOTRACE report.They represent your logical 


I/Os--the number of times you had to latch a buffer in order to inspect it.The less we latch,the 


better.In general,the less logical I/O we can do,the better.