Archive for 四月, 2008
星期三, 四月 30th, 2008
10g中CBO优化器已经全面的代替了RBO优化器。CBO优化器的基础就是对象上的统计信息，所以10g中增加
了dynamic sampling的功能，对那些没有统计信息的或者统计信息陈旧的对象进行动态的统计。
一个新的初始化参数optimizer_dynamic_sampling被引入
SQL> show parameter dy
NAME TYPE VALUE
------------------------------------ ----------- -------
optimizer_dynamic_sampling integer 2
optimizer_dynamic_sampling参数控制的是动态分析的取样量，参数的范围是0到10，10g中的默认值是2
。下面做个测试，看看dynamic sampling的效果。
SQL> create table sunwg as select * from dba_objects;
表已创建。
SQL> set autot traceonly
SQL> select count(*) from sunwg;
执行计划
----------------------------------------------------------
Plan hash value: 2736253977
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | ...
Posted in oracle | No Comments »
星期二, 四月 29th, 2008
flashback table最早是在9i中提出的，但是在9i中的仅仅能恢复dml操作。在10g中对flashback table进
行了增加，并且还引入了flashback database的操作。这样DBA又有了一个数据库恢复的强有力的工具。
打开flashback database有如下步骤：
1,确保数据库运行在归档模式
shutdown immediate
startup mount
alter database archivelog
2,配置恢复区(recovery area)相关初始化参数
db_recovery_file_dest
db_recovery_file_dest_size
3,以mount exclusive方式打开数据库，修改flashback database属性
startup mount exclusive
alter database flashback on
4,设置flashback database恢复目标
db_flashback_retention_target
可以通过下面的sql检查数据库是否成功开启flashback database
select flashback_on from v$database
flashback database常用的两个视图是v$flashback_database_stat和v$flashback_database_log。
flashback database可以通过以下两种方法：
1,rman
flashback database to time = to_date ('2008-04-29 15:30:30','yyyy-mm-dd hh24:mi:ss')
2,sql command
flashback database to timestamp(SYSDATE – 5/24)
flashback database to scn 88888
关闭flashback
startup mount
alter database flashback off
最后还有一点要说明的是：
在flashback database之后如果是需要resetlogs的，如果不确定是否flashback到正确的时间点，最好先
用read only方式打开数据库后在resetlogs。
参考：http://www.dbazine.com/olc/olc-articles/liu6
Posted in oracle | No Comments »
星期一, 四月 28th, 2008
19.最近24小时新创建的对象
col OWNER FOR a15
col OBJECT_NAME FOR a30
col SUBOBJECT_NAME FOR a30
col OBJECT_TYPE FOR a20
SELECT OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,OBJECT_TYPE,TO_CHAR(CREATED,'yyyy-mm-dd hh24:mi:ss')
FROM dba_objects
WHERE CREATED > sysdate - 1
ORDER BY 1,2
20.最近24小时创建的表及其大小
col OWNER FOR a15
col OBJECT_NAME FOR a30
col SUBOBJECT_NAME FOR a30
col OBJECT_TYPE FOR a20
SELECT B.OWNER,B.OBJECT_NAME,B.SUBOBJECT_NAME,B.OBJECT_ID,a.bytes/(1024*1024) as "size(M)" ,TO_CHAR(CREATED,'yyyy-mm-dd hh24:mi:ss')
FROM DBA_SEGMENTS a,
dba_objects b
WHERE a.segment_name=b.object_name
AND a.owner = ...
Posted in oracle | No Comments »
星期一, 四月 28th, 2008
6.Check OS process id from Oracle sid
SELECT spid
FROM v$process
WHERE addr IN ( SELECT paddr
FROM v$session
WHERE sid = &sid )
7.Check Oracle sid from OS process id
SELECT sid
FROM v$session
WHERE paddr IN ( SELECT addr
FROM v$process
WHERE spid = &spid )
8.Check current SQL in a SESSION
SELECT SQL_TEXT
FROM ...
Posted in oracle | No Comments »
星期一, 四月 28th, 2008
1.监控表空间使用率与剩余空间大小
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024),2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE,0)/SPACE*100,2) ...
Posted in oracle | No Comments »
