由 bind_mismatch 引起的 大量 version_count 问题

 

AWR报告里发现一个SQL存在大量的version_count.

 

SYS@xezf(qs-xezf-db1)> select sql_id,version_count from v$sqlarea where version_count> 500 order by 2 desc ;

 

SQL_ID        VERSION_COUNT

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

9rwd4wkwm4bsy        3046

cpqsn8zak6sw4          2985

66x4djqka2ppy          976

0z7n7sst85222           617

 

 

            v$sqlarea 中保存了SQLcursor,当有大量的version_count,说明虽然SQL 语句相同,但是Oracle 发现因为某些原因不可重用这些SQL。当这类SQL执行次数很多,就会占用大量的shared pool,引起library cache pinlibrary cache 的等待事件。

 

可以使用如下SQL 查看占用内存大小:

/* Formatted on 2011/6/24 21:54:00 (QP5 v5.163.1008.3004) */

SELECT SUM (sharable_mem) / 1024 / 1024 || 'M'

  FROM v$sqlarea

 WHERE sql_id = 'cpqsn8zak6sw4';

 

可以通过如下SQL 查看是什么原因导致的不匹配:

SYS@xezf(qs-xezf-db1)> select sql_id,child_number,BIND_MISMATCH from v$sql_shared_cursor where sql_id='9rwd4wkwm4bsy' and BIND_MISMATCH='Y' and rownum<10;

 

SQL_ID        CHILD_NUMBER B

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

9rwd4wkwm4bsy            3 Y

9rwd4wkwm4bsy           24 Y

9rwd4wkwm4bsy           29 Y

9rwd4wkwm4bsy           33 Y

9rwd4wkwm4bsy           35 Y

9rwd4wkwm4bsy           38 Y

9rwd4wkwm4bsy           51 Y

9rwd4wkwm4bsy           55 Y

9rwd4wkwm4bsy           81 Y

 

我这是过滤之后的信息,当这些信息有Y时,就是表示cursor 不能重用的原因。

 

SYS@xezf(qs-xezf-db1)> select count(*) from v$sql_shared_cursor where sql_id='9rwd4wkwm4bsy' and BIND_MISMATCH='Y' ;

 

  COUNT(*)

----------

  120

 

 

            bind_mismatch一般是由于bind value的长度不同导致bind buffer无法重用,最终导致cursor无法重用。

 

            例如:            对于字符类型的字段,进行绑定变量的时候,第一次会使用32字节的BUFFER,如果该值小于32字节的话,第二次执行这个SQL的时候,如果小于32字节,那么可以共享这个CURSOR,如果大于,就无法共享,原因就是BIND_MISMATCH,此时会产生一个子CURSOR,同时分配128字节的BIND BUFFER,以此类推。

 

            正常情况不会产生这么大量的子CURSOR。但是由于一些BUG,会导致问题。

 

如果没有补丁,一个临时性的解决方案,设置一个较大的BUFFER

            SQL>ALTER SESSION SET EVENTS '10503 trace name context level <buffer length>, forever';

 

 

通过v$sql_bind_capture 视图查看一下每次绑定变量的值:

SYS@xezf(qs-xezf-db1)> select position,LAST_CAPTURED,datatype_string,value_string from v$sql_bind_capture where sql_id='9rwd4wkwm4bsy' and rownum<50;

 

POSITION  LAST_CAPTURED   DATATYPE_STRING     VALUE_STRING

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

         1 2011-06-24 15:54:22 VARCHAR2(32)         cp102328

         2 2011-06-24 15:54:22 NUMBER               103

         3 2011-06-24 15:54:22 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 15:54:22 VARCHAR2(32)         yyyy-mm-dd

         1 2011-06-24 16:02:54 VARCHAR2(32)         s13791223344

         2 2011-06-24 16:02:54 NUMBER               103

         3 2011-06-24 16:02:54 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 16:02:54 VARCHAR2(32)         yyyy-mm-dd

         1 2011-06-24 16:10:41 VARCHAR2(32)         7027976

         2 2011-06-24 16:10:41 NUMBER               103

         3 2011-06-24 16:10:41 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 16:10:41 VARCHAR2(32)         yyyy-mm-dd

         1 2011-06-24 17:09:28 VARCHAR2(32)         BILLQQ

         2 2011-06-24 17:09:28 NUMBER               103

         3 2011-06-24 17:09:28 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 17:09:28 VARCHAR2(32)         yyyy-mm-dd

         1 2011-06-24 16:59:16 VARCHAR2(32)         wantai1472888

         2 2011-06-24 16:59:16 NUMBER               103

         3 2011-06-24 16:59:16 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 16:59:16 VARCHAR2(32)         yyyy-mm-dd

         1 2011-06-24 16:59:10 varchar2(32)         gy928888@vip.qq.com

         2 2011-06-24 16:59:10 NUMBER               103

         3 2011-06-24 16:59:10 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 16:59:10 VARCHAR2(32)         yyyy-mm-dd

         1 2011-06-24 16:59:09 VARCHAR2(32)         22501165422

         2 2011-06-24 16:59:09 NUMBER               103

         3 2011-06-24 16:59:09 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 16:59:09 VARCHAR2(32)         yyyy-mm-dd

         1 2011-06-24 16:59:07 VARCHAR2(32)         12801165830

         2 2011-06-24 16:59:07 NUMBER               103

         3 2011-06-24 16:59:07 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 16:59:07 VARCHAR2(32)         yyyy-mm-dd

         1 2011-06-24 16:59:00 VARCHAR2(32)         235896734

         2 2011-06-24 16:59:00 NUMBER               103

         3 2011-06-24 16:59:00 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 16:59:00 VARCHAR2(32)         yyyy-mm-dd

         1 2011-06-24 16:58:56 varchar2(32)         978a62e0bbb767d99bda

         2 2011-06-24 16:58:56 NUMBER               103

         3 2011-06-24 16:58:56 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 16:58:56 VARCHAR2(32)         yyyy-mm-dd

         1 2011-06-24 16:58:34 VARCHAR2(32)         708888718@qq.com

         2 2011-06-24 16:58:34 NUMBER               209

         3 2011-06-24 16:58:34 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 16:58:34 VARCHAR2(32)         yyyy-mm-dd

         1 2011-06-24 16:57:51 varchar2(32)         syyxQS20110624000364

         2 2011-06-24 16:57:51 NUMBER               103

         3 2011-06-24 16:57:51 VARCHAR2(32)         yyyy-mm-dd

         4 2011-06-24 16:57:51 VARCHAR2(32)         yyyy-mm-dd

   

            通过以上的查询结果,我们可以肯定是sql_id='9rwd4wkwm4bsy' SQL的第一绑定变量值的长度不同造成bind_mismatch, 从而产生大量的version_counts.

 

 

相关的bug信息如下:
Bug:9689310:
- Non sharability of cursors due to BIND_MISMATCH.

Bug:6981690:
- Non sharability of cursors due to PQ_SLAVE_MISMATCH


Bug:8981059:
- Non sharability of cursors due to USER_BIND_PEEK_MISMATCH.

 

 

对于Bug 9689310,在MOS上搜了一下,该bug存在的版本如下:

 

Affects:

Product (Component)

Oracle Server (Rdbms)

Range of versions believed to be affected

Versions BELOW 12.1

Versions confirmed as being affected

Platforms affected

Generic (all / most platforms affected)

Fixed:

This issue is fixed in

 

MOS 上给了一个变通的解决方法:Workaround

  Alter the client application code so that it uses constant sizes for the MAX bind lengths.

 

 

            我的库是10.2.0.5的,这个没说修复,也没说存在bug,还真不好确定,看来还是需要测试一下。

 

            不过我这个库上的cursor_sharing 参数是设置为similar的,这样会将SQL 中的谓词值自动用变量来代替。 这样会增加cursor的数量。 为了减少cursorlibrary cache的占用,还是先将cursor_shring 参数改成了默认的exact模式。 这样version_count 会减少很多,但是硬解析的次数也会增加,可能会增加Library Cache Latch等待 现在只能这样修改一下,在找个环境测试一下。

 

Oracle cursor_sharing 参数 详解

http://www.cndba.cn/Dave/article/1543

 

 


 

 

 

 

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

QQ:492913789

Email:ahdba@qq.com

Blog: http://www.cndba.cn/dave


DBA1 群:62697716();   DBA2 群:62697977()   DBA3 群:62697850()  

DBA 超级群:63306533();  DBA4 群: 83829929  DBA5群: 142216823   

DBA6 群:158654907  聊天 群:40132017   聊天2群:69087192

--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请

以下是iperf3的C代码,可以在GitHub上找到完整的代码: ```c #include <stdio.h> #include <stdlib.h> #include <string.h> #include <unistd.h> #include <signal.h> #include <errno.h> #include <netdb.h> #include <sys/types.h> #include <sys/socket.h> #include <netinet/in.h> #include <arpa/inet.h> #include "iperf.h" #include "iperf_api.h" #include "iperf_locale.h" #include "units.h" #include "net.h" #include "timer.h" #include "tcp_window_size.h" #include "cjson.h" #ifdef HAVE_SSL #include <openssl/ssl.h> #include <openssl/err.h> #endif /* Forwards. */ static int iperf_run(struct iperf_test *test); /**************************************************************************/ int main(int argc, char **argv) { int i; struct iperf_test *test; int result; struct sigaction sa; char client_version[10] = IPERF_VERSION; setlocale(LC_ALL, ""); bindtextdomain("iperf3", LOCALEDIR); textdomain("iperf3"); /* Initialize settings. */ test = iperf_new_test(); /* Register a few signal handlers to make clean exit more likely. */ memset(&sa, 0, sizeof(sa)); sa.sa_handler = &iperf_got_sigint; sigaction(SIGINT, &sa, NULL); sa.sa_handler = &iperf_got_sigpipe; sigaction(SIGPIPE, &sa, NULL); /* Parse command line options, handling some options immediately. */ i = iperf_parse_arguments(test, argc, argv); if (i < 0) { fprintf(stderr, "%s", iperf_strerror(i)); exit(1); } if (test->version) { printf("%s\n", client_version); exit(0); } else if (test->help) { iperf_printf(test, "\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n", "Usage: iperf3 [-options] [-s|-c host] [options]\n", "Client/Server:", " -V, --version show version information and quit.", " -h, --help show this message and quit.", " -i, --interval n seconds between periodic bandwidth reports.", " -f, --format [kmKM] format to report: Kbits, Mbits, KBytes, MBytes", " -P, --parallel n number of parallel client streams to run."); iperf_printf(test, "\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n", "Client specific:", " -c, --client <host> run in client mode, connecting to <host>.", " -u, --udp use UDP rather than TCP.", " -b, --bitrate #[KMG][/#] target bitrate in bits/sec (0 for unlimited)", " (default 1 Mbit/sec for UDP, unlimited for TCP)", " -t, --time n time in seconds to transmit for (default 10 secs)", " -n, --bytes n number of bytes to transmit (instead of -t)", " -k, --blockcount #[KMG] number of blocks (packets) to transmit (instead of -t or -n)", " -l, --length #[KMG] length of buffer to read or write", " (default 128 KB for TCP, dynamic or 8 KB for UDP)", " -R, --reverse reverse the direction of a test (client sends, server receives)."); iperf_printf(test, "\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n", "Server specific:", " -s, --server run in server mode.", " -D, --daemon run the server as a daemon", " -I, --pidfile file write PID file (default /var/run/iperf3.pid)", " -1, --one-off handle one client connection then exit.", " -B, --bind <host> bind to a specific interface, e.g. eth0"); iperf_printf(test, "\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n%s\n\n", "JSON options:", " -J, --json output in JSON format", " --logfile f send output to a log file", "", "For more information and tuning options, see iperf3's man page.", "", "Please report bugs to https://github.com/esnet/iperf", ""); exit(0); } /* Check for version number consistency. */ if (test->protocol->id == Ptcp && test->version == 3 && test->tcp.omit_version) { i = 2; } else { i = 3; } if (strncmp(client_version, test->version_string, i) != 0) { iperf_printf(test, "warning: version number mismatch (client %s, server %s)\n", client_version, test->version_string); } /* Initialize IP protocol */ if (test->cookiefile) { test->cookie = iperf_load_cookie(test->cookiefile); if (test->cookie == NULL) { iperf_errexit(test, "unable to load cookie from file '%s'", test->cookiefile); } } if (test->reverse) test->settings->reverse = 1; if (test->protocol->id == Pudp) { if (test->settings->mss || test->settings->socket_bufsize) { iperf_printf(test, "warning: MSS and socket buffer size settings are not used in UDP mode.\n"); } if (test->settings->no_delay) { iperf_printf(test, "warning: the TCP_NODELAY option is not used in UDP mode.\n"); } if (test->settings->pmtu != -1) { iperf_printf(test, "warning: the PMTU option is not used in UDP mode.\n"); } } #ifdef HAVE_SSL /* Initialize SSL library */ if (test->protocol->id == Ptcp && test->settings->ssl) { SSL_load_error_strings(); SSL_library_init(); test->sslctx = SSL_CTX_new(TLS_client_method()); if (test->sslctx == NULL) { iperf_errexit(test, "failed to create SSL context\n"); } if (test->settings->ssl_cafile) { if (SSL_CTX_load_verify_locations(test->sslctx, test->settings->ssl_cafile, NULL) != 1) { SSL_CTX_free(test->sslctx); iperf_errexit(test, "failed to load CA certificates from %s\n", test->settings->ssl_cafile); } } if (test->settings->ssl_cert) { if (SSL_CTX_use_certificate_chain_file(test->sslctx, test->settings->ssl_cert) != 1) { SSL_CTX_free(test->sslctx); iperf_errexit(test, "failed to load SSL certificate from %s\n", test->settings->ssl_cert); } } if (test->settings->ssl_key) { if (SSL_CTX_use_PrivateKey_file(test->sslctx, test->settings->ssl_key, SSL_FILETYPE_PEM) != 1) { SSL_CTX_free(test->sslctx); iperf_errexit(test, "failed to load SSL key from %s\n", test->settings->ssl_key); } } SSL_CTX_set_verify(test->sslctx, SSL_VERIFY_PEER, NULL); } #endif /* Daemon mode. */ if (test->daemon) { if (daemon(0, 0) != 0) { iperf_errexit(test, "error - failed to become a daemon: %s\n", strerror(errno)); } if (test->pidfile) { FILE *f; f = fopen(test->pidfile, "w"); if (f == NULL) { iperf_errexit(test, "error - unable to write PID file '%s': %s\n", test->pidfile, strerror(errno)); } fprintf(f, "%d\n", getpid()); fclose(f); } } /* Ignore SIGPIPE to simplify error handling */ sa.sa_handler = SIG_IGN; sigaction(SIGPIPE, &sa, NULL); /* If we are doing a single client (-1) and it is a daemon, don't do -D again */ if (test->daemon && test->num_ostreams == 1) test->daemon = 0; /* Defer daemon mode until after the above check for single client + daemon */ if (test->daemon) { if (daemon(0, 0) != 0) { iperf_errexit(test, "error - failed to become a daemon: %s\n", strerror(errno)); } if (test->pidfile) { FILE *f; f = fopen(test->pidfile, "w"); if (f == NULL) { iperf_errexit(test, "error - unable to write PID file '%s': %s\n", test->pidfile, strerror(errno)); } fprintf(f, "%d\n", getpid()); fclose(f); } } /* Ignore SIGPIPE to simplify error handling */ sa.sa_handler = SIG_IGN; sigaction(SIGPIPE, &sa, NULL); /* Set up the output stream */ if (test->json_output) { test->outfile = json_get_output_stream(test->json_output_file); } else if (test->logfile) { test->outfile = fopen(test->logfile, "w"); if (test->outfile == NULL) { iperf_errexit(test, "error - unable to write log to file '%s': %s\n", test->logfile, strerror(errno)); } } else { test->outfile = stdout; } /* Start the client or server */ if (test->server) { if (test->daemon) { iperf_printf(test, "Server listening on port %d\n", test->server_port); } else { iperf_printf(test, "-----------------------------------------------------------\n"); iperf_printf(test, "Server listening on %s port %d\n", test->settings->domain == AF_INET6 ? "[::]" : "0.0.0.0", test->server_port); iperf_printf(test, "-----------------------------------------------------------\n"); } result = iperf_run_server(test); } else { if (test->daemon) { iperf_printf(test, "Client connecting to %s, TCP port %d\n", test->server_hostname, test->server_port); } else { if (test->reverse) iperf_printf(test, "-----------------------------------------------------------\n"); iperf_printf(test, "Client connecting to %s, %s port %d\n", test->server_hostname, test->protocol->name, test->server_port); if (test->reverse) iperf_printf(test, "-----------------------------------------------------------\n"); } result = iperf_run_client(test); } if (test->cookie) iperf_delete_cookie(test->cookie); #ifdef HAVE_SSL if (test->protocol->id == Ptcp && test->settings->ssl) { SSL_CTX_free(test->sslctx); } ERR_free_strings(); #endif iperf_free_test(test); return result; } static int iperf_run(struct iperf_test *test) { test->start_time = milliseconds(); test->next_time = test->start_time; test->bytes_sent = 0; test->blocks_sent = 0; test->retransmits = 0; if (test->server_hostname) { test->server_hostname_len = strlen(test->server_hostname); } if (test->bind_address) { test->bind_address_len = strlen(test->bind_address); } if (test->json_output) { cJSON *json_output = cJSON_CreateObject(); if (json_output == NULL) { iperf_errexit(test, "error - cJSON_CreateObject failed: %s\n", strerror(errno)); } cJSON_AddItemToObject(json_output, "start", cJSON_CreateNumber((double) test->start_time / 1000)); if (test->verbose) { cJSON_AddItemToObject(json_output, "verbose", cJSON_CreateNumber(1)); } cJSON_AddItemToObject(json_output, "system_info", cJSON_CreateObject()); iperf_json_printf(json_output, "version", "%s", test->version); iperf_json_printf(json_output, "system_info", "%s", get_system_info()); if (test->title) { iperf_json_printf(json_output, "title", "%s", test->title); } if (test->extra_data) { cJSON_AddItemToObject(json_output, "extra_data", cJSON_Parse(test->extra_data)); } test->json_start_time = milliseconds(); test->json_output_string = cJSON_Print(json_output); cJSON_Delete(json_output); if (test->json_output_string == NULL) { iperf_errexit(test, "error - cJSON_Print failed: %s\n", strerror(errno)); } } if (test->protocol->id == Ptcp) { if (test->settings->socket_bufsize) { if (iperf_set_tcp_windowsize(test) != 0) { iperf_errexit(test, "error - %s\n", strerror(errno)); } } } if (test->reverse) { if (test->protocol->id == Ptcp) { if (iperf_create_streams(test, test->reverse) < 0) { iperf_errexit(test, "error - %s\n", strerror(errno)); } } if (iperf_connect(test, test->reverse) < 0) { iperf_errexit(test, "error - %s\n", strerror(errno)); } } else { if (iperf_create_streams(test, test->reverse) < 0) { iperf_errexit(test, "error - %s\n", strerror(errno)); } if (iperf_connect(test, test->reverse) < 0) { iperf_errexit(test, "error - %s\n", strerror(errno)); } } if (test->json_output) { cJSON *json_output = cJSON_CreateObject(); if (json_output == NULL) { iperf_errexit(test, "error - cJSON_CreateObject failed: %s\n", strerror(errno)); } cJSON_AddItemToObject(json_output, "start", cJSON_CreateNumber((double) test->start_time / 1000)); if (test->verbose) { cJSON_AddItemToObject(json_output, "verbose", cJSON_CreateNumber(1)); } iperf_json_printf(json_output, "interval", "%d", test->settings->stats_interval); if (test->title) { iperf_json_printf(json_output, "title", "%s", test->title); } if (test->extra_data) { cJSON_AddItemToObject(json_output, "extra_data", cJSON_Parse(test->extra_data)); } iperf_json_printf(json_output, "start_connected", "%d", test->connected); cJSON_AddItemToObject(json_output, "intervals", cJSON_CreateArray()); test->json_output_string = cJSON_Print(json_output); cJSON_Delete(json_output); if (test->json_output_string == NULL) { iperf_errexit(test, "error - cJSON_Print failed: %s\n", strerror(errno)); } } if (test->protocol->id == Pudp) { if (iperf_udp_connect(test) < 0) { iperf_errexit(test, "error - %s\n", strerror(errno)); } } if (test->reverse) { if (test->protocol->id == Ptcp) { if (iperf_listen(test) < 0) { iperf_errexit(test, "error - %s\n", strerror(errno)); } } if (test->protocol->id == Pudp || test->protocol->id == Psctp) { if (iperf_run_server_udp(test) < 0) { iperf_errexit(test, "error - %s\n", strerror(errno)); } } else { if (iperf_run_server_tcp(test) < 0) { iperf_errexit(test, "error - %s\n", strerror(errno)); } } } else { if (test->protocol->id == Ptcp) { if (iperf_run_client_tcp(test) < 0) { iperf_errexit(test, "error - %s\n", strerror(errno)); } } else { if (iperf_run_client_udp(test) < 0) { iperf_errexit(test, "error - %s\n", strerror(errno)); } } } if (test->json_output) { cJSON *json_output = cJSON_CreateObject(); if (json_output == NULL) { iperf_errexit(test, "error - cJSON_CreateObject failed: %s\n", strerror(errno)); } cJSON_AddItemToObject(json_output, "start", cJSON_CreateNumber((double) test->start_time / 1000)); if (test->verbose) { cJSON_AddItemToObject(json_output, "verbose", cJSON_CreateNumber(1)); } iperf_json_printf(json_output, "interval", "%d", test->settings->stats_interval); if (test->title) { iperf_json_printf(json_output, "title", "%s", test->title); } if (test
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值