Archive for the ‘Oracle’ Category
Monday, April 21st, 2008
开始，在master数据库上创建一个可选的快照日志log。如果你不想做快速刷新，你就不需要创建日志log。
但是有一点需要注意快速刷新不支持复杂的查询。
在快照站点snapshot site上创建一个snapshot/materialized 视图。具体查看下面这个例子：
MASTER SITE:
SQL> create materialized view log on table EMP;
SNAPSHOT SITE:
SQL> create materialized view emp
refresh fast ...
Posted in Oracle | No Comments »
Saturday, February 2nd, 2008
Oracle中列出指定数据表的全部索引列，可参考如下sql:
/* ************************************************************* */
/* Oracle中列出指定数据表的全部索引列 */
/* */
/* ************************************************************* */break on index_name skip 1
col index_name format a30
col uniuenes format a12
col column_name format a30
prompt Indexes for table: &&1
select c.index_name, i.uniqueness, c.column_name
from user_indexes i, user_ind_columns c
where i.index_name = c.index_name
and i.table_name = upper('&&1')
order by c.index_name, c.column_position
/
(the end)
Posted in Oracle | No Comments »
Tuesday, January 22nd, 2008
Oracle数据库性能调优中，我们需要观察缓存命中率(Measure the Buffer Cache Hit Ratio
)时，可以使用下面的语句:
rem-----------------------------------------------------------------------
rem 测量缓存命中率
rem -----------------------------------------------------------------------
-- 获取初始缓存命中率...
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/
-- 我们人为来增加缓存命中率...
DECLARE
v_dummy dual.dummy%TYPE;
BEGIN
...
Posted in Oracle | No Comments »
