kgl simulator,共享池simulator以及缓冲simulator相关解释

从9i开始学习的朋友一定习惯了v$sgastat视图中 一系列的sim项,主要包括各种 kglsim,sim cache segment trace等等。如下例:

SQL> select * from v$sgastat where lower(name) like '%sim%' order by name;

shared pool  kglsim count of pinned he        1920
shared pool  kglsim free heap list              96
shared pool  kglsim free obj list               96
shared pool  kglsim hash table                8208
shared pool  kglsim hash table bkts        4194304
shared pool  kglsim heap                    782208
shared pool  kglsim main lru count           76800
shared pool  kglsim main lru size           151040
shared pool  kglsim object batch           1163520
shared pool  kglsim pin list arr               288
shared pool  kglsim recovery area             1920
shared pool  kglsim sga                      30552
shared pool  kglsim size of pinned mem        3776

shared pool  ksim client list                  168
shared pool  log_simultaneous_copies           800
shared pool  sim cache nbufs                   640
shared pool  sim cache sizes                   640
shared pool  sim kghx free lists                 8
shared pool  sim lru segments                 1280
shared pool  sim segment hits                 1280
shared pool  sim segment num bufs              640
shared pool  sim state object                   40
shared pool  sim trace buf                    5144
shared pool  sim trace buf context             200
shared pool  sim_knlasg                       1280
shared pool  simulator hash buckets         131328

shared pool  simulator hash latch             5120
shared pool  simulator latch/bucket st        2816

28 rows selected.

有时你也会发现存在sim相关的latch,

SQL> select name from v$latch where name like '%sim%';

sim partition latch
simulator hash latch
simulator lru latch

显然Oracle实例中存在着这许多的模拟器(simulator),他们是 干啥的?

这些模拟器帮助Oracle实例获知当拥有更大的内存容量(涉及buffer_cache,shared_pool_size等)时可以节省多少时间和功。这些信息被汇总到几个常用视图中,如 V$SHARED_POOL_ADVICE, V$MTTR_TARGET_ADVICE, V$DB_CACHE_ADVICE等等。模拟器特性从9i开始被引入,也就是这些视图第一次出现的时候。

MMAN进程也利用这些信息作为SGA动态调整的基本依据(在sga_target或memory_target启用的情况下)。

KGL simulator会跟踪共享池内存分配并扮演模拟器引擎的角色。

KGL是 kernel general Library cache manager,它管理library cache中的对象 包括游标,PL/SQL存储过程,表的定义等等。

KGL simulator 评估当缓存大于目前情况的获益。 其通过将flush out出library cache的object的hash code保存至kgl simulator的 hash 列表中,以造成object似乎仍在缓存中的假象,方便其统计相关的数据。

当有对象被读入library cache中时,Oracle检查kgl simulator中的hash列表,若命中说明历史hash列表中的对象对当前实例仍有用,即更大的内存空间对实例来说是有益的。

11g内存管理新特性的internal表现

11g中自动内存管理(Automatic Memory Management ,amm), 令dba在数据库内存配置的相关工作更加简单. AMM现在将SGA与PGA整合到一起管理,而您只需要设置memory_target参数即可限定Oracle将使用到的内存尺寸,Oracle将自动分配这些内存空间.

您一定很困惑Oracle在unix平台上是如何对共享的sga内存空间与私有的pga内存空间进行切换的?这意味着Oracle需要经常释放sga中的部分内存以便允许pga去使用它们.传统的sys V 使用的共享内存shm接口不具备如此的灵活性.我们来看看Oracle是如何做到的?

先来获取我们需要的11g实例共享内存id(shared memory id)

[oracle@rh2 ~]$ sysresv                // 该命令需要设置了正确的LD_LIBRARY_PATH
IPC Resources for ORACLE_SID “T11” :
Shared Memory:
ID              KEY
65537           0x95c84bb8
Semaphores:
ID              KEY
327681          0xdf521034
Oracle Instance alive for sid “T11”

试着找出对应的sys V共享内存段:

[oracle@rh2 ~]$ ipcs -m

—— Shared Memory Segments ——–
key        shmid      owner      perms      bytes      nattch     status
0x95c84bb8 65537      oracle    660        4096       0

对应的存在着共享内存段,但该段很小只有 4096 byte哦,既然Oracle不再把sga放到共享段中,那藏到哪里去了呢?

我们接下来检查Oracle实例进程的内存影射状况.

[oracle@rh2 ~]$  pmap `pgrep -f lgwr`|less
14889:   ora_lgwr_T11
0000000000400000 155016K r-x–  /usr/oracle/product/11g/db_1/bin/oracle
0000000009c62000  12404K rw—  /usr/oracle/product/11g/db_1/bin/oracle
000000000a87f000    732K rwx–    [ anon ]
0000000060000000      4K r–s-  /dev/shm/ora_T11_65537_0
0000000060001000  16380K rw-s-  /dev/shm/ora_T11_65537_0
0000000061000000  16384K rw-s-  /dev/shm/ora_T11_65537_1
0000000062000000  16384K rw-s-  /dev/shm/ora_T11_65537_2
0000000063000000  16384K rw-s-  /dev/shm/ora_T11_65537_3
0000000064000000  16384K rw-s-  /dev/shm/ora_T11_65537_4
0000000065000000  16384K rw-s-  /dev/shm/ora_T11_65537_5
0000000066000000  16384K rw-s-  /dev/shm/ora_T11_65537_6
0000000067000000  16384K rw-s-  /dev/shm/ora_T11_65537_7
0000000068000000  16384K rw-s-  /dev/shm/ora_T11_65537_8
0000000069000000  16384K rw-s-  /dev/shm/ora_T11_65537_9
000000006a000000  16384K rw-s-  /dev/shm/ora_T11_65537_10
000000006b000000  16384K rw-s-  /dev/shm/ora_T11_65537_11
000000006c000000  16384K rw-s-  /dev/shm/ora_T11_65537_12
000000006d000000  16384K rw-s-  /dev/shm/ora_T11_65537_13
000000006e000000  16384K rw-s-  /dev/shm/ora_T11_65537_14
000000006f000000  16384K rw-s-  /dev/shm/ora_T11_65537_15
0000000070000000  16384K rw-s-  /dev/shm/ora_T11_65537_16
0000000071000000  16384K rw-s-  /dev/shm/ora_T11_65537_17
0000000072000000  16384K rw-s-  /dev/shm/ora_T11_65537_18
0000000073000000  16384K rw-s-  /dev/shm/ora_T11_65537_19
0000000074000000  16384K rw-s-  /dev/shm/ora_T11_65537_20
0000000075000000  16384K rw-s-  /dev/shm/ora_T11_65537_21
0000000076000000  16384K rw-s-  /dev/shm/ora_T11_65537_22
0000000077000000  16384K rw-s-  /dev/shm/ora_T11_65537_23
0000000078000000  16384K rw-s-  /dev/shm/ora_T11_65537_24
0000000079000000  16384K rw-s-  /dev/shm/ora_T11_65537_25
000000007a000000  16384K rw-s-  /dev/shm/ora_T11_65537_26
000000007b000000  16384K rw-s-  /dev/shm/ora_T11_65537_27
000000007c000000  16384K rw-s-  /dev/shm/ora_T11_65537_28
000000007d000000  16384K rw-s-  /dev/shm/ora_T11_65537_29
000000007e000000  16384K rw-s-  /dev/shm/ora_T11_65537_30
000000007f000000  16384K rw-s-  /dev/shm/ora_T11_65537_31
0000000080000000  16384K rw-s-  /dev/shm/ora_T11_65537_32
0000000081000000  16384K rw-s-  /dev/shm/ora_T11_65537_33
0000000082000000  16384K rw-s-  /dev/shm/ora_T11_65537_34
0000000083000000  16384K rw-s-  /dev/shm/ora_T11_65537_35
0000000084000000  16384K rw-s-  /dev/shm/ora_T11_65537_36
0000000085000000  16384K rw-s-  /dev/shm/ora_T11_65537_37
0000000086000000  16384K rw-s-  /dev/shm/ora_T11_65537_38
0000000087000000  16384K rw-s-  /dev/shm/ora_T11_65537_39
0000000088000000  16384K rw-s-  /dev/shm/ora_T11_65537_40
0000000089000000  16384K rw-s-  /dev/shm/ora_T11_65537_41
000000008a000000  16384K rw-s-  /dev/shm/ora_T11_65537_42

............

0000003e79109000      4K rw—  /lib64/tls/librt-2.3.4.so
0000003e7910a000     64K rw—    [ anon ]
0000003e79600000     84K r-x–  /lib64/libnsl-2.3.4.so
0000003e79615000   1020K —–  /lib64/libnsl-2.3.4.so
0000003e79714000      4K r—-  /lib64/libnsl-2.3.4.so
0000003e79715000      4K rw—  /lib64/libnsl-2.3.4.so
0000003e79716000      8K rw—    [ anon ]
0000007fbfff3000     52K rwx–    [ stack ]
ffffffffff600000      4K r-x–    [ anon ]
total          2497724K

pmap工具诠释了进程相关共享内存的情况,可以看到许多个16MB的"文件"对应了Oracle服务进程的空间地址.这是linux上POSIX风格的共享内存管理模式,使用"文件"形式包含共享内存段.

借助于将sga分割成许多块,Oracle可以很容易地把sga部分内存返回给OS,而服务器进程即可以利用到这些内存.(当memory_max_target>1024时,颗粒为16MB,否则为4MB).

接下来我们测试下Oracle是如何释放部分sga内存的.

对比实例启动前后:

启动前:

[oracle@rh2 ~]$ ls -l /dev/shm
总用量 0

启动后:
[oracle@rh2 ~]$ ls -l /dev/shm
总用量 1373704
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_0
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_1
-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_10
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_100
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_101
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_102
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_103
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_104
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_105
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_106
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_107
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_108
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_109
-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_11
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_110
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_111
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_112
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_113
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_114
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_115
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_116
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_117
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_118
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_119
-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_12

可以看到启动后出现的16MB文件形式共享内存中部分大小为0,这些块被选出当发生内存交换时来被’destory’.使用pmap工具仍可以看到该部分影射,而实际上已经被Oracle释放了.

现在我们加大pga,观察其交换情况.

SQL> alter system set pga_aggregate_target=1900M ;

System altered.

[oracle@rh2 ~]$ ls -l /dev/shm
总用量 289984
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_0
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_1
-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_10
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_100
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_101
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_102
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_103
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_104
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_105
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_106
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_107
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_108
-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_109
-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_11
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_110
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_111
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_112
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_113
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_114
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_115
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_116
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_117
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_118
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_119
-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_12
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_120
-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_121

可以看到出现了大量size为0的"文件",期许的交换出现了.

可见在11g中Oracle采用了新的共享内存实现方式,区别于旧的"一块式"共享段,更为灵活了.

PHP使用mail函数发送邮件标题乱码问题

PHP程序使用mail()函数发送邮件的时候,标题中文的话会出现乱码。
解决方法:
先用函数base64_encode() ― 使用 MIME base64 对数据进行编码
标题字符串前加编码类型例如: =?UTF-8?B?
标题字符串后加:?=
邮件header说明Content-type ― 防止邮件正文也乱码

举例:
$to         = ‘admin@askmac.cn’;
$subject  = “=?UTF-8?B?”.base64_encode(‘邮件标题’).”?=”;
$headers  = ‘MIME-Version: 1.0’ . “\r\n”;
$headers .= ‘Content-type: text/html; charset=utf-8’ . “\r\n”;
// Additional headers
$headers .= ‘To: Xinple <‘”>admin@askmac.cn>’ . “\r\n”;
$headers .= ‘From: Admin <‘”>admin@askmac.cn>’ . “\r\n”;
$headers .= ‘Reply-To: Xinple <‘”>webmaster@163gl.com>’ . “\r\n”;
mail($to, $subject, $message, $headers);

Oracle内部错误ORA-07445[kpopfr()+339] [SIGFPE]一例

当所有列长度综合超过1048576时可能引发的一个dump错误,session会自动关闭。一般只有列很多且单列较“宽”时可能出现该错误。

已经测试的在10.2.0.1,以及10.2.0.3上均可以再现该问题,测试方法:

create table test

( c000 char(2000),

c001 char(2000),

c523 char(2000),

c524 char(576));

— sum of all column size is 1048576(0x100000).

Run next shell script.

while [ 1 ]

do

echo “set feedback off”

echo “select * from test where c001 = ‘A’;”

done | sqlplus -s scott/tiger

Note 245840.1 Information on the sections in this article

以上循环执行一段时间后session会被关闭,告警日志中出现

ORA-07445: exception encountered: core dump [kpopfr()+339] [SIGFPE] [Integer divide by zero][0x002327FF5] [] []的记录。没有在9i版本上测试,不能确定其影响。

该bug在10.2.0.4 patch set中已被修复,也可以通过小补丁形式修复,Oracle发布的小布丁只针对10.2.0.3版本,即10.2.0.1上是不能打的。

附bug描述原文:
Subject:     Bug 5753629 – Query may dump [in kpopfr / kposdi]

Doc ID:     5753629.8     Type:     PATCH

Modified Date :     03-APR-2009     Status:     PUBLISHED

@ Note to support: do not edit this note – it is auto generated
Bug 5753629  Query may dump [in kpopfr / kposdi]

This note gives a brief overview of bug 5753629.
The content was last updated on: 03-APR-2009
Click here for details of each of the sections below.
Affects:

Product (Component)     Oracle Server (Rdbms)
Range of versions believed to be affected     Versions < 11
Versions confirmed as being affected

* 10.2.0.3

Platforms affected     Generic (all / most platforms affected)

Fixed:

This issue is fixed in

* 10.2.0.3 Patch 9 on Windows Platforms
* 10.2.0.4 (Server Patch Set)
* 11.1.0.6 (Base Release)
* Process May Dump (ORA-7445) / Abend / Abort
* Dump in or under kpopfr / kposdi
* (None Specified)

Symptoms:

Related To:

Description

Repeatedly executing a query can lead to a dump in kpopfr.

eg:

create table test

( c000 char(2000),

c001 char(2000),

c523 char(2000),

c524 char(576));

— sum of all column size is 1048576(0x100000).

Run next shell script.

while [ 1 ]

do

echo “set feedback off”

echo “select * from test where c001 = ‘A’;”

done | sqlplus -s scott/tiger

^

Dump occurs

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any

symptoms here does not confirm that you are encountering this problem. Always consult with Oracle

Support for advice.

References

Bug 5753629 (This link will only work for PUBLISHED bugs)

Restoring a user's original password

WARNING
=======

The method described in this article is not officially supported by development,
therefore when you get errors using this procedure support cannot file a bug for it.
Please make no mistakes when setting the HEX value with the VALUES keyword as bad
values may cause internal errors. This undocumented feature exists to support
export / import and retain the original passwords.

Problem Description:
====================

How can you restore a users original password ?

Solution Description:
=====================
With the output of dba_users you can restore a user's old password by
issuing the following:

   alter user <name> identified by values '<original_encrypted_password>';

For example:
============
SQL> create user test identified by test
  2  default tablespace users temporary tablespace temp;

User created.

SQL> select username, password from dba_users where username= 'TEST';

USERNAME   PASSWORD
---------- ------------------------------
TEST       CDC423BEB32B1812

SQL> alter user test identified by test1;
User altered.

SQL> select username, password from dba_users where username= 'TEST';

USERNAME   PASSWORD
---------- ------------------------------
TEST       22F2E341BF4B8764

SQL> alter user test identified by values 'CDC423BEB32B1812';
User altered.

SQL> connect test/test
Connected.

Solution Explanation:
=====================
To do this you would need a spool of dba_users taken before the password was
changed.

Oracle中如何追踪savepoint

Oracle中的savepoint是事务中的标示符,以帮助我们做到部分操作的回滚。

我们可以通过trace找出会话中自上次commit以来所有的savepoint保存点,要阅读这些trace内容你需要有user_dump_dest目录的相关权限。

SQL> oradebug setmypid;
已处理的语句
SQL>
SQL>
SQL> insert into  system.linktest values(2);

已创建 1 行。

SQL> savepoint a;

保存点已创建。

SQL> insert into system.linktest values(3);

已创建 1 行。

SQL> savepoint b;

保存点已创建。

SQL> insert into system.linktest values(4);

已创建 1 行。

SQL> savepoint d;

保存点已创建。

SQL> oradebug event immediate trace name savepoints level 1;

已处理的语句
SQL> oradebug tracefile_name;
e:\oracle\product\10.2.0\admin\orcln\udump\orcln_ora_1756.trc

其中 oradebug setmypid;  表示要trace的session为当前会话。

在非mts环境下,我们也可以通过如:

oradebug setospid OSPID;         — 通过指定OS中的进程号,即v$process中的spid

oradebug setorapid ORAPID;      — 通过指定Oracle 中的进程号,即v$process中的pid

来定制我们需要trace的相关session。

退回到命令行格式,该trace主要内容如下:

*** 2009-09-15 12:39:37.828
*** SERVICE NAME:(SYS$USERS) 2009-09-15 12:39:37.828
*** SESSION ID:(151.83) 2009-09-15 12:39:37.828
====================================================
SAVEPOINT FOR CURRENT PROCESS
——————————
flag: 0x1
name: D
dba: 0x800084, sequence #: 177, record #: 51, savepoint #: 81
status: VALID, next: 28236068
name: B
dba: 0x800084, sequence #: 177, record #: 50, savepoint #: 66
status: VALID, next: 282373F4
name: A
dba: 0x800084, sequence #: 177, record #: 49, savepoint #: 52
status: VALID, next: 00000000

metalink中的相关介绍如下:

Subject:

How To Find Out The Savepoint For Current Process

Doc ID:

108611.1

Type:

BULLETIN

Modified Date:

16-JUN-2004

Status:

PUBLISHED

PURPOSE

——-

This information shows you how to get the savepoints you have

issued since the last commit was issued.

SCOPE & APPLICATION

——————-

Any user can follow the steps to find out the savepoints in

their own session.  However, only the the user who have read

permission to the user_dump_dest directory can view the

output.

Steps to Retrieve the Savepoint Issued after the Last Commit

————————————————————

In the following illustration, two savepoints are created in

the same session.  Then a savepoints dump is issued to get

the savepoint information.  An example of the dump file is

included.

SQL> insert into emp (empno, ename)

2  values (9995, ‘vso’);

1 row created.

SQL> savepoint pt1;

Savepoint created.

SQL> insert into emp (empno, ename)

2  values (9994, ‘vso’);

1 row created.

SQL> savepoint pt2;

Savepoint created.

SQL> alter session set events

2  ‘immediate trace name savepoints level 1’;

Session altered.

A trace file is generated in the user_dump_directory.  The

content of the trace file is included in the following:

Dump file /u04/app/oracle/admin/R805/udump/r805_ora_18763.trc

Oracle8 Enterprise Edition Release 8.0.5.2.1 – Production

With the Partitioning and Objects options

PL/SQL Release 8.0.5.2.0 – Production

ORACLE_HOME = /u04/app/oracle/product/8.0.5

System name:    SunOS

Node name:      rtcsol1

Release:        5.6

Version:        Generic_105181-17

Machine:        sun4u

Instance name: R805

Redo thread mounted by this instance: 1

Oracle process number: 8

Unix process pid: 18763, image: oracleR805

*** 2000.04.18.14.11.37.000

*** SESSION ID:(7.391) 2000.04.18.14.11.37.000

====================================================

SAVEPOINT FOR CURRENT PROCESS

——————————

flag: 0x1

name: PT2

dba: 0x80020e, sequence #: 0, record #: 9, savepoint #: 131

status: VALID, next: 10a0868

name: PT1

dba: 0x80020e, sequence #: 0, record #: 7, savepoint #: 109

status: VALID, next: 0

OPT_PARAM Hint

Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.3 – Release: 10.2 to 10.2
Information in this document applies to any platform.
Goal
This article is explains the new optimizer hint “OPT_PARAM” introduced in 10g R2.
Solution
“OPT_PARAM” is a new optimizer hint introduced in 10g Release 2. This hint behaves the same way as
setting a parameter (e.g, using alter session) except that the effect is for the statement only. The hint only works for optimizer parameters. Global parameters such as optimizer_features_enable are not covered but optimizer_features_enable specifically has its own hint:

/*+ optimizer_features_enable(‘9.2.0’) */

@For an INTERNAL list of usable parameters see: Note:986618.1 Parameters useable by OPT_PARAM hint
Hint Syntax
The syntax is:

/*+ opt_param( [,] ) */

parameter_name is the name of a parameter
parameter_value is its value.

If the parameter contains a numeric value, the parameter value has to be specified without quotes.
The hint can be used to set multiple parameters by repeating the hint, i.e.

/*+ opt_param( [,] )
opt_param( [,] )
*/

Basic Usage Example
For example, the following hint sets <> to ‘false’ when added to a statement:

/*+ opt_param(‘hash_join_enabled’,’false’) */

e.g.:
Without the hint:


SQL> select empno from emp e, dept d where e.ename=d.dname

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     8 |   160 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     8 |   160 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    40 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    28 |   280 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

With the hint the hash join is disabled choosing a different plan:


SQL> select /*+ opt_param('hash_join_enabled','false') */ empno 
from emp e, dept d where e.ename=d.dname;


----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     8 |   160 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN         |      |     8 |   160 |     8  (25)| 00:00:01 |
|   2 |   SORT JOIN         |      |     4 |    40 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    40 |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |    28 |   280 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP  |    28 |   280 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------



Multiple Parameter Settings example
The OPT_PARAM hint can be specified more than once Time to adjust more than one parameter at once as follows:


/*+ OPT_PARAM('_always_semi_join' 'off')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('query_rewrite_enabled' 'false')
      OPT_PARAM('_new_initial_join_orders' 'false')
      OPT_PARAM('optimizer_dynamic_sampling' 1)
      OPT_PARAM('optimizer_index_cost_adj' 1) */

如何找出Oracle中需要或值得重建的索引

This script determines whether an index is a good candidate for a rebuild or for
a bitmap index.  All indexes for a given schema or for a subset of schema’s are
analyzed (except indexes under SYS and SYSTEM)

Instructions

Execution Environment:
<SQL, SQL*Plus, iSQL*Plus>

Access Privileges:
Requires DBA privileges in order to be executed.

Usage:
sqlplus <user>/<pw> @rebuild.index.sql

Instructions:
Copy the script into the file ind_an.sql. Execute the script from SQL*Plus connected
with a user with DBA privileges.  The script requires to parameters:

1. Name of the output file where the report while be generated
2. Name of the SCHEMA to be analyzed.

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.

Description

This script determines whether an index is a good candidate for a rebuild or for
a bitmap index.  All indexes for a given schema or for a subset of schema’s are
analyzed (except indexes under SYS and SYSTEM).

REM =============================================================
REM
REM                         rebuild_indx.sql
REM
REM  Copyright (c) Oracle Software, 1998 - 2000
REM
REM  Author  : Jurgen Schelfhout
REM
REM  The sample program in this article is provided for educational
REM  purposes only and is NOT supported by Oracle Support Services.
REM  It has been tested internally, however, and works as documented.
REM  We do not guarantee that it will work for you, so be sure to test
REM  it in your environment before relying on it.
REM
REM  This script will analyze all the indexes for a given schema
REM  or for a subset of schema's. After this the dynamic view
REM  index_stats is consulted to see if an index is a good
REM  candidate for a rebuild or for a bitmap index.
REM
REM  Database Version : 7.3.X and above.
REM
REM  NOTE:  If running this on 10g, you must exclude the
REM  objects in the Recycle Bin
REM        cursor c_indx is
REM          select owner, table_name, index_name
REM            from dba_indexes
REM           where owner like upper('&schema')
REM             and table_name not like 'BIN$%'
REM             and owner not in ('SYS','SYSTEM');
REM
REM  Additional References for Recycle Bin functionality:
REM  Note.265254.1 Flashback Table feature in Oracle Database 10g
REM  Note.265253.1 10g Recyclebin Features And How To Disable it(_recyclebin)
REM
REM =============================================================

prompt
ACCEPT spoolfile CHAR prompt 'Output-file : ';
ACCEPT schema CHAR prompt 'Schema name (% allowed) : ';
prompt
prompt
prompt Rebuild the index when :
prompt   - deleted entries represent 20% or more of the current entries
prompt   - the index depth is more then 4 levels.
prompt Possible candidate for bitmap index :
prompt   - when distinctiveness is more than 99%
prompt

spool &spoolfile;
set serveroutput on;
set verify off;
set linesize 140;
declare
  c_name        INTEGER;
  ignore        INTEGER;
  height        index_stats.height%TYPE := 0;
  lf_rows       index_stats.lf_rows%TYPE := 0;
  del_lf_rows   index_stats.del_lf_rows%TYPE := 0;
  distinct_keys index_stats.distinct_keys%TYPE := 0;
  cursor c_indx is
    select owner, table_name, index_name
      from dba_indexes
     where owner like upper('&schema')
       and owner not in ('SYS', 'SYSTEM');
begin
  dbms_output.enable(1000000);
  dbms_output.put_line('Owner           Index Name                              % Deleted Entries Blevel Distinctiveness');
  dbms_output.put_line('--------------  ---------------------------------            ------------  -----           -----');

  c_name := DBMS_SQL.OPEN_CURSOR;
  for r_indx in c_indx loop
    DBMS_SQL.PARSE(c_name,
                   'analyze index ' || r_indx.owner || '.' ||
                   r_indx.index_name || ' validate structure',
                   DBMS_SQL.NATIVE);
    ignore := DBMS_SQL.EXECUTE(c_name);

    select HEIGHT,
           decode(LF_ROWS, 0, 1, LF_ROWS),
           DEL_LF_ROWS,
           decode(DISTINCT_KEYS, 0, 1, DISTINCT_KEYS)
      into height, lf_rows, del_lf_rows, distinct_keys
      from index_stats;
    /*
    - Index is considered as candidate for rebuild when :
    -   - when deleted entries represent 20% or more of the current entries
    -   - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
    - Index is (possible) candidate for a bitmap index when :
    -   - distinctiveness is more than 99%
    */
    if (height > 5) OR ((del_lf_rows / lf_rows) > 0.2) then
      dbms_output.put_line(rpad(r_indx.owner, 16, ' ') ||
                           rpad(r_indx.index_name, 40, ' ') ||
                           lpad(round((del_lf_rows / lf_rows) * 100, 3),
                                17,
                                ' ') || lpad(height - 1, 7, ' ') ||
                           lpad(round((lf_rows - distinct_keys) * 100 /
                                      lf_rows,
                                      3),
                                16,
                                ' '));
    end if;

  end loop;
  DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/
spool off;
set verify on;

Sample Output:

SQL> @rebuild_index

Output-file : index_rebuild
Schema name (% allowed) : maclean

Rebuild the index when :
- deleted entries represent 20% or more of the current entries
- the index depth is more then 4 levels.
Possible candidate for bitmap index :
- when distinctiveness is more than 99%

Owner           Index Name                              % Deleted Entries Blevel Distinctiveness
--------------  ---------------------------------            ------------  -----           -----
MACLEAN         SYS_MTABLE_00000CFD4_IND_2                             25      0              25
MACLEAN         SYS_MTABLE_00000D3F3_IND_2                         33.333      0          33.333
PL/SQL procedure successfully completed.

Oracle在硬件领域正式向IBM宣战

在对原SUN客户的公示中,Larry Ellison火药味十足地表示将在硬件领域与龙头老大IBM争霸。

公示向原SUN客户表示一下四点:

  1. 在Sparc架构上花费比SUN更多的开发费用。(当然光钱多,似乎有点形而上)
  2. 在Solaris操作系统上花费比SUN更多的开发费用。(Solaris已经十分健全,Oracle可能让其更自动化–auto tuning,更好的支持?大metalink计划?)
  3. 投入超过2倍于SUN的销售和技术专家服务于Sparc和Solaris的业务。(销售当然要多,这是Oracle的老路)
  4. 整合Oracle软件与Sun硬件,将明显提升原Sun的硬件性能。

公示结尾为larry Ellison的结语:

We’re in it to win it. IBM,we ‘re looking foward to competing with you in hardware business.

oracle com iobm

这份开战宣言表明了Oracle在硬件领域发展的决心,以及同IBM争霸的勇气。

两家同样以销售著名的公司(相对SUN等技术著名公司),将在未来几年内为我们展示新一轮的IT航母兼并战。

沪ICP备14014813号-2

沪公网安备 31010802001379号