-
编辑 删除
Cache Buffers chains,存在共享模式？
Latch cache buffers chains大约是Oracle中child latch数量最多，使用最为频繁的闩锁了。其子闩总数受到初始化参数(8i中的db_block_buffers/4)的影响，Oracle中有大量kernel函数有机会接手持有cache buffer chains l...
-
编辑 删除
_shared_pool_reserved_pct or shared_pool_reserved_size with ASMM
http://www.oracledatabase12g.com/archives/_shared_pool_reserved-or-shared_pool_reserved_size-with-asmm.html共享池是Oracle著名的SGA的一个重要组成部分，当我们尝试从共享池中分配较大的连续区域时(默认来说...
-
编辑 删除
LINUX 查看真正PGA占用的内存大小
也不知道起什么题目好，PGA的内存是进程私有内存，如何查看进程真正占有的操作系统内存，是不是依据v$process.PGA_ALLOC_MEM就可以，还是还需要通过其他手段。select PGA_ALLOC_MEM/1024/1024 MEM from v$process O...
-
编辑 删除
数据库迁移，spm baseline 保持执行计划的稳定性
目前负责的数据库压力过大，要把其中一个比较核心的SCHEMA迁移到另一套环境里来降低压力。迁移前后的版本是一样的，都是11.1.0.7.统计信息也是通过DBMS_STATS包导出导入的，按理说，执行计划变化的可能性不大。但是...
-
编辑 删除
如何创建SQL 调优集（二） 从AWR加载
Example 1 --From SNAP ID 1,2 Load sql id dmqch2g6rtvzf with plan_hash_value --and its sql plan and all execution statistics --into STS test2 . --This will create an empty SQL Tuning set test2. exec D...
-
编辑 删除
如何创建SQL 调优集（—） 从共享池加载
1）创建一个新的SQL 调优集，新创建的调优集是空的，后面需要把SQL STATEMENT加载进来exec dbms_sqltune.create_sqlset(sqlset_name=>'vodka_set');2）从共享池加载SQL 调优集DECLARE cur DBMS_SQLTUNE.SQL...
-
编辑 删除
11.1.0.7 row cache lock 修改用户名密码 bug 7715339
This note gives a brief overview of bug 7715339. The content was last updated on: 27-JUL-2011 Click here for details of each of the sections below.Affects:Product (Component)Oracle S...
-
编辑 删除
_optimizer_invalidation_period参数,11G以后不起作用了？
select count(*) from foo;alter system set "_optimizer_invalidation_period" = 100 scope=memory;exec dbms_stats.gather_table_stats(null,'foo',no_invalidate => DBMS_STATS.AUTO_INVALIDATE)!sleep 100se...
-
编辑 删除
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过程中，会在...
