除了ORION, 如果想更真实测试存储的IO能力, Kevin Closson的工具SLOB(Silly Little Oracle Benchmark)可能会很有用. 见他的博客介绍, 需要翻墙访问.
http://kevinclosson.wordpress.com/2012/02/06/introducing-slob-the-silly-little-oracle-benchmark/
下面是下载链接, 配置很简单, 看一下README-FIRST就知道了.
http://oaktable.net/articles/slob-silly-little-oracle-benchmark
使用SLOB, 不同类型的测试只需要控制db_cache_size大小.
Continue reading
这篇文章源于和Kamus的讨论, 出现’gc current blocks received’的一种特殊情况. 当select语句请求gc cr时, 为什么会出现等待事件’gc current block 2-way’, 同时’gc current blocks received’统计值增加?
一个gc cr请求的出现, 常见的原因是数据块存在于其他实例的缓存中. 出现gc cr请求时, 可能会出现三种情况
1. 请求的数据块不在gc中
2. 请求的数据块在gc中且状态是xcur
3. 请求的数据块在gc中且状态是scur
Continue reading
This two weeks, I encountered an interesting case related to the function UTL_FILE.GET_LINE. One data loading application failed on ORA-04030 error, the physical free memory on the DB server is exhausted, by the server process 13225.
Errors in file /home/oracle/diag/rdbms/V11/V11/trace/V11_ora_13225.trc (incident=16305):
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pl/sql vc2)
ORA-29282: invalid file ID
$free -m
total used free shared buffers cached
Mem: 16050 15960 90 0 126 3777
-/+ buffers/cache: 12056 3994
Swap: 4095 0 4095
$top
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
13225 oracle 15 0 10.2g 4.0g 51m S 0.0 25.8 29:05.39 oracle
1278 root 17 0 1602m 308m 9508 S 0.0 1.9 37:45.32 java
coe_load_sql_baseline.sql是Oracle在SQLT中提供的一个脚本(Note 215187.1), 在11g中可以用来锁定执行计划. 10g对应的脚本是coe_load_sql_profile.sql.
DESCRIPTION
This script loads a plan from a modified SQL into the SQL Plan Baseline of the original SQL. If a good performing plan only reproduces with CBO Hints then you can load the plan of the modified version of the SQL into the SQL Plan Baseline of the orignal SQL. In other words, the original SQL can use the plan that was generated out of the SQL with hints.PRE-REQUISITES
1. Have in cache or AWR the text for the original SQL.
2. Have in cache the plan for the modified SQL
(usually with hints).PARAMETERS
1. ORIGINAL_SQL_ID (required)
2. MODIFIED_SQL_ID (required)
3. PLAN_HASH_VALUE (required)
数据类型转换是数据库设计和开发中常见的错误, 经过型转换, 优化器可能无法精确地估算cardinality和cost, 导致次优的执行计划, 这里讨论三种情况:
1. Varchar2 –> Number: 如果把数字存为字符串, 查询的使用Number类型, 优化器会使用to_number函数把存储的字符串转为数字. 这种情况除了修改代码, 建立To_number的函数索引也可以解决.
2. Date –> Timestamp: 这是我见得最多的, 一些Java开发工具像Toplink, 默认时间类型是timestamp, 如果数据存储使用Date类型, 比较时优化器会加上internal_function转换, 对于internal_function, 无法通过增加函数索引解决.
3. Char –> Varchar2: 这种情况比较隐蔽, 如果不观察执行计划中的绑定变量, 或者用10046时间记录绑定变量, 可能不会发现. Char和Varchar一样的字符串比较, 可能会导致优化器错误的估算.
Continue reading
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small" // *Cause: rollback records needed by a reader for consistent read // are overwritten by other writers // *Action: If in Automatic Undo Management mode, increase // undo_retention setting. Otherwise, use larger rollback segments
出现ORA-01555直接的原因是一致读所需的undo records被覆盖, 一致读失败有两种情况:
1. 数据块中ITL结构对应的undo block被覆盖, 无法构造一致读.
2. transaction table in undo segment header: 延迟块清除发生时, 如果Oracle需要回滚对应的transaction table, 找到事务确切提交的时间. 而且所需的undo record被覆盖, ORA-01555也会发生.
Continue reading