-
编辑 删除
指定LOB参数RETENTION,PCTVERSION的一个小BUG
原文链接：http://www.biti-feng.me/2011/01/30/%E6%8C%87%E5%AE%9Alob%E5%8F%82%E6%95%B0retentionpctversion%E7%9A%84%E4%B8%80%E4%B8%AA%E5%B0%8Fbug/偶然发现，用dbms_metadata.get_ddl()抓取带LOB 的TABLE建...
-
编辑 删除
no_unnest,push_subq用法小试
create table t1 as select * from dba_objects;Table created.create table t2 as select * from dba_objects;Table created.create table t3 as select * from dba_objects;Table created.create index w_1 ...
-
编辑 删除
唯一索引导致的死锁
一个事务可以通过回滚段号+槽号+序列号被唯一标示。V$LOCK中ID1代表的是回滚段号+槽号，ID2代表序列号。 举例：假如ID1为1048576，根据以下方法可以换算成回滚段号和槽号。ID2不需要换算。QL> select trunc(10485...
-
编辑 删除
通过v$sql_bind_capture 查看绑定变量。
最近在排查CRMG性能SQL的过程中，大量的SQL需要确定绑定变量的值，如果一个个联系开发确定，效率太低。还有一些技术手段比如10046，LOGMINER又复杂的要死。大多数人都知道通过v$sql_bind_capture视图，可以查看绑定...
-
编辑 删除
11G，可以只删除直方图信息
Deleting Object StatisticsYou can delete object statistics from the data dictionary. Except for testing purposes, this isusually not necessary. Nevertheless, it might happen that a table should not ha...
-
编辑 删除
11G新特性，比较统计信息
In the following three common situations, you end up with several sets of object statistics forthe very same object:• When you instruct the package dbms_stats (through the parameters statown, st...
-
编辑 删除
11G新特性，待定的统计信息
------------创建测试表apollo@CRMG>drop table wxh_tbd;Table dropped.apollo@CRMG>create table wxh_tbd as select * from dba_objects;Table created.apollo@CRMG>exec dbms_stats.gather_table_stats(user,'wxh_...
-
编辑 删除
user_tab_modifications学习
To recognize whether object statistics are stale, the database engine counts the number of rows modifiedthrough SQL statements for each object. The result of that counting is externalized through the ...
-
编辑 删除
查看统计信息分析每次经历的时间
As of Oracle Database 10g, the procedures in the package dbms_stats working at database, datadictionary, or schema level, log information about their execution in the data dictionary. Thislogging info...
-
编辑 删除
导入导出统计信息
-----------创建中间表begindbms_stats.create_stat_table('test','test_table');end;/-----------查询中间表，确认没有记录testROSE> select count(*) from test_table; COUNT(*)---------- &n...
-
编辑 删除
统计信息历史的备份、还原功能
As of Oracle Database 10g, whenever system statistics or object statistics are gathered throughthe package dbms_stats, instead of simply overwriting current statistics with the new statistics,the curr...
-
编辑 删除
全局索引有缺陷啊
老熊博客里的例子SQL> create table t1 ( a int, b varchar2(300)) partition by range(a) 2 ( 3 partition p01 values less than (1000), 4 partition p02 value...
-
编辑 删除
估算SQL已经执行了多少时间
Oracle 11g introduced V$SESSION.SQL_EXEC_START column.可以用这个sysdate-SQL_EXEC_START 来获取一个ing的SQL执行了多长时间了。SQL_ID &nb...
-
编辑 删除
direct path read 测试
CRMG的数据库等待事件TOP 5里，总会出现一个10G以前几乎不可能在TOP里的等待，direct path read。10G以前这个等待跟并行查询有关。在执行parallel query时，slave session所执行的direct path I/O引发的。如果这个...
-
编辑 删除
DBMS_ADVANCED_REWRITE and DML
原文地址：http://dioncho.wordpress.com/page/12/Oracle 10g has introduced DBMS_ADVANCED_REWRITE package which enables a specific query to be transformed to another form. Very powerful feature, but th...
-
编辑 删除
Function-based Index and Or-Expansion
原文连接：http://dioncho.wordpress.com/page/17/1. Create objects.drop table t1 purge;create table t1(c1 int, c2 int, c3 int);insert into t1select level, level, levelfrom dualconnect by level <= 10...
-
编辑 删除
通过内部的hint来控制执行计划
通过内部的hint来控制执行计划SQL> create table t1(c1 number, c2 number);Table created.SQL> create table t2(c1 number, c2 number);Table created.SQL> create table t3(c1 number, c2 number);Table created....
-
编辑 删除
如何获取绑定变量值
Bind Capture HistoryAs of 10g, Oracle captures the bind value and stores it into the repository(AWR), but with following restrictions.Captured periodically(_cursor_bind_capture_interval), not at rea...
-
编辑 删除
存储过程里调用编译自己是什么情况？
CREATE OR REPLACE procedure test_kgllk as begin execute immediate 'alter procedure test_kgllk compile'; end;/SESSION 1:apolloCRMG>exec test_kgllk;hang在那，...
-
编辑 删除
表压缩技术
我们为了降低表的大小，一般有以下两个手段1） 降低表的pctfree值。极端的情况可以是02） 采用表的压缩技术，即在表创建的时候显式的指定compress关键词。 让我们看看压缩的效果：1） 普通方式的创建表142...
