ITPub博客

经常见到有人在系统设计时用GUID或MD5产生的32位字符串做主键，即使一些按日期产生的业务编码如2001010100001这样的字符串，都用varchar2来存储，总觉得不是很好，如果数据量不大的话，也无可厚非，但如果运用于存储海量的数据，那将会造成存储成本的巨增，请看下面的测试：[@more@]16:58:49 SQL> create table test(a1 varchar2(32),a2 number);Table created.16:59:03 SQL> insert into test values('20010101000001',20010101000001);1 row created.16:59:26 SQL> commit;Commit complete.16:59:27 SQL> select dump(a1),d

如果你的系统中，通过statspack report或awr report，发现这样的SQL执行比较频繁，而且test表的记录数比较多，test表中附合status = '1'的记录数也非常多，占test表记录数90%的比例吧，从statspack report或awr report可以发现该SQL的逻辑读会相当大，假定该表没有任何索引，那DBA该如何着手进行SQL的优化呢？SELECT seller_idFROM (SELECT * FROM (SELECT seller_id, max(gmt_create) FROM test WHERE status = 1 GROUP BY seller_id)ORDER BY gmt_create DESC)WHERE rownum <= 10;[@more@]方案A假如test表需要存储空间4g，在test表的

在11g中，优化器有两种模式：普通模式与调化模式。优化器在普通模式下，当SQL被执行时，查询优化器将生成SQL的执行计划，如果SQL的可选路径很多，优化器必须是限制时间内，选择一个合适的执行计划；当优化器在调优模式下，优化器需要执行额外的分析去判断优化器在普通模式下产生的执行计划是否有可能被改进，此时优化器输出的不是一个执行计划，而是一系列的动作，根据调优的原理、产生一个更优化的执行计划，优化器需发花费一定的时间去调优单个的sql，自动调优的优化器每次查询都是硬解析。[@more@]The Automatic Tuning Optimizer perf

系统变更会影响SQL的执行计划，比如数据库升级，优化参数调整，表结构变更，索引维护（创建，删除，重建），统计信息收集等，Oracle为了预防系统变更所引起的不良后果，特推出SPA，SPA做为RAT(Real Application Test)的一部分，可以消除一些不知预知的错误，Oracle提供dbms_sqlpa包来实现系统变更前后的性能分析，SPA主要应用在以上方面：[@more@]■ Database upgrade■ Configuration changes to the operating system, hardware, or database■ Database initialization parameter changes■ Schema changes, for example, adding new i

在11g中，Real Application Testing（RAT）是一个独立的收费选项，所以当我们需要订购此项服务时，我们该了解该服务有那些限制，否则付了钱，发现有许多地方存在或多或少的冲突，有些冲突的消除可能要花费更大的成本，所以在上项目前，可行性技术研究是必不可少的，如果想使用RAT，需要关注现有的数据库系统是否涉及到如下技术：[@more@]Workload Capture Restrictions:■ Direct path load of data from external files using utilities such as SQL*Loader■ Shared server requests (Oracle MTS)■ Oracle Streams■ Advanced replicat

在Oracle 11g，SMB是SPM的控制端，SMB是数据字典的一部分，SMB存储在sysaux表空间，SMB存储SQL语句日志，Plan Histories，以及SQL Profiles等，SMB允许每周清除一次未使用的SQL Plan & Log，SMB已经配置了自动存储空间管理。[@more@]1 Disk Space UsageSMB默认可以使用sysaux表空间的10%，SMB被允许使用sysaux的存储范围是1%-50%，每周有后台进程来测量SMB使用sysaux的空间，如果SMB超过了允许的范围，就会在al*.log中写入一条警告，可以通过dbms_spm的configure过程设置SMB允许使用存储空间的范围。BEGIN DBMS_SPM.CONFIGURE('space_

在11g，oracle提供dbms_spm包来管理SQL Plan，SPM是一个预防机制，它记录并评估sql的执行计划，将已知的高效的sql执行计划建立为SQL Plan Baselines，SQL Plan Baseline的功能是保持SQL的性能而不必关注系统的改变。[@more@]1 Capturing SQL Plan Baselines在SQL Plan BaseLines捕获阶段，Oracle记录SQL的执行计划并检测该执行计划是否已经改变，如果SQL改变后的执行计划是安全的，则SQL就使用新的执行计划，因此，Oracle维护单个SQL执行计划的历史信息，Oracle维护的SQL执行计划的历史仅仅针对重复执行的SQL，SQL Plan Baseline可以手工l

从11g开始，表与索引的统计信息收集完毕后，可以选择收集的统信息立即发布，也可以选择使新收集的统计信息处于pending状态，待确定处于pending状态的统计信息是安全的，再使处于pending状态的统计信息发布，这样就会避免一些因为收集统计信息立即发布而导致SQL执行计划走错的灾难。[@more@]1 如何判断是否有pending的统计信息需要生效？SQL> Select dbms_stats.get_prefs('PUBLISH') publish from dual;PUBLISH--------------------------TRUEdbms_stats的get_prefs函数返回true，表示对象的统计信息收集后立即生效，如果返回flase，收

在11g中，dbms_stats package提供了组合列的统计信息的收集，如果在where条件中使用了组合列进行查询，则优化器将会得到更准确的统计信息，进而输出执行计划时，可以输出更接近与真实数据的统计结果，11g的Extended Statistics包含了组合列与表达式统计信息的收集，表过式统计信息收集主要是针对函数索引字段，下面就组合列统计信息的收集做一下测试：[@more@]SQL> create index idx_object_objtype on tmp_objects(object_type,owner) compute statistics;Index created.SQL> select index_name,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,

在oracle 10g中，优化统计信息的收集是通过scheduler job来自动完成的，dba可以通过dbms_scheduler package的enable procedure与disable procedure来启动与关闭，在oracle 11g中，oracle提供了全新的package dbms_auto_task_admin来完成统计信息的收集工作，测试脚本如下所示：[@more@]启动：BEGINDBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);END;/关闭：BEGINDBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',op

五 如何在DB中配置DRCP？在11g中，系统默认包含了一个连接池：SYS_DEFAULT_CONNECTION_POOL，这个池默认被创建，但默认该池是不启动的，如果要启动默认的连接池，必须显式地执行启动DRCP的命令，oracle提供dbms_connection_pool package来执行这个工作，如果连接池被显式地启动，必须显式地被停掉，当实例宕掉时，如果DRCP是活动的，则实例启动时，DRCP也将自动实动。启动DRCP需要以下步骤：A 用sysdba权限连接到sqlplusB 执行exec dbms_connection_pool.start_pool();C 查看DRCP的状态[@more@]六 客户端如何连接到DRCP？如果是专用服务器

三Dedicated Servers,Shared Servers与DRCP的内存需求一般情况下，由于每个会话需要消耗400k的内存，每个进程需要消耗4m的内存，现在我们以DRCP的pool size是100，shared server的shared server进程是100为例，假如有5000个客户端连接到这些环境，则这些主机的内存分配如下：A Dedicated Server Memory used = 5000 X (400 KB + 4 MB) = 22 GBB Shared ServerMemory used = 5000 X 400 KB + 100 X 4 MB = 2.5 GBOut of the 2.5 GB, 2 GB is allocated from the SGA.C Database Resident Connection PoolingMemory used = 100 X (400 KB +

在oracle中，进程的频繁创建是一个比较消耗资源的开消，在11g之前，oracle提供专用器进程与共享服务器进程供用户选择适合自己的进程机制，在11g中，oracle又推出DRCP(database resident connection pool)，类似于sqlrelay那种轻量级中间件的进程机制为客户服务，DRCP适用于web application（比如php/apache），可以提供成千上万的连接，使用DRCP可以共享主机资源。[@more@]一 什么时间使用DRCP1 使用较小内存的、大量的客户端连接2 客户端应用是相似的，可以共享或重用会话3 客户端占用数据库连接的周期相当短4 会话不需要跨客户请求5 客户

在11g中，很多功能oracle都引导用户通过OEM来操作，但喜欢用API的dba，照样可以写脚本来完成自己的工作，下面给大家介绍一下database replay的配置方法，database replay可以捕捉整个数据库的负载，并且传递到需要进行测试数据库上，然后重演负载以测试系统调优后的效果，当然也可以为升级硬件进行压力测试。[@more@]A 创建目录create directory sqlplay as /home/oracle11g/worksh/sqlplay';B 启动捕获进程SQL> BEGIN 2 DBMS_WORKLOAD_CAPTURE.START_CAPTURE ( 3 name => 'capture_by_yekai', 4

在oracle 11g以前的版本中，如果对大表进行全表扫描，通过v$session_wait可以看到wait event是：db file scattered read；在11g中，如果对大表进行全表扫描，通过v$session_wait可以看到wait event是：direct path read；也就是说，在11g中，大表全表扫描是将数据块直接读入会话的pga区域。[@more@]10g：[oracle10g@csdba worksh]$ sh list_sql.sh--------------------------------------------------------------------------YEKAI(159,235) ospid=5318 hash_value=1577916882 execs=918 els_time=1.04program=sqlplus@csdba (TNS V1-V3)

在11g以前的版本,SQL的运行情况可以通过监控v$session_longops来了解,当某个操作执行时间超过6秒,就会被v$session_longops感知,通常可以监控到比如全表扫描,全索引扫描,哈希联接,并行查询等;在11g中,当sql并行运行时,马上会被real-time monitor到,当sql单进程运行时,如果运行时间超过5秒,它也会被监控到。[@more@]可以通过v$sql_monitor与v$sql_plan_monitor视图查看sql执行的统计信息，可以联合v$active_session_history，v$session，v$session_longops，v$sql, v$sql_plan等视图，查看sql更多的信息。v$sql_monitor收集关键的一些指标，

11g在awr report方面，还是增加了一些吸引眼球的地方，比如增加了对OS等主机配置的报告，对于dba来讲，在接手一个新环境时,或做现场服务时，该报表将会简少一些额外的工作，比如报表中已经可以显示主机名，操作系统，CPU个数，物理内存等，最让人关注的还是增加了主机负载的一些情况，具体变化请看下文：[@more@]主机方面:Host Name Platform CPUs Cores Sockets Memory(GB)----------- ------------------ ---- ----- ------- ----------log51 Linux IA (32-bit) 4 2 2 3.96LOAD PROFILE:Load Profile Per Second Per Trans Per Exec Per

在11g以前的版本中,如果表上存在未提交的事务,则对表进行DDL操作将失败,在11g中,这个限制已经被取消啦,执行ADD COLUMN操作时,并不会受未提交事务的影响,另外,ADD COLUMN还有一个增强,就是在一个非常大的表上执行ADD COLUMN xxx DATATYPE DEFAULT yyy NOT NULL时,它不会再去将默认值更新到表中已经存在的记录,这样改进的好处是非常大的,DBA对表结构的变更将更加容易,而且不会产生大量的library cache lock/pin等,更不会阻塞事务,它是怎么做到的呢?[@more@]其实现方法很简单,ORACLE引擎仅仅依赖数据字典中记录的字段的默认值来对新增字段中为

在11g中，oracle提供了一个新的可动态调整的参数ddl_lock_timeout，该参数允许存在未提交的事务的表上，执行DDL操作，如果事务在ddl_lock_timeout允许的时间内提交，则DDL操作将会成功，否则就会报ORA-00054错误，如果对该表执行ADD COLUMN操作，则会直接提交表上发生的未提交的事务，所以用11g时，有些东西还是要注意的，当然也不排除这是11g的一个bug哦。。。[@more@]11g以前的版本测试如下：SESSION 1:16:18:34 SQL> delete from tmp_lg_log where rownum < 2;1 row deleted.SESSION 2:16:21:10 SQL> alter table tmp_lg_log

在测试9iR2,10gR1,10gR2的stream时,非常希望能够有种同步capture的机制,该同步capture机制与log-based real-time capture机制是不同的,它应该是一种内部触发的机制,比如当有活动事务时,DB引擎判断该表配置有stream的同步capture标志,就可以直接把dml打包成LCRs并压入队列...[@more@]幸运的是,在10gR1中,已经看到了该功能已经被实现,但通过文档可以看到,oracle建议该功能仅适用于需要复制的表比较少的情况下,也可以理解需要复制的表dml不是很频繁,当然如果是全库进行复制,log-based real-time capture可能更高效一些,毕竟扫描一个500m以上的