admin – 第305页

如何公开Oracle trace文件？

2010/08/11 by admin 3 Comments

隐式参数_trace_files_public决定了Oracle产生的trace文件是否公开，该参数默认值为FALSE，也就是非DBA/OINSTALL组的用户是没有权限读取数据库产生的trace文件的;在某些场合中我们需要让非DBA组的用户也能访问trace文件，就可以通过修改该参数实现。请看下面的例子:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> col name for a20
SQL> col value for a20
SQL> col describ for a40
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.inst_id = USERENV ('Instance')
  4   AND y.inst_id = USERENV ('Instance')
  5   AND x.indx = y.indx
  6  AND x.ksppinm LIKE '%_trace_files_public%'
  7  order by x.ksppinm;

NAME                 VALUE                DESCRIB
-------------------- -------------------- ----------------------------------------
_trace_files_public  FALSE                Create publicly accessible trace files

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug ipc;
Information written to trace file.
SQL> oradebug tracefile_name;
/s01/10gdb/admin/YOUYUS/udump/youyus_ora_10268.trc
SQL> !ls -l /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10268.trc
-rw-r----- 1 maclean oinstall 4206 Aug 11 20:51 /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10268.trc
/*所产生的trace文件权限为640,非oinstall组用户无权限读取该文件*/

SQL> alter system set "_trace_files_public"=true;
alter system set "_trace_files_public"=true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
/*修改该参数需要重启实例*/

SQL> alter system set "_trace_files_public"=true scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2084264 bytes
Variable Size             922747480 bytes
Database Buffers          637534208 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug ipc;
Information written to trace file.
SQL> oradebug tracefile_name;
/s01/10gdb/admin/YOUYUS/udump/youyus_ora_10430.trc
SQL> ! ls -l /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10430.trc
-rw-r--r-- 1 maclean oinstall 5471 Aug 11 20:54 /s01/10gdb/admin/YOUYUS/udump/youyus_ora_10430.trc
/*other组用户也具有了读权限*/

SQL> ! ls -l /s01/10gdb/admin/YOUYUS/
total 24
drwxr-x--- 2 maclean oinstall 4096 Aug 11 20:56 adump
drwxr-x--- 2 maclean oinstall 4096 Aug 11 20:54 bdump
drwxr-x--- 2 maclean oinstall 4096 Aug  5 21:35 cdump
drwxr-x--- 2 maclean oinstall 4096 Aug  5 21:36 dpdump
drwxr-x--- 2 maclean oinstall 4096 Aug  5 21:37 pfile
drwxr-x--- 2 maclean oinstall 4096 Aug 11 20:54 udump
/*请注意修改_trace_files_public为true,并不会修改trace所在目录的权限，Oracle默认建立bdump/udump等trace目录时分配的权限为750,other组用户无法进入这些目录，需要修改目录权限为755,即o+r+x*/

SQL> ! chmod o+r+x /s01/10gdb/admin/YOUYUS/*dump

SQL>  ! ls -l /s01/10gdb/admin/YOUYUS/
total 24
drwxr-xr-x 2 maclean oinstall 4096 Aug 11 20:56 adump
drwxr-xr-x 2 maclean oinstall 4096 Aug 11 20:54 bdump
drwxr-xr-x 2 maclean oinstall 4096 Aug  5 21:35 cdump
drwxr-xr-x 2 maclean oinstall 4096 Aug  5 21:36 dpdump
drwxr-x--- 2 maclean oinstall 4096 Aug  5 21:37 pfile
drwxr-xr-x 2 maclean oinstall 4096 Aug 11 20:54 udump

/*需要注意的另一点是修改_trace_files_public参数并不会引起既有的trace文件的权限被修改，典型的例子是alert log告警日志*/
[maclean@rh2 bdump]$ ls -l
total 20
-rw-r----- 1 maclean oinstall 12971 Aug 11 21:17 alert_YOUYUS.log
-rw-r--r-- 1 maclean oinstall   690 Aug 11 21:12 youyus_lgwr_10514.trc

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.inst_id = USERENV ('Instance')
  4   AND y.inst_id = USERENV ('Instance')
  5   AND x.indx = y.indx
  6  AND x.ksppinm LIKE '%_trace_files_public%'
  7  order by x.ksppinm;

NAME                 VALUE                DESCRIB
-------------------- -------------------- ----------------------------------------
_trace_files_public  FALSE                Create publicly accessible trace files

SQL> alter system set "_trace_files_public"=true scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2084264 bytes
Variable Size             922747480 bytes
Database Buffers          637534208 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.
SQL> !ls -l
total 32
-rw-r----- 1 maclean oinstall 21189 Aug 11 21:20 alert_YOUYUS.log
-rw-r--r-- 1 maclean oinstall   690 Aug 11 21:12 youyus_lgwr_10514.trc
-rw-r--r-- 1 maclean oinstall   690 Aug 11 21:20 youyus_lgwr_11136.trc

Filed Under: Oracle Tagged With: database, ORA, Oracle, Oracle Bug, RAC, sql

11g新特性之IO校准(IO Calibration)

2010/08/11 by admin 1 Comment

IO校准(IO Calibration)特性可以帮助我们了解存储系统的真实性能，以进一步判断I/O性能问题是由数据库还是存储系统自身引起的。I/O校准特性(IO Calibration)通过对Oracle数据文件的随机I/O访问存储介质，其结论值更符合数据库IO性能的真实情况。
在使用该特性前，我们要确保满足以下条件:

调用该存储过程需要用到SYSDBA权限

TIME_STATISTICS 参数为true:

SQL> show parameter timed_statistics;

NAME TYPE VALUE
———————————— ———– ——————————
timed_statistics boolean TRUE

必须打开异步IO；注意在使用文件系统时，FILESYSTEMIO_OPTIONS需设为SETALL或ASYNC，否则Oracle不会启用异步IO。

可以通过以下查询检验是否启用了异步IO:

SQL> SELECT NAME, ASYNCH_IO
2    FROM V$DATAFILE F, V$IOSTAT_FILE I
3   WHERE F.FILE# = I.FILE_NO
4     AND FILETYPE_NAME = 'Data File';

NAME                                                                             ASYNCH_IO
-------------------------------------------------------------------------------- ---------
D:\TOOLS\ADMINSTRATORG\ORADATA\PROD\DATAFILE\O1_MF_SYSTEM_65DN8HXT_.DBF       ASYNC_ON
D:\TOOLS\ADMINSTRATORG\ORADATA\PROD\DATAFILE\O1_MF_SYSAUX_65DN8J18_.DBF       ASYNC_ON
D:\TOOLS\ADMINSTRATORG\ORADATA\PROD\DATAFILE\O1_MF_UNDOTBS1_65DN8J1S_.DBF     ASYNC_ON
D:\TOOLS\ADMINSTRATORG\ORADATA\PROD\DATAFILE\O1_MF_USERS_65DN8J2X_.DBF        ASYNC_ON

IO Calibration特性可以通过DBMS_RESOURCE_MANAGER.CALIBRATE_IO存储过程调用；该过程会对Oracle数据文件引发一系列IO敏感的只读工作负载(由1MB大小的随机IO组成)，从而判断存储系统所能持续的最大IOPS(每秒最大IO请求数)和MBPS(每秒IO传输速率)。为了使结果更具代表性，应当保持IO Calibration测试过程中整个数据库是空闲的，没有其他IO负载损耗。

让我们具体来看一下CALIBRATE_IO存储过程的调用方法:

 -- Initiate an I/O calibration
 --
 -- Input arguments:
 --  num_physical_disks   - Approximate number of physical disks in
 --                         the database storage
 --  max_latency          - Maximum tolerable latency in milliseconds for
 --                         database-block-sized IO requests
 --
 -- Output arguments:
 --  max_iops       - Maximum number of I/O requests per second that can be
 --                   sustained.  The I/O requests are randomly-distributed,
 --                   database-block-sized reads.
 --  max_mbps       - Maximum throughput of I/O that can be sustained,
 --                   expressed in megabytes per second. The I/O requests
 --                   are randomly-distributed, 1 megabyte reads.
 --  actual_latency - Average latency of database-block-sized I/O
 --                   requests at "max_iops" rate, expressed in milliseconds.
 --
  PROCEDURE calibrate_io (
   num_physical_disks  IN  PLS_INTEGER DEFAULT 1,
   max_latency         IN  PLS_INTEGER DEFAULT 20,
   max_iops            OUT PLS_INTEGER,
   max_mbps            OUT PLS_INTEGER,
   actual_latency      OUT PLS_INTEGER
   );

其中输入参数num_physical_disks用以指定数据库存储所用物理磁盘的近似数；max_latency指定了客户所能容忍的最大IO 延迟时间，单位为ms;一般我们认为IO平均等待在10ms左右是一种较好的表现，远大于这个数字往往说明IO负载过高了，这里我们假定15ms是我们的deadline。

SQL> set serveroutput on;
SQL> DECLARE
  2    lat  INTEGER;
  3    iops INTEGER;
  4    mbps INTEGER;
  5  BEGIN
  6  -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);
  7     DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 15, iops, mbps, lat);
  8
  9    DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
 10    DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
 11    dbms_output.put_line('max_mbps = ' || mbps);
 12  end;
 13  /

max_iops = 127
latency  = 15
max_mbps = 38

PL/SQL procedure successfully completed
/*平均延迟为15ms时，最大iops为127,mbps为38*/

/*执行过程中也可以通过V$IO_CALIBRATION_STATUS视图了解进度*/
SQL> select * from V$IO_CALIBRATION_STATUS;

STATUS        CALIBRATION_TIME
------------- --------------------------------------------------------------------------------
IN PROGRESS   11-8月 -10 12.02.35.121 下午

SQL> alter session set nls_timestamp_format='HH24:MI';

Session altered
SQL> col start_time for a10;
SQL> col end_time for a10;
SQL> select * from DBA_RSRC_IO_CALIBRATE;

START_TIME END_TIME     MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY NUM_PHYSICAL_DISKS
---------- ---------- ---------- ---------- ---------- ---------- ------------------
12:55      12:59             127         38         14         15                  2
/*DBA_RSRC_IO_CALIBRATE视图记录了IO CALIBRATION的历史信息*/

[gview file=”http://askmac.cn/wp-content/uploads/resource/calibrate_io.pdf”]

Filed Under: Oracle Tagged With: dbms package, io, New Feature, Oracle 11g新特性

11g中AWR新快照视图

2010/08/09 by admin 2 Comments

DBA_HIST_IOSTAT_DETAIL视图记录了不同类型和组件功能所作IO的统计数据。这个视图的数据来自于V$IOSTAT_FILE和V$IOSTAT_FUNCTION 2个动态视图的快照。值得一提的是V$IOSTAT_FILE，它是11g中新引入的动态性能视图:

SQL> select filetype_name, asynch_io, access_method, retries_on_error
  2    from v$iostat_file;

FILETYPE_NAME                ASYNCH_IO ACCESS_METH RETRIES_ON_ERROR
---------------------------- --------- ----------- ----------------
Other                        ASYNC_OFF OS_LIB                     0
Control File                 ASYNC_OFF                            0
Log File                     ASYNC_OFF                            0
Archive Log                  ASYNC_OFF                            0
Data File Backup             ASYNC_OFF                            0
Data File Incremental Backup ASYNC_OFF                            0
Archive Log Backup           ASYNC_OFF                            0
Data File Copy               ASYNC_OFF                            0
Flashback Log                ASYNC_OFF                            0
Data Pump Dump File          ASYNC_OFF                            0
Data File                    ASYNC_ON  OS_LIB                     0
/*ASYNCH_IO列很好地标示了Oracle对于该类型文件是否启用了异步IO,这样我们就无需通过SYSTEM CALL TRACE来确定这一点了;
 retries_on_error累计了物理读取的失败次数，对我们发现磁盘坏道有一定帮助。目前的11.2.0.1官方Reference没有ACCESS_METHOD列的资料，猜想可能分为OS_LIB和ASM及Exdata等多种情况，有待验证。

我们回过头来继续讨论DBA_HIST_IOSTAT_DETAIL视图!

SQL> desc dba_hist_iostat_detail;
Name                  Type         Nullable Default Comments
--------------------- ------------ -------- ------- --------
SNAP_ID               NUMBER
DBID                  NUMBER
INSTANCE_NUMBER       NUMBER
FUNCTION_ID           NUMBER
FUNCTION_NAME         VARCHAR2(30)
FILETYPE_ID           NUMBER
FILETYPE_NAME         VARCHAR2(30)
SMALL_READ_MEGABYTES  NUMBER
SMALL_WRITE_MEGABYTES NUMBER
LARGE_READ_MEGABYTES  NUMBER
LARGE_WRITE_MEGABYTES NUMBER
SMALL_READ_REQS       NUMBER
SMALL_WRITE_REQS      NUMBER
LARGE_READ_REQS       NUMBER
LARGE_WRITE_REQS      NUMBER
NUMBER_OF_WAITS       NUMBER
WAIT_TIME             NUMBER

SQL> col SMALL_READ_MEGABYTES for 999999;
SQL> col SMALL_WRITE_MEGABYTES for 999999;
SQL> col LARGE_READ_MEGABYTES for 999999;
SQL> col LARGE_WRITE_MEGABYTES for 999999;
SQL> col FILETYPE_NAME for a25;
SQL> select snap_id,
  2         function_name,
  3         filetype_name,
  4         SMALL_READ_MEGABYTES,
  5         SMALL_WRITE_MEGABYTES,
  6         LARGE_READ_MEGABYTES,
  7         LARGE_WRITE_MEGABYTES,
  8         WAIT_TIME
  9    from dba_hist_iostat_detail
 10   where rownum < 16;

   SNAP_ID FUNCTION_NAME                  FILETYPE_NAME             SMALL_ SMALL_ LARGE_ LARGE_  WAIT_TIME
---------- ------------------------------ ------------------------- ------ ------ ------ ------ ----------
         1 DBWR                           Control File                   1      0      0      0        310
         1 LGWR                           Control File                   0      0      0      0         94
         1 Others                         Control File                 122     37      0      0      71635
         2 DBWR                           Control File                   1      0      0      0        310
         2 LGWR                           Control File                   0      0      0      0         94
         2 Others                         Control File                 251     77      0      0     159025
         3 Others                         Control File                  14      7      0      0      10339
         4 Others                         Control File                  96     60      0      0      87516
         5 Others                         Control File                 151     96      0      0     139796
         6 DBWR                           Control File                   0      0      0      0         15
         6 Others                         Control File                 210    135      0      0     189114
         7 DBWR                           Control File                   0      0      0      0         15
         7 Others                         Control File                 269    174      0      0     239640
         8 DBWR                           Control File                   0      0      0      0         15
         8 Others                         Control File                 328    213      0      0     288425

15 rows selected

其中SMALL_READ/WRITE代表单块读写，LARGE_READ/WRITE代表多块读写，wait_time的单位是千分之一秒(ms)。

SQL> select snap_id,
  2         function_name,
  3         filetype_name,
  4         SMALL_READ_MEGABYTES,
  5         SMALL_WRITE_MEGABYTES,
  6         LARGE_READ_MEGABYTES,
  7         LARGE_WRITE_MEGABYTES,
  8         WAIT_TIME
  9    from dba_hist_iostat_detail
 10   where filetype_name = 'Control File'
 11     and (LARGE_READ_REQS > 0 or LARGE_WRITE_REQS > 0);

未选定行
/*Oracle对控制文件只做单块读写*/

SQL> col FILETYPE_NAME for a15;
SQL> col function_name for a10;
SQL> select function_name,
  2         filetype_name,
  3         small_read_reqs,
  4         small_write_reqs,
  5         large_read_reqs,
  6         large_write_reqs
  7    from dba_hist_iostat_detail
  8   where filetype_name = 'Log File'
  9  and (SMALL_READ_REQS > 0 or LARGE_READ_REQS > 0);

FUNCTION_N FILETYPE_NAME   SMALL_READ_REQS SMALL_WRITE_REQS LARGE_READ_REQS LARGE_WRITE_REQS
---------- --------------- --------------- ---------------- --------------- ----------------
LGWR       Log File                      4             9140               0               86
LGWR       Log File                      4             2762               0              218
LGWR       Log File                      8             3512               0              222
LGWR       Log File                      8             4304               0              226
LGWR       Log File                      4             1996               0              210
LGWR       Log File                      8             5296               0              252
LGWR       Log File                      8             6016               0              254
LGWR       Log File                      8             7224               0              274
LGWR       Log File                      4            11536               0              232
LGWR       Log File                      8            13320               0              256

SQL> select snap_id,
  2         function_name,
  3         filetype_name,
  4         SMALL_READ_MEGABYTES,
  5         SMALL_WRITE_MEGABYTES,
  6         LARGE_READ_MEGABYTES,
  7         LARGE_WRITE_MEGABYTES,
  8         WAIT_TIME
  9    from dba_hist_iostat_detail
 10   where filetype_name = 'Log File';

   SNAP_ID FUNCTION_NAME                  FILETYPE_NAME             SMALL_ SMALL_ LARGE_ LARGE_  WAIT_TIME
---------- ------------------------------ ------------------------- ------ ------ ------ ------ ----------
         2 LGWR                           Log File                       0     60      0     27         30
        35 LGWR                           Log File                       0     23      0     48          0
         6 LGWR                           Log File                       0     12      0      8          0
         7 LGWR                           Log File                       0     15      0     10          0
         8 LGWR                           Log File                       0     18      0     12          0
        13 LGWR                           Log File                       0      5      0      4          0
        15 LGWR                           Log File                       0      6      0      2          0
        17 LGWR                           Log File                       0      4      0      3          0
        19 LGWR                           Log File                       0     11      0      7          0
        20 LGWR                           Log File                       0     14      0      8          0
        37 LGWR                           Log File                       0     13      0     27          0
        38 LGWR                           Log File                       0     15      0     29          0
        41 LGWR                           Log File                       0     13      0    152         46
        42 LGWR                           Log File                       0     16      0    153         61
        43 LGWR                           Log File                       0     19      0    155         61
        49 LGWR                           Log File                       0      2      0      0          0
         3 LGWR                           Log File                       0      1      0      0          0
         4 LGWR                           Log File                       0      7      0      4          0
         5 LGWR                           Log File                       0      9      0      6          0
        12 LGWR                           Log File                       0      1      0      0          0
/*日志文件在写出时会伴有少量的日志单块读操作，日志文件的写兼有单块写和多块写2种操作*/

另外，你大概和我一样不太喜欢使用OEM界面，不过OEM界面有个很方便的功能就是显示短期内的Avg Active Session Count(AAS)。什么？那你从来没看到过这种东西？下面这张截图大概可以勾起你的回忆:

早在10g时代就有人写了一个显示短期内AAS的脚本，在11g中该脚本得到了简化了:

set echo off;
set verify off;
alter session set nls_date_format='HH24:MI';
select *
  from (select nvl(wait_class, 'CPU') activity,
               trunc(sample_time, 'MI') time
          from v$active_session_history) v pivot(count(*) for activity in('CPU' as
                                                                          "CPU",
                                                                          'Concurrency' as
                                                                          "Concurrency",
                                                                          'System I/O' as
                                                                          "System I/O",
                                                                          'User I/O' as
                                                                          "User I/O",
                                                                          'Administrative' as
                                                                          "Administrative",
                                                                          'Configuration' as
                                                                          "Configuration",
                                                                          'Application' as
                                                                          "Application",
                                                                          'Network' as
                                                                          "Network",
                                                                          'Commit' as
                                                                          "Commit",
                                                                          'Scheduler' as
                                                                          "Scheduler",
                                                                          'Cluster' as
                                                                          "Cluster",
                                                                          'Queueing' as
                                                                          "Queueing",
                                                                          'Other' as
                                                                          "Other"))
 where time > sysdate - interval '&last_min' minute
 order by time;

我们也可以通过ASH查找Oracle中的TOP SESSION和TOP SQL:

/*找出短期内TOP SQL的sql_id和活动历史*/
select ash.SQL_ID,
       sum(decode(ash.session_state, 'ON CPU', 1, 0)) "CPU",
       sum(decode(ash.session_state, 'WAITING', 1, 0)) -
       sum(decode(ash.session_state,
                  'WAITING',
                  decode(en.wait_class, 'User I/O', 1, 0),
                  0)) "WAIT",
       sum(decode(ash.session_state,
                  'WAITING',
                  decode(en.wait_class, 'User I/O', 1, 0),
                  0)) "IO",
       sum(decode(ash.session_state, 'ON CPU', 1, 1)) "TOTAL"
  from v$active_session_history ash, v$event_name en
 where SQL_ID is not NULL
   and en.event# = ash.event#
 group by sql_id
 order by sum(decode(session_state, 'ON CPU', 1, 1)) desc;

SQL_ID               CPU       WAIT         IO      TOTAL
------------- ---------- ---------- ---------- ----------
a01hp0psv0rrh          0          2          7          9
24g90qj2b7ywk          0          5          1          6
2amsp6skc6tjv          0          0          5          5
46quk68k7akpa          0          3          1          4
2ufrf9vk4kcwj          0          0          3          3
1w8m6dwy66ttn          0          0          3          3
8uxr3scz9bmxd          0          0          3          3
6htq3p9j91y0s          0          0          3          3
cvn54b7yz0s8u          0          0          3          3
92f47aa2q2rmd          0          2          1          3

/*找出变量ivl指定分钟内的TOP CPU SESSION*/
Select session_id, count(*)
  from v$active_session_history
 where session_state = 'ON CPU'
   and SAMPLE_TIME > sysdate -(&ivl/(24 * 60))
 group by session_id
 order by count(*) desc;
输入 ivl 的值:  10
原值    4:    and SAMPLE_TIME > sysdate -(&ivl/(24 * 60))
新值    4:    and SAMPLE_TIME > sysdate -(10/(24 * 60))

SESSION_ID   COUNT(*)
---------- ----------
       136          4

/*找出变量ivl指定分钟内TOP WAITING SESSION*/
Select session_id, count(*)
  from v$active_session_history
 where session_state = 'WAITING'
   and SAMPLE_TIME > SYSDATE - (&ivl / (24 * 60))
 group by session_id
 order by count(*) desc;

输入 ivl 的值:  10
原值    4:    and SAMPLE_TIME > SYSDATE - (&ivl / (24 * 60))
新值    4:    and SAMPLE_TIME > SYSDATE - (10 / (24 * 60))

SESSION_ID   COUNT(*)
---------- ----------
         3         11

/*找出短期内的TOP SESSION及活动历史*/
select ash.session_id,
       ash.session_serial#,
       ash.user_id,
       ash.program,
       sum(decode(ash.session_state, 'ON CPU', 1, 0)) "CPU",
       sum(decode(ash.session_state, 'WAITING', 1, 0)) -
       sum(decode(ash.session_state,
                  'WAITING',
                  decode(en.wait_class, 'User I/O', 1, 0),
                  0)) "WAITING",
       sum(decode(ash.session_state,
                  'WAITING',
                  decode(en.wait_class, 'User I/O', 1, 0),
                  0)) "IO",
       sum(decode(session_state, 'ON CPU', 1, 1)) "TOTAL"
  from v$active_session_history ash, v$event_name en
 where en.event# = ash.event#
 group by session_id, user_id, session_serial#, program
 order by sum(decode(session_state, 'ON CPU', 1, 1));

以上脚本完全可以被ashrpt报表所替代，但在短期内做针对检查仍十分有效。

Filed Under: Oracle Tagged With: ORA, Oracle, Oracle 11g新特性, RAC, sql

分析Oracle Mutex等待事件

2010/08/08 by admin 5 Comments

Troubleshooting or analyzing waits on mutex events, i.e. ones that start with “cursor:”, are very similar to each other and below are some guidelines.

There are many bugs related to problems using mutexes in Oracle 10g, the first version where mutexes were introduced. If you are seeing excessive waits on mutexes, a quick fix may be to go back to the library cache latching mechanism. To do this, you can set the parameter _kks_use_mutex_pin=false.

Note: For mutex related waits, v$session.blocking_session is not populated in 10.2. However, v$session.blocking_session is populated in 11g R1.

To do further analysis, the P2RAW column in v$session gives the blocking session, i.e. the holder SID of the mutex in the upper 8 bytes. It is in hex so it needs to be converted in decimal. The following query provides the blockers with the most sessions blocked behind it as well as converts the P2RAW to an usable SID:

select p2raw, to_number(substr(to_char(rawtohex(p2raw)), 1, 8), ‘XXXXXXXX’) sid, count(1) sessions_waiting
from v$session
where event = ‘cursor: pin S wait on X’
group by p2raw, to_number(substr(to_char(rawtohex(p2raw)), 1, 8), ‘XXXXXXXX’);

P2RAW                      SID    SESSIONS_WAITING
—————-            —                  —————-
0000001F00000000    31                                    79

On 64 bit platforms 8 bytes are used, so the top 4 bytes are the holding session Id (if the mutex is held X), and the bottom 4 bytes are the ref count (if the mutex is held S). On 32 bit platforms 4 bytes are used, so the top 2 bytes are the holding session Id (if the mutex is held X) and the bottom 2 bytes are the ref count (if the mutex is held S).

p1 = The mutex Id
This has the same definition as v$mutex_sleep_history.mutex_identifier

p2raw = Holding Session Id | Ref Count

The most significant bytes always store the Holding Session Id (Holding SID). The least significant bytes always store the Ref Count.

Filed Under: Oracle Tagged With: mutex

Script:Lists Text index and it’s dependent objects

2010/08/07 by admin Leave a Comment

Script:Lists Text index and it’s dependent objects

set linesize 85;
set verify off;
spool bde_chk_imt.lst
accept idx_name prompt 'Enter the Text index name: '
accept tbl_name prompt 'Enter the base table name: '
prompt '-- Printing Object Information'

set pagesize 20
column segment_name format a25     heading 'Object|Name'
column tablespace_name format a10  heading 'Tablespace'
column segment_type format a10     heading 'Object|Type'
column owner format a10            heading 'Owner'       

select /*+ FIRST_ROWS */ unique s.segment_name, s.segment_type,s.tablespace_name,s.owner
from dba_segments s
where 
s.segment_name in (
(select /*+ FIRST_ROWS */ unique ic.index_name
  from dba_ind_columns IC
where 
ic.table_name like upper('%&&tbl_name%')) 
union
(select /*+ FIRST_ROWS */ unique t.table_name 
  from dba_tables T
where 
t.table_name like upper('%&&tbl_name%')) 
union 
(select /*+ FIRST_ROWS */ unique l.segment_name 
  from dba_lobs L
where 
l.table_name like upper('%&&tbl_name%'))
union
(select /*+ FIRST_ROWS */ unique ic.table_name
  from dba_ind_columns IC
where 
ic.index_name like upper('%&&tbl_name%')));

prompt '-- Printing Index Information'
prompt '-- $X index should be created with compress2 (i.e. Comp=ENAB)'
prompt '-- alter index  rebuild compress 2;'
prompt '-- Example: alter index DR$FND_LOBS_CTX$X rebuild compress 2;'

column index_name  format a35      heading 'Index|Name'
column index_type  format a10      heading 'Index|Type'
column compression format a4       heading 'Comp'
column table_name  format a30      heading 'Table|Name'

select unique index_name,index_type,substr(compression,1,4) compression, table_name 
from dba_indexes
where table_name like upper('%&&tbl_name%')
or index_name like upper('%&&idx_name%')
group by index_name,index_type, compression,table_name;

prompt'--Printing LOB Information'
prompt'--$R Table should be cached (i.e. Cached = YES)'
prompt'--ALTER TABLE tabname MODIFY LOB (lobname) ( CACHE );'
prompt'--tabname = R_TABLE name'
prompt'--lobname = lob column of R_TABLE, which is the 'DATA' column'
prompt'--(example: alter table DR$FND_LOBS_CTX$R modify lob (DATA) (CACHE);' 

column index_name format a25      heading 'Index|Name'
column segment_name format a25      heading 'LOB|Name'
column table_name format a20      heading 'Table|Name'
column cache format a6            heading 'Cached'

select segment_name,table_name, index_name, cache 
from dba_lobs
where table_name like upper('%&&tbl_name%')
order by segment_name,table_name, index_name, cache;

spool off;

Filed Under: Oracle, Oracle脚本script Tagged With: DBA_INDEXES

UNION ALL returning wrong results?

2010/08/06 by admin 5 Comments

有应用人员反映某套Linux上的11.2.0.1数据库系统中出现了UNION ALL后返回的结果集不正确的问题,我们具体分析下出现问题的其中一条语句:

SELECT MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME,
       MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID,
       MTL_SECONDARY_INVENTORIES.DESCRIPTION,
       MTL_SECONDARY_INVENTORIES.AVAILABILITY_TYPE,
       MTL_SECONDARY_INVENTORIES.MATERIAL_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.MATERIAL_OVERHEAD_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.RESOURCE_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.OVERHEAD_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.OUTSIDE_PROCESSING_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.ASSET_INVENTORY,
       MTL_SECONDARY_INVENTORIES.EXPENSE_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.ENCUMBRANCE_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.ATTRIBUTE3,
       MTL_SECONDARY_INVENTORIES.ATTRIBUTE5,
       WORKFLOW_START_TIMES.WORKFLOW_START_TIME
  FROM REPEMEAERP.MTL_SECONDARY_INVENTORIES,
       REPEMEAERP.WORKFLOW_START_TIMES
 WHERE MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT >
       TO_DATE('01/01/1900 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
   AND MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT <=
       WORKFLOW_START_TIMES.WORKFLOW_START_TIME
   AND WORKFLOW_START_TIMES.WORKFLOW_NAME =
       LTRIM(RTRIM('w_int_FreqBatch_EMEA'))
/*以上是QUERY A*/
UNION ALL
/*以下是QUERY B*/
SELECT DISTINCT 'WORKORDERS',
                MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID,
                'WORK ORDERS WITH WIP AS CATEGORY VALUE',
                1,
                0,
                0,
                0,
                0,
                0,
                1,
                0,
                0,
                'MOI',
                '0',
                WORKFLOW_START_TIMES.WORKFLOW_START_TIME
  FROM REPEMEAERP.MTL_SECONDARY_INVENTORIES, EIMMAINT.WORKFLOW_START_TIMES
 WHERE MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT >
       TO_DATE('01/01/1900 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
   AND MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT <=
       WORKFLOW_START_TIMES.WORKFLOW_START_TIME
   AND WORKFLOW_START_TIMES.WORKFLOW_NAME =
       LTRIM(RTRIM('w_int_FreqBatch_EMEA'))
/
138 rows selected.

以上查询语句中，QUERY A部分(也就是UNION ALL之前的SELECT语句)单独查询时返回返回69条记录，QUERY B部分单独查询时返回15记录，UNION ALL后返回的结果却是138条记录，而非84条记录。实际上这套系统也是最近才从10g迁移到11gr2上，之前在10g中同样的应用没有出过类似的问题，可以猜测是11g中新引入的某种特性存在可能引发wrong result的Bug。

具体思路虽然有了，但仍无法确定问题的关键所在；我们来看看该SQL的执行计划:

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                               |     7 |  2443 |    52   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                   |                               |     7 |  2443 |    52   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL             | WORKFLOW_START_TIMES          |     1 |    29 |    48   (0)| 00:00:01 |
|   3 |   VIEW                          | VW_JF_SET$9BAED2EA            |     1 |   320 |     4   (0)| 00:00:01 |
|   4 |    UNION ALL PUSHED PREDICATE   |                               |       |       |            |          |
|*  5 |     FILTER                      |                               |       |       |            |          |
|   6 |      TABLE ACCESS BY INDEX ROWID| MTL_SECONDARY_INVENTORIES     |     3 |   336 |     2   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN          | IDX_MTL_SECONDARY_INVENTORIES |     1 |       |     1   (0)| 00:00:01 |
|*  8 |     FILTER                      |                               |       |       |            |          |
|   9 |      TABLE ACCESS BY INDEX ROWID| MTL_SECONDARY_INVENTORIES     |     3 |    36 |     2   (0)| 00:00:01 |
|* 10 |       INDEX RANGE SCAN          | IDX_MTL_SECONDARY_INVENTORIES |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("WORKFLOW_START_TIMES"."WORKFLOW_NAME"='w_int_FreqBatch_EMEA')
5 - filter(TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')<"WORKFLOW_START_TIMES"."WORKFLOW_START_TIME") 7 - access("MTL_SECONDARY_INVENTORIES"."DW_UPDATE_DT">TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "MTL_SECONDARY_INVENTORIES"."DW_UPDATE_DT"<="WORKFLOW_START_TIMES"."WORKFLOW_START_TIME"
)
8 - filter(TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')<"WORKFLOW_START_TIMES"."WORKFLOW_START_TIME") 10 - access("MTL_SECONDARY_INVENTORIES"."DW_UPDATE_DT">TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "MTL_SECONDARY_INVENTORIES"."DW_UPDATE_DT"<="WORKFLOW_START_TIMES"."WORKFLOW_START_TIME"
)

你可能从以上执行计划中发现了两处十分陌生的字眼:UNION ALL PUSHED PREDICATE和VW_JF_SET$。它们是什么！？

先来说说JF,JF是join factorization的缩写，你可以把它翻译作链接因式分解，如果你学过离散数学或者数据库原理的话，那么这种在11.2.0.1中最新推出的基于成本的变换操作对你来说并不陌生。用公式的样式来表达大概是下面这样:

YYA,YYB和YYC是3个关联的数据对象亦或者是3个关联的结果集;
(YYA JOIN YYB) UNION [ALL] (YYA JOIN YYC)
可以转换成为:
YYA JOIN (YYB UNION [ALL] YYC)

这样做YYA部分只需要读取一次，还可以少做一次JOIN,听上去是挺不错的吧！
下面我们来看一个Oracle使用join factorization的十分简单的实例:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> drop table yya;

drop table yya

           *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> drop table yyb;

drop table yyb

           *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> create table yya as select rownum id1,rownum id2,rownum id3 from dual connect by level<=20000;
Table created.
SQL> create table yyb as select rownum id1,rownum id2,rownum id3 from dual connect by level<=20000;
Table created.

SQL> explain plan for
2  select * from yya ,yyb where yya.id1=yyb.id1
3  union all
4  select * from yya, yyb where yya.id1=yyb.id1;

Explained.

SQL> set linesize 100 pagesize 1400;

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 744914999

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    | 40000 |  2500K|    49   (3)| 00:00:01 |
|*  1 |  HASH JOIN           |                    | 40000 |  2500K|    49   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | YYA                | 20000 |   234K|    16   (0)| 00:00:01 |
|   3 |   VIEW               | VW_JF_SET$6E3F6682 | 40000 |  2031K|    32   (0)| 00:00:01 |
|   4 |    UNION-ALL         |                    |       |       |            |          |
|   5 |     TABLE ACCESS FULL| YYB                | 20000 |   761K|    16   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| YYB                | 20000 |   761K|    16   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("YYA"."ID1"="ITEM_1")

/*执行计划中出现了VW_JF_SET$F22B2A93,Oracle选择了使用join factorization，该执行计划总成本49*/

SQL> alter session set "_optimizer_join_factorization"=false;

Session altered.

/*隐藏参数_optimizer_join_factorization决定了优化器是否可以选用join factorization,现在我们禁用它*/
SQL> explain plan for
  2  select * from yya join yyb on yya.id1=yyb.id1
  3  union all
  4  select * from yya join yyb on yya.id1=yyb.id1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3439541885

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 40000 |  1992K|    66  (52)| 00:00:01 |
|   1 |  UNION-ALL          |      |       |       |            |          |
|*  2 |   HASH JOIN         |      | 20000 |   996K|    33   (4)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| YYA  | 20000 |   234K|    16   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| YYB  | 20000 |   761K|    16   (0)| 00:00:01 |
|*  5 |   HASH JOIN         |      | 20000 |   996K|    33   (4)| 00:00:01 |
|   6 |    TABLE ACCESS FULL| YYA  | 20000 |   234K|    16   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL| YYB  | 20000 |   761K|    16   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("YYA"."ID1"="YYB"."ID1")
   5 - access("YYA"."ID1"="YYB"."ID1")
/*禁用链接因式分解后，Oracle使用了常规的"笨办法"，成本上升到66*/

/*有趣的是下面的测试*/

SQL> alter session set "_optimizer_join_factorization"=true;

Session altered.

SQL> create table yyc as select * from yyb;

Table created.

SQL> explain plan for
  2  select * from yya,yyc where yya.id1=yyc.id1
  3  union all
  4  select * from yya,yyb where yya.id1=yyb.id1;

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4240055274

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 40000 |  1992K|    66  (52)| 00:00:01 |
|   1 |  UNION-ALL          |      |       |       |            |          |
|*  2 |   HASH JOIN         |      | 20000 |   996K|    33   (4)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| YYA  | 20000 |   234K|    16   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| YYC  | 20000 |   761K|    16   (0)| 00:00:01 |
|*  5 |   HASH JOIN         |      | 20000 |   996K|    33   (4)| 00:00:01 |
|   6 |    TABLE ACCESS FULL| YYA  | 20000 |   234K|    16   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL| YYB  | 20000 |   761K|    16   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("YYA"."ID1"="YYC"."ID1")
   5 - access("YYA"."ID1"="YYB"."ID1")
/*confused，Oracle有什么理由在这里反而不用join factorization了呢?看起来短期内join factorization的实际应用还有待"商榷"
*/

/*10053事件能解释这一问题吗?*/
SQL> alter system flush shared_pool;

System altered.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10053 trace name context forever,level 1;
Statement processed.
SQL> explain plan for
  2  select * from yya join yyb on yya.id1=yyb.id1
  3  union all
  4  select * from yya join yyc on yya.id1=yyc.id1;

Explained.

SQL> oradebug event 10053 trace name context off;
Statement processed.
SQL> oradebug tracefile_name;
/home/maclean/app/maclean/diag/rdbms/prod/PROD/trace/PROD_ora_7907.trc

view /home/maclean/app/maclean/diag/rdbms/prod/PROD/trace/PROD_ora_7907.trc
***********************************
Cost-Based Join Factorization
***********************************
Join-Factorization on query block SET$1 (#1)
JF: Using search type: exhaustive
JF: Generate basic transformation units
Validating JF unit: (branch: {2, 3} table: {YYA, YYA})
  rejected: join predicates do not match

JF: Generate transformation units from basic units
JF: No state generated.
/*优化器认为其链接谓词不符合使用join  factorization的条件，JF题案被驳回，"悬案"!*/

join factorization是很棒的新技术，这点没错，但新技术往往又是horrible(可怕的)，最近我常用这个词。我们的问题是不是这个新来的引起的呢?通过join factorization关键字检索MOS，可以发现一个今年(2010)3月出现的Bug 9504322,quote:

Hdr: 9504322 11.2.0.1 RDBMS 11.2.0.1 QRY OPTIMIZER PRODID-5 PORTID-226
Abstract: WRONG RESULTS WITH UNION_ALL AND INLINE VIEWS

*** 03/24/10 05:38 am ***

PROBLEM:
--------
Wrong results on 11.2 for queries of type:

SELECT * FROM
(
SELECT ... FROM view, table WHERE ...
UNION ALL
SELECT ... FROM view, table WHERE NOT ...
);

DIAGNOSTIC ANALYSIS:
--------------------
Problem seen between 10.2.0.4 and 11.2.0.1.
If we remove the use of inline view the correct results are returned.

WORKAROUND:
-----------
N/A

RELATED BUGS:
-------------

REPRODUCIBILITY:
----------------
It is reproducing on generic 11.2.0.1

呵呵，似乎有点眉目了，不过实践是检验真理的唯一标准:


SQL> alter session set "_optimizer_join_factorization"=true;

Session altered.

SELECT MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME,
       MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID,
       MTL_SECONDARY_INVENTORIES.DESCRIPTION,
       MTL_SECONDARY_INVENTORIES.AVAILABILITY_TYPE,
       MTL_SECONDARY_INVENTORIES.MATERIAL_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.MATERIAL_OVERHEAD_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.RESOURCE_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.OVERHEAD_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.OUTSIDE_PROCESSING_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.ASSET_INVENTORY,
       MTL_SECONDARY_INVENTORIES.EXPENSE_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.ENCUMBRANCE_ACCOUNT,
       MTL_SECONDARY_INVENTORIES.ATTRIBUTE3,
       MTL_SECONDARY_INVENTORIES.ATTRIBUTE5,
       WORKFLOW_START_TIMES.WORKFLOW_START_TIME
  FROM REPEMEAERP.MTL_SECONDARY_INVENTORIES,
       REPEMEAERP.WORKFLOW_START_TIMES
 WHERE MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT >
       TO_DATE('01/01/1900 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
   AND MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT <=
       WORKFLOW_START_TIMES.WORKFLOW_START_TIME
   AND WORKFLOW_START_TIMES.WORKFLOW_NAME =
       LTRIM(RTRIM('w_int_FreqBatch_EMEA'))
/*以上是QUERY A*/
UNION ALL
/*以下是QUERY B*/
SELECT DISTINCT 'WORKORDERS',
                MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID,
                'WORK ORDERS WITH WIP AS CATEGORY VALUE',
                1,
                0,
                0,
                0,
                0,
                0,
                1,
                0,
                0,
                'MOI',
                '0',
                WORKFLOW_START_TIMES.WORKFLOW_START_TIME
  FROM REPEMEAERP.MTL_SECONDARY_INVENTORIES, EIMMAINT.WORKFLOW_START_TIMES
 WHERE MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT >
       TO_DATE('01/01/1900 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
   AND MTL_SECONDARY_INVENTORIES.DW_UPDATE_DT <=
       WORKFLOW_START_TIMES.WORKFLOW_START_TIME
   AND WORKFLOW_START_TIMES.WORKFLOW_NAME =
       LTRIM(RTRIM('w_int_FreqBatch_EMEA'))
/

138 rows selected.

结果和我们猜想的大相径庭，join factorization并非罪魁，找不到终点让我们回到原点。
至此UNION ALL PUSHED PREDICATE有了极大的嫌疑，什么是PUSH PREDICATE？我把它叫做谓词前推，这玩样最早出现在10g上，但一直问题多多！它到底是何种OPERATION呢？让我们来看看下面的例子:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table youyus (t1 int,t2 varchar2(20));

Table created.

SQL> alter table youyus add primary key(t1);

Table altered.

SQL> explain plan for
  2  select *
  3    from youyus
  4  union all
  5  select * from youyus;

Explained.
/*在之后的语句中将用到这个子查询*/
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1959159425

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     2 |    50 |     4  (50)| 00:00:01 |
|   1 |  UNION-ALL         |        |       |       |            |          |
|   2 |   TABLE ACCESS FULL| YOUYUS |     1 |    25 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| YOUYUS |     1 |    25 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
/*在之后的语句中将用到这个子查询,这里它的"原始"执行计划十分简单*/

SQL> explain plan for
  2  select v2.t1, v2.t2
  3    from (select t1 from youyus where rownum=1) v1,
  4         (select *
  5            from youyus
  6          union all
  7          select * from youyus) v2
  8   where v1.t1 = v2.t1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2456530141

-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |     1 |    27 |     1   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |              |     1 |    27 |     1   (0)| 00:00:01 |
|   2 |   VIEW                         |              |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY               |              |       |       |            |          |
|   4 |     INDEX FULL SCAN            | SYS_C0010819 |     1 |    13 |     1   (0)| 00:00:01 |
|   5 |   VIEW                         |              |     1 |    14 |     0   (0)| 00:00:01 |
|   6 |    UNION ALL PUSHED PREDICATE  |              |       |       |            |          |
|   7 |     TABLE ACCESS BY INDEX ROWID| YOUYUS       |     1 |    25 |     0   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN         | SYS_C0010819 |     1 |       |     0   (0)| 00:00:01 |
|   9 |     TABLE ACCESS BY INDEX ROWID| YOUYUS       |     1 |    25 |     0   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN         | SYS_C0010819 |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(ROWNUM=1)
   8 - access("YOUYUS"."T1"="V1"."T1")
  10 - access("YOUYUS"."T1"="V1"."T1")
/* PUSHED PREDICATE将谓词逻辑前推到UNION ALL的子查询中，其优势在于可以避免全表扫描，利用索引*/

SQL> set linesize 100 pagesize 1400;
SQL>
SQL> explain plan for
  2  select /*+ no_push_pred(v2) */ v2.t1, v2.t2
  3    from (select t1 from youyus where rownum=1) v1,
  4         (select *
  5            from youyus
  6          union all
  7          select * from youyus) v2
  8   where v1.t1 = v2.t1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2769827061

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |     1 |    38 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN           |              |     1 |    38 |     6  (17)| 00:00:01 |
|   2 |   VIEW               |              |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY     |              |       |       |            |          |
|   4 |     INDEX FULL SCAN  | SYS_C0010819 |     1 |    13 |     1   (0)| 00:00:01 |
|   5 |   VIEW               |              |     2 |    50 |     4   (0)| 00:00:01 |
|   6 |    UNION-ALL         |              |       |       |            |          |
|   7 |     TABLE ACCESS FULL| YOUYUS       |     1 |    25 |     2   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL| YOUYUS       |     1 |    25 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("V1"."T1"="V2"."T1")
   3 - filter(ROWNUM=1)
/*no_push_pred hint让Oracle 放弃使用PUSHED PREDICATE，使用常规UNION-ALL操作后，子查询执行计划回归成全表扫描，整个计划成本上升*/

Filed Under: Oracle Tagged With: database, ORA, Oracle, Oracle 11g新特性, Oracle Bug, RAC, sql

共享池中的NETWORK BUFFER

2010/08/05 by admin 8 Comments

中午休闲时在itpub看到一个关于network buffer占用大量内存的求助帖,帖子原文如下:

各位大侠们，请教个问题。昨天遇到一个solaris10平台下的oracle10g(10.2.0.4)数据库报共享内存不足，发现数据库的sga_target才2512M，而在v$sgastat视图中查到的
shared pool–>NETWORK BUFFER就有1848744416字节，是什么引起network buffer这么大呢，在udmp目录下1分钟产生几个跟 ORA-4031相关的文件。

==================
SQL> show parameter sga

NAME                               TYPE       VALUE
———————————— ———– ——————————
lock_sga                            boolean    FALSE
pre_page_sga                      boolean    FALSE
sga_max_size                      big integer 2512M
sga_target                         big integer 2512M
SQL> show parameter share

NAME                               TYPE       VALUE
———————————— ———– ——————————
hi_shared_memory_address          integer    0
max_shared_servers                integer
shared_memory_address             integer    0
shared_pool_reserved_size          big integer 72142028
shared_pool_size                   big integer 0
shared_server_sessions             integer
shared_servers                      inte

NETWORK BUFFER对我们来说或许有些陌生，那是因为绝大多数场合都采用dedicated server模式，共享服务器模式下NETWORK BUFFER将被大量使用。MOS文档[741523.1]叙述了NETWORK BUFFER的主要用途:

On 10.2, after upgrading from 9iR2, the following error occurs:

ORA-07445: exception encountered: core dump [] [] [] [] [] []

plus

Dispatcher Trace file contains an ORA-4031 Diagnostic trace, with:
Allocation request for: NETWORK BUFFER

…followed by…

found dead dispatcher ‘D000’, pid = (12, 1)

The amount of memory used by NETWORK BUFFERs in the shared pool has significantly grown between 9.2 and 10.2. The side-effect is to run-out of Shared Pool memory (reporting an ORA-4031), when a large number of sessions are connecting to the server (in the order of 1000’s).

While a session is being established, we allocate 3 buffers each of 32k in size. After the session is established, we use the 3 SDU-sized buffers, however we do not deallocate the 3x32k buffer we allocated initially.

This issue has been logged in unpublished Bug 5410481.

Additionally, there is Bug 6907529.

NS buffers are allocated based on the SDU specified by the user. The negotiated SDU could be considerably lower. The difference between these two is wasted.

For example, the dispatcher specifies an SDU of 32k. Clients, by default, use an SDU of 8k. The remaining 24k is never used.

Issue in Bug 6907529 is fixed in 11.2.

Bug 5410481 is fixed in 10.2.0.3.

As a workaround to 5410481, the ADDRESS part of DISPATCHERS parameter can be used to specify a smaller SDU size.

For example:
DISPATCHERS=”(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp))(SDU=8192))”

To implement the change;

connect to the database as SYSDBA

alter system set dispatchers='(address=(protocol=tcp)(host=IP-Address)(sdu=8192))(dispatchers=DispatcherCount)’ scope=spfile;

re-start the database

你可能会问SDU是什么？Oracle NET缓存的数据以SDU为基本单位，SDU即 session data unit，一般默认为8192 bytes。当这些数据单元被写满，或被client读取时，他们将被传递给Oracle Network层(oracle network layer)。譬如Data Guard环境中redo传输的每个Chunk往往要大于8192 bytes，那么默认的SDU就不太适用。当有大量重做数据要传输到standby库时，增大SDU buffer的大小可以改善Oracle的网络性能。你可以很方便的通过修改sqlnet.ora配置文件来修改SDU,如在该文件内加入以下条目:
DEFAULT_SDU_SIZE=32767 /*修改全局默认SDU到32k*/
当然你也可以在tnsnames.ora中定义服务别名时个别指定SDU，下文我们会用到。
如上文所述在版本10.2.0.3以前当会话建立时，Oracle会以dispatchers参数定义的SDU为单位，分配3个单位的NETWORK BUFFER，而实际上client端可能并未指定和dispatchers一致的SDU，若dispatchers中定义的SDU为32k，而client使用默认的8k SDU,则一个会话可能要浪费3*32-3*8=72k的NETWORK BUFFER。

为什么共享服务器模式下会用到共享池中的NETWORK BUFFER，而独享服务器模式下没有呢？因为在独享服务器模式下每个会话所分配的三个SDU是从PGA中获取的；当使用共享服务器模式时会话与服务进程形成一对多的映射关系，这三个SDU 的NETWORK BUFFER同UGA一样转移到了SGA中。

下面我们通过实践来进一步验证。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
/*实验服务器端采用10.2.0.4版本*/
SQL> show parameter dispatch

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (address=(protocol=tcp)(host=1
                                                        92.168.1.103)(sdu=32768))(SERV
                                                        ICE=cXDB)(dispatchers=10)
/*dispatchers中指定了SDU为32k*/

C:\Windows\System32>tnsping cXDB
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-8月 -2010 22:51:27
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
已使用的参数文件:
D:\tools\adminstratorg\orahome\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (SDU=8192) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = cXDB)))
OK (30 毫秒)
/* client端采用11.2.0.1版本,定义了共享服务器模式的服务别名，显式指定SDU为8192字节*/

这里我们要用到一个简单的java程序，用来模拟大量会话登录；这个程序很傻瓜，但是总比你一个个开SQLPLUS要明智的多:

/*这是一个很简单的java程序，登录远程数据库，并尝试打开600个回话,并且都指定了SDU为8192*/
package javaapplication2;
import oracle.jdbc.*;
import java.sql.*;
public class Main
{
    public static void main(String[] args) throws SQLException
    {
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }
        catch(Exception e )
        {
        }
        Connection cnn1=DriverManager.getConnection("jdbc:oracle:thin:@(DESCRIPTION = (SDU=8192) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = cXDB)))", "system", "password");
        Statement stat1=cnn1.createStatement();
        ResultSet rst1=stat1.executeQuery("select * from v$version");
        while(rst1.next())
        {
            System.out.println(rst1.getString(1));
        }
        Connection m[]=new Connection[2000];
        Statement s[]=new Statement[2000];
        ResultSet r[]=new ResultSet[2000];
        int i=0;
        while(i<600)
        {
            try
            {
                m[i]=DriverManager.getConnection("jdbc:oracle:thin:@(DESCRIPTION = (SDU=8192) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = cXDB)))", "system", "password");
            }
            catch (Exception em)
            {
                System.out.println(em.getMessage());
            }
            try
            {
                Thread.sleep(3);
            }
            catch (Exception e)
            {
            }
            s[i]=m[i].createStatement();
            m[i].setAutoCommit(false);
            i++;
            System.out.println(i+"is ok !");
        }
        System.out.println("We are waiting!");
        try
        {
            Thread.sleep(1000);
        }
        catch (Exception e)
        {
        }
    }
}

编译上面这段程序，尝试执行看看，执行的同时留意观察NETWORK BUFFER:

SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';

NAME                       POOL              BYTES
-------------------------- ------------ ----------
NETWORK BUFFER             shared pool      328080

java -jar ora_network_buffer_test_8.jar
/*启动编译后的测试程序*/

SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';
NAME                       POOL              BYTES
-------------------------- ------------ ----------
NETWORK BUFFER             shared pool    69608200

SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';
NAME                       POOL              BYTES
-------------------------- ------------ ----------
NETWORK BUFFER             shared pool      348960
/*会话终止后，NETWORK BUFFER回缩*/

修改上述程序中的SDU到32k，重新编译后再次测试
java -jar ora_network_buffer_test_32.jar

SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';

NAME                       POOL              BYTES
-------------------------- ------------ ----------
NETWORK BUFFER             shared pool      328080

SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';

NAME                       POOL              BYTES
-------------------------- ------------ ----------
NETWORK BUFFER             shared pool    99148576
/*可以看到同样的会话数量，client端SDU增大到32k后，NETWORK BUFFER有了大幅增长*/

我们修改dispatchers参数中的SDU到8k看看
SQL> alter system set dispatchers='';

System altered.

SQL> alter system set dispatchers='(address=(protocol=tcp)(host=192.168.1.103)(sdu=8192))(SERVICE=cXDB)(dispatchers=10)';

System altered.
SQL> show parameter dispatchers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (address=(protocol=tcp)(host=1
                                                        92.168.1.103)(sdu=8192))(SERVI
                                                        CE=cXDB)(dispatchers=10)
SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';

NAME                       POOL              BYTES
-------------------------- ------------ ----------
NETWORK BUFFER             shared pool      328080

java -jar ora_network_buffer_test_32.jar

SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';

NAME                       POOL              BYTES
-------------------------- ------------ ----------
NETWORK BUFFER             shared pool    99148552
/*看起来dispatcher中的SDU优先级并没有client中的高*/
我们再来看看client中SDU为8k的情况
SQL> show parameter dispatchers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (address=(protocol=tcp)(host=1
                                                        92.168.1.103)(sdu=8192))(SERVI
                                                        CE=cXDB)(dispatchers=10)
SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';

NAME                       POOL              BYTES
-------------------------- ------------ ----------
NETWORK BUFFER             shared pool      328080

java -jar ora_network_buffer_test_8.jar

SQL> select name,pool,bytes from v$sgastat where name like '%NETWORK%';
NAME                       POOL              BYTES
-------------------------- ------------ ----------
NETWORK BUFFER             shared pool    69608200
/*与dispatchers中为32k,而client为8k时一样*/

由以上实践可知10.2.0.4之后，NETWORK BUFFER的使用量由客户端设定的SDU和共享服务器会话数决定。我在之前的博文中曾经列出过TNS协议的几个基础类描述(见《Oracle 网络TNS协议的几个基础类描述》),其中Session包含了setSDU(int i)方法，其代码如下:

public void setSDU(int i)
{
if(i <= 0) sdu = 2048;
else if(i > 32767)
sdu = 32767;
else if(i < 512)
sdu = 512;
else
sdu = i;
}

由以上代码可知，客户端设定的SDU时，其最大最小值分别为32k和512bytes，大于32k时被强制设为32k,而小于512bytes时被强制设为512bytes,若设定SDU<0，则被强制修正为2048 bytes,在512 bytes- 32767 bytes之间则为原值不变。

Filed Under: Oracle Tagged With: memory, ORA-07445, sdu, shared pool, shared servers, sqlnet

Oracle中清除游标缓存的几种方法

2010/08/05 by admin 3 Comments

9i以后引入了bind peeking绑定变量窥视特性，但该特性常有帮当忙之嫌，所以有了11g的自适应游标特性。排除因绑定变量窥视造成的因素外，统计信息讹误也会造成执行计划偏差，这时我们就可能需要清除指定游标的缓存信息，从而达到重新解析的目的。

下面我们列举几种可以达到清除游标缓存的方法，权作抛砖引玉:

1. alter system flush shared_pool; /* 最简单最粗暴的方法，清除所有游标缓存，可能造成短期内大量解析，不推荐*/

2. dbms_shared_pool 包很早就有了，但该包名下的purge过程却要到10.2.0.4才出现，Bug 5614566最早在2006年描述了需要清除游标缓存接口的要求：

Hdr: 5614566 10.2.0.2 RDBMS 10.2.0.2 DICTIONARY PRODID-5 PORTID-176
Abstract: WE NEED A FLUSH CURSOR INTERFACE
*** 10/20/06 07:48 am ***

而且该过程在10.2.0.4中默认是无法正常使用的，需要通过设置event 5614566或者打上5614566补丁来启用；具体设置方法如下：

alter system set events ‘5614566 trace name context forever’;

该存储过程的具体argument如下:

PROCEDURE PURGE
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
NAME                           VARCHAR2                IN
FLAG                           CHAR                    IN     DEFAULT
HEAPS                          NUMBER                  IN     DEFAULT

其中NAME指定了需要清除的对象名,这里分成2种。PL/SQL对象，触发器，序列，类型和JAVA对象以其命名指定；SQL游标对象通过该SQL的address与hash_value组合指定。FLAG指定了对象的类型，若没有指定该参数，Oracle将认为之前代入的NAME参数对应到包/存储过程/函数的命名空间，需要注意的是该参数是大小写敏感的，包括了以下各类型:

FLAG值	对应对象类型
P	包/存储过程/函数
Q	序列
R	触发器
T	类型
JS	Java源程序
JC	Java类程序
JR	Java资源
JD	Java共享数据
C	cursor

HEAP参数指定了清除对象的哪些堆信息，以SQL游标为例,其最主要的信息包括在HEAP 0和HEAP 6中，HEAP 0包括了游标自身的大多数信息，而HEAP 6则存放了游标相关的执行计划。如果我们想要清除HEAP 0和HEAP 6中的信息，则2的0次方+2的6次方=1+64=65,那么我们在代入HEAP参数为65 即可；如果我们只想清除游标的执行计划则清除HEAP 6即可，代入HEAP参数为2的6次方即64。该参数的默认值为1，清除HEAP 0将会导致整个对象的缓存信息被清除掉。

下面我们来演示如何利用该存储过程来清除SQL缓存:

SQL> alter system flush shared_pool;

系统已更改。

SQL> select /* cache_me */  count(*)  from youyus;

  COUNT(*)

----------

         9

SQL> select sql_id,
 2         address,
 3         hash_value,
 4         executions,
 5         loads,
 6         version_count,
 7         invalidations,
 8         parse_calls
 9    from v$sqlarea
 10   where sql_text like '%cache_me%'
 11     and sql_text not like '%v$sqlarea%';

SQL_ID        ADDRESS  HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
------------- -------- ---------- ---------- ---------- ------------- ------------- -----------
25asu5a76nqmn 2F51508C 2389334644          3          1             1             0           3

SQL> select address, plan_hash_value
 2    from v$sql_plan
 3   where sql_id = '25asu5a76nqmn';

ADDRESS  PLAN_HASH_VALUE
-------- ---------------
2F51508C      2542806819
2F51508C      2542806819
2F51508C      2542806819

SQL> exec dbms_shared_pool.purge('2F51508C,2389334644','C',64);

PL/SQL 过程已成功完成。

SQL> select sql_id,
  2         address,
  3         hash_value,
  4         executions,
  5         loads,
  6         version_count,
  7         invalidations,
  8         parse_calls,
  9         plan_hash_value
 10    from v$sqlarea
 11   where sql_text like '%cache_me%'
 12     and sql_text not like '%v$sqlarea%';

SQL_ID        ADDRESS  HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F51508C 2389334644          4          1             1             0           4      2542806819

SQL> select * from v$sql_plan where plan_hash_value= 2542806819;
未选定行

/*执行计划消失了，而游标主体信息仍在*/
SQL> select /* cache_me */  count(*)  from youyus;

 COUNT(*)
----------
 9

SQL> select sql_id,
 2         address,
 3         hash_value,
 4         executions,
 5         loads,
 6         version_count,
 7         invalidations,
 8         parse_calls,
 9         plan_hash_value
 10    from v$sqlarea
 11   where sql_text like '%cache_me%'
 12     and sql_text not like '%v$sqlarea%';

SQL_ID        ADDRESS  HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F51508C 2389334644          5          1             1             0           5      2542806819
/*这里新增的一次parse call是硬解析*/

SQL>  select address,operation from v$sql_plan where plan_hash_value= 2542806819;

ADDRESS  OPERATION
-------- ------------------------------------------------------------
2F51508C SELECT STATEMENT
2F51508C SORT
2F51508C TABLE ACCESS

SQL> exec dbms_shared_pool.purge('2F51508C,2389334644','C',1);

PL/SQL 过程已成功完成。

SQL> select sql_id,
 2         address,
 3         hash_value,
 4         executions,
 5         loads,
 6         version_count,
 7         invalidations,
 8         parse_calls,
 9         plan_hash_value
 10    from v$sqlarea
 11   where sql_text like '%cache_me%'
 12     and sql_text not like '%v$sqlarea%';

未选定行
SQL> select address,operation from v$sql_plan where plan_hash_value= 2542806819;

未选定行
SQL> select /* cache_me */  count(*)  from youyus;

 COUNT(*)
----------
 9

SQL> select sql_id,
 2         address,
 3         hash_value,
 4         executions,
 5         loads,
 6         version_count,
 7         invalidations,
 8         parse_calls,
 9         plan_hash_value
 10    from v$sqlarea
 11   where sql_text like '%cache_me%'
 12     and sql_text not like '%v$sqlarea%';

SQL_ID        ADDRESS  HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F51508C 2389334644          1          2             1             1           1      2542806819

/*清除游标heap 0后，包括执行计划的所有信息都被清除了,甚至于simulator中的信息*/

3.如果您的环境中恰好无法利用dbms_shared_pool.purge存储过程，我们也可以采用一些折中的方法来清除游标缓存；譬如通过一个无关紧要的grant/revoke操作,但这样也会造成所有该授权/撤职对象相关SQL的执行计划失效:

SQL> alter system flush shared_pool;

系统已更改。

SQL> select /* cache_me */  count(*)  from youyus;

  COUNT(*)
----------
         9

SQL> select sql_id,
  2         address,
  3         hash_value,
  4         executions,
  5         loads,
  6         version_count,
  7         invalidations,
  8         parse_calls,
  9         plan_hash_value
 10    from v$sqlarea
 11   where sql_text like '%cache_me%'
 12     and sql_text not like '%v$sqlarea%';

SQL_ID        ADDRESS  HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F540EA0 2389334644          1          1             1             0           1      2542806819

SQL> select address,operation,to_char(timestamp,'HH24:MI:SS') from v$sql_plan where plan_hash_value= 2542806819;

ADDRESS  OPERATION                                                    TO_CHAR(
-------- ------------------------------------------------------------ --------
2F540EA0 SELECT STATEMENT                                             13:39:28
2F540EA0 SORT                                                         13:39:28
2F540EA0 TABLE ACCESS                                                 13:39:28

SQL> revoke select on youyus from scott;

撤销成功。

SQL> select sql_id,
  2         address,
  3         hash_value,
  4         executions,
  5         loads,
  6         version_count,
  7         invalidations,
  8         parse_calls,
  9         plan_hash_value
 10    from v$sqlarea
 11   where sql_text like '%cache_me%'
 12     and sql_text not like '%v$sqlarea%';

SQL_ID        ADDRESS  HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F540EA0 2389334644          1          1             1             1           1      2542806819

/*授权/撤销会造成执行计划invalid，此处 INVALIDATIONS上升到1*/

SQL> select /* cache_me */  count(*)  from youyus;

  COUNT(*)
----------
         9
/*重新执行SQL，将引发一次硬解析*/
SQL> select address,operation,to_char(timestamp,'HH24:MI:SS') from v$sql_plan where plan_hash_value= 2542806819;

ADDRESS  OPERATION                                                    TO_CHAR(
-------- ------------------------------------------------------------ --------
2F540EA0 SELECT STATEMENT                                             13:40:23
2F540EA0 SORT                                                         13:40:23
2F540EA0 TABLE ACCESS                                                 13:40:23

/*执行计划的时间戳发生了变化，达到了重新解析游标的目的*/

4.或许你不是一个位高权重的DBA，无法执行授权/撤职命令，但如果你能分析游标所涉及对象的统计信息或者执行其他一些ddl操作，那么也可以达到同样的目的:

SQL> alter system flush shared_pool;

系统已更改。

SQL>
SQL> select /* cache_me */  count(*)  from youyus;

  COUNT(*)
----------
         9

SQL> select sql_id,
  2         address,
  3         hash_value,
  4         executions,
  5         loads,
  6         version_count,
  7         invalidations,
  8         parse_calls,
  9         plan_hash_value
 10    from v$sqlarea
 11   where sql_text like '%cache_me%'
 12     and sql_text not like '%v$sqlarea%';

SQL_ID        ADDRESS  HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F540EA0 2389334644          1          1             1             0           1      2542806819

SQL> analyze table  youyus compute statistics;

表已分析。

SQL> select sql_id,
  2         address,
  3         hash_value,
  4         executions,
  5         loads,
  6         version_count,
  7         invalidations,
  8         parse_calls,
  9         plan_hash_value
 10    from v$sqlarea
 11   where sql_text like '%cache_me%'
 12     and sql_text not like '%v$sqlarea%';

SQL_ID        ADDRESS  HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F540EA0 2389334644          1          1             1             1           1      2542806819
/*统计信息更新，造成了invalid*/

SQL>    create index ind_youyus on youyus(t1);

索引已创建。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select /* cache_me */  count(*)  from youyus;

  COUNT(*)
----------
         9

SQL> select sql_id,
  2         address,
  3         hash_value,
  4         executions,
  5         loads,
  6         version_count,
  7         invalidations,
  8         parse_calls,
  9         plan_hash_value
 10    from v$sqlarea
 11   where sql_text like '%cache_me%'
 12     and sql_text not like '%v$sqlarea%';

SQL_ID        ADDRESS  HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F464EA0 2389334644          1          1             1             0           1      2542806819

SQL> alter index ind_youyus rebuild online;

索引已更改。

SQL> select sql_id,
  2         address,
  3         hash_value,
  4         executions,
  5         loads,
  6         version_count,
  7         invalidations,
  8         parse_calls,
  9         plan_hash_value
 10    from v$sqlarea
 11   where sql_text like '%cache_me%'
 12     and sql_text not like '%v$sqlarea%';

SQL_ID        ADDRESS  HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS PLAN_HASH_VALUE
------------- -------- ---------- ---------- ---------- ------------- ------------- ----------- ---------------
25asu5a76nqmn 2F464EA0 2389334644          1          1             1             1           1      2542806819
/*在线重建索引也可以达到同样的目的*/

That's Great!

Filed Under: Oracle Tagged With: ORA, Oracle, Oracle 11g新特性, Oracle Bug, RAC, sql

Upgrading to RAC 11g R2 What you should know

2010/08/05 by admin Leave a Comment

How to upgrade?
Recommended: “Swing Kit”
Set up a new cluster on separate hardware
Install Grid Infrastructure at your leisure
Avoid time pressure
Create a physical standby on the new cluster
At a pre-defined outage window, perform a switchover
Remember: you don’t need to upgrade the database at the same time as you upgrade to Grid Infrastructure
The trick is to keep other standby databases in sync

Other upgrade options
If you don’t have Swing Kit…
Upgrade the existing software stack
Grid Infrastructure is an out of place upgrade
New Oracle home
Pre 11.2 Clusterware home and ASM home made redundant after successful upgrade
Out of place upgrade should make downgrade easier
Have not tested downgrade personally
Usually only one in the audience did

[gview file=”https://www.askmac.cn/wp-content/uploads/2011/08/072811_96317_ppt.ppt”]

Filed Under: Oracle, Oracle 11g新特性, Oracle RAC Tagged With: 11gr2, RAC, upgrade

哪里有深入学习Oracle的资料?

2010/08/04 by admin Leave a Comment

订阅了oracle-l@freelists.org邮件列表(顺便提一下，订阅这个邮件列表也是学习Oracle的好方法，它完全免费，且有许多Oracle界的”大师”出没于此，当然要通过审查才能发言；你可以从freelists.org上找到入口！)的朋友可能在若干天前收到了一封来自Guillermo Alan Bort关于到哪里可以找到学习Oracle内部工作原理资料的邮件。
热心的邮友给出了五花八门的答案，我们来梳理一下这些渠道，以方便来者。

著名的juliandyke的巢穴，他写了许多独创的专题，本人也从他的著作中获益良多	http://juliandyke.com
oraktable.net上汇集了众多专家在博客上发表的最新技术文章	http://www.oaktable.net/members
致力于挖掘RAC技术，可以说是研究RAC最好的站点	http://www.oracleracsig.org
Oracle 8i service internal作者的站点，虽然已经多年未更新，却是你不能错过的一课	http://www.ixora.com.au/
Tanel Poder可能是世界上真正的第一个OCM，他有很多关于internal和performance的著作	http://blog.tanelpoder.com/
著名的《Cost Based Oracle Fundamentals》的作者Jonathan Lewis在wordpress上的博客	http://jonathanlewis.wordpress.com/
这就像是专门存放Oracle资料的图书馆	http://www.morganslibrary.org/library.html
Pete Finnigans是Oracle security方面的专家，发表了大量关于hack Oracle的文章	http://www.petefinnigan.com
我们之前说的邮件列表的入口！	http://www.freelists.org/webpage/oracle-l

to be continued ………..

Filed Under: Oracle Tagged With: ocm, ORA, Oracle, RAC, web