-
编辑 删除
dbms_shared_pool.purge在11.1.0.7的bug
select OBJECT_STATUS,address,hash_value,executions,parse_calls,IS_SHAREABLE from v$sql where sql_id='9tgj4g8y4rwy8';OBJECT_STATUS &nbs...
-
编辑 删除
11G R2 V$SQL_MONITOR查看绑定变量
Read bind variable values of currently executing SQLIf you are running on Oracle 11.2+ and have the licenses for Oracle Diagnostics & Tuning Packs and when Oracle's SQL monitoring feature actuall...
-
编辑 删除
10G后查看没有绑定的文本的方法
http://intermediatesql.com/oracle/a-better-way-to-find-literal-sqls-in-oracle-10g/Not sure if it is news, but here we go …I was recently looking at hard parsing SQL statements overtaking one of my...
-
编辑 删除
获取查询块名字
http://intermediatesql.com/oracle/how-to-add-a-hint-to-oracle-query-without-touching-its-text/
-
编辑 删除
baseline依赖SQL文本还是SQL ID?
create table t1 (col1 number);select /*+ find_me */ * from t1 where col1 = 3;select /*+ find_me */ * from t1 where &...
-
编辑 删除
awr baseline
Generating Various Types of AWR ReportsAWR reports can be generated by running various SQL scripts to satisfy various requirements. Each report is available in HTML or TXT format: &nb...
-
编辑 删除
dbms_stats Invalidation
While working on a demo a while back I discovered the hard way that things do indeed change! Of course this is nothing new but by sharing my experience I hope to avoid others re-inventing the same whe...
-
编辑 删除
伪造baseline
SQL Baslines in 11g are the new Stored Outlines – and one of the nicest features of SQL Baselines is that you are allowed to fake them; or rather, it’s legal to generate an execution plan for one qu...
-
编辑 删除
dbms_xplan.display_cursor管道函数
One of the questions that pops up on the internet from time to time is the one about finding SQL that’s doing full tablescans. Since the appearance of the dynamic performance view v$sql_plan in Orac...
-
编辑 删除
通过dbms_stats包修改统计信息里的最大值最小值，等频等高直方图
-------------------------------------------------------------------- 创建测试表-------------------------...
-
编辑 删除
用python了一个监控ORACLE逻辑读等参数的小程序
import osimport cx_Oracleimport timedef getcon(): connection= cx_Oracle.connect('system/a35baba@crmgtest'); return connection;conn=getcon();sql="""sele...
-
编辑 删除
删除列过程中，需要持有对象的锁
SESSION 1:alter table wxh_tbd drop column object_name;SESSION 2:alter session set events 'immediate trace name library_cache level 10'; 查看dump文件，搜索APOLLO.WXH_TBD，看看执行这个DDL过程中，会在...
-
编辑 删除
通过swap_join_inputs来控制多表连接hash join中的build table
hash_join可以通过no_swap_join_inputs/swap_join_inputs来强制控制build表，配合leading或者ordered可以控制多表之前的连接顺序----------------创建4个测试表create table t1 as select * from dba_objects;create...
-
编辑 删除
分区统计信息
----------------------创建测试表create table t1 ( part_col not null, id not null, small_vc, padding )partition by range(part_col) ( partition p0200 v...
-
编辑 删除
disable or 扩展
SQL> select /*+ use_concat */ * from hao2 where object_id=1 or object_id=2 or object_id=3;执行计划----------------------------------------------------------Plan hash value: 2739802529---------------...
-
编辑 删除
push_pred用法
create table hao1 as select * from dba_objects;create table hao2 as select * from dba_objects;create table hao3 as select * from dba_objects;create index hao3idx on hao3(object_id);create index hao2id...
-
编辑 删除
no_merge的含义
create table wxh_tbd1 as select * from dba_objects;create table wxh_tbd2 as select * from dba_objects;create index ind_oi on wxh_tbd1(object_name);create index t_22 on wxh_tbd1(object_id);create inde...
-
编辑 删除
nest loop内层循环会按照rowid排序吗？
create table wxh_tbd1 as select * from dba_objects;create table wxh_tbd2 as select * from dba_objects;create index t_1 on wxh_tbd1(object_name);create index t_2 on wxh_tbd1(object_id);create index t_3...
-
编辑 删除
swapping join input
select * from wxh_tbd1 a ,wxh_tbd2 b where a.id=b.id(+);对于如上查询，在10G以前，执行计划的结果只可能为(我们只考虑hash join)：--------------------------------------------------------------------------...
-
编辑 删除
新增分区+导入统计信息导致的问题
最近出了一个问题，由于新增分区后，导入了表上一个分区的统计信息做为新分区的统计信息，一条SQL走错了。类似如下的查询select * from a where b=1 and c>sysdate-7。由于c列是个日期型。导入的统计信息记录的最大...
