鄙人菜鸟遇一oracle的问题,先贴sql语句: select round(100 * (1 - (a.bytes / b.value)), 2) MemoryUsage from v$sgastat a, v$parameter b
where a.pool = 'shared pool' and a.name = 'free memory' and b.name = 'shared_pool_size'当程序运行到调用该sql语句的时候,由于系统表v$parameter.value=0(b.value=0)抛出异常,除数不能为0.有没有哪位大神 能给我一个方法 得到当b.value=0是,查出memoryUsage的值。
where a.pool = 'shared pool' and a.name = 'free memory' and b.name = 'shared_pool_size'当程序运行到调用该sql语句的时候,由于系统表v$parameter.value=0(b.value=0)抛出异常,除数不能为0.有没有哪位大神 能给我一个方法 得到当b.value=0是,查出memoryUsage的值。
select bytes from v$sgainfo where name='Shared Pool Size';
fw0124,,,select bytes from v$sgainfo where name='Shared Pool Size' 查出来的东西等价于 select round(100 * (1 - (a.bytes / b.value)), 2) MemoryUsage from v$sgastat a, v$parameter b
where a.pool = 'shared pool' and a.name = 'free memory' and b.name = 'shared_pool_size'查出来的东西吗?
select bytes from v$sgainfo where name='Shared Pool Size';
查出来的东西替换你的b.value。
哥们 谢谢你的回答,,
我想确定一下 在自动内存管理的情况下 select bytes from v$sgainfo where name='Shared Pool Size'; 查出来v$sgainfo.bytes,然后这个v$sgainfo.bytes是等价于 不是自动内存管理情况下 select value from v$parameter where name = 'shared_pool_size' 查询出来的v$parameter.value.你是这个意思吧?因为我也不知道这个情况怎么验证。
我在网上问的另外一个哥们 他说,在自动内存管理的情况下 可以用select current_size from v$sga_dynamic_components where COMPONENT='shared pool' 来替换v$parameter.value的值呢。。你觉得他说得对吗?
对,这两个查出来结果应该是一样的。tony@ORCL1> select current_size from v$sga_dynamic_components where component='shared pool';CURRENT_SIZE
------------
125829120tony@ORCL1> select bytes from v$sgainfo where name='Shared Pool Size'; BYTES
----------
125829120
亲,,问题基本上是你这样解决的。。
问最后一个问题,怎么在一个sql语句里面实现:
当v$parameter=0时,select round(100 * (1 - (a.bytes / b.bytes)), 2) MemoryUsage from v$sgastat a, v$sgainfo b where a.pool = 'shared pool' and a.name = 'free memory' and b.name = 'shared pool size'
当v$parameter不等于0时,select round(100 * (1 - (a.bytes / b.value)), 2) MemoryUsage from v$sgastat a, v$parameter b where a.pool = 'shared pool' and a.name = 'free memory' and b.name = 'shared_pool_size'。我问了一个人说用case when语句,我表示oracle菜鸟自己研究压力大呀,谢谢大神~~
sgainfo as (select 1 as id, bytes as val from v$sgainfo where name = 'Shared Pool Size'),
sgastat as (select 1 as id, bytes as val from v$sgastat where pool = 'shared pool' and name = 'free memory')
select round(100 * (1 - (sgastat.val / (case param.val when '0' then sgainfo.val else (0+param.val) end))), 2) MemoryUsage
from param, sgainfo, sgastat where param.id=sgainfo.id and param.id=sgastat.id;
我只能说我爱死你了~~大神
去http://topic.csdn.net/u/20120813/16/391076ad-b4fa-4817-9c8e-d000ad59a004.html 这留个言我结贴送分吧~~都没人鸟我这贴子~~~哈哈~~~~