配置環境_1_ZenDei技術網路在線

恢復內容開始如何正確地建立SCOTT/TIGER演示模式執行腳本（1）cd [ORACLE_HOME]/sqlplus/demo。（2）以任意用戶身份連接後運行demobld.sql。註：關於腳本文件的位置和名稱，不同的版本有所不同。如我的Oracle版本是10G，並運行在Windows ...

如何正確地建立SCOTT/TIGER演示模式

執行腳本

　　（1）cd [ORACLE_HOME]/sqlplus/demo。

　　（2）以任意用戶身份連接後運行demobld.sql。

註：關於腳本文件的位置和名稱，不同的版本有所不同。

如我的Oracle版本是10G，並運行在Windows操作系統模式下，該腳本的位置和名稱為：D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlsampl.sql

在不使用腳本的情況下創建模式

CREATE TABLE EMP
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);

INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-7-87','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-7-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;

INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES
(20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');

alter table emp add constraint emp_pk primary key(empno);
alter table dept add constraint dept_pk primary key(deptno);
alter table emp add constraint emp_fk_dept foreign key(deptno) references dept;
alter table emp add constraint emp_fk_emp foreign key(mgr) references emp;

設置環境

SQL*Plus允許建立一個login.sql文件，每次啟動SQL*Plus時都會執行這個腳本。

另外，還允許設置一個環境變數SQLPATH，這樣不論這個login.sql腳本具體在哪個目錄中，SQL*Plus都能找到它。

我是用的login.sql腳本如下：

define_editor= Notepad
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr(global_name, 1, decode(dot, 0, length(global_name),
dot-1)) global_name
from (select global_name, instr(global_name, '.') dot from global_name);
set sqlprompt '&gname> '
set termout on

下麵對這個腳本做些說明：

define_editor：設置SQL*Plus使用的預設編輯器。可以把它設置你最喜歡的文本編輯器（而不是字處理器），如記事本（Notepad）或emacs或vi。

set setveroutput on size 1000000 ：這會預設的打開DBMS_OUTPUT（這樣就不必每次在鍵入這個命令了）。另外，也將預設緩衝區大小設置的儘可能大。

set trimspool on ：假離線輸出文本時，會去除文本行兩端的空格，而且行寬不定。如果設置為OFF（預設設置），假離線輸出的文本行寬度則等於所設置的linesize。

set long 5000 ：設置選擇LONG和CLOB列時顯示的預設位元組數。

set linesize 100 ：設置SQL*Plus顯示的文本行寬為100個字元。

set pagesize 9999 ：pagesize 可以控制SQL*Plus多久列印一次標題，這裡講pagesize 設置成一個很大的值（所以每頁只有一組標題）。

column plan_plus_exp a80 ：設置由AUTOTRACE得到的解釋計劃輸出（explain plan output）的預設寬度。a80通常足以放下整個計劃。

define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr(global_name, 1, decode(dot, 0, length(global_name),
dot-1)) global_name
from (select global_name, instr(global_name, '.') dot from global_name);
set sqlprompt '&gname> '

column global_name new_value gname 指令告訴SQL*Plus取得global_name列中的最後一個值，並將這個值賦給替換變數gname。接下來我從資料庫中選出global_name，並與我的登錄用戶連接。

這樣得到的SQL*Plus提示符為：

psl@orcl>

這樣一來，我就能知道我是誰，還有我在哪兒。

設置SQL*Plus的AUTOTRACE

AUTOTRACE是SQL*Plus中的一個工具，可以顯示所執行查詢的解釋計劃（explain plan）以及所用的資源。本書大量使用了AUTOTRACE工具。配置AUTOTRACE的方法不止一種。

初始配置

以下是我採用的方法：

（1）cd [ORACLE_HOME]/rdbms/admin；

（2）作為SYSTEM登錄SQL*Plus；

（3）運行@utlxplan；

（4）運行CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;

（5）運行GRANT ALL ON PLAN_TABLE TO PUBLIC。

如果願意，可以把GRANT TO PUBLIC中的 PUBLIC替換為某個用戶。

通過將它設置為PUBLIC，任何人都可以使用SQL*Plus進行跟蹤（在我看來並不是件壞事）。

這麼一來，就不需要每個用戶都安裝自己的計劃表。

還有一種做法是，在想要使用AUTOTRACE的每個模式中分別運行@utlxplan。

下一步是創建並授予PLUSTRACE角色：

（1）cd [ORACLE_HOME]/sqlplus/admin；

（2）作為SYS或SYSDBA登錄SQL*Plus；

（3）運行@plustrce；

（4）運行GRANT PLUSTRACE TO PUBLIC。

重申一遍，如果願意，可以把GRANT 命令中的PUBLIC替換為某個用戶。

控制報告

你會自動得到一個AUTOTRACE報告，其中可能列出SQL優化器所用的執行路徑，以及語句的執行統計信息。成功執行SQL DML（即 SELECT、DELETE、UPDATE、MERGE和 INSERT）語句後就會生成這個報告。它對於監視並調優這些語句的性能很有幫助。

通過設置AUTOTRACE系統變數可以控制這個變數。

SET AUTOTRACE OFF ：不生成AUTOTRACE報告。這是預設設置。

SET AUTOTRACE ON EXPLAIN ：AUTOTRACE只顯示優化器執行路徑。

SET AUTOTRACE ON STATISTICS ：AUTOTRACE只顯示SQL語句的執行統計信息。

SET AUTOTRACE ON ：報告既包含優化器執行路徑，又包括SQL語句的執行統計信息。

SET AUTOTRACE TRACEONLY ：這與 SET AUTOTRACE ON 類似，但是不顯示用戶的查詢輸出（如果有的話）。

附註：

下麵是腳本文件utlxplan內容：

create table PLAN_TABLE (
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_alias varchar2(65),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth 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),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(30),
other_xml clob

);

可以看出，此腳本是用於創建計劃表PLAN_TABLE的。

再來看腳本文件plustrce：

set echo on

drop role plustrace;
create role plustrace;

grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;

set echo off

可以看出，這個腳本創建了一個角色plustrace，並把3張動態性能視圖的SELECT授予該角色，再把該角色授予DBA用戶，並使其具有將該系統許可權授予其他用戶的許可權。

配置Statspack

只有作為SYSDBA連接（CONNECT / AS SYSDBA）時才能安裝Statspack。

所以，要想安裝Statspack，必須能以SYSDBA的角色連接。

在許多安裝中，必須由DBA或管理員來完成這個任務。

只要能（作為SYSDBA）連接，安裝Statspack就是小菜一碟了，只需運行@spcreate.sql。這個腳本可以在[ORACLE_HOME]\rdbms\admin中找到，作為SYSDBA連接，通過SQL*Plus執行腳本。

運行spcreate.sql腳本之前，你要瞭解3個信息：

將創建的PERFSTAT模式將使用什麼密碼？

PERFSTAT使用的預設表空間是什麼？

PERFSTAT使用的臨時表空間是什麼？

運行的腳本如下所示：

sqlplus / as sysdba

@spcreate.sql

執行腳本時，會提示你輸入這些信息。如果輸入有誤，或者不小心取消了安裝，在下一次嘗試安裝Statspack之前應該先用$ORACLE_HOME/rdbms/admin中的spdrop.sql刪除用戶（PERFSTAT）和已經安裝的視圖。安裝Statspack會創建一個名為spcpkg.lis的文件。如果出現錯誤，就應該檢查這個文件。不過，只要提供了有效的表空間名（而且尚沒有PERFSTAT用戶），PL/SQL代碼應該能順利地安裝。

附註：

下麵是腳本文件spcreate.sql內容：

-- Create PERFSTAT user and required privileges
@@spcusr

--
-- Build the tables and synonyms
connect perfstat/&&perfstat_password
@@spctab
-- Create the statistics Package
@@spcpkg

從這個腳本可以看出，這個腳本文件調用同目錄下的另外三個腳本文件，分別用於創建用戶PERFSTAT，創建表和同義詞，最後創建統計包。

定製腳本

在這一節中，我會介紹本書所用腳本的相關需求。另外，還會分析腳本底層的代碼。

runstats

runstats是我開發的一個工具，能對做同一件事的兩個不同方法進行比較，得出孰優孰劣的結果。

你只需要提供兩個不同的方法，餘下的事情都由runstats負責。runstats只是測量3個要素。

牆上時間（wall clock）或耗用時間（elapsed time）：知道牆上時間或耗用時間很有用，不過這不是最重要的信息。

系統統計結果 ：會併排地顯示每個方法做某件事（如執行一個解析調用）的次數，並展示二者之差。

閂定（latching）：這是這個報告的關鍵輸出。

你在本書中會瞭解到，閂（latch）是一種輕量級的鎖。

鎖（lock）是一種串列化設備，而串列化設備不支持併發。

如果應用不支持併發，可擴展性就比較差，只能支持較少的用戶，而且需要更多的資源。

構建應用時，我們往往希望應用能很好地擴展，也就是說，為1位用戶服務於為1000或10000位用戶服務是一樣的。應用中使用的閂越少，性能就越好。

如果一種方法從牆上時間來看運行時間較長，但是只使用了另一種方法10%的閂，我可能會選擇前者。因為我知道，與使用更多的閂的方法相比，使用較少的方法能更好地擴展。

Runstats最好獨立使用，也就是說，最好在一個單用戶資料庫上運行。我們會測量各個方法的統計結果和閂定（鎖定）活動。Runstats在運行過程中，不希望其他任務對系統的負載或閂產生影響。只需要一個很小的測試資料庫就能很好地完成這些測試。例如，我就經常使用我的台式機或手提電腦進行測試。

要使用Runstats，需要能訪問幾個V$視圖，並創建一個表來存儲統計結果，還要創建Runstats包。

為此，需要訪問4個V$表（就是那些神奇的動態性能表）：V$STATNAME、V$MYSTAT、V$TIMER和V$LATCH。以下是我使用的視圖：

create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch
union all
select 'STAT ...Elapsed Time', hsecs from v$timer;

註意：

需要授權訪問具體對象名應該是V_$STATNAME、V_$MYSTAT等。也就是說，授權中適用的對象名應該以V_$而不是V$開頭。這些V$名只是同義詞，它們分別指向名字以V_$開頭的底層視圖。因此，V$STATNAME就是指向V_$STATNAME（一個視圖）的同義詞。實際上需要授權允許訪問這個視圖。

如何你能直接得到V$STATNAME、V$MYSTAT、V$LATCH和V$TIMER的直接授權，就能直接在這些表執行SELECT操作（相應的可自行創建視圖）；否則，

可以由其他人對這些表執行SELECT 操作為你創建視圖，並授予你在這個視圖上執行SELECT的許可權。

一旦建立視圖，接下來只需要一個小表來收集統計結果：

create global temporary table run_stats
(runid varchar2(15),
name varchar2(80),
value int )
on commit perserve rows;

註：這是一個會話級的臨時表。

最後，需要創建Runstats包。其中包含3個簡單的API調用。

Runstats測試開始時調用RS_START（Runstats開始）。

正如你想像的，RS_MIDDLE會在測試中間調用。

完成時調用RS_STOP，列印報告。

創建Runstats包的規範如下：

create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop(p_difference_threshold in number default 0);
end;

參數p_difference_threshold用於控制最後列印的數據量。Runstats會收集並得到每次運行的統計結果和閂信息，然後列印一個報告，說明每次測試（每個方法）使用了多少資源，以及不同測試（不同方法）的結果之差。可以使用這個參數來控制只查看差值大於這個數的統計結果和閂信息。由於這個參數預設為0，所以預設情況下可以看到所有輸出。

下麵逐一分析包體中的過程。包前面是一些全局變數，這些全局變數用於記錄每次運行得耗用時間：

create or replace package body runstats_pkg
as
g_start number;
g_run1 number;
g_run2 number;

下麵是RS_START常式。這個常式只是清空保存統計結果的表，並填入“上一次”（before）得到的統計結果和閂信息；然後獲得當前定時器值，這是一種時鐘，可用於計算耗用時間（單位為百分之一秒）：

procedure rs_start
is
begin
delete from run_stats;
insert into run_stats
select 'before', stats.* from stats;
g_start := dbms_utility.get_cpu_time;
end;

接下來是RS_MIDDLE常式。這個常式只是把第一次測試運行的耗用時間記錄在G_RUN1中，然後插入當前的一組統計結果和閂信息。如果把這些值與先前在RS_START中保存的值相減，就會發現第一個方法使用了多少閂，以及使用了多少游標（一種統計結果），等等。

最後，記錄下一次運行的開始時間：

procedure rs_middle
is
begin
g_run1 := (dbms_utility.get_cpu_time-g_start);
insert into run_stats
select 'after 1', stats.* from stats;
g_start :=dbms_utility.get_cpu_time;
end;

這個包中下一個也是最後一個過程是RS_STOP常式。它的任務就是列印每次運行的累計CPU時間，然後分別列印兩次運行的統計/閂值之差（只列印差值超出閾值時的結果）：

procedure rs_stop(p_difference_threshold in number default 0)
is
begin
g_run2 := (dbms_utility.get_cpu_time-g_start);
dbms_output.put_line
( 'Run1 ran in ' || g_run1 || ' cpu hsecs ' );
dbms_output.put_line
( 'Run2 ran in ' || g_run2 || ' cpu hsecs ' );
if ( g_run2 <> 0)
then
dbms_output.put_line
( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
'% of the time');
end if;
dbms_output.put_line( chr(9) );
insert into run_stats
select 'after 2', stats.* from stats;
dbms_output.put_line
(rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||
lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );

for x in
( select rpad(a.name, 30 ) ||
to_char( b.value-a.value, '999,999,999' ) ||
to_char( c.value-b.value, '999,999,999' ) ||
to_char(((c.value-b.value)-(b.value-a.value)), '999,999,999') data
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = 'before'
and b.runid = 'after 1'
and c.runid = 'after 2'
and abs((c.value-b.value)-(b.value-a.value))
>p_difference_threshold
order by abs((c.value-b.value)-(b.value-a.value))
)loop
dbms_output.put_line(x.data);
end loop;

dbms_output.put_line(chr(9));
dbms_output.put_line
('Run1 latches total versus runs -- difference and pct');
dbms_output.put_line
(lpad('Run1', 12) || lpad('Run2', 12) ||
lpad('Diff', 12) || lpad('Pct', 10));

for x in
(select to_char(run1, '999,999,999') ||
to_char(run2, '999,999,999') ||
to_char(diff, '999,999,999') ||
to_char(round(run1/decode(run2, 0,
to_number(0), run2) *100, 2), '999,999,999') || '%' data
from (select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
sum((c.value-b.value)-(b.value-a.value)) diff
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = 'before'
and b.runid = 'after 1'
and c.runid = 'after 2'
and a.name like 'LATCH%'
)
)loop
dbms_output.put_line(x.data);
end loop;
end;
end;

下麵就可以使用runstats了。我們將通過例子來說明如何使用runstats對批量插入（INSERT）和逐行處理進行比較，看看哪種方法效率更高。首先建立兩個表，要在其中插入1000000行記錄（稍後提供BIG_TABLE創建腳本）：

create table t1
as
select * from big_table
where 1=0;

create table t2
as
select * from big_table
where 1=0;

接下來使用第一種方法插入記錄，也就是使用單獨一條SQL語句完成批量插入。首先調用RUNSTARTS_PKG.RS_START:

SQL> EXEC runstats_pkg.rs_start;
PL/SQL procedure successfully completed

SQL> insert into t1 select * from big_table where rownum <= 100000;
40784 rows inserted

SQL> commit;
Commit complete

SQL> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed

SQL> begin
2 for x in (select * from big_table where rownum <= 100000)
3 loop
4 insert into t2 values x;
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed

SQL> exec runstats_pkg.rs_stop(100000);
PL/SQL procedure successfully completed

SQL> set serveroutput on
SQL> exec runstats_pkg.rs_stop(100000);
Run1 ran in 7 cpu hsecs
Run2 ran in 178 cpu hsecs
run 1 ran in 3.93% of the time

Name Run1 Run2 Diff
STAT...physical read bytes 212,992 0 -212,992
STAT...physical read bytes 212,992 0 -212,992
STAT...physical read total byt 212,992 0 -212,992
STAT...physical read total byt 212,992 0 -212,992
STAT...session uga memory max 261,964 0 -261,964
STAT...session pga memory max 262,144 0 -262,144
LATCH.cache buffers chains 25,070 296,950 271,880
LATCH.cache buffers chains 25,070 297,605 272,535
STAT...session uga memory max 261,964 1,053,080 791,116
STAT...session pga memory max 262,144 1,114,112 851,968
STAT...undo change vector size 166,788 2,704,772 2,537,984
STAT...undo change vector size 166,788 2,706,780 2,539,992
STAT...redo size 4,907,816 15,225,404 10,317,588
STAT...redo size 4,907,816 15,228,152 10,320,336

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
85,092 1,040,272 955,180 8%
PL/SQL procedure successfully completed

這就確保全裝好了RUNSTATS_PKG包。這裡表明瞭在開發應用時，為什麼應該儘量使用一個SQL語句，而不是一批過程式代碼。

mystat

mystat.sql和相應的mystat2.sql用於展示完成某操作之前和之後的某些Oracle“統計結果”的變化情況。mystat.sql只是獲得統計結果的開始值：

set echo off
set verify off
column value new_val V
define S="&1"

set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.STATISTIC#
and lower(a.name) like '%' || lower('&S') || '%'
/
set echo on

mystat2.sql 用於報告差值（通過運行第一個腳本mystat.sql來填充&V，為此它使用了SQL*Plus NEW_VAL特性，其中包含由上一個查詢選擇的最後一個VALUE）：

set echo off
set verify off
select a.name, b.value V, to_char(b.value-&V, '999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.STATISTIC#
and lower(a.name) like '%' || lower('&S') || '%'
/
set echo on

例如，要查看某個UPDATE生成的redo數，可以使用以下命令：

SQL> @C:\Users\john\Desktop\mystat.sql "redo size"
NAME VALUE
---------------------------------------------------------------- ----------
redo size 116

SQL> set echo off
SQL> update big_table set owner=lower(owner) where rownum<=1000;
1000 rows updated

SQL> @C:\Users\john\Desktop\mystat2.sql
NAME V DIFF
---------------------------------------------------------------- ---------- ----------------
redo size 92400 92,284

SQL> set echo off

由此可見，1000行記錄的UPDATE會生成92,284位元組的REDO。

BIG_TABLE

在全書的例子中，我使用了一個名為BIG_TABLE的表。根據所用的系統，這個表的記錄數在1條和400萬條之間，而且大小也不定，為200M~800M。不過，不論怎樣，表結構都是一樣的。

為了創建BIG_TABLE，我編寫了一個可以完成以下功能的腳本。

根據ALL_OBJECTS創建一個空表。這個字典視圖用於填充BIG_TABLE。

置這個表為NOLOGGING。這是可選的，我之所以這樣做，是為了提高性能。對測試表使用NOLOGGING模式是安全的；由於生產系統中不會使用這樣一個測試表，所以不會啟用諸如Oracle Data Guard之類的特性。

用ALL_OBJECT內容填充表，然後迭代地插入其自身中，每次迭代會使表大小幾乎加倍。

對這個創建一個主鍵約束。

收集統計結果。

要建立BIG_TABLE表，可以在SQL*Plus提示視窗運行以下腳本，傳入希望在表中插入的行記錄表。

如果達到這個行數，腳本則停止執行。

create table big_table

select rownum id, a.*

from all_objects a

where 1=0;

alter table big_table nologging;

declare
l_cnt number;
l_rows number := &1;
begin
insert /*+ append */
into big_table
select rownum, a.*
from all_objects a
where rownum <= &1;
l_cnt := sql%rowcount;
commit;
while(l_cnt<l_rows)
loop
insert /*+ append */ into big_table
select rownum+l_cnt, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE,
CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;

alter table big_table add constraint big_table_pk primary key(id);

我只估算了這個表的基準統計結果。與主鍵關聯的索引在創建時會自動計算統計結果。

---恢復內容結束---

如何正確地建立SCOTT/TIGER演示模式

執行腳本

　　（1）cd [ORACLE_HOME]/sqlplus/demo。

　　（2）以任意用戶身份連接後運行demobld.sql。

註：關於腳本文件的位置和名稱，不同的版本有所不同。

如我的Oracle版本是10G，並運行在Windows操作系統模式下，該腳本的位置和名稱為：D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlsampl.sql

在不使用腳本的情況下創建模式

CREATE TABLE EMP
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;

設置環境

SQL*Plus允許建立一個login.sql文件，每次啟動SQL*Plus時都會執行這個腳本。

另外，還允許設置一個環境變數SQLPATH，這樣不論這個login.sql腳本具體在哪個目錄中，SQL*Plus都能找到它。

我是用的login.sql腳本如下：

下麵對這個腳本做些說明：

define_editor：設置SQL*Plus使用的預設編輯器。可以把它設置你最喜歡的文本編輯器（而不是字處理器），如記事本（Notepad）或emacs或vi。

set setveroutput on size 1000000 ：這會預設的打開DBMS_OUTPUT（這樣就不必每次在鍵入這個命令了）。另外，也將預設緩衝區大小設置的儘可能大。

set long 5000 ：設置選擇LONG和CLOB列時顯示的預設位元組數。

set linesize 100 ：設置SQL*Plus顯示的文本行寬為100個字元。

set pagesize 9999 ：pagesize 可以控制SQL*Plus多久列印一次標題，這裡講pagesize 設置成一個很大的值（所以每頁只有一組標題）。

column plan_plus_exp a80 ：設置由AUTOTRACE得到的解釋計劃輸出（explain plan output）的預設寬度。a80通常足以放下整個計劃。

這樣得到的SQL*Plus提示符為：

psl@orcl>

這樣一來，我就能知道我是誰，還有我在哪兒。

設置SQL*Plus的AUTOTRACE

初始配置

以下是我採用的方法：

（1）cd [ORACLE_HOME]/rdbms/admin；

（2）作為SYSTEM登錄SQL*Plus；

（3）運行@utlxplan；

（4）運行CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;

（5）運行GRANT ALL ON PLAN_TABLE TO PUBLIC。

如果願意，可以把GRANT TO PUBLIC中的 PUBLIC替換為某個用戶。

通過將它設置為PUBLIC，任何人都可以使用SQL*Plus進行跟蹤（在我看來並不是件壞事）。

這麼一來，就不需要每個用戶都安裝自己的計劃表。

還有一種做法是，在想要使用AUTOTRACE的每個模式中分別運行@utlxplan。

下一步是創建並授予PLUSTRACE角色：

（1）cd [ORACLE_HOME]/sqlplus/admin；

（2）作為SYS或SYSDBA登錄SQL*Plus；

（3）運行@plustrce；

（4）運行GRANT PLUSTRACE TO PUBLIC。

重申一遍，如果願意，可以把GRANT 命令中的PUBLIC替換為某個用戶。

控制報告

通過設置AUTOTRACE系統變數可以控制這個變數。

SET AUTOTRACE OFF ：不生成AUTOTRACE報告。這是預設設置。

SET AUTOTRACE ON EXPLAIN ：AUTOTRACE只顯示優化器執行路徑。

SET AUTOTRACE ON STATISTICS ：AUTOTRACE只顯示SQL語句的執行統計信息。

SET AUTOTRACE ON ：報告既包含優化器執行路徑，又包括SQL語句的執行統計信息。

SET AUTOTRACE TRACEONLY ：這與 SET AUTOTRACE ON 類似，但是不顯示用戶的查詢輸出（如果有的話）。

附註：

下麵是腳本文件utlxplan內容：

);

可以看出，此腳本是用於創建計劃表PLAN_TABLE的。

再來看腳本文件plustrce：

set echo on

drop role plustrace;
create role plustrace;

grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;

set echo off

配置Statspack

只有作為SYSDBA連接（CONNECT / AS SYSDBA）時才能安裝Statspack。

所以，要想安裝Statspack，必須能以SYSDBA的角色連接。

在許多安裝中，必須由DBA或管理員來完成這個任務。

運行spcreate.sql腳本之前，你要瞭解3個信息：

將創建的PERFSTAT模式將使用什麼密碼？

PERFSTAT使用的預設表空間是什麼？

PERFSTAT使用的臨時表空間是什麼？

運行的腳本如下所示：

sqlplus / as sysdba

@spcreate.sql

附註：

下麵是腳本文件spcreate.sql內容：

-- Create PERFSTAT user and required privileges
@@spcusr

--
-- Build the tables and synonyms
connect perfstat/&&perfstat_password
@@spctab
-- Create the statistics Package
@@spcpkg

從這個腳本可以看出，這個腳本文件調用同目錄下的另外三個腳本文件，分別用於創建用戶PERFSTAT，創建表和同義詞，最後創建統計包。

定製腳本

在這一節中，我會介紹本書所用腳本的相關需求。另外，還會分析腳本底層的代碼。

runstats

runstats是我開發的一個工具，能對做同一件事的兩個不同方法進行比較，得出孰優孰劣的結果。

你只需要提供兩個不同的方法，餘下的事情都由runstats負責。runstats只是測量3個要素。

牆上時間（wall clock）或耗用時間（elapsed time）：知道牆上時間或耗用時間很有用，不過這不是最重要的信息。

系統統計結果 ：會併排地顯示每個方法做某件事（如執行一個解析調用）的次數，並展示二者之差。

閂定（latching）：這是這個報告的關鍵輸出。

你在本書中會瞭解到，閂（latch）是一種輕量級的鎖。

鎖（lock）是一種串列化設備，而串列化設備不支持併發。

如果應用不支持併發，可擴展性就比較差，只能支持較少的用戶，而且需要更多的資源。

構建應用時，我們往往希望應用能很好地擴展，也就是說，為1位用戶服務於為1000或10000位用戶服務是一樣的。應用中使用的閂越少，性能就越好。

要使用Runstats，需要能訪問幾個V$視圖，並創建一個表來存儲統計結果，還要創建Runstats包。

為此，需要訪問4個V$表（就是那些神奇的動態性能表）：V$STATNAME、V$MYSTAT、V$TIMER和V$LATCH。以下是我使用的視圖：

註意：

如何你能直接得到V$STATNAME、V$MYSTAT、V$LATCH和V$TIMER的直接授權，就能直接在這些表執行SELECT操作（相應的可自行創建視圖）；否則，

可以由其他人對這些表執行SELECT 操作為你創建視圖，並授予你在這個視圖上執行SELECT的許可權。

一旦建立視圖，接下來只需要一個小表來收集統計結果：

create global temporary table run_stats
(runid varchar2(15),
name varchar2(80),
value int )
on commit perserve rows;

註：這是一個會話級的臨時表。

最後，需要創建Runstats包。其中包含3個簡單的API調用。

Runstats測試開始時調用RS_START（Runstats開始）。

正如你想像的，RS_MIDDLE會在測試中間調用。

完成時調用RS_STOP，列印報告。

創建Runstats包的規範如下：

create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop(p_difference_threshold in number default 0);
end;

下麵逐一分析包體中的過程。包前面是一些全局變數，這些全局變數用於記錄每次運行得耗用時間：

create or replace package body runstats_pkg
as
g_start number;
g_run1 number;
g_run2 number;

procedure rs_start
is
begin
delete from run_stats;
insert into run_stats
select 'before', stats.* from stats;
g_start := dbms_utility.get_cpu_time;
end;

最後，記錄下一次運行的開始時間：

procedure rs_middle
is
begin
g_run1 := (dbms_utility.get_cpu_time-g_start);
insert into run_stats
select 'after 1', stats.* from stats;
g_start :=dbms_utility.get_cpu_time;
end;

create table t1
as
select * from big_table
where 1=0;

create table t2
as
select * from big_table
where 1=0;

接下來使用第一種方法插入記錄，也就是使用單獨一條SQL語句完成批量插入。首先調用RUNSTARTS_PKG.RS_START:

SQL> EXEC runstats_pkg.rs_start;
PL/SQL procedure successfully completed

SQL> insert into t1 select * from big_table where rownum <= 100000;
40784 rows inserted

SQL> commit;
Commit complete

SQL> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed

SQL> begin
2 for x in (select * from big_table where rownum <= 100000)
3 loop
4 insert into t2 values x;
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed

SQL> exec runstats_pkg.rs_stop(100000);
PL/SQL procedure successfully completed

這就確保全裝好了RUNSTATS_PKG包。這裡表明瞭在開發應用時，為什麼應該儘量使用一個SQL語句，而不是一批過程式代碼。

mystat

mystat.sql和相應的mystat2.sql用於展示完成某操作之前和之後的某些Oracle“統計結果”的變化情況。mystat.sql只是獲得統計結果的開始值：

set echo off
set verify off
column value new_val V
define S="&1"

set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.STATISTIC#
and lower(a.name) like '%' || lower('&S') || '%'
/
set echo on

mystat2.sql 用於報告差值（通過運行第一個腳本mystat.sql來填充&V，為此它使用了SQL*Plus NEW_VAL特性，其中包含由上一個查詢選擇的最後一個VALUE）：

例如，要查看某個UPDATE生成的redo數，可以使用以下命令：

SQL> @C:\Users\john\Desktop\mystat.sql "redo size"
NAME VALUE
---------------------------------------------------------------- ----------
redo size 116

SQL> set echo off
SQL> update big_table set owner=lower(owner) where rownum<=1000;
1000 rows updated

SQL> @C:\Users\john\Desktop\mystat2.sql
NAME V DIFF
---------------------------------------------------------------- ---------- ----------------
redo size 92400 92,284

SQL> set echo off

由此可見，1000行記錄的UPDATE會生成92,284位元組的REDO。

SHOW_SPACE

SHOW_SPACE常式用於列印資料庫段的空間利用率信息。其介面如下：

SQL> desc show_space
Parameter Type Mode Default?
----------- -------- ---- --------
P_SEGNAME VARCHAR2 IN
P_OWNER VARCHAR2 IN Y
P_TYPE VARCHAR2 IN Y
P_PARTITION VARCHAR2 IN Y

P_SEGNAME ：段名（例如表或索引名）。

P_OWNER ：預設為當前用戶，不過也可以使用這個常式查看另外某個模式。

P_TYPE ：預設是TABLE，這個參數表示查看哪種類型的對象（段）。例如，select distinct segment_type from dba_segments會列出有效的段類型。

P_PARTITION ：顯示分區對象的空間時所用的分區名。SHOW_SPACE一次只顯示一個分區的空間利用率。

這個常式的輸出如下，這裡段位於一個自動段空間管理（Automatic Segment Space Management， ASSM）表空間中：

SQL> exec show_space('BIG_TABLE');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 14
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 582
Total Blocks............................ 1,280
Total Bytes............................. 10,485,760
Total MBytes............................ 10
Unused Blocks........................... 666
Unused Bytes............................ 5,455,872
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 19,593
Last Used Block......................... 102
PL/SQL procedure successfully completed

報告的各項結果說明如下：

Unformatted Blocks ：為表分配的位於高水位線之下但未使用的塊數。把未格式化和未用的塊加在一起，就是以為表分配但從未用於保存ASSM對象數據的總塊數。

FS1 Blocks-FS1 Blocks ：包含數據的格式化塊。項名後的數字區間表示各塊的“空閑度”。例如，（0-25）是指空閑度為0~25%的塊數。

Full Blocks ：已滿的塊數，不能再對這些塊執行插入。

Total Blocks、Total Bytes、Total MBytes ：為所查看的段分配的總空間量，單位分配是資料庫塊、位元組和兆位元組。

Unused Blocks、Unused Bytes ：表示未用空間所占的比例（未用空間量），這些塊已經分配給所查看的段，但目前在段的高水位線之上。

Last Used Ext FileId ：包含最後一個區塊（其中包含數據）的文件的文件ID。

Last Used Ext BlockId ：最後一個區段開始處的塊ID，這是最後使用的文件中的塊ID。

Last Used Block ：最後一個區段中最後一個塊的偏移量。

如果對象在用戶空間管理的表空間中，使用SHOW_SPACE查看時，輸出如下：

SQL> exec show_space('TEST');
Free Blocks............................. 0
Total Blocks............................ 8
Total Bytes............................. 65,536
Total MBytes............................ 0
Unused Blocks........................... 7
Unused Bytes............................ 57,344
Last Used Ext FileId.................... 8
Last Used Ext BlockId................... 9
Last Used Block......................... 1
PL/SQL procedure successfully completed

這裡唯一的區別是報告中最前面的Free Blocks項。這是段的第一個freelist（自由列表）組中的塊數。我的腳本只測試了第一個freelist組。如果想測試多個freelist組，還需要修改這個腳本。

BIG_TABLE

為了創建BIG_TABLE，我編寫了一個可以完成以下功能的腳本。

根據ALL_OBJECTS創建一個空表。這個字典視圖用於填充BIG_TABLE。

用ALL_OBJECT內容填充表，然後迭代地插入其自身中，每次迭代會使表大小幾乎加倍。

對這個創建一個主鍵約束。

收集統計結果。

要建立BIG_TABLE表，可以在SQL*Plus提示視窗運行以下腳本，傳入希望在表中插入的行記錄表。

如果達到這個行數，腳本則停止執行。

create table big_table

select rownum id, a.*

from all_objects a

where 1=0;

alter table big_table nologging;

alter table big_table add constraint big_table_pk primary key(id);

我只估算了這個表的基準統計結果。與主鍵關聯的索引在創建時會自動計算統計結果。

代碼約定

需要指出這本書使用的一個編程約定，也就是PL/SQL代碼中的變數如何命名。例如，考慮如下包體：

create or replace package body my_pkg
as
g_variable varchar2(25);
procedure p(p_variable in varchar2)
is
l_variable varchar2(25);
begin
null;
end;
end;

這裡有三個變數，一個全局包變數G_VARIABLE，一個過程形參P_VARIABLE，還有一個局部變數L_VARIABLE。我是根據變數的作用域來命名的。所有全局變數都以G_開頭，參數用P_開頭，局部變數用L_開頭。這樣做的主要原因是為了區別PL/SQL變數和資料庫表中的列。例如，有以下過程：

create procedure p(ENAME in varchar2)
as
begin
for x in (select * from emp where ename = ENAME)LOOP
Dbms_output.put_line(x.empno);
end loop;
end;

EMP表中ENAME非空的所有行都會列印出來，當SQL看到ename = ENAME時，它會把ENAME列與自身比較（這是當然的）。為了避免這種錯誤，可以使用ename

=P.ENAME,也就是說，用過程名來限定對PL/SQL變數的引用，但很容易忘記加過程名進行限定。一旦忘了，就會導致錯誤。

我總是根據作用域來命名變數。這樣一來，可以很容易地把參數與局部變數和全局變數區分開，而且還可以消除列名和變數名的任何歧義。