你的位置:首页 > 数据库

[数据库]配置环境_1


如何正确地建立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通常足以放下整个计划。

login.sql中下面这部分用于建立SQL*Plus提示符:

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_

);

可以看出,此脚本是用于创建计划表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创建脚本):

 

 

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提示窗口运行以下脚本,传入希望在表中插入的行记录表。