Archive for the 'Oracle RDBMS' Category

Oct 25 2008

Automatic Statistics Gathering

Published by kamus under Oracle RDBMS

在Oracle10g中引入的优化器统计信息（Optimizer Statistics）自动收集，是一个看上去很不错的功能，但是在实际应用中却往往没有起到相应的效果，甚至在某些系统中我们会建议禁用这个功能。

阐述一些该功能的相关知识点。

1. Automatic Statistics Gathering是由Scheduler调度GATHER_STATS_JOB作业来完成的，在GATHER_STATS_JOB作业中则调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程。

2. 该作业在创建数据库的自动创建，并且设置为每天晚上10点到第二天早上6点和周六周日的全天为运行窗口期。在运行窗口期内，该作业都会运行，根据stop_on_window_close属性来决定，如在窗口期结束以后，该作业如果还没有运行完毕，是继续运行还是结束运行。

3. GATHER_DATABASE_STATS_JOB_PROC是内部的存储过程，基本上跟DBMS_STATS.GATHER_DATABASE_STATS的功能一样，但是有内部的优先顺序考虑，更新越多的表将会越优先收集统计信息。

4. 收集统计信息的表对象是，之前从来没有收集过的或者是更新的（包括insert,update,delete,truncate）记录数超过当前总记录数10%的表。记录数的更改量由Oracle数据库自动监控，在初始化参数statistics_level设置为TYPICAL或者ALL时，自动监控即会生效。

5. 在USER_TAB_MODIFICATIONS表中记录了所有被监控的表的数据量更改信息。该信息的更新将会稍微滞后于真实的修改，可以通过DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO存储过程来立刻将更改的信息更新到USER_TAB_MODIFICATIONS表中。对于更新之后再rollback的记录，仍然算为已经受影响的记录，Oracle不会在rollback之后再去更新USER_TAB_MODIFICATIONS表。

  1. SQL> select * from user_tab_modifications where table_name='EMP';
  2.  
  3. no rows selected
  4.  
  5. SQL> select count(*) from emp;
  6.  
  7.   COUNT(*)
  8. ----------
  9.         14
  10.  
  11. SQL> update emp set sal=sal+100;
  12.  
  13. 14 rows updated.
  14.  
  15. SQL> select * from user_tab_modifications where table_name='EMP';
  16.  
  17. no rows selected
  18.  
  19. SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
  20.  
  21. PL/SQL procedure successfully completed.
  22.  
  23. SQL> select inserts,updates,deletes from user_tab_modifications where table_name
  24. ='EMP';
  25.  
  26.    INSERTS    UPDATES    DELETES
  27. ---------- ---------- ----------
  28.          0         14          0
  29.  
  30. SQL> rollback;
  31.  
  32. Rollback complete.
  33.  
  34. SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
  35.  
  36. PL/SQL procedure successfully completed.
  37.  
  38. SQL> select inserts,updates,deletes from user_tab_modifications where table_name
  39. ='EMP';
  40.  
  41.    INSERTS    UPDATES    DELETES
  42. ---------- ---------- ----------
  43.          0         14          0
  44.  
  45. SQL>

6. 在Oracle10g版本（包括最新的10.2.0.4）中没有已知的修改10%这个阀值的方法。但是在Oracle11g中则提供了SET_TABLE_PREFS等函数。

以下命令将指定表的STALE默认值从10%改为5%，该值可以从新的dba_tab_stat_prefs数据字典中查询获得。

  1. --仅限于Oracle11g版本
  2. BEGIN
  3.   DBMS_STATS.SET_TABLE_PREFS ( ownname =>'KAMUS', tabname =>'T1', pname =>'STALE_PERCENT', pvalue =>'5');
  4. END;
  5. /
  6.  
  7. SQL> select * from dba_tab_stat_prefs;
  8.  
  9. OWNER      TABLE_NAME PREFERENCE_NAME      PREFE
  10. ---------- ---------- -------------------- -----
  11. KAMUS      T1         STALE_PERCENT        5

7. 运行以下命令，可以禁用统计信息自动收集功能。

  1. BEGIN
  2.   DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
  3. END;
  4. /

No responses yet

Oct 18 2008

Oracle TimesTen体验 - Cache Group刷新

Published by kamus under Oracle RDBMS

回头看自己写的第一篇Timesten的文章，居然已经过去了1年半。事情有开始，应该就有个结束，完成它吧，即使今天碰到一万个挫折。

上回书说到，“1. 安装TimesTen 7.0 …”，现在最新版本已经是TimesTen Release 7.0.5.0.0了，这次重新安装了TimesTen，安装过程中我的手很欠，点上了本来不是默认选项的Enable Access Control，启用了用户存取权限控制，导致后面不得不查阅各个联机文档，费尽心机，才完成整个实验。当然手欠也有手欠的好处，否则谁会在刚开始实验一个新产品的时候去研究那些劳什子的安全设置呢。

2. 创建一个系统DSN，命名为ttdemo。
按照OTN上的How to Create TimesTen Databases，如果没有激活Access Control，应该一路下去很顺畅，我却卡在了第43页上，连接Datastore总是报错。

D:\Temp>ttIsql -connStr “DSN=ttdemo;UID=kamus”

Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or “help” for help, type “exit” to quit ttIsql.
All commands must end with a semicolon character.

connect “DSN=ttdemo;UID=kamus”;
7001: User authentication failed
The command failed.
Done.

耗时良久，最终发现，是因为启用了Access Control，所以必须要在Timesten中也创建跟Oracle Database中相同的Internal User，并且赋予这个User相应的权限，才可以连接后台是Oracle数据库的Datastore。

如果执行下面的命令有同样的输出，那么就是启用了Access Control。

D:\Temp>ttVersion -m | grep access_control
access_control=1

可是如何创建这个Internal User呢？

D:\Temp>ttisql

Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or “help” for help, type “exit” to quit ttIsql.
All commands must end with a semicolon character.

Command> create user kamus identified by oracle;
An existing connection is required to execute this command.
Try executing “connect [connection_string];” first.
The command failed.

直接在ttIsql中创建一个跟数据库用户kamus一样的TT用户，会要求先有一个连接，这个连接该是哪个呢？再去查文档。文档里面说的不清不楚，几经周折，终于发现在TT安装的过程中会自动创建一系列的DSN，其中名字是tt_tt70_32的为Instance data store，在这个datastore中可以进行TT用户的管理。

连接这个Instance data store，需要用instance administrator user用户登陆，可以通过下面的命令获得这个User的名字，在Windows系统中应该跟登陆操作系统的用户名一样。

D:\Temp>ttVersion -m | grep instance_admin
instance_admin=leyzhang

3. 创建TT User

D:\Temp>ttisql

Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or “help” for help, type “exit” to quit ttIsql.
All commands must end with a semicolon character.

Command> connect “dsn=tt_tt70_32; UID=leyzhang”;
Connection successful: DSN=tt_tt70_32;UID=leyzhang;DataStore=C:\TimesTen\DEMODA~
1\TT_tt70_32;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVE
R=C:\TimesTen\tt70_32\bin\ttdv70.dll;Authenticate=0;PermSize=20;TypeMode=0;
(Default setting AutoCommit=1)
Command> create user kamus identified by ‘oracle’;
Command> grant ddl to kamus;
Command> connect “dsn=tt_tt70_32; UID=kamus”;
Enter password for ‘kamus’:
Connection successful: DSN=tt_tt70_32;UID=kamus;DataStore=C:\TimesTen\DEMODA~1\T
T_tt70_32;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=C
:\TimesTen\tt70_32\bin\ttdv70.dll;Authenticate=0;PermSize=20;TypeMode=0;
(Default setting AutoCommit=1)

4. 使用TT user连接第二步创建的Datastore

D:\Temp>ttIsql -connStr “DSN=ttdemo;UID=kamus”

Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or “help” for help, type “exit” to quit ttIsql.
All commands must end with a semicolon character.

connect “DSN=ttdemo;UID=kamus”;
Connection successful: DSN=ttdemo;UID=kamus;DataStore=c:\TimesTen\DemoDataStores
\ttdemo;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=C:\
TimesTen\tt70_32\bin\ttdv70.dll;OracleId=orcl11g;LogDir=c:\TimesTen\DemoDataStor
es\log;PermSize=20;TempSize=20;TypeMode=0;
(Default setting AutoCommit=1)

我这里的测试没有要求输入kamus用户的密码，是因为在DSN中已经配置过了，加密的密码需要用ttUser -pwdcrypt命令创建。

5. 测试Passthrough。

所谓passthrough是指，TT中可以设定，当SQL语句检索的表在TT实例中不存在，那么将自动去后台的Oracle数据库中查询，并将查询的结果返回到TT中。这也是我们可以通过只读Cache Group来更新Oracle数据的方法。

Command> select * from v$version;
2206: Table KAMUS.V$VERSION not found
The command failed.
Command> autocommit 0;
Command> set passthrough 2;
Command> select * from v$version;
5220: Permanent Oracle connection failure error in OCIServerAttach(): ORA-12154
: TNS:could not resolve the connect identifier specified rc = -1
5131: Cannot connect to Oracle database: connect_string = “orcl11g”, uid = “KAM
US”, pwd is hidden
The command failed.

第一个2206错误是正常的，因为在创建DSN的时候，默认的passthrough值为0，表示不会将SQL发送到后台的Oracle数据库中，而TT实例中又没有v$version表，所以会报not found的错误。需要先将passthrough设置为1或者2或者3（每个数字的含义请自行查阅文档）。
第二个5220错误，就比较诡异了。在文档中一直描述DSN的OracleID值可以是后台Oracle数据库的SID或者是Service Name，但是实际上这个值却必须是tnsnames.ora文件中指向Oracle数据库的TNS名称。这也是为什么在TimesTen服务器中至少需要安装Oracle Client端的原因。

修改完DNS配置，再次执行，返回了数据库中的记录。

Command> select * from v$version;
< Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production >
< PL/SQL Release 11.1.0.6.0 - Production >
< CORE 11.1.0.6.0 Production >
< TNS for 32-bit Windows: Version 11.1.0.6.0 - Production >
< NLSRTL Version 11.1.0.6.0 - Production >
5 rows found.

6. 创建Readonly Cache Group

–设置cache administration user ID
Command> call ttCacheUidPwdSet(’kamus’,'oracle’);

–启动cache agent
Command> call ttCacheStart;

–创建cache group，必须显示指定跟后台oracle数据库中表结构一样的TT表结构，Primary Key必须存在
Command> CREATE READONLY CACHE GROUP readcache
>AUTOREFRESH INTERVAL 5 SECONDS
>FROM dept
>(DEPTNO NUMBER(2,0) NOT NULL PRIMARY KEY, DNAME VARCHAR2(14), LOC VARCHAR2(13));

–将Oracle的表数据加载到TT表中
Command> LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS;
4 cache instances affected.

–检索TT表，确认数据已经加载完毕
Command> select * from dept;
< 10, ACCOUNTING, NEW YORK >
< 20, RESEARCH, DALLAS >
< 30, SALES, CHICAGO >
< 40, OPERATIONS, BOSTON >
4 rows found.

–检查cache group设置
Command> cachegroups;

Cache Group KAMUS.READCACHE:

Cache Group Type: Read Only
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Seconds

Root Table: KAMUS.DEPT
Table Type: Read Only

1 cache group found.

7. 测试Cache Group的功能

–在Oracle表中插入2条数据
SQL> insert into dept values(50,’CONSULTING’,'BEIJING’);
SQL> insert into dept values(60,’SUPPORT’,'SHANGHAI’);

–检查TT表，由于是5秒刷新间隔，几乎是立刻就可以看到TT表中已经存在这2条数据了
Command> select * from dept;
< 10, ACCOUNTING, NEW YORK >
< 20, RESEARCH, DALLAS >
< 30, SALES, CHICAGO >
< 40, OPERATIONS, BOSTON >
< 50, CONSULTING, BEIJING >
< 60, SUPPORT, SHANGHAI >
6 rows found.

–停止cache agent，此时cache group将不再会自动刷新
Command> call ttCacheStop;

–通过passthrough在TT中执行insert操作，可以看到成功插入一条记录，但是TT表中却仍然只有6条。此处passthrough必须设置为2，原因请自行查阅文档。
Command> autocommit 0;
Command> passthrough 2;
Command> insert into dept values(70,’APPS’,'NANJING’);
1 row inserted.
Command> select * from dept;
< 10, ACCOUNTING, NEW YORK >
< 20, RESEARCH, DALLAS >
< 30, SALES, CHICAGO >
< 40, OPERATIONS, BOSTON >
< 50, CONSULTING, BEIJING >
< 60, SUPPORT, SHANGHAI >
6 rows found.

–在oracle表中检索，发现记录数增加了。
SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 CONSULTING BEIJING
60 SUPPORT SHANGHAI
70 APPS NANJING

7 rows selected.

–再次启动cache agent，重新检索TT表，发现这次记录数也增加了。
Command> call ttcachestart;
Command> select * from dept;
< 10, ACCOUNTING, NEW YORK >
< 20, RESEARCH, DALLAS >
< 30, SALES, CHICAGO >
< 40, OPERATIONS, BOSTON >
< 50, CONSULTING, BEIJING >
< 60, SUPPORT, SHANGHAI >
< 70, APPS, NANJING >
7 rows found.

最后，再给一个推荐SQL Developer的理由，还有哪个产品可以用图像化的界面管理Timesten呢？

2 responses so far

Sep 16 2008

SQL Test Case Builder

Published by kamus under Oracle RDBMS

在Oracle11gR1中出现的很多新功能，现在都已经backport到最新的Oracle 10.2.0.4中，比如之前有谈到的Real Applcation Testing，而这次有助于客户更简单的寻求Oracle技术支持帮助的SQL Test Case Builder（TCB）也可以在Oracle 10.2.0.4中使用了。

一. 什么是Test Case Builder

TCB是Oracle数据库Fault Diagnosability Infrastructure架构中的一部分，关于FDI的架构可以参看Oracle 11gR1 Administrator文档中的相关章节。

我们先描述一个场景，假如你是某个系统的数据库管理员，忽然有一天，你发现系统中的某个SQL运行变得非常缓慢，但是在简单检查之后却无法发现问题的实际原因，因此你需要寻求Oracle技术支持的帮助，或者说，你需要一个更NB的朋友的帮忙。你需要做的是，详细地向他描述你这个SQL是怎样的，如果有绑定变量，那么值是什么，牵涉到的表结构是怎样的，数据量有多少，数据如何分布的，统计信息如何收集的，这个SQL的执行计划是怎样的，等等等等一系列情况，如果大家有经验在Oracle Metalink上寻求过Oracle Support的支持，那么应该知道这就几乎跟提供所有的trace一样繁琐。。。但是我们不能责骂对方，因为如果没有完备的信息，对方就无法重现你的错误，无法重现也就意味着即使给你提供了解决方案，那也都是基于猜测的。

能不能有一个工具，我们只需要告诉它我哪个SQL有问题了，它就能自动抓取并存放下足以重现这个问题的所有相关数据呢？好吧，TCB完全满足你的这个需求。

TCB究竟抓取了哪些数据呢？借用一下Oracle Optimizer Team的blog中相关文章中的描述。

1. Permanent information

* SQL text
* PL/SQL functions, procedures, packages
* Statistics
* Bind variables
* Compilation environment
* User information (like privileges)
* SQL profiles, stored outlines, or other SQL Management Objects
* Meta data on all the objects involved
* Optimizer statistics
* The execution plan information
* The table content (sample or full). This is optional.

2. Transient information

… in addition to the permanent information, SQL Test Case Builder captures transient information, e.g. information that is only available as part of the compilation of the SQL statement. This includes dynamic sampling results, cached information, some run time information, like the actual degree of parallelism used, etc.

二. 如何使用Test Case Builder

使用TCB很简单，只需要简短的几个步骤就可以。下面我们从Oracle 10.2.0.4中导出一个SQL的相关数据，再在Oracle 11.1.0.6中重现。

1. 从Oracle 10.2.0.4中导出SQL

  1. -- 创建所需要的Directory，TCB抓取的数据将存放在这个目录中。
  2. SQL> create or replace directory TCB_DIR_EXP as 'D:\oracle\tmp';
  3.  
  4. Directory created.
  5.  
  6. SQL> grant read,write on directory TCB_DIR_EXP to scott;
  7.  
  8. Grant succeeded.
  9.  
  10. -- 使用dbms_sqldiag包有些限制，为了方便直接授予scott用户dba角色
  11. SQL> grant dba to scott;
  12.  
  13. Grant succeeded.
  14.  
  15. --创建一个包含问题SQL语句的包，当然我们也可以忽略这个步骤，而在下一步中指定该SQL在系统中的sql_id
  16. create or replace package define_vars is
  17.   sql_stmt1     varchar2(2000) := q'# select ename,dname,job from emp, dept
  18.                         where emp.deptno= dept.deptno
  19.                                     #';
  20. end;
  21. /
  22.  
  23. --开始导出，在export_sql_testcase存储过程中有更多的参数可以选择，具体可以参看$ORACLE_HOME/rdbms/admin/dbmsdiag.sql
  24. declare
  25.   tco           clob;
  26. begin
  27.   -- Export test case
  28.   dbms_sqldiag.export_sql_testcase
  29.   (
  30.     directory           => 'TCB_DIR_EXP',
  31.     sql_text            => define_vars.sql_stmt1,
  32.     user_name           => 'SCOTT',
  33.     exportData          => TRUE,
  34.     testcase            => tco
  35.   );

详细看一下，结果文件中都包含了哪些内容。

D:\oracle\tmp>ls
ORATCB1_009F000D0001DPEXP.DMP oratcb1_009F000D0001sql.xml
README.txt oratcb1_009F000D0001ssimp.sql
oratcb1_009F000D0001dpexp.log oratcb1_009F000D0001xpl.txt
oratcb1_009F000D0001dpexp.sql oratcb1_009F000D0001xplf.sql
oratcb1_009F000D0001main.xml oratcb1_009F000D0001xplo.sql
oratcb1_009F000D0001ol.xml oratcb1_009F000D0001xpls.sql

其中README.txt文件是非常体贴的设计，里面记录了这些文件各自包含的内容，同时也写明了如果在另外一个库中要导入这些文件需要执行的SQL语句。.DMP文件是TCB使用datapump技术导出的SQL相关表和数据。.xml包含了需要执行的SQL语句，该SQL语句的执行计划。

注意，在这一系列文件中一定有一个命名为oratcbn_xxxxxxmain.xml的文件，描述了所有需要导入的信息文件的名称，而这个文件名也是在下一步导入的时候需要作为参数输入的。

2. 导入到Oracle 11.1.0.6中

  1. --同样需要先创建一个directory，因为在我的测试环境中是同一台机器，因此这个目录跟上面的导出目录是一样，当然在真实环境中可以是完全不同的两台机器，在这个目录中应该包含上面导出步骤中生成的所有文件，这可以通过任何文件传输的方法来实现，比如cp或者ftp。
  2. SQL> create or replace directory TCB_DIR_IMP as 'D:\oracle\tmp';
  3.  
  4. Directory created.
  5.  
  6. --将目录的读写权限赋予一个新的用户，注意，这个用户下面没有任何上述SQL能够正确执行需要的表，没有dept表也没有emp表。
  7. SQL> grant read,write on directory TCB_DIR_IMP to kamus;
  8.  
  9. Grant succeeded.
  10.  
  11. --以该用户登录，导入SQL
  12. conn kamus
  13.  
  14. begin
  15.   -- Import test case
  16.   dbms_sqldiag.import_sql_testcase
  17.   (
  18.     directory           => 'TCB_DIR_IMP',
  19.     filename            => 'oratcb1_009F000D0001main.xml',
  20.     importData          => TRUE
  21.   );
  22.  
  23. end;
  24. /
  25.  
  26. --检查一下结果，导入过程中TCB自动创建了相关的表和索引。
  27. SQL> col object_name for a20
  28. SQL> select object_name, object_type, created from user_objects
  29.   2  where trunc(created)=trunc(sysdate);
  30.  
  31. OBJECT_NAME          OBJECT_TYPE         CREATED
  32. -------------------- ------------------- ------------
  33. DEFINE_VARS          PACKAGE             16-SEP-08
  34. PK_EMP               INDEX               16-SEP-08
  35. PK_DEPT              INDEX               16-SEP-08
  36. EMP                  TABLE               16-SEP-08
  37. DEPT                 TABLE               16-SEP-08

三. 结论
有了TCB之后，在要求远程的某人帮助调整一个SQL或者一段PL/SQL的时候，只需要将相关内容exp出来，然后将结果文件的存放目录打个包，给对方发过去就OK了。

One response so far

Aug 29 2008

问题拾遗

Published by kamus under Oracle RDBMS

今天在itpub上参与了两个比较有趣的小问题，记录一下。

1. sys_guid()问题
sys_guid()这个函数在Windows版本的Oracle数据库中和Linux版本的数据库中表现不同，在Windows中会返回随机的GUID，而Linux中则始终是相同的GUID。

2.Storeoutline问题
在Oracle 10.2.0.4版本中，Storeoutline似乎有功能上的bug，当然现在还不能确认。

No Tags

No responses yet

Page 1 of 2512345»...Last »