latch: shared pool等待事件

2011/08/09 by Leave a Comment

The shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool. Typically a miss rate for this latch less than 95% results in poor performance. There is only one latch of this type in the shared pool, so when there is contention for this latch, significant degradation in performance can occur.

Problem

ORA-4031 errors are seen in the alert log if a trace file is generated (such as when a background process receives this error instead of when a user proc may get it. This means the session cannot allocate contiguous memory in shared pool for incoming SQL statement.

Solutions

Since the number of shared pool latch gets is influenced by the volume of shared pool activity like parse operations, anything that can reduce the this activity will improve the availability of this latch and overall database performance (see Reducing the Number of SQL Statements).

Filed Under: Oracle Tagged With: ,

Data Pump failed with ORA-04031/ORA-4030?

2011/06/19 by Leave a Comment

在10g中引入了数据泵Data Pump导入导出工具，DataPump的工作流如下图:

data_pump_workflow

我们在使用Data Pump工具时经常会遇到著名的ORA-04031/ORA-04030错误，主要影响DataPump的内存组件有PGA和SGA中的共享池Shared Pool、流池Streams Pool。Expdp/Impdp对shared Pool的开销主要体现在其运行过程中需要调用一系列的包体PACKGE BODY，它们包括:

PACKAGE_NAME                             TYPE                 SHARABLE_MEM
---------------------------------------- -------------------- ------------
SYS.KUPM$MCP                             PACKAGE BODY               425448
SYS.KUPW$WORKER                          PACKAGE BODY               386000
SYS.DBMS_METADATA_INT                    PACKAGE BODY               325856
SYS.DBMS_REPCAT_UTL                      PACKAGE BODY               269064
SYS.DBMS_METADATA                        PACKAGE BODY               226624
SYS.DBMS_DATAPUMP                        PACKAGE BODY               192888
SYS.DBMS_PRVTAQIS                        PACKAGE BODY               147288
SYS.DBMS_PRVTAQIM                        PACKAGE BODY               142680
SYS.KUPF$FILE                            PACKAGE BODY               142008
SYS.DBMS_METADATA_UTIL                   PACKAGE BODY               115224
SYS.KUPD$DATA                            PACKAGE BODY               109400
SYS.DBMS_LOGREP_EXP                      PACKAGE BODY               102648
SYS.DBMS_SCHED_MAIN_EXPORT               PACKAGE BODY                86816
SYS.DBMS_SYS_SQL                         PACKAGE BODY                72280
SYS.DBMS_AW                              PACKAGE BODY                68128
SYS.DBMS_SQL                             PACKAGE BODY                68064
SYS.DBMS_DM_EXP_INTERNAL                 PACKAGE BODY                57040
SYS.DBMS_AW_EXP                          PACKAGE BODY                52256
SYS.KUPC$QUE_INT                         PACKAGE BODY                52088
SYS.DBMS_CUBE_EXP                        PACKAGE BODY                48432
SYS.KUPV$FT_INT                          PACKAGE BODY                47184
SYS.DBMS_LOGREP_UTIL                     PACKAGE BODY                45856
SYS.DBMS_CDC_EXPDP                       PACKAGE BODY                44616
SYS.DBMS_EXPORT_EXTENSION                PACKAGE BODY                38728
SYS.DBMS_CDC_UTILITY                     PACKAGE BODY                37712
SYS.KUPV$FT                              PACKAGE BODY                34536
SYS.DBMS_DM_MODEL_EXP                    PACKAGE BODY                33904
SYS.DBMS_AQ                              PACKAGE BODY                33512
SYS.DBMS_IJOB                            PACKAGE BODY                33488
SYS.DBMS_AQ_SYS_EXP_INTERNAL             PACKAGE BODY                29464
SYS.DBMS_FILE_GROUP_EXP                  PACKAGE BODY                29440
SYS.KUPD$DATA_INT                        PACKAGE BODY                29424
SYS.DBMS_RULE_EXP_RL_INTERNAL            PACKAGE BODY                29400
SYS.KUPP$PROC                            PACKAGE BODY                25368
SYS.DBMS_AQ_IMPORT_INTERNAL              PACKAGE BODY                25352
SYS.DBMS_AQADM                           PACKAGE BODY                25320
SYS.DBMS_ODCI                            PACKAGE BODY                21200
SYS.UTL_XML                              PACKAGE BODY                21200
SYS.DBMS_CDC_DPUTIL                      PACKAGE BODY                21192
SYS.DBMS_STREAMS_DATAPUMP_UTIL           PACKAGE BODY                21144
SYS.KUPF$FILE_INT                        PACKAGE BODY                17104
SYS.DBMS_SESSION                         PACKAGE BODY                17048
SYS.DBMS_AQ_SYS_EXP_ACTIONS              PACKAGE BODY                17048
SYS.DBMS_STREAMS_DATAPUMP                PACKAGE BODY                17032
SYS.KUPC$QUEUE_INT                       PACKAGE BODY                17032
SYS.DBMS_RULE_ADM                        PACKAGE BODY                17032
SYS.DBMS_LOCK                            PACKAGE BODY                17032
SYS.DBMS_RULEADM_INTERNAL                PACKAGE BODY                12952
SYS.DBMS_TRANSFORM_EXIMP_INTERNAL        PACKAGE BODY                12952
SYS.DBMS_REFRESH_EXP_SITES               PACKAGE BODY                12952
SYS.DBMS_REPCAT_RGT_EXP                  PACKAGE BODY                12936
SYS.UTL_RAW                              PACKAGE BODY                12936
SYS.DBMS_FLASHBACK                       PACKAGE BODY                12936
SYS.DBMS_TRANSFORM_EXIMP                 PACKAGE BODY                12936
SYS.DBMS_SCHED_JOB_EXPORT                PACKAGE BODY                12936
SYS.DBMS_REFRESH_EXP_LWM                 PACKAGE BODY                12936
SYS.DBMS_SCHED_EXPORT_CALLOUTS           PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_QUEUE_TABLES             PACKAGE BODY                 8856
SYS.DBMS_DEFER_IMPORT_INTERNAL           PACKAGE BODY                 8856
SYS.DBMS_INTERNAL_SAFE_SCN               PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_INDEX_TABLES             PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_HISTORY_TABLES           PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_SIGNATURE_TABLES         PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_DEQUEUELOG_TABLES        PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_SUBSCRIBER_TABLES        PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_CMT_TIME_TABLES          PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_TIMEMGR_TABLES           PACKAGE BODY                 8856
SYS.DBMS_RULE_EXP_RULES                  PACKAGE BODY                 8840
SYS.DBMS_RULE_EXP_UTLI                   PACKAGE BODY                 8840
SYS.DBMS_AQADM_INV                       PACKAGE BODY                 8840
SYS.DBMS_ZHELP_IR                        PACKAGE BODY                 8840
SYS.KUPC$QUEUE                           PACKAGE BODY                 8840
SYS.DBMS_AQ_EXP_ZECURITY                 PACKAGE BODY                 8840
SYS.KUPU$UTILITIES_INT                   PACKAGE BODY                 8840
SYS.KUPU$UTILITIES                       PACKAGE BODY                 8840
SYS.DBMS_REPCAT_EXP                      PACKAGE BODY                 8840
SYS.DBMS_CDC_EXPVDP                      PACKAGE BODY                 8840

total<<10MB

DataPump内部利用高级队列 Advanced Queue技术，所以也会用到流池Streams Pool，与之相关的Streams Pool组件包括:

POOL         NAME                            BYTES
------------ -------------------------- ----------
streams pool sob_kgqmrec                     19584
streams pool Sender info                     17616
streams pool recov_kgqbtctx                  11904
streams pool kwqbcqini:spilledovermsgs        3168
streams pool kgqbt_alloc_block                2096
streams pool recov_kgqmsub                    1608
streams pool kwqbsinfy:bqg                    1232
streams pool recov_kgqmctx                    1104
streams pool kwqbsinfy:mpr                    1088
streams pool kwqbsinfy:sta                     768
streams pool kgqmsub                           584
streams pool fixed allocation callback         448
streams pool kwqbsinfy:cco                     376
streams pool image handles                     288
streams pool kwqbsinfy:bms                     256
streams pool name_kgqmsub                      256
streams pool spilled:kwqbl                     256
streams pool deqtree_kgqmctx                   144
streams pool substree_kgqmctx                  144
streams pool kgqmdm_fl_1                       144
streams pool time manager index                144
streams pool msgtree_kgqmctx                   144

当Streams Pool分配过小同样可能引发Expdp/Impdp因ORA-04031 (“streams pool”, …)错误而意外终止，详见<EXPDP Fails With ORA-04031 (“streams pool”, …) [ID 457724.1]>

此外DataPump还可能从Large Pool中分配PX msg pool作为并行进程通信池，但是这种内存开销很小。

DataPump对PGA的消耗主要体现在koh-kghu sessi sub-heap上，已知的Bug 10404544(ORA – 4030 DURING EXPDP)、7681160(EXPDP FAILS WITH ORA-4030 WHEN SELECT FROM SYS.KU$_PROCACT_SCHEMA_VIEW)说明该子堆sub-heap(和另一个sub-heap kxs-heap-w)在10.2.0.4上使用expdp时可能引发内存泄露memory-leak。我们来具体看一下DataPump Manager DM00的PGA使用情况:

SQL> oradebug setospid 5278;
Oracle pid: 51, Unix process pid: 5278, image: oracle@rh2.oracle.com (DM00)

SQL> oradebug dump heapdump 536870917;
Statement processed.

SQL> oradebug tracefile_name;
/s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_dm00_5278.trc

SQL> select pga_alloc_mem / 1024 / 1024, pga_used_mem / 1024 / 1024, pname
  2    from v$process
  3   where addr = '00000000DCBC51F8'
  4  /

PGA_ALLOC_MEM/1024/1024 PGA_USED_MEM/1024/1024 PNAME
----------------------- ---------------------- -----
             8.06555557             7.25846481 DM00

[oracle@rh2 ~]$ egrep "HEAP DUMP heap name|Total heap size|Permanent space" 
/s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_dm00_5278.trc

HEAP DUMP heap name="session heap"  desc=0x7fae4fc167f8
Total heap size    =  4517512
Permanent space    =   192664
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f9db150
Total heap size    =   362416
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4fc47fb0
Total heap size    =   336104
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f984660
Total heap size    =   156792
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f988c88
Total heap size    =   154912
Permanent space    =       80
HEAP DUMP heap name="Alloc environm"  desc=0x7fae4fc3e090
Total heap size    =   129512
Permanent space    =      416
HEAP DUMP heap name="Alloc statemen"  desc=0x7fae4f8c91f8
Total heap size    =    10264
Permanent space    =      696
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f9f3650
Total heap size    =    10192
Permanent space    =     1056
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc4fed0
Total heap size    =     9912
Permanent space    =     1488
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8e4690
Total heap size    =     9080
Permanent space    =      560
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8aeda0
Total heap size    =     8992
Permanent space    =      568
HEAP DUMP heap name="pga heap"  desc=0xb7c8ba0
Total heap size    =  2353064
Permanent space    =   654544
HEAP DUMP heap name="KFK_IO_SUBHEAP"  desc=0x7fae4fdfaa98
Total heap size    =   730640
Permanent space    =       80
HEAP DUMP heap name="koh-kghu call "  desc=0x7fae4fb650d0
Total heap size    =   246080
Permanent space    =       80
HEAP DUMP heap name="diag pga"  desc=0x7fae500347e0
Total heap size    =    70816
Permanent space    =    13472
HEAP DUMP heap name="Alloc environm"  desc=0x7fae4fdd15c8
Total heap size    =    68064
Permanent space    =     1336
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fb50ee0
Total heap size    =    12272
Permanent space    =     2096
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fb66c00
Total heap size    =    11784
Permanent space    =     1856
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc14fc0
Total heap size    =     9816
Permanent space    =     1392
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc10068
Total heap size    =     9664
Permanent space    =     1240
HEAP DUMP heap name="PLS PGA hp"  desc=0x7fae4fdeeab8
Total heap size    =    46784
Permanent space    =       80
HEAP DUMP heap name="top call heap"  desc=0xb7ce3c0
Total heap size    =   458584
Permanent space    =     1920
HEAP DUMP heap name="callheap"  desc=0xb7cd578
Total heap size    =   343104
Permanent space    =    65536
HEAP DUMP heap name="callheap"  desc=0xb7cd4c0
Total heap size    =    21616
Permanent space    =      968
HEAP DUMP heap name="kti call subhe"  desc=0x7fae4fdee018
Total heap size    =    20584
Permanent space    =       80
HEAP DUMP heap name="callheap"  desc=0x7fae4f6bd108
Total heap size    =     8952
Permanent space    =      200
HEAP DUMP heap name="callheap"  desc=0x7fae4f6bd030
Total heap size    =     2072
Permanent space    =     1672
HEAP DUMP heap name="top uga heap"  desc=0xb7ce5e0
Total heap size    =  4520496
Permanent space    =       80
HEAP DUMP heap name="session heap"  desc=0x7fae4fc167f8
Total heap size    =  4517512
Permanent space    =   192664
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f9db150
Total heap size    =   362416
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4fc47fb0
Total heap size    =   336104
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f984660
Total heap size    =   156792
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f988c88
Total heap size    =   154912
Permanent space    =       80
HEAP DUMP heap name="Alloc environm"  desc=0x7fae4fc3e090
Total heap size    =   129512
Permanent space    =      416
HEAP DUMP heap name="Alloc statemen"  desc=0x7fae4f8c91f8
Total heap size    =    10264
Permanent space    =      696
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f9f3650
Total heap size    =    10192
Permanent space    =     1056
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc4fed0
Total heap size    =     9912
Permanent space    =     1488
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8e4690
Total heap size    =     9080
Permanent space    =      560
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8aeda0
Total heap size    =     8992
Permanent space    =      568

一般遇到这类memory leak的问题，Oracle内部会使用一个名叫heap.awk的dump分析工具(类似于ass.awk)来找出问题子堆(problematic sub-heap),我们可以使用图形化的免费工具Membai来替代heap.awk。

heap_dump_analysis

总结：

DataPump工具Expdp/Impdp需要从PGA和SGA的Shared Pool、Streams Pool和Large Pool分配必要的内存。为了避免Expdp/Impdp出现ORA-04031/ORA-04030错误，我们有必要在自动管理模式下设置合理的pga_aggregate_target和sga_target(抑或者memory_target)内存初始化参数，如果使用手动的SGA管理的话，那么有必要保证shared_pool_size的设置适宜，对于Streams Pool和Large Pool一般设置为150MB大小。

Filed Under: Oracle Tagged With: , , , , ,

共享池中的NETWORK BUFFER

2010/08/05 by 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;

  1. connect to the database as SYSDBA
  2. 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: , , , , ,

    Oracle中SQL解析的流程

    2010/08/02 by 8 Comments

    Oracle中SQL解析的主要流程:
    sql_parse_digram

    我们说的游标概念比较复杂，它可以是客户端程序中的游标，服务进程中的私有游标，以及服务器端共享池里的共享游标。假设一个游标被打开了，一般来说它的共享游标信息(包括执行计划，优化树等)总是会在SQL AREA里，无需再次软/硬解析。

    SESSION_CACHED_CURSORS是Oracle中的一个初始化参数(修改必须重启实例)，指定了每个会话缓存的游标上限(保留在PGA中)；客户端程序中open cursor的要求仍会被传递给服务进程，服务进程首先扫描自身缓存的游标信息，如果命中则可以避免软解析，也有人称它为“软软解析”。

    HOLD_CURSOR是预编译程序中的一个参数，它指定了私有游标是否因该被缓存，这里不做展开。

    在分析工具tkprof中hard parse与soft parse被同等对待，都被认为是parse；软解析即会造成parse总数上升。

    软解析避免了硬解析过程中的几个步骤，但仍包括了初始化的语法，语义解析并计算语句HASH值与SQL AREA中已有语句进行对比；若匹配则查询优化等昂贵的操作得以避免。

    另请注意，10053事件仅在硬解析过程中被触发。

    Filed Under: Oracle, Oracle SQL性能调优 Tagged With: , , , , , , ,

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

    2009/10/05 by 2 Comments

    从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列表中的对象对当前实例仍有用，即更大的内存空间对实例来说是有益的。

    Filed Under: Oracle Tagged With: , , ,

    沪ICP备14014813号-2

    沪公网安备 31010802001379号