求助 oracle 吃cpu

4pal

最初由 sanow 发布
[B]

能否详细说一下怎么没有用绑定变量？ [/B]

declare l_number number;
begin
    for i in 1 .. 10000
    loop
        l_number:=dbms_random.random;
        execute immediate 'insert into t values ( '||l_number||','||l_number||','||l_number||','||l_number||')';
    end loop;
    commit;
end;

lovewwd

再次感谢各位兄弟，特别是4pal。
必须介绍一下应用的情况：
1. 因考虑到开发效率，对数据库操作作了封装，全部采用proc动态sql, 记得动态sql是每次都要重新解析的，不知道是不是造成hard parse的原因？。而且，这样的话，是不是我的shared_pool_size 还不能取的太小？

2. 至于逻辑读和回滚比较高的情况，那跟应用有关，省不下来，索引已优化过，full table scan 很少了。

不知道说的对不对，shared_pool_size ，和db_block如何调整

4pal

记得动态sql是每次都要重新解析的

没有这样的说法，但是我估计你们动态生成的sql语句，跟我举的例子是差不多的。比如 insert into temp values (1); insert into temp values (2); ......insert into temp values (N); 是完全不同的sql语句，肯定要硬解析的说。因此为什么不用insert into temp values (:a);这样的也可以是动态sql语句来代替呢？
shared_pool_size的增大，只能延缓瘫痪的时间，但会造成更加严重的恶果，也纵容了不好好写代码的惰性

第2点你说的对，跟应用有关。俺们不知道你们的应用，但我感觉 返回的结果集太大，是不是真的需要这么多的查询数据呢？

hexianguo

最初由 lovewwd 发布
[B]第一次tunning , 不懂的地方多多，大家一定指正呀   

仓促中只统计了3小时数据，不知道前辈们能看出什么毛病来吗？
忘了说机器配置
true64 （2 cpu）
oracle 816  
2G memeory
磁盘阵列几十个G吧

贴不上来，用附件了 [/B]

db_block_size                 4096
严重地太小了！！！ORACLE数据块大小对数据库性能会产生整体地重大地影响。4K的配置是我所见过的最小配置，包括测试数据库、开发数据库，也是安装ORACLE的默认值。
    我想你应该设置为256K左右吧。具体的设置你可以再请教一下有经验的专家，有机会时也可以自己做一下实验。
    遗憾的是数据库一旦创建，该值就不允许再修改了。重新创建数据库时设置为合适的值。
    类似地，我想你的默认存储参数设置的也不一定合适。

hexianguo

最初由 4pal 发布
[B]没有这样的说法，但是我估计你们动态生成的sql语句，跟我举的例子是差不多的。比如 insert into temp values (1); insert into temp values (2); ......insert into temp values (N); 是完全不同的sql语句，肯定要硬解析的说。因此为什么不用insert into temp values (:a);这样的也可以是动态sql语句来代替呢？
shared_pool_size的增大，只能延缓瘫痪的时间，但会造成更加严重的恶果，也纵容了不好好写代码的惰性

第2点你说的对，跟应用有关。俺们不知道你们的应用，但我感觉 返回的结果集太大，是不是真的需要这么多的查询数据呢？ [/B]

4pal 的分析很精辟。
   另外，进行ORACLE优化的顺序应该是
（1）进行应用系统设计的优化
（2）应用系统SQL语句编写的优化
（3）内存优化
（4）。。。。。（其他优化内容）
    应该使用绑定变量的地方没有使用，使用了过多或者过少的全表扫描，这些是（1）（2）中应该优化的内容。对（1）（2）完成优化后，在此基础上进行内存优化才是正常的顺序。
    靠内存优化只能缓解或部分解决由于（1）（2）未优化造成的影响，并不能彻底解决。对于有些情况也是无能为力的。正如4PAL举的例子，用绑定变量则是1个SQL，用常量则是10000个SQL。这点也不全是夸张，假设程序中用身份证号做为条件查询个人信息就完全可能有类似情况。

4pal

最初由 hexianguo 发布
[B]

db_block_size                 4096
严重地太小了！！！ORACLE数据块大小对数据库性能会产生整体地重大地影响。4K的配置是我所见过的最小配置，包括测试数据库、开发数据库，也是安装ORACLE的默认值。
    我想你应该设置为256K左右吧。 [/B]

NONONONONONONONONONONONONONONONO，别乱说

一般这个参数就徘徊在2K~8k之间

wwsccd

提个另外原因：
实例内存设置不合理，总SGA1.6G，物理内存2G，只有400M留给系统及用户连接使用，在总内存超过2G时引起系统内存交换，所以在关闭系统后仍慢。
建议将shared_pool_size设为100M，db_block_buffers设为800M。
另外改应用程序为绑定变量是很费时的，而应用开发人员也不一定愿改(我就是一开发人员)，设shared_pool_size为100M对这种情况会有好处的。

hexianguo

最初由 4pal 发布
[B]

NONONONONONONONONONONONONONONONO，别乱说

一般这个参数就徘徊在2K~8k之间 [/B]

基本同意。我查了一下ORACLE文档，8i版本中的默认值是2K或4K，不超过64K。


以下是教材[M09213]第9-10页的说明。另外要考虑和OS块之间的关系，对于盘阵要考虑和STRIP间的关系。

The default block size on most Oracle platforms is either 2 or 4 KB.
Some operating systems now allow block sizes of up to 64 KB. To configure the block size, check the documentation specific to your operating system, and particularly the Oracle installation and configuration guide for your platform.



Small Oracle Blocks
Advantages
• Small blocks reduce block contention because there are fewer rows per block.
• Small blocks are good for small rows.
• Small blocks are good for random access. Because it is unlikely that a block will
be reused after it is read into memory, a smaller block size makes more efficient
use of the buffer cache. This is especially important when memory resources are
scarce, because the size of the database buffer cache is limited.

Disadvantages
• Small blocks have relatively large overhead.
• You may end up storing only a small number of rows per block, depending on the
size of the row. This can cause additional I/Os.
• Small blocks can cause more index blocks to be read.

Large Oracle Blocks
Advantages
• There is relatively less overhead and thus more room to store useful data.
• Large blocks are good for sequential reads.
• Large blocks are good for very large rows.
• Larger blocks improve performance of index reads. The larger blocks can hold
more index entries in each block, which reduces the number of levels in large
indexes. Fewer index levels mean fewer I/Os when traversing the index branches.

Disadvantages
• A large index block size is not good when used in an OLTP type of environment,
because it increases block contention on the index leaf blocks.
• Space in the buffer cache is wasted if there are random accesses to small rows and
in a large block. For example, with an 8 KB block size and a 50-byte row size, you
would be wasting 7,950 bytes in the buffer cache if there were random accesses.
If you resize database blocks and there is no additional memory, you also need to reset
DB_BLOCK_BUFFERS. This affects the cache hit percentage.

melocy

分析精辟。
关注ing
这是ITPUB少有的好贴。
建议置顶。

husthxd

受益非浅!继续关注ing