上周末看到asktom上的一个帖子，其中有人提到session_cached_cursors和cursor_space_for_time对library cache gets&pin的影响，帖子的url为
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:465420331879

其中tom还为我们展示了session_cached_cursors和cursor_space_for_time对library cache gets&pin在9i和10g中的影响是不同的。

先让我们来了解一下session_cached_cursors，cursor_space_for_time，library cache gets，pin

session_cached_cursors:
设置pga端的cache list的长度，当session_cached_cursors设置为0时，pga的cache list长度为0，这时候当sga中的cursor关闭的时候它相关的library cache handle的lock位被清0，从v$open_cursor里看不到这个被关闭的cursor，它服从于shared pool的lru机制，当shared pool需要新的buffer空间时，它将会被flush出shared pool。当session_cached_cursors设置为非0值时，pga的cache list长度为session_cached_cursors值的大小，同时pga cache list将会保留一份拷贝，这时候即使sga中的cursor关闭的时候它相关的library cache handle始终被加了null mode lock，当shared pool空间紧张时library cache handle始终将会被保留在shared pool中.而新的应用访问这个cursor的时候会直接去自己的pga cache list里面搜索。

cursor_space_for_time：
当设置了session_cached_cursors为非0值后，如果cursor_space_for_time值被设为false，那么当shared pool空间紧张时，虽然library cache handle不会被flush出去，但是它指向的library cached object(lco,其中包含了handle和children handle的地址，权限，类型，状态，指向kgl block的指针，其中kgl block包含了真正的代码段和执行计划等内容）将会被flush出去而不管它相关的cursor有没关闭，如果需要lco的时候将要reloads。
如果cursor_space_for_time值被设为true，那么当cursor在打开状态下，handle指向的lco将不会被flush出shared pool，这样就可以降低reloads出现的频率。不过对于sql共享做的不好的数据库，设置
cursor_space_for_time将会带来一些问题，share pool可能会出现04031的错误。

gets:
当试图parse一句sql时，oracle要先获得一个handle，在handle上加载一个lock，gets表示handle request times。

pin：
当获得handle后，定位到lco，然后pin住lco使它在被执行的时候不被flush出去。

既然理解了以上一些概念，那么我们可以通过一些代码演示session_cached_cursors,cursor_space_for_time对pin，gets的影响

在9i和10g中两个参数对pin,gets的影响也不一样

9i：

vi 1.sql

set wrap off
set linesize 100
set pagesize 0
set verify off
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = ‘SQL AREA’ ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = ‘SQL AREA’ ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = ‘SQL AREA’ ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = ‘SQL AREA’ ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = ‘SQL AREA’ ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = ‘SQL AREA’ ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = ‘SQL AREA’ ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = ‘SQL AREA’ ;
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = ‘SQL AREA’ ;

执行10次sql

SQL 9I>show parameter cursor
NAME TYPE VALUE
———————————— ———– ——————————
cursor_space_for_time boolean FALSE
session_cached_cursors integer 0

SQL 9I>@$HOME/1.sql
NAMESPACE GETS GETHITS PINS PINHITS
————— ———- ———- ———- ———-
SQL AREA 2942908 2939105 75218597 75211231
SQL AREA 2942909 2939106 75218600 75211234
SQL AREA 2942910 2939107 75218603 75211237
SQL AREA 2942911 2939108 75218606 75211240
SQL AREA 2942912 2939109 75218609 75211243
SQL AREA 2942913 2939110 75218612 75211246
SQL AREA 2942914 2939111 75218615 75211249
SQL AREA 2942915 2939112 75218618 75211252
SQL AREA 2942916 2939113 75218621 75211255
SQL AREA 2942917 2939114 75218624 75211258

可以看到gets每次增加1，pin每次增加3

修改session_cached_cursors＝100；

SQL 9I>alter session set session_cached_cursors=100;
SQL 9I>@$HOME/1.sql
SQL AREA 2942935 2939123 75218728 75211344
SQL AREA 2942935 2939123 75218730 75211346
SQL AREA 2942935 2939123 75218732 75211348
SQL AREA 2942935 2939123 75218734 75211350
SQL AREA 2942935 2939123 75218736 75211352
SQL AREA 2942935 2939123 75218738 75211354
SQL AREA 2942935 2939123 75218740 75211356
SQL AREA 2942935 2939123 75218742 75211358
SQL AREA 2942935 2939123 75218744 75211360
SQL AREA 2942935 2939123 75218746 75211362

gets不再增加，pin增加2

SQL 9I>show parameter cursor
NAME TYPE VALUE
———————————— ———– ——————————
cursor_space_for_time boolean TRUE
session_cached_cursors integer 100

SQL 9I>@$HOME/1.sql
NAMESPACE GETS GETHITS PINS PINHITS
————— ———- ———- ———- ———-
SQL AREA 932 508 3561 3159
SQL AREA 933 509 3563 3161
SQL AREA 934 510 3565 3163
SQL AREA 934 510 3566 3164
SQL AREA 934 510 3567 3165
SQL AREA 934 510 3568 3166
SQL AREA 934 510 3569 3167
SQL AREA 934 510 3570 3168
SQL AREA 934 510 3571 3169
SQL AREA 934 510 3572 3170

cursor_space_for_time改为ture后，pin变成每次增加1

10g：
SQL 10G>show parameter cursor
cursor_space_for_time boolean FALSE
session_cached_cursors integer 0

SQL 10G>@$HOME/1.sql
SQL AREA 3328 98 23112 21363
SQL AREA 3328 98 23113 21364
SQL AREA 3328 98 23114 21365
SQL AREA 3328 98 23115 21366
SQL AREA 3328 98 23116 21367
SQL AREA 3328 98 23117 21368
SQL AREA 3328 98 23118 21369
SQL AREA 3328 98 23119 21370
SQL AREA 3328 98 23120 21371
SQL AREA 3328 98 23121 21372

gets不变化，pin每次增加1

SQL 10G>alter session set session_cached_cursors=100;
SQL 10G>@$HOME/1.sql
SQL AREA 3513 98 28456 26335
SQL AREA 3513 98 28457 26336
SQL AREA 3513 98 28458 26337
SQL AREA 3513 98 28459 26338
SQL AREA 3513 98 28460 26339
SQL AREA 3513 98 28461 26340
SQL AREA 3513 98 28462 26341
SQL AREA 3513 98 28463 26342
SQL AREA 3513 98 28464 26343
SQL AREA 3513 98 28465 26344
设置了session set session_cached_cursors后没有变化

SQL 10G>show parameter cursor
NAME TYPE VALUE
———————————— ———– ——————————
cursor_space_for_time boolean TRUE
session_cached_cursors integer 100
SQL 10G>@$HOME/1.sql
NAMESPACE GETS GETHITS PINS PINHITS
————— ———- ———- ———- ———-
SQL AREA 1038 24 4404 3488
SQL AREA 1038 24 4405 3489
SQL AREA 1038 24 4406 3490
SQL AREA 1038 24 4407 3491
SQL AREA 1038 24 4408 3492
SQL AREA 1038 24 4409 3493
SQL AREA 1038 24 4410 3494
SQL AREA 1038 24 4411 3495
SQL AREA 1038 24 4412 3496
SQL AREA 1038 24 4413 3497

设置cursor_space_for_time为true后也没有变化

但是当设置cursor_space_for_time为true后pl/sql block将会使pin也停止增长

看tom的例子

ops$tkyte@ORA10G> create or replace procedure p
2 authid current_user
3 as
4 l_ns varchar2(4000);
5 l_gets number;
6 l_gethits number;
7 l_pins number;
8 l_pinhits number;
9 l_sgets number;
10 l_sgethits number;
11 l_spins number;
12 l_spinhits number;
13 begin
14 for i in 1 .. 1000
15 loop
16 execute immediate
17 ’select namespace, gets, gethits, pins, pinhits
18 from v$librarycache
19 where namespace = ”SQL AREA” ‘
20 into l_ns, l_gets, l_gethits, l_pins, l_pinhits;
21
22 if ( i in (1,1000) )
23 then
24 if ( i = 1 )
25 then
26 l_sgets := l_gets; l_sgethits := l_gethits;
27 l_spins := l_pins; l_spinhits := l_pinhits;
28 end if;
29 dbms_output.put_line
30 ( l_ns || to_char(l_gets,’999,999′) ||
31 to_char(l_gethits,’999,999′) ||
32 to_char(l_pins,’999,999′) ||
33 to_char(l_pinhits,’999,999′) );
34 if ( i = 1000 )
35 then
36 dbms_output.put_line
37 ( l_ns || to_char(l_gets-l_sgets,’999,999′) ||
38 to_char(l_gethits-l_sgethits,’999,999′) ||
39 to_char(l_pins-l_spins,’999,999′) ||
40 to_char(l_pinhits-l_spinhits,’999,999′) );
41 end if;
42 end if;
43 end loop;
44 end;
45 /

Procedure created.

sys@ORA10G> alter system set session_cached_cursors=100 scope=spfile;

System altered.

sys@ORA10G> alter system set cursor_space_for_time=TRUE scope=spfile;

System altered.

sys@ORA10G> startup force
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 777956 bytes
Variable Size 145760540 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
sys@ORA10G> @connect /
sys@ORA10G> set termout off
ops$tkyte@ORA10G> @login
ops$tkyte@ORA10G> set termout off
ops$tkyte@ORA10G> REM GET afiedt.buf NOLIST
ops$tkyte@ORA10G> set termout on
ops$tkyte@ORA10G> exec p
SQL AREA 1,181 373 4,828 4,171
SQL AREA 1,181 373 4,828 4,171
SQL AREA 0 0 0 0

PL/SQL procedure successfully completed.

可以看到10g的pl/sql引擎也有了变化。

再来看一下设置session_cached_cursors后handle的dump

BUCKET 110279:
LIBRARY OBJECT HANDLE: handle=9df283a8 mutex=0×9df2845c(1)
name=select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = ‘SQL AREA’
hash=f1deb637acf7a42dd55d86a8ae3baec7 timestamp=08-30-2005 17:07:35
namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/MED/KST/DBN/MTX/[500100d4]
kkkk-dddd-llll=0001-0001-0001 lock=N pin=0 latch#=7 hpc=0002 hlc=0002

而不设置的话
BUCKET 110279:
LIBRARY OBJECT HANDLE: handle=9df283a8 mutex=0×9df2845c(1)
name=select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = ‘SQL AREA’
hash=f1deb637acf7a42dd55d86a8ae3baec7 timestamp=08-30-2005 17:07:35
namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/MED/KST/DBN/MTX/[500100d4]
kkkk-dddd-llll=0001-0001-0001 lock=0 pin=0 latch#=7 hpc=0002 hlc=0002

如果执行alter system flush shared_pool 那么没有加载null mode lock的handle将被flush出shared pool。

第一小结　　library cache 的内存结构



Library cache的作用，最主要的就是存储已解析的SQL声明，避免硬解析。我们可以通过一个视图V$librarycache来了解Library cache点中率，已此来评估SQL声明的解析情况。应时时注意此视图中点中率情况，好及时发现并解决问题。
一、Library cache中所存储的信息：
1. 按对象类型分类：
共享游标（SQL and PL/SQL objects）、数据库对象（tables, indexes, and so on）
2. 按存在时间分类：
存贮对象：如表、索引、视图等（老化后 磁盘上还有 所以叫永久存储 并不是永久存储在LIBRARY 中）
瞬时对象：如游标（老化后就没有了）（简单了解在Library cache中数据库对象的信息是什么）
二、Library cache中如何存储信息 
了解Library cache的简单原理，对于理解一些调优概念，是十分必要的。 
共享池内存的结构，是计算机中常用的哈希表形式的扩展。常用的哈希表形式，总是先有一个哈希表，保存对象地址（或句柄），然后，根据对象地址（或句柄）访问对象，计算机中常以这种方式组织数据。其基本形式如下：


[attach]292840[/attach]

注意地址和句柄的区别，一般来说，地址只是一个位置信息。而句柄，除了位置，还包含一些其他信息。
还要注意“堆（Heap）”的概念，程序开发者在程序员使用系统函数分配的内存。
注：此处补充一点概念，除了堆之外，还有栈，也是内存中比较常见的名词。开发者在程序中定义的各种变量，就在栈中分配。
上图每一组哈希值、链表头，叫做一个哈希桶。简单的说就是：哈希桶（Hash Bucket）指向对象句柄（Object Handles），对象句柄存有对象所占的堆内存的地址。由于对象的堆往往不只一个，Oracle习惯称这些堆为子堆。通常，对象句柄中存有0号子堆的地址，而0号子堆中存有其他各个子堆的地址，而Library cache中所存贮对象的信息，就在从0号开始的各个子堆中。


第二小节 Library cache的PIN与LOCK


一、详述Library cache lock与Library cache pin
1.
Library cache lock与Library cache pin的模式。
       所有在Library cache中的对象，都由两部分组成，一个句柄、至少一个子堆。这一点可以参考上面的图。
句柄中记录的有对象的名字、命名空间、Lock的持有者和等待者、Pin的持有者和等待者、一些标志信息，最重要的，句柄中记有堆的地址。
在Library cache中寻找对象时，先计算HASH值，在HASH表中找到句柄，再经由句柄，找到对象实际的内存地址。在这个过程中，有两个重要的数据项需要被锁保护起来。一个是对象句柄、另一个就是对象的内存堆。在对象句柄上加的锁就是Library cache lock，在内存堆上加的锁，就是Library cache pin。
下面先讨论一下Library cache lock。
（1）。 Library cache lock
Locks 除了阻止不相容的对句柄的访问，以保护句柄中数据的完整性外，获得Locks也是在缓存中定位对象的唯一方式，即：进程在对句柄上加锁的同时，完成在内存中定位堆的操作。
在句柄上获得Lock、并在内存中定位到堆后，对象可以Pin自己的堆。如果对象相关信息不在内存中，Pinning一个对象将导致它和它的的子堆被装载（此种情况时，如果是多个对象Pin一个对象，将可能会造成Pin等待）
Lock 有三种模式
  · Share(s) : 读对象
  · Exclusive(x) : 修改或创建对象
  · Null(n) : 专用于为会话持续
注意： 存贮对象可以被锁在以上任意一种方式，瞬时对象只能被锁在Null方式。
Null 锁在执行SQL声明的解析阶段被获得，此后一直持有。它不阻止任何DDL。也用术语“易碎解析锁”称乎它（breakable parse lock）。
在以下两种情况下Null锁被打碎：
· 当锁所在对象有一个独占Pin时
· 锁所在对象的任何依赖对象有一个独占Pin时
（2）。Library cache pin：
Pin有两种模式：
· Share (s) : 读一个对象堆
· Exclusive (x) : 修改一个对象堆
无论存贮对象还是瞬时对象，都能被Pinned在Share或Exclusive模式。当修改对象时，进程首先会以Share模式Pin对象，进行错误和安全检查，然后在以Exclusive模式Pin住对象。Pin的解除将会导致相关对象上的易碎锁Break
不同类型的操作所需要的不同类型的lock/pin：
1).
所有的DDL操作都会在需要处理的对象上放一个Exclusive(排他)类型的Lock和Pin(仅仅当执行的时候加上)。
如:重编译,截断表,给对象授权,等等
2).
所有对对象的使用都需要一个null类型lock和shared类型的pin(仅仅当执行的时候加上)。如:使用视图,执行过程,等等。
以上规则，也同样应用于对象所有依赖的对象。如:一个依赖于其他视图的视图,一个依赖于其他包的包。

下面我们用一个例子，来验证一下Library cache lock/pin。
例10：观察Library cache lock/pin 的状态：
先建立一下如下过程：
create or replace procedure jj_cur is
cursor aa is select kglhdlmd,kglhdpmd 
from x$kglob where kglnaobj='select * from aa_1 where id=1' and kglhdadr<>kglhdpar;

kk number:=5;

kk1 number:=5;

begin

for i in 1..2000 loop

kk:=5;

kk1:=5;

open aa;

fetch aa into kk,kk1;

dbms_output.put_line(kk||'-'||kk1);

close aa;

end loop;


end;
/
Kglhdadr是游标句柄，而Kglhdpar是父游标句柄，条件中的kglhdadr<>kglhdpar ，目的是只显示子游标。Kglhdlmd是Library cache lock的模式，为0时表示没有锁，1是NULL锁，2是共享锁，3是独占锁。Kglhdpmd是Library cache pin的模式，0是没有Pin，2是共享Pin，3是独占Pin。
另外，我们作为测试用的SQL声明select * from aa_1 where id=1，要保证只有一个子游标，这样做的目的，就是让过程中的游标AA只选出来一条记录。在过程执行完毕后，最好再执行声明：select kglhdlmd,kglhdpmd from x$kglob where kglnaobj='select * from aa_1 where id=1' and kglhdadr<>kglhdpar一次，看一下能选出来几行，如果多于一行，说明声明子游标的数量多于一条，应该换一条声明再试。
在会话A中：
spool e:\oracle\aaa.txt
exec jj_cur;
在会话B中：
select * from aa_1 where id=1;
在会话A中：
spool off
查看e:\oracle\aaa.txt，可以看到，在会话B的声明执行时：Library cache lock先是1，然后是0。而Library cache pin先是3，后是2，然后是0。会话B中的声明是第一次执行，这是硬解析，所以会有很短时间的独占Pin。上面的例子，可以再试一次，这次，就是软解析了。试验过程同上，结果是，Library cache lock仍是1，然后是0，而Library cache pin则是2，然后是0，不再有3了。
       我们可以再用上面的方法，测试一下父游标上的锁的情况，这里就略过了。
       我记得有资料上说，声明在解析时，在父、子游标上会有独占锁的，但是在上面的测试中，却没有看到。有可能是我的过程执行速度不过快，无法捕找到句柄上的独占锁。 library.GIF (17.71 KB)

oracle中性能视图V$SYSSTAT小结

4.5数据恢复

Oracle Database 10g:为 DBA 提供的最佳前 20 位的特性 请在今后 20 周内参加 Oracle 杂志的 2003 “年度 DBA” Arup Nanda 的活动，他将列出为数据库管理员提供的最佳 Oracle Database 10g 特性 密切关注！下载 Oracle Database 10g

4.5.1闪回表

使用 Oracle Database 10g 中的闪回表特性，可以毫不费力地恢复被意外删除的表

以下是一个不该发生却经常发生的情况：用户删除了一个非常重要的表 — 当然是意外地删除 — 并需要尽快地恢复。（在某些时候，这个不幸的用户可能就是 DBA！）

Oracle9i Database 推出了闪回查询选项的概念，以便检索过去某个时间点的数据，但它不能闪回 DDL 操作，如删除表的操作。唯一的恢复方法是在另一个数据库中使用表空间的时间点恢复，然后使用导出/导入或其他方法，在当前数据库中重新创建表。这一过程需要 DBA 进行大量工作并且耗费宝贵的时间，更不用说还要使用另一个数据库进行克隆。

请使用 Oracle Database 10g 中的闪回表特性，它使得被删除表的恢复过程如同执行几条语句一样简单。让我们来看该特性是如何工作的。

删除那个表！

首先，让我们查看当前模式中的表。

SQL> select * from tabs;

TNAME                    TABTYPE  CLUSTERID

------------------------ ------- ----------

RECYCLETEST              TABLE

现在，我们意外地删除了该表：

SQL> drop table recycletest;

Table dropped.

现在让我们来查看该表的状态。

SQL> select * from tabs;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE

表 RECYCLETEST 已不存在，但是请注意出现新表 BIN$04LhcpndanfgMAAAAAANPw==$0。这就是所发生的事情：被删除的表 RECYCLETEST 并没有完全消失，而是重命名为一个由系统定义的名称。它存在于同一个表空间中，具有与原始表相同的结构。如果在该表上定义了索引或触发器，则它们也被重命名，使用与表相同的命名规则。任何相关源（如过程）都失效；原始表的触发器和索引被改为放置在重命名的表 BIN$04LhcpndanfgMAAAAAANPw==$0 上，保持被删除表的完整对象结构。

表及其相关对象被放置在一个称为“回收站”的逻辑容器中，它类似于您 PC 机中的回收站。但是，对象并没有从它们原先所在的表空间中删除；它们仍然占用那里的空间。回收站只是一个列出被删除对象目录的逻辑结构。在 SQL*Plus 提示符处使用以下命令来查看其内容（您需要使用 SQL*Plus 10.1 来进行此操作）：

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ ------------------

RECYCLETEST      BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE        2004-02-16:21:13:31

结果显示了表的原始名称 RECYCLETEST，并显示了回收站中的新名称，该名称与我们看到的删除后所创建的新表名称相同。（注意：确切的名称可能因平台不同而不同。）为恢复该表，您所需要做的就是使用 FLASHBACK TABLE 命令：

SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;

FLASHBACK COMPLETE.

SQL> SELECT * FROM TABs;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

RECYCLETEST                    TABLE

瞧！表毫不费力地恢复了。如果现在查看回收站，它将是空的。

记住，将表放在回收站里并不在原始表空间中释放空间。要释放空间，您需要使用以下命令清空回收站：

PURGE RECYCLEBIN;

但是如果您希望完全删除该表而不需要使用闪回特性，该怎么办？在这种情况下，可以使用以下命令永久删除该表：

DROP TABLE RECYCLETEST PURGE;

此命令不会将表重命名为回收站中的名称，而是永久删除该表，就象 10g 之前的版本一样。

管理回收站

如果在该过程中没有实际删除表 — 因而没有释放表空间 — 那么当被删除的对象占用了所有空间时，会发生什么事？

答案很简单：这种情况根本不会出现。当表空间被回收站数据完全占满，以至于必须扩展数据文件来容纳更多数据时，可以说表空间处于“空间压力”情况下。此时，对象以先进先出的方式从回收站中自动清除。在删除表之前，相关对象（如索引）被删除。

同样，空间压力可能由特定表空间定义的用户限额而引起。表空间可能有足够的空余空间，但用户可能将其在该表空间中所分配的部分用完了。在这种情况下，Oracle 自动清除该表空间中属于该用户的对象。

此外，有几种方法可以手动控制回收站。如果在删除名为 TEST 的特定表之后需要从回收站中清除它，可以执行

PURGE TABLE TEST;

或者使用其回收站中的名称：

PURGE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0";

此命令将从回收站中删除表 TEST 及所有相关对象，如索引、约束等，从而节省了空间。但是，如果要从回收站中永久删除索引，则可以使用以下命令来完成工作：

purge index in_test1_01;

此命令将仅仅删除索引，而将表的拷贝留在回收站中。

有时在更高级别上进行清除可能会有用。例如，您可能希望清除表空间 USERS 的回收站中的所有对象。可以执行：

PURGE TABLESPACE USERS;

您也许希望只为该表空间中特定用户清空回收站。在数据仓库类型的环境中，用户创建和删除许多临时表，此时这种方法可能会有用。您可以更改上述命令，限定只清除特定的用户：

PURGE TABLESPACE USERS USER SCOTT;

诸如 SCOTT 等用户可以使用以下命令来清空自己的回收站

PURGE RECYCLEBIN;

DBA 可以使用以下命令清除任何表空间中的所有对象

PURGE DBA_RECYCLEBIN;

可以看到，可以通过多种不同方法来管理回收站，以满足特定的需要。

表版本和闪回功能

用户可能会经常多次创建和删除同一个表，如：

CREATE TABLE TEST (COL1 NUMBER);

INSERT INTO TEST VALUES (1);

commit;

DROP TABLE TEST;

CREATE TABLE TEST (COL1 NUMBER);

INSERT INTO TEST VALUES (2);

commit;

DROP TABLE TEST;

CREATE TABLE TEST (COL1 NUMBER);

INSERT INTO TEST VALUES (3);

commit;

DROP TABLE TEST;

此时，如果您要对表 TEST 执行闪回操作，那么列 COL1 的值应该是什么？常规想法可能认为从回收站取回表的第一个版本，列 COL1 的值是 1。实际上，取回的是表的第三个版本，而不是第一个。因此列 COL1 的值为 3，而不是 1。

此时您还可以取回被删除表的其他版本。但是，表 TEST 的存在不允许出现这种情况。您有两种选择：

• 使用重命名选项：

·         FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST2;

·         FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;

这些语句将表的第一个版本恢复到 TEST1，将第二个版本恢复到 TEST2。 TEST1 和 TEST2 中的列 COL1 的值将分别是 1 和 2。或者，

• 使用表的特定回收站名称进行恢复。为此，首先要识别表的回收站名称，然后执行：

·         FLASHBACK TABLE "BIN$04LhcpnoanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST2;

·         FLASHBACK TABLE "BIN$04LhcpnqanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST1;

这些语句将恢复被删除表的两个版本。

警告……

取消删除特性使表恢复其原始名称，但是索引和触发器等相关对象并没有恢复原始名称，它们仍然使用回收站的名称。在表上定义的源（如视图和过程）没有重新编译，仍然保持无效状态。必须手动得到这些原有名称并应用到闪回表。

信息保留在名为 USER_RECYCLEBIN 的视图中。在对表进行闪回操作前，请使用以下查询来检索原有名称。

SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE

FROM USER_RECYCLEBIN

WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN

WHERE ORIGINAL_NAME = 'RECYCLETEST')

AND ORIGINAL_NAME != 'RECYCLETEST';

OBJECT_NAME                    ORIGINAL_N TYPE

------------------------------ ---------- --------

BIN$04LhcpnianfgMAAAAAANPw==$0 IN_RT_01   INDEX

BIN$04LhcpnganfgMAAAAAANPw==$0 TR_RT      TRIGGER

在表进行闪回操作后，表 RECYCLETEST 上的索引和触发器将按照 OBJECT_NAME 列中所示进行命名。根据以上查询，可以使用原始名称重新命名对象，如下所示：

ALTER INDEX "BIN$04LhcpnianfgMAAAAAANPw==$0" RENAME TO IN_RT_01;

ALTER TRIGGER "BIN$04LhcpnganfgMAAAAAANPw==$0" RENAME TO TR_RT;

一个值得注意的例外情况是位图索引。当删除位图索引时，它们并不放置在回收站中 — 因此无法检索它们。约束名称也无法从视图中检索。必须从其他来源对它们进行重命名。

闪回表的其他用途

闪回删除表功能不仅限于恢复表的删除操作。与闪回查询类似，您还可以使用它将表恢复到不同的时间点，利用表的“过去”版本来替代整个表。例如，以下语句将表恢复到系统更改号 (SCN) 2202666520。

FLASHBACK TABLE RECYCLETEST TO SCN 2202666520;

此特性使用 Oracle 数据泵技术来创建不同的表，使用闪回功能将该 SCN 处的数据版本填充到表中，然后用新表替代原始表。为找出能够在何种程度上对表进行闪回操作，可以使用 Oracle Database 10g 的版本控制特性。（更多详细信息请参见本系列第 1 周的内容。）在闪回子句中也可以指定时间戳记而不是指定 SCN。

您可以在 Oracle 数据库管理员指南 10g 第 1 版 (10.1) 中阅读有关闪回表特性的更多内容。

4.5.2 删除数据恢复

²  ALTER TABLE t1 ENABLE ROW MOVEMENT;

查看(350) 评论(1) 收藏 分享 管理

Oracle游标

---------输入一个表的特定字段------

declare
begin
for my_dep in (select s.id ,s.name,d.dname from staff s,dep d where s.jid=d.id )
loop
insert into user_info(id,name,depname) values(my_dep.id,my_dep.name,my_dep.dname);
end loop;
end;
/

 

 

SESSION_CACHED_CURSORS,就是说的是一个session可以缓存多少个cursor，让后续相同的SQL语句不再打开游标,从而避免软解析的过程来提高性能。(绑定变量是解决硬解析的问题)，软解析同硬解析一样,比较消耗资源.所以这个参数非常重要。

oracle有一个概念，那就是session cursor cache,中文描述就是有一块内存区域，用来存储关闭了的cursor。当一个cursor关闭之后，oracle会检查这个cursor的request次数是否超过3次，如果超过了三次，就会放入session cursor cache，这样在下次parse的时候，就可以从session cursor cache中找到这个statement, session cursor cache的管理也是使用LRU。
session_cached_cursors这个参数是控制session cursor cache的大小的。session_cached_cursors定义了session cursor cache中存储的cursor的个数。这个值越大，则会消耗的内存越多。
另外检查这个参数是否设置的合理，可以从两个statistic来检查。

SQL> select name,value from v$sysstat where name like ''%cursor%'';

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 16439
opened cursors current 55
session cursor cache hits 8944
session cursor cache count 101
cursor authentications 353

SQL> select name,value from v$sysstat where name like ''%parse%'';

NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 0
parse time elapsed 0
parse count (total) 17211
parse count (hard) 1128
parse count (failures) 2

session cursor cache hits 和parse count（total） 就是总的parse次数中，在session cursor cache中找到的次数，所占比例越高，性能越好。如果比例比较低，并且有剩余内存的话，可以考虑加大该参数。

Oracle 9i及以前,该参数缺省是0,10G上缺省是20。

open_cursors 是充许打开的游标的数量
session_cached_cursors 是充许放入缓存的游标的数量

当一个session打算关闭一个cursor时,如果这个cursor的parse count超过3次,那么这个cursor将会被加到session cursor cache list的MRU端.当一个session打算parse一个sql时,它会先去pga内搜索session cursor cache list,如果找到那么会把这个cursor脱离list,然后当关闭的时候再把这个cursor加到MRU端. session_cached_cursor提供了快速软分析的功能,提供了比soft parse更高的性能.

Oracle 11g sga_target 参数设置

今天在一台linux测试机上装了Oracle 11g，安装好以后按照惯例调整sga_target 1G,pga_aggregate_target 200M
(对了，这2个参数在11g里面默认是0)，然后重启数据库

结果报错

ORA-00844: Parameter not taking MEMORY_TARGET into account, see alert log for more information

MEMORY_TARGET?新参数?虽然在安装过程中瞟过一眼，但是也没太多注意，后来理解MEMORY_TARGET就是原来10g的
sga_target + pga_aggregate_target的总和，越来越智能了。

再看导致错误原因：
由于设置了Oracle的MEMORY_TARGET参数的值小于了SGA_TARGET和PGA_TARGET的总和，因此报错。

但是此时数据库已经启不来了，在数据库不能更改spfile的情况下，我们可以更改pfile，再用pfile启动

SQL> create pfile ='/home/oracle/initora11g.ora' from spfile;
SQL> !echo '*.memory_target=1024m' >>'/home/oracle/initora11g.ora';

将pfile里面的memory_target设置成1G

报错，再次验证memory_target>= sga_target + pga_aggregate_target
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 1224M
ORA-01078: failure in processing system parameters

再改
SQL> !echo '*.memory_target=1224m' >>'/home/oracle/initora11g.ora';
SQL> !echo '*.sga_target=0' >>'/home/oracle/initora11g.ora';

报新错误了又
ORA-00845: MEMORY_TARGET not supported on this system

这个问题是由于设置SGA的大小超过了操作系统/dev/shm的大小
解决办法：http://yangtingkun.itpub.net/post/468/408235

最终启动成功

启动后再改回从spfile启动
SQL>create spfile from pfile = '/home/oracle/initora11g.ora';

然后再启动一次

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2151248 bytes
Variable Size             511708336 bytes
Database Buffers          549453824 bytes
Redo Buffers                5623808 bytes
Database mounted.
Database opened.

最后
SQL> show parameter target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 1300M
memory_target                        big integer 1224M
pga_aggregate_target                 big integer 0
sga_target                           big integer 0

以下是http://yangtingkun.itpub.net/post/468/408235 正文内容

Oracle 11g的Linux版本在修改了MEMORY_TARGET或者SGA_TARGET后启动可能会报错：

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system

这个问题是由于设置SGA的大小超过了操作系统/dev/shm的大小：

[root@enterprice64 ~]# df -k /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
tmpfs 4089416 0 4089416 0% /dev/shm

Oracle在metalink的文档：Doc ID: Note:460506.1中进行了说明。解决这个问题只有两个方法，一种是修改初始化参数，使得初始化参数中SGA的设置小于/dev/shm的大小，另一种方法就是调整/dev/shm的大小。

修改/dev/shm的大小可以通过修改/etc/fstab来实现：

[root@enterprice64 ~]# vi /etc/fstab

LABEL=/ / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
LABEL=/data /data ext3 defaults 1 2
devpts /dev/pts devpts gid=5,mode=620 0 0
#tmpfs /dev/shm tmpfs defaults 0 0
tmpfs /dev/shm tmpfs defaults,size=10240M 0 0
LABEL=/home /home ext3 defaults 1 2
LABEL=/opt /opt ext3 defaults 1 2
proc /proc proc defaults 0 0
sysfs /sys sysfs defaults 0 0
LABEL=/usr /usr ext3 defaults 1 2
LABEL=/var /var ext3 defaults 1 2
LABEL=SWAP-sda8 swap swap defaults 0 0
~
~
"/etc/fstab" 13L, 965C written
[root@enterprice64 ~]# umount /dev/shm
[root@enterprice64 ~]# mount /dev/shm
[root@enterprice64 ~]# df -k /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
tmpfs 10485760 0 10485760 0% /dev/shm
[root@enterprice64 ~]# su - oracle
[oracle@enterprice64 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 23 15:42:42 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4743446528 bytes
Fixed Size 2143824 bytes
Variable Size 3892316592 bytes
Database Buffers 805306368 bytes
Redo Buffers 43679744 bytes
Database mounted.
Database opened.

修改/etc/fstab，重新mount /dev/shm，然后就可以启动数据库了。

db file sequential read

db file sequential read 等待事件有3 个参数：file ＃，first block ＃，和block 数量 。在10g 中，这等待事件受到用户I/O 等待级别的影响。当处理db file sequential read 等待事件的时候，牢记以下关键想法。

l         Oracle 进程需要一个当前不在SGA 中的块，等待数据库块从磁盘读入到SGA 中

l         要看的两个重要的数字是单独会话 的TIME_WAITED 和AVERAGE_WAIT 。

l         重要db file sequential read 等待时间最可能是一个应用问题。

 

db file sequential read 等待时间是由于执行对索引，回滚（ undo ）段，和表（当借助 rowid 来访问），控制文件和数据文件头 的单块读操作 SQL 语句（用户和递归）引起的。

 

对于这些对象的物理 I/O 请求是很正常的，因此 db file sequential read 等待的存在不是一定意味库或应用出错了。如果会话在这事件上花了好长事件，它可能也不是一个糟糕的事情。相反，如果会话花了大量时间在 equeue 或 latch free 上，那么一定是有问题。这儿单块读变的复杂了。

 

==========

目的：从得到各个session 中db file sequential read 等待事件的总的等待时间，和等待时间所占总的等待时间 ( 各种等待事件的总和时间) 的比例 中分析哪一个sid 更高，更重要。

==========

select a.sid,

       a.event,

       a.time_waited,

       a.time_waited / c.sum_time_waited * 100 pct_wait_time,

       round((sysdate - b.logon_time) * 24) hours_connected

from   v$session_event a, v$session b,

       (select sid, sum(time_waited) sum_time_waited

        from   v$session_event

        where  event not in (

                    'Null event',

                    'client message',

                    'KXFX: Execution Message Dequeue - Slave',

                    'PX Deq: Execution Msg',

                    'KXFQ: kxfqdeq - normal deqeue',

                     'PX Deq: Table Q Normal',

                    'Wait for credit - send blocked',

                    'PX Deq Credit: send blkd',

                    'Wait for credit - need buffer to send',

                    'PX Deq Credit: need buffer',

                     'Wait for credit - free buffer',

                    'PX Deq Credit: free buffer',

                    'parallel query dequeue wait',

                    'PX Deque wait',

                    'Parallel Query Idle Wait - Slaves',

                    'PX Idle Wait',

                    'slave wait',

                    'dispatcher timer',

                    'virtual circuit status',

                    'pipe get',

                    'rdbms ipc message',

                    'rdbms ipc reply',

                     'pmon timer',

                    'smon timer',

                    'PL/SQL lock timer',

                    'SQL*Net message from client',

                    'WMON goes to sleep')

        having sum(time_waited) > 0 group by sid) c

where  a.sid         = b.sid

and    a.sid         = c.sid

and    a.time_waited > 0

and    a.event       = 'db file sequential read'

order by hours_connected desc, pct_wait_time;

 

SID EVENT                   TIME_WAITED PCT_WAIT_TIME HOURS_CONNECTED

---- ----------------------- ----------- ------------- ---------------

  186 db file sequential read       64446    77.0267848             105

  284 db file sequential read     1458405     90.992838             105

  194 db file sequential read     1458708    91.0204316              105

  322 db file sequential read     1462557    91.1577045             105

  139 db file sequential read      211325    52.6281055              11

         256 db file sequential read      247236    58.0469755              11

  192 db file sequential read      243113    88.0193625               2

 
 

你能做两件事来最小化db file sequential read 事件：

l         通过降低physical 和logical read 来优化导致大多数wait 的SQL 语句

l         降低平均等待时间

此外，当前正运行的 SQL 语句可能或不可能是导致 wait 的。这就是没有历史数据的交互式诊断经常是无功而返的原因。你能查询v$sql 视图来查找有高平均DISK_READS 的语句，但然后你怎样才能判断他们属于会话？因为这些限制，你可能必须确定和下次明确跟踪会话的SQL 语句。一旦你已经找到，优化目标就将降低物理和逻辑读的数量。

注意：除了DISK_READS 字段外，oracle10g 中的V$SQL 和V$SQLAREA 视图有不错的新字段：USER_IO_WAIT_TIME ，DIRECT_WRITES ，APPLICATION_WAIT_TIME ，CONCURRENCY_WAIT_TIME ，CLUSTER_WAIT_TIME ，PLSQL_EXEC_TIME 和JAVA_EXEC_TIME 。你能找到有最高的累计或平均的USER_IO_WAIT_TIME 的sql 语句。

 

=======

目的： 根据db file sequential read 中的P1,P2 两个参数得到对象名和分区名（该等待事件单块读等待的对象名和分区名） ，使用v$bh 的缺点你必须等待块被读入到buffer cache 中；否则X$BH 视图在buffer 中没有P1,P2 参数所指的信息。DBA_OBJECTS 视图也不包含P1 和P2 所指的rollback 或undo 段对象：

======

 

select b.sid,

       nvl(substr(a.object_name,1,30),

                  'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,

       a.subobject_name,

       a.object_type

from   dba_objects a, v$session_wait b, x$bh c

where  c.obj = a.object_id(+)

and    b.p1 = c.file#(+)

and    b.p2 = c.dbablk(+)

and    b.event = 'db file sequential read'

union

select b.sid,

       nvl(substr(a.object_name,1,30),

                  'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,

       a.subobject_name,

       a.object_type

from   dba_objects a, v$session_wait b, x$bh c

where  c.obj = a.data_object_id(+)

and    b.p1 = c.file#(+)

and    b.p2 = c.dbablk(+)

and    b.event = 'db file sequential read'

order  by 1;

SID OBJECT_NAME               SUBOBJECT_NAME            OBJECT_TYPE

----- ------------------------- ------------------------- -----------------

   12 DVC_TRX_REPOS             DVC_TRX_REPOS_PR64        TABLE PARTITION

  128 DVC_TRX_REPOS             DVC_TRX_REPOS_PR61        TABLE PARTITION

  154 ERROR_QUEUE               ERROR_QUEUE_PR1           TABLE PARTITION

  192 DVC_TRX_REPOS_1IX         DVC_TRX_REPOS_20040416    INDEX PARTITION

  194 P1=22 P2=30801 P3=1

  322 P1=274 P2=142805 P3=1

   336 HOLD_Q1_LIST_PK                                      INDEX

 

针对 索引的sequential reads 解决方案：

1.SQL 调优，得到sql语句

select a.sid,        a.seq#,             a.event,    a.p1text,
         a.p1,         a.p1raw,           a.p2text,   a.p2,
         a.p2raw,      a.p3text,          a.p3,       a.p3raw,
         a.wait_time,  a.seconds_in_wait, a.state,    b.serial#,
         b.username,   b.osuser,           b.paddr,    b.logon_time,
         b.process,    b.sql_hash_value,  b.saddr,    b.module,
         b.row_wait_obj#, b.row_wait_file#, b.row_wait_block#,
         b.row_wait_row#
  from   v$session_wait a, v$session b
  where  a.sid       = b.sid
  and    b.username is not null
  and    b.type     <> 'BACKGROUND'
  and    a.event in (
         'db file sequential read',
         'db file scattered read',
         'latch free',
         'direct path read',
         'direct path write',
         'enqueue',
         'library cache pin',
         'library cache load lock',
         'buffer busy waits',
         'free buffer waits');

 

select hash_value,address,piece,sql_text

   from v$sqltext

where  hash_value = <cursor hash value>

order by piece;

 
 

 

2.如果执行计划是 table access by index rowid ，检查索引的 clustering factor 也是值得做的。

 

select id.index_name,tb.table_name,id.clustering_factor,tb.num_rows,tb.blocks
 from dba_indexes id,dba_tables tb
 where id.table_name=tb.table_name
 and tb.table_name='&1' and tb.owner='&2';

 

如果 DBA_INDEXES.CLUSTERING_FACTOR 接近表中块的数量，那么表中大多数行是排序的。这是期望的。然而，如果 clustering factor 接近表中行的数量，它意味着表中行是随机排列。这种情况，对于在同样叶块中的索引块来说，指向同样数据块中行是不可能的，因此它要求更多 I/Os 来完成这操作。你可以采取 rebuilding 表来改善索引 clustering fator ，为了行根据索引键来被排序，其后重建索引。如果表不只有一个索引，什么会发生？好，它会下降。你仅能迎合最多使用的索引

 

3.也检查来看是否应用有最近已经引入一个新的索引，通过以下查询。新索引的引入可能导致优化器为访问表的SQL 语句选择一个不同的执行计划。新计划可能产生一个比旧计划更好的，中性的，或糟糕的性能。

select owner,

        substr(object_name,1,30) object_name,

        object_type,

        created

from   dba_objects

where  object_type in ('INDEX','INDEX PARTITION')

order by created;

 

      OPTIMIZER_INDEX_COST_ADJ 和OPTIMIZER_INDEX_CACHING 初始化参数能影响优化器去采用nested loops 操作和在全表扫描上选择一个索引访问路径。OPTIMIZER_INDEX_COST_ADJ 参数默认是100 。较低的值哄骗优化器认为索引访问路径更便宜 。OPTIMIZER_INDEX_CACHING 参数默认值是0 。较高的值通知优化器一个更高的百分比索引块已经在buffer cache 中，nested loops 操作更便宜。 一些第三方的应用使用这方法来改善索引使用。这些参数的不合适的使用能导致重大的I/O 等待时间。查明会话正以什么值运行。直到9i 数据库，这信息仅能通过跟踪以trace event 10053 的级别1 的会话，并检查trace 文件。在oracle10g 中，这可以查询v$ses_optimizer_env 视图。

 

      确保所有对象的统计数据是当前数据的典型，因为不准确的统计数据的确会导致优化器生成糟糕的不该用索引读却调用索引读的执行计划。记住，统计数据需要是有代表性的，而不必最新的，并且执行计划可能在统计数据被收集的每一次而改变。

 

      注意：当使用一个低estimate 比例值分析表或索引的时候，oracle 正常情况使用单个块读，这将增加该会话的db file sequential read 统计数据（v$session_event ）和实例（v$system_event ）。

 

 

 
 

针对表（ table access by index rowid ）的sequential reads

你可以看db file sequential read 等待事件，通过P1,P2 参数得到是表而不是索引。对于SQL 语句来说通过从索引获得的rowid 访问表是正常的，如下面解释计划显示，当通过rowid 来读一个表的时候，oracle 使用一个单独块I/O ：

LVL OPERATION                         OBJECT               

--- --------------------------------- --------------------- 

1 SELECT STATEMENT  

2   TABLE ACCESS BY INDEX ROWID     RESOURCE_ASGN_SNP    

     3     INDEX RANGE SCAN              RESOURCE_ASGN_SNP_4IX

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ZengMuAnSha/archive/2009/11/06/4777428.aspx

Oracle's V$ Views

v$archive_dest Shows all archived redo log destinations. Use this view to find out to which place archived redo logs are copied: select dest_id,destination from v$archive_dest These values correspond to the init parameter log_archive_dest_n. v$archive_dest_status This view allows to find status and errors for each of the defined v$archived_log Displays successfully archived redo logs. shows received logs on a primary standby database. v$archive_gap Lists sequence numbers of the archived los that are known to be missing for each thread on a (physical?) standby database (highest gap only). v$archive_processes This view provides information on the archive processes. It can be used to find out if an ARCH process is active or not. v$controlfile Displays the location and status of each controlfile in the database. v$controlfile_record_section See sections in a controlfile. v$bh This dynamic view has an entry for each block in the database buffer cache. The column status can be: free This block is not in use xcur Block held exclusively by this instance scur Block held in cache, shared with other instance cr Block for consistent read read Block being read from disk mrec Block in media recovery mode irec Block in instance (crash) recovery mode v$buffer_pool See buffer pools. This view's column id can be joined with x$kcbwds.indx See also x$kcbwbpd v$buffer_pool_statistics v$database This view lets you access database information. For example, you can check (using log_mode) whether or not the database is in archivelog mode: ADPDB>select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG checkpoint_change# records the SCN of the last checkpoint. switchover_status: can be used to determine if it is possible to perform. a switchover operation Only available for physical standby databases. Can be: NOT ALLOWED, SESSIONS ACTIVE, SWITCHOVER PENDING, SWITCHOVER LATENT, TO PRIMARY, TO STANDBY or RECOVERY NEEDED. See protection modes in data guard for the columns protection_mode and protection_level. database_role determines if a database is a primary or a logical standby database or a physical standby database. force_logging tells if a database is in force logging mode or not. v$datafile This view contains an entry for each datafile of the database. This view can be used to find out which datafiles must be backed up in a cold backup: select name from v$datafile v$datafile_header Various information about datafile headers. For example, if you're interested in when the a file's last checkpoint was: select name, checkpoint_change#, to_char(checkpoint_time, 'DD.MM.YYYY HH24:MI:SS') from v$datafile_header v$dataguard_status Shows error messages in a data guard environment. v$db_object_cache This view displays objects that are cached (pinned) in the library cache. See also dbms_shared_pool. v$enqueue_stat If there are a lot of enqueue waits "in" v$session_event or v$system_event, v$enqueue_stat allows to break down those enqueues in enqueue classes. For each such class, the gets, waits, failures and the cumulative sum of waited time can be found. For a list of enqueue types, refer to enqueue types in x$ksqst. The column cum_wait_time stems from x$ksqst.ksqstwtim. v$eventmetric This view is new in Oracle 10g and allows improved timing and statistics. v$event_name Contains a record for each wait event. v$filemetric This view is new in Oracle 10g and allows improved timing and statistics. v$filestat v$fixed_table This view contains the name of all V$, X$ and GV$ tables. In oracle 8.1.7, there are 187 different v$ tables: ORA81> select count(*) from v where name like 'V$%'; COUNT(*) ---------- 185 If you want to know, which x$ tables there are, do a select name from v$fixed_table where name like 'X$%'; v$fixed_view_definition Contains the defintion in its attribute view_definition for the views of v$fixed_table. v$flash_recovery_area_usage See also v$recovery_file_dest v$instance instance_role can be used to determine if an instance is an active instance (=primary instance) or a secondary instance (in a standby environment. dbms_utility.db_version can be used to retrieve the same version as the field version in v$instance. v$latch Oracle collects statistics for the activity of all latches and stores these in this view. Gets is the number of successful willing to wait requests for a latch. Similarly, misses is how many times a process didn't successfully request a latch. Spin_gets: number of times a latch is obtained after spinning at least once. Sleeps indicates how many times a willing to wait process slept. Waiters_woken tells how often a sleeping process was 'disturbed'. v$librarycache v$lock This view stores all information relating to locks in the database. The interesting columns in this view are sid (identifying the session holding or aquiring the lock), type, and the lmode/request pair. Important possible values of type are TM (DML or Table Lock), TX (Transaction), MR (Media Recovery), ST (Disk Space Transaction). Exactly one of the lmode, request pair is either 0 or 1 while the other indicates the lock mode. If lmode is not 0 or 1, then the session has aquired the lock, while it waits to aquire the lock if request is other than 0 or 1. The possible values for lmode and request are: 1: null, 2: Row Share (SS), 3: Row Exclusive (SX), 4: Share (S), 5: Share Row Exclusive (SSX) and 6: Exclusive(X) If the lock type is TM, the column id1 is the object's id and the name of the object can then be queried like so: select name from sys.obj$ where obj# = id1 A lock type of JI indicates that a materialized view is being refreshed. A more detailed example can be found here See also x$kgllk. v$locked_object Who is locking what: select oracle_username os_user_name, locked_mode, object_name, object_type from v$locked_object a,dba_objects b where a.object_id = b.object_id v$log Contains information on each log group. See also online redo log. Comman values for the status column are: UNUSED: Oracle8 has never written to this group, CURRENT: This is the active group. ACTIVE: Oracle has written to this log before, it is needed for instance recovery. The active log is the one with the current log sequence number INACTIVE: Oracle has written to this log before; it is not needed for instance recovery. v$logfile This view can be queried to find the filenames, group numbers and states of redo log files. For example, to find all files of group 2, use select member from v$logfile where group# = 2 v$logmnr_contents See dbms_logmnr. v$log_history This view contains an entry for each Log Switch that occured. The column first_time indicates the time of the first entry??? On physical standby databases, this view shows applied logs. v$logstdby Can be used to verify that archived redo logs are being applied to standby databases. v$managed_standby Monitors the progress of a standby database in managed recovery mode, more exactly, it displays information about the activities of log transport service and log apply service. see here select process, pid, status, client_process, group# "Stdby Redo Log Gr", block# from v$managed_standby; client_process: the corresponding primary database process. If lgwr log transmission is chosen, one row should have client_process=LGWR. If ARCH transmission is chosen, one row should have ARCH. v$mystat This view records statistical data about the session that accesses it. Join statistic# with v$statname. v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates. See also recording statistics with oracle. v$nls_parameters The NLS parameters that are in effect for the session quering this view. The view NLS_SESSION_PARAMETERS is based on v$nls_parameters. See also v$nls_valid_values. v$nls_valid_values This view can be used to obtain valid values for NLS parameters such as supported character sets languages territories sorting orders v$object_usage v$object_usage gathers information about used (accessed) indexes when an index is monitored using alter index ... monitoring usage. See On verifying if an index is used. v$open_cursor v$option This view lets you see which options are installed in the server. See also dba_registry. v$parameter Lists the name-value pairs of the init.ora file (or their default, if not in the init.ora). For example, if you need to know what your block size is: select value from v$parameter where name = 'db_block_size' The columns isses_modifiable and issys_modifiable can be used to determine if a parameter can be changed at session level using alter session or at system level using alter system. A parameter is modifiable at session level if isses_modifiable = 'TRUE'. A parameter is modifiable at system level if issys_modifiable = 'DEFERRED' or issys_modifiable = 'IMMEDIATE'. However, if a parameter is changed at system level if issys_modifiable = 'DEFERRED' it only affects sessions that are started after chaning the parameter. Additionally, the alter system set ... deferred option must be used. There are also some undocumented (or hidden?) parameters. v$pgastat See also pga. Thanks to Oleg who notified me of a typo (v$pgastat instead of v$pga_stat). v$process Join v$process's addr with v$session paddr. The column traceid is equal to the value used in alter session set. v$pwfile_users Lists all users who have been granted sysdba or sysoper privileges. See adding user to a password file. v$recover_file Useful to find out which datafiles need recovery. Join with v$datafile to see filenames instead of numbers.... v$recovery_file_dest See also v$flash_recovery_area_usage v$reserved_words This view can be consulted if one is in doubt wheter a particular word is a reserved word (for example when writing PL/SQL Code or assigning a password to a user). Until 9i (is this correct?), the view only consist of two columns: keyword and length. From 10g onwards (?), it has also the columns reserved, res_type, res_attr, res_semi and duplicate. Each of these new columns can only be either 'Y' (meaning: yes) or 'N' (meaning: no) See also reserved words in SQL and reserved words in PL/SQL. v$resource_limit v$rollname The names of online rollback segments. This view's usn field can be joined with v$rollstat's usn field and with v$transaction's xidusn field. v$transaction can be used to track undo by session. v$rollstat Statistics for rollback segements v$session The column audsid can be joined with sys_context('userenv','SESSIONID') to find out which session is the "own one". Alternatively, dbms_support.mysid can be used. The fields module and action of v$session can be set with dbms_application_info.set_module. (See v$session_longops for an example. The field client_info can be set with dbms_application_info.set_client_info Join sid with v$sesstat if you want to get some statistical information for a particular sesssion. A record in v$session contains sid and serial#. These numbers can be used kill a session (alter system kill session). A client can set some information in client_info. For example, RMAN related sessions can be found with .... where client_info like 'rman%'; What a session is waiting for can be queried with v$session_wait. However, with Oracle 10g, this is not nessessary anymore, as v$session_wait's information will be exposed within v$session as well. See also sessions. v$sessmetric This view is new in Oracle 10g and allows improved timing and statistics. v$session_event This views is similar to v$system_event. However, it breaks it down to currently connected sessions. v$session_event has also the column max_wait that shows the maximum time waited for a wait event. v$session_longops Use v$session_longops if you have a long running pl/sql procedure and want to give feedback on how far the procedure proceeded. If the following Procedure is run, it will report its progress in v$session_longops. The Procedure will also set the module attribute in v$session which makes it possible to find the sid and serial# of the session. create table f(g number); create or replace procedure long_proc as rindex pls_integer := dbms_application_info.set_session_longops_nohint; slno pls_integer; -- Name of task op_name varchar2(64) := 'long_proc'; target pls_integer := 0; -- ie. The object being worked on context pls_integer; -- Any info sofar number; -- how far proceeded totalwork number := 1000000; -- finished when sofar=totalwork -- desc of target target_desc varchar2(32) := 'A long running procedure'; units varchar2(32) := 'inserts'; -- unit of sofar and totalwork begin dbms_application_info.set_module('long_proc',null); dbms_application_info.set_session_longops ( rindex, slno); for sofar in 0..totalwork loop insert into f values (sofar); if mod(sofar,1000) = 0 then dbms_application_info.set_session_longops ( rindex, slno, op_name, target, context, sofar, totalwork, target_desc, units); end if; end loop; end long_proc; If the procedure long_proc is run, you can issue the following query to get feedback on its progress: select time_remaining,sofar,elapsed_seconds from v$session_longops l, v$session s where l.sid=s.sid and l.serial# = s.serial# and s.module='long_proc' v$session_wait This views shows what wait event each session is waiting for, or what the last event was that it waited for. In contrast, v$session_event lists the cumulative history of events waited for in a session. The columns P1, P2 and P3 are parameters that are dependant on the event. With Oracle 10g, v$session_wait's information will be exposed within v$session as well. Since 10g, Oracle displays the v$session_wait information also in the v$session view. v$session_wait_history This view is new in Oracle 10g and allows improved timing and statistics. v$sesstat This view is similar to v$mystat except that it shows cumulated statistics for all sessions. Join sid with v$session and join statistic# with v$statname. v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates. v$sga Shows how much memory the shared global area uses. Selecting * from v$sga is roughly the same as typing show sga in sql plus with the exeption that the latter also show the total. v$sgastat Showing free space in the sga: select * from v$sgastat where name = 'free memory' v$sga_dynamic_components Information about SGA resize operations since startup. This view can also be used to find out the granule size of SGA components. v$sga_resize_ops v$sort_usage See temporary tablespaces v$sort_segment See Temporary Tablespaces v$spparameter Returns the values for the spfile. v$sql v$sql is similar to v$sqlarea, the main difference being that v$sql drills down to select * from x$kglob whereas v$sqlarea drills down to select sum from x$kglob. See also here. v$sqlarea Join v$sqlarea's address with v$session's sql_address. Find the SQL-text of currently running SQL statements: select sql_text from v$sqlarea where users_executing > 0; The field version_count indicates how many versions an sql statement has. v$sqltext v$sql_plan variable addr varchar2(20) variable hash number variable child number exec :addr := '&sqladdr'; :hash := &hashvalue; :child := &childno; select lpad(' ', 2*(level-1))||operation||' '|| decode(id, 0, 'Cost = '||position) "OPERATION", options, object_name from v$sql_plan start with (address = :addr and hash_value = :hash and child_number = :child and id=0 ) connect by prior id = parent_id and prior address = address and prior hash_value = hash_value and prior child_number = child_number order by id, position ; In order to find valid values for sqladdr, hashvalue and childno, this SQL statement can be used: select sql_text,address,hash_value,child_number from v$sql where users_executing > 0; v$sql_text_with_newlines This view can be used to construct the entire text for each session's actual SQL statement. Use the following statement to to that: set serveroutput on size 1000000 declare v_stmt varchar2(16000); v_sql_text v$sqltext_with_newlines.sql_text%type; v_sid v$session.sid%type; begin for r in ( select sql_text,s.sid from v$sqltext_with_newlines t, v$session s where s.sql_address=t.address order by s.sid, piece) loop v_sid := nvl(v_sid,r.sid); if v_sid <> r.sid then dbms_output.put_line(v_sid); <a href='oru_10028.html'>put_line</a>(v_stmt,100); v_sid := r.sid; v_stmt := r.sql_text; else v_stmt := v_stmt || r.sql_text; end if; end loop; dbms_output.put_line(v_sid); dbms_output.put_line(v_stmt,100); end; / Thanks to Sarmad Zafar who notified me of an error in this PL/SQL Block. Note: the function put_line is found here and can be used to prevent ORU-10028. v$sql_bind_data Join cursor_num with cno of v$sql_cursor. v$sql_bind_capture New with Oracle 10g This view captures bind variables for all sessions and is faster than setting 10046 on level 4. v$sql_cursor Join parent_handle with address of v$sql or v$sqlarea. v$sql_workarea v$sql_workarea can be joined with v$sqlarea on address and hash_value, and it can be joined with v$sql on address, hash_value and child_number. v$standby_log v$statname Use this view to get decoded names for the statistic# field of v$mystat, v$sysstat and v$sesstat. v$sysaux_occupants v$sysaux_occupants doesn't exist in Oracle versions prior to Oracle 10g. See occupants in the sysaux tablepsaces. v$sysmetric This view is new in Oracle 10g and allows improved timing and statistics. v$sysmetric_history This view is new in Oracle 10g and allows improved timing and statistics. v$sysstat v$sysstat is similar to v$sesstat. While v$sesstat displays statitics for the current session, v$sysstat displays the cumulated statitics since startup of the database. For example, it is possible to find out the CPU time (name = 'CPU used by this session') This view is (among others) used to calculate the Hit Ratio. v$system_event This view displays the count (total_waits) of all wait events since startup of the instance. If timed_statistics is set to true, the sum of the wait times for all events are also displayed in the column time_waited. The unit of time_waited is one hundreth of a second. Since 10g, an additional column (time_waited_micro) measures wait times in millionth of a second. total_waits where event='buffer busy waits' is equal the sum of count in v$waitstat. v$enqueue_stat can be used to break down waits on the enqueue wait event. While this view totals all events in an instance, v$session_event breaks it down to all currently connected sessions. v$undostat undo tablespaces v$tempfile v$tempseg_usage v$tempseg_usage is a public synonym for v$sort_usage. v$tempstat v$thread The Oracle SID can be retrieved through select instance from v$thread v$timer This view has only one column (hsecs) which counts hundreths of seconds. Whenever it overflows four bytes, it starts again with 0. v$transaction Important fields of v$transaction are used_ublk and used_urec. They tell of how many blocks and records the undo for a transaction consists. In order to find out the name of the corresponding rollback segemnt, join the xidusn field with the usn field of v$rollname. This is demonstrated in Transactions generate undo v$timezone_names See also timezones for some values of tzabbrev. v$transportable_platform Which platforms are supported for cross platform. transportable tablespaces. v$version Use this view to find out what version you actually work on: select * from v$version; BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production CORE 8.1.7.0.0 Production TNS for 32-bit Windows: Version 8.1.7.0.0 - Production NLSRTL Version 3.4.1.0.0 - Production v$waitstat total_waits where event='buffer busy waits' is equal the sum of count in v$system_event.

原文地址 http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html

Flashback Database

Flashback Database 功能非常类似与RMAN的不完全恢复， 它可以把整个数据库回退到过去的某个时点的状态， 这个功能依赖于Flashback log 日志。 比RMAN更快速和高效。 因此Flashback Database 可以看作是不完全恢复的替代技术。 但它也有某些限制：

1. Flashback Database 不能解决Media Failure， 这种错误RMAN恢复仍是唯一选择

2. 如果删除了数据文件或者利用Shrink技术缩小数据文件大小，这时不能用Flashback Database技术回退到改变之前的状态，这时候就必须先利用RMAN把删除之前或者缩小之前的文件备份restore 出来， 然后利用Flashback Database 执行剩下的Flashback Datbase。

3. 如果控制文件是从备份中恢复出来的，或者是重建的控制文件，也不能使用Flashback Database。

4. 使用Flashback Database锁能恢复到的最早的SCN， 取决与Flashback Log中记录的最早SCN。

Flashback Database 架构

Flashback Database 整个架构包括一个进程Recover Writer(RVWR)后台进程，Flashback Database Log日志 和Flash Recovery Area。一旦数据库启用了Flashback Database， 则RVWR进程会启动，该进程会向Flash Recovery Area中写入Flashback Database Log， 这些日志包括的是数据块的 " 前镜像(before image)"， 这也是Flashback Database 技术不完全恢复块的原因。

[oracle@dba ~]$ ps -ef|grep rvw

oracle   12620 12589  0 13:21 pts/1    00:00:00 grep rvw

启用Flashback Database

数据库的Flashback Database功能缺省是关闭的，要想启用这个功能，就需要做如下配置。

1. 配置Flash Recovery Area

  要想使用Flashback Database， 必须使用Flash Recovery Area，因为Flashback Database Log只能保存在这里。 要配置的2个参数如下，一个是大小，一个是位置。如果数据库是RAC，flash recovery area 必须位于共享存储中。数据库必须处于archivelog 模式.

启用Flash Recovery Area：

SQL>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G SCOPE=BOTH;

SQL>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/DBA/FB' SCOPE=BOTH;

禁用Flash Recovery Area：

SQL>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' ;

对于Flash Recovery Area，Oracle 是这样建议的，flash recovery area 设置的越大，flashback database 的恢复能力就越强，因此建议flash recovery area 能够放的下所有的数据文件，增量备份，以及所有尚未备份的归档文件，当然还有它自己产生的flashback logs。

在数据库运行过程中，oracle 自动向该区域写入文件，当剩余空间不足15%的时候，它就会在alert 中增加警告，提示你空间不足。但此时不会影响数据库的正常运转，直到所有空间统统被用掉之后，oracle 首先尝试删除寻些过期的文件，冗余文件或备份过的文件，如果这些做完了，还是没有空闲空间的话，数据库就被hang 住了。

对于因Flash Recovery Area导致的数据库hang的处理，请参考：

http://blog.csdn.net/tianlesoftware/archive/2009/10/14/4668991.aspx

或者

http://user.qzone.qq.com/251097186/blog/1244650673

2. 启用数据库Flashback 功能

1). 数据库启动到mount状态

SQL> startup mount；

2). 检查Flashback 功能， 缺省时功能是关闭的。

SQL> select name, current_scn, flashback_on from v$database;

NAME    CURRENT_SCN  FLASHBACK_ON

-------    -----------          ------------------

 

DBA      945715          NO

3). 启动Flashback功能

SQL>  alter database flashback on;

数据库已更改。

SQL>  select name, current_scn, flashback_on from v$database;

NAME      CURRENT_SCN FLASHBACK_ON

--------- ----------- ------------------

DBA                 0 YES

4). 设置初始化参数：DB_FLASHBACK_RETENTION_TARGET：

SQL>alter system set db_flashback_retention_target=1440 scope=both;

该参数用来控制flashback log 数据保留的时间，或者说，你希望flashback database 能够恢复的最早的时间点。默认值是1440，单位是minute，即24 小时，需要注意的是该参数虽然未直接指定flash recovery area大小，但却受其制约，举个例子假如数据库每天有10%左右的数据变动的话，如果该初始化参数值设置为1440，则flash recovery area 的大小至少要是当前数据库实际容量的10%，如果该初始化参数设置为2880，则flash recovery area 的大小就至少是数据库所占容量的20%。

5). 启动数据库

SQL>alter database open;

Flashback Database操作示例

做操作前先备份数据库

RMAN> backup database;

1. 检查是否启动了flash recovery area:­

SQL> show parameter db_recovery_file

NAME                    TYPE        VALUE

 

------------------------------------  ----------- ------------------------------

 

db_recovery_file_dest       tring       D:\oracle/flash_recovery_area

 

db_recovery_file_dest_size  big integer 1G

2. 检查是否启用了归档­

SQL> archive log list;

数据库日志模式      存档模式

自动存档            启用

存档终点            USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列  9

下一个存档日志序列  11

查看(674) 评论(0) 收藏 分享 管理