ORACLE--预编译与共享池--SGA基本介绍-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

ORACLE--预编译与共享池--SGA基本介绍

简介:

我们暂时先不说其他的,我们先做一个简单的实验来证明来看出一些问题,最后通过为什么来说明实验的结论,并介绍原理和常规查看方式,好了,我们先看看下面三段代码分别执行的结果。

首先为了测试,我们需要创建一张表:

CREATE TABLE PRE_TEST_TABLE(
   C1  NUMBER,
   C2  VARCHAR2(100)
);

好了,我们做一个插入操作的对比:

代码段1

BEGIN

      FOR I IN 1..20000 LOOP

           EXECUTE IMMEDIATE 'INSERT INTO PRE_TEST_TABLE VALUES('||i||',''test'')';

      END LOOP;

      COMMIT;

END;

 

代码片段2

BEGIN

      FOR I IN 1..20000 LOOP

           EXECUTE IMMEDIATE 'INSERT INTO PRE_TEST_TABLE VALUES(:V1,:V2)' USING I,'test';

      END LOOP;

      COMMIT;

END;

 

代码片段3

BEGIN

      FOR I IN 1..20000 LOOP

           INSERT INTO PRE_TEST_TABLE VALUES(I,'TEST');

      END LOOP;

      COMMIT;

END;

 

三段代码执行效率你可以清晰的对比出来,代码段1是最慢的,而且比后两者慢很多倍,而代码片段2和代码片段3执行效率基本是一样的,为什么会有这样的效果呢?看了下面的推敲,我们就清楚了,我们先把数据清理掉,共享池清理一下(下面我们再说),在做操作比较好。 

 

现在我需要做的SQL语句操作是对表的插入PRE_TEST_TABLE VALUES表的操作:

INSERT INTO PRE_TEST_TABLE VALUES....

 

至于参数如何,我们不一定,首先查询一下共享池内部做此操作的记录:

SELECT * FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'INSERT INTO PRE_TEST_TABLE VALUES%';

 

发现数据太多,有多少不好说了,暂时不数了,因为对得出结论没有意义,需要清理下共享池方便试验。

 

TRUNCATE TABLE PRE_TEST_TABLE;//清空表

 

ALTER SYSTEM FLUSH SHARED_POOL;//清空缓冲区

 

查询共享池:

SELECT * FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'INSERT INTO PRE_TEST_TABLE VALUES%';

发现没有任何数据。。。。我们开始比较干净的做实验了。。。

 

开始试验:

我们将上述试验的循环次数降低为3

首先执行代码段1

BEGIN

      FOR I IN 1..3 LOOP

           EXECUTE IMMEDIATE 'INSERT INTO PRE_TEST_TABLE VALUES('||i||',''test'')';

      END LOOP;

      COMMIT;

END;

 

查询共享池(发现多了3条记录):

SQL> SELECT SQL_TEXT,HASH_VALUE,PARSE_CALLS FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'INSERT INTO PRE_TEST_TABLE VALUES%';

SQL_TEXT                                                            HASH_VALUE      PARSE_CALLS
--------------------------------------------------------------- -------------- ---------------------
INSERT INTO PRE_TEST_TABLE VALUES(1,'test')        2868385396       1
INSERT INTO PRE_TEST_TABLE VALUES(2,'test')        2513099158       1
INSERT INTO PRE_TEST_TABLE VALUES(3,'test')        455953479         1

 

再执行第二段代码:

BEGIN

      FOR I IN 1..3 LOOP

           EXECUTE IMMEDIATE 'INSERT INTO PRE_TEST_TABLE VALUES(:V1,:V2)' USING I,'test';

      END LOOP;

      COMMIT;

END;

 

再查询共享池(发现只多了一条SQL):

SQL> SELECT SQL_TEXT,HASH_VALUE,PARSE_CALLS FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'INSERT INTO PRE_TEST_TABLE VALUES%';

SQL_TEXT                                                              HASH_VALUE  PARSE_CALLS
--------------------------------------------------------------- ---------------- ------------
INSERT INTO PRE_TEST_TABLE VALUES(1,'test')         2868385396    1
INSERT INTO PRE_TEST_TABLE VALUES(:V1,:V2)        357326048      3
INSERT INTO PRE_TEST_TABLE VALUES(2,'test')         2513099158    1
INSERT INTO PRE_TEST_TABLE VALUES(3,'test')         455953479      1

 

再执行第三段代码:

BEGIN

      FOR I IN 1..3 LOOP

           INSERT INTO PRE_TEST_TABLE VALUES(I,'TEST');

      END LOOP;

      COMMIT;

END;

 

再查询共享池(发现也只多了一条SQL):

SQL> SELECT SQL_TEXT,HASH_VALUE,PARSE_CALLS FROM V$SQLAREA
  2  WHERE SQL_TEXT LIKE 'INSERT INTO PRE_TEST_TABLE VALUES%';

SQL_TEXT                                                             HASH_VALUE   PARSE_CALLS
------------------------------------------------------------- ------------------  ------------
INSERT INTO PRE_TEST_TABLE VALUES(1,'test')          2868385396    1
INSERT INTO PRE_TEST_TABLE VALUES(:B1 ,'TEST')     2239119514    3
INSERT INTO PRE_TEST_TABLE VALUES(:V1,:V2)         357326048      3
INSERT INTO PRE_TEST_TABLE VALUES(2,'test')          2513099158    1
INSERT INTO PRE_TEST_TABLE VALUES(3,'test')          455953479      1

 

从这里可能大家基本可以得出初步的结论,就是第一段代码每条SQL都会占用共享池,并征用共享池,而且用过一次后就不会在使用了,这个答案是正确的,我首先给出预编译和拼SQLORACLE的四个不同影响进行定义,下面再说明为什么:

1、拼SQL会导致共享池的长期征用状态

2、拼SQL会导致共享池中存在一堆的垃圾SQL

3、拼SQL会硬解析(Hard parse),而预编译可以充分利用软解析。

4、拼SQL会导致注入型错误。

 

疑问:

为什么第二段代码和第三段代码是一个效果呢,这里说明一下,在存储过程提高效率的基本因素就是你如果按照过程化去写SQL,存储过程默认就会按照预编译方式去执行,因为这是ORACLE优化的基本原则,而即使在过程中使用EXECUTE IMMEDIATE SQL_STR去执行,请注意,EXECUTE IMMEDIATE不是SQL语句,而是ORACLE在过程中支持的命令,即直接发送执行命令给ORACLE的分析器,所以这个是否进行编译完全看你的SQL是什么样的了,而不是ORACLE自己能决定的。

 

过程说明:

SQL从终端通过1521 TCP服务端口以字符串方式传送至ORACLE后(包含JAVA程序也是这样,其他应用程序,如出现String sql = "SELECT * FROM A WHERE ID="+id;也会出现和代码段1一样的结果),ORACLE通过HASH算法对其SQL转换,并在共享池中查找是否存在同样HASH值的SQL(即:SQL即使是参数或者大小写不同,也会导致找不到一样的HASH值),如果找到了,直接执行已经编译完的SQL,并修改使用率(这个有用);若没有,则首先通过硬解析工具对其进行各项语法分析和性能指标分析等等,然后开始征用共享池(此为共享资源),并注册到共享池中,标志调用次数为1,然后再执行,当在大量征用共享资源时候,并且在硬解析过程中,高并发将导致阻塞。可以将上述第一段代码的循环次数增加为10万,基本机器可以弄死,呵呵。。。。

 

共享池的大小肯定是有限制的,所以ORACLE在共享池不够用的时候,采用基于LRU为核心的算法进行替换(上述的PARSE_CALLS字段可以基本看出SQL被调用的次数,但是不完全依赖于它),我们最希望的就是不要做这样的操作,因为这样的操作必然面临磁盘读取,在内存中获取我们称为命中,命中率高才能提高利用率,系统的整体性能才能得到保证。

 

第四点中提到的至于注入型错误或攻击就是传送特殊字符串,导致SQL执行SQL的修改,为什么,因为你的SQL是拼出来的,我举个简单例子:

你的程序中:

String sql = "SELECT * FROM A WHERE A.NAME = '"+name+"'";

name假如为查询条件传入,那么此时我在查询条件文本框中这样输入:

第一种输入:

' OR 1=1--

此时你的SQL变成:

SELECT * FROM A WHERE A.NAME ='' OR 1=1 --'

后面两杠是屏蔽你后面的SQL,用一个OR 1=1前面的东东不论是啥就永远成立了(注意:ORACLE的SQL执行,优先级是AND 大于 OR的,所以只要OR 1=1,无论有多少个AND,最后会和这个OR 1=1去匹配,所有的数据都会提取出来),此时分页或者说导出控制天数或者数据量,都控制不了,有多少数据就会出来多少数据,首先数据权限没有了,然后开几个浏览器系统就能挂掉,呵呵!

 

另一种更加损的输入招数是:

';DROP TABLE DUAL--

在我以前用SQL SERVERJDBC时,这种方式是支持的,这样是很损的一种办法。其实输入的方式有些通过URL有些通过条件,不一定,而且千奇百怪,有些想都想不到,至少他可能会导致你的SQL执行不是那么顺利篡改了SQL执行的原有意义。

 

所以OLTP系统使用这样的SQL,尤其对于ORACLE数据库(其余数据库请自己研究下),是非常恶心的,所以我们在OLTP系统是封杀这样的SQL存在。

 

 

简要共享池的使用介绍:

谈到共享池我们大致介绍一些SGA的内容,ORACLE数据库我们操作主要对象是实例,而非数据库本身,主要原因为:性能、安全性。而实例大致分:SGAPGA,本文只是由共享池介绍一下SGA,细节说下其共享池部分,而PGA后续讨论。

 

SGA内部主要包含:数据缓冲区、共享池、JAVA池、大池、Stream池、重做日志缓冲区

 

PGA内部包含:用户Session信息、排序信息、Hash area、堆栈。这些信息被后台进程所控制,版本递增的后台进程也在不断增加,细节的信息后续讨论。

 

其实今天所谓查询共享池,也是查询共享池内部的Library cache。我们在SQLPLUS中最常用的命令就是:

SQL> show sga;

Total System Global Area 1.7062E+10 bytes
Fixed Size                      2102776 bytes
Variable Size                  4613736968 bytes
Database Buffers            1.2432E+10 bytes
Redo Buffers                  14671872 bytes

也可以使用一下方式查询和上述一样的SGA信息:

 

 

SQL> SELECT * FROM V$SGA;

NAME                      VALUE
-------------------- ----------
Fixed Size               2102776
Variable Size           4613736968
Database Buffers     1.2432E+10
Redo Buffers           14671872

 

分别解释下几个字段的意义:

Total System Global Area:代表SGA的总体大小,包含下面几者之和,都是以byte为单位,即字节;

Fixed Size:字典信息、控制信息、状态信息。

Variable Size共享池(shared pool)、Java(Java Pool)、大池(Large Pool)Stream pool

Database Buffers:为数据缓冲区,OLTP系统要求这块设置较大。

Redo Buffer:重做日志缓冲区,适当提高缓冲区,减少文件组切换,可以提高效率。

 

通过一下SQL可以得到SGA内部详细的组件分配情况:

 

SQL> SELECT * FROM V$SGASTAT;

POOL         NAME                            BYTES
------------ -------------------------- ----------
                  fixed_sga                     2102776
                  buffer_cache               1.2432E+10
                  log_buffer                   14671872
shared pool  transaction                   8062512
shared pool  table definiti                  80336
shared pool  KGSKI scheduler heap 2 de         232
shared pool  kspd run-time context              16
shared pool  PX subheap                      61344
shared pool  partitioning d                 455480
shared pool  message pool freequeue         757568
shared pool  qesblGF:bfm                       728

POOL         NAME                            BYTES
------------ -------------------------- ----------

等等数据。。。。。。。自己查看一下即可,我这由于篇幅所限,就输出这么多了。

 

 

 

--下面SQL用于查看SGA中可进行手工调配参数的列表:

SQL> SELECT * FROM V$SGA_DYNAMIC_COMPONENTS;

COMPONENT                                                        CURRENT_SIZE   MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_
---------------------------------------------------------------- ------------ ---------- ----------
shared pool                                                        4194304000 4194304000          0          4194304000          0 STATIC                              
large pool                                                          134217728  134217728          0           134217728          0 STATIC                              
java pool                                                           134217728  134217728          0           134217728          0 STATIC                              
streams pool                                                        117440512  117440512          0           117440512          0 STATIC                              
DEFAULT buffer cache                                               1.2264E+10 1.2264E+10          0          1.2264E+10          2 SHRINK        MANUAL    07-4

KEEP buffer cache                                                   100663296          0          0           100663296          8 GROW          MANUAL    07-4
-10
RECYCLE buffer cache                                                 67108864          0          0            67108864          1 GROW          MANUAL    07-4
-10
DEFAULT 2K buffer cache                                                     0          0          0                   0          0 STATIC                              
DEFAULT 4K buffer cache                                                     0          0          0                   0          0 STATIC                              
DEFAULT 8K buffer cache                                                     0          0          0                   0          0 STATIC                              
DEFAULT 16K buffer cache                                                    0          0          0                   0          0 STATIC                              

COMPONENT                                                        CURRENT_SIZE   MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_
---------------------------------------------------------------- ------------ ---------- ----------
DEFAULT 32K buffer cache                                                    0          0          0                   0          0 STATIC                              
ASM Buffer Cache                                                            0          0          0          1.2465E+10          0 STATIC                  

 

 

查询共享池大小:

SQL> show parameter shared_pool_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 4000M

 

SQL> SELECT NAME,TYPE,VALUE
  2      FROM V$PARAMETER A
  3      WHERE A.NAME='shared_pool_size';

NAME                                       TYPE          VALUE
-------------------- ----------------------------- ----------------------------
shared_pool_size                       6               4194304000

 

 

这里顺便说下,很多时候大家不知道数据字典是什么,很多时候数据字典的名字很长,而且有些后面又s,有些后面没有S,大家记录不下来,此时大家只需要知道大致是什么就OK了,然后用下面的基于视图的总视图去查询视图的实际名称(我们以不知道共享池的视图是什么):

SQL> SELECT * FROM DICT T
  2  WHERE T.TABLE_NAME LIKE '%V$SHARED%';

 

TABLE_NAME                                COMMENTS
------------------------------ -------------------------------------------------------------------
V$SHARED_SERVER_MONITOR        Synonym for V_$SHARED_SERVER_MONITOR
V$SHARED_SERVER                       Synonym for V_$SHARED_SERVER
V$SHARED_POOL_RESERVED          Synonym for V_$SHARED_POOL_RESERVED
V$SHARED_POOL_ADVICE              Synonym for V_$SHARED_POOL_ADVICE
GV$SHARED_SERVER_MONITOR      Synonym for GV_$SHARED_SERVER_MONITOR
GV$SHARED_SERVER                     Synonym for GV_$SHARED_SERVER
GV$SHARED_POOL_RESERVED        Synonym for GV_$SHARED_POOL_RESERVED
GV$SHARED_POOL_ADVICE            Synonym for GV_$SHARED_POOL_ADVICE

 

其中GV$开头的视图是用于集群中的,我们一般只关心V$开头的信息;可能你连表的大致意思都不清楚,你只大致记得有一个字段大致的名称,如我们知道一个视图内部字段的名称有一个以SCN开头的列,那么我们这样也可以反向给它定位:

SQL> SELECT T.TABLE_NAME, T.COLUMN_NAME
  2    FROM DICT_COLUMNS T
  3   WHERE T.COLUMN_NAME LIKE 'SCN%';

 

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
ALL_SUMDELTA                   SCN
DBA_AUDIT_TRAIL                SCN
USER_AUDIT_TRAIL               SCN
DBA_AUDIT_STATEMENT            SCN
USER_AUDIT_STATEMENT           SCN
DBA_AUDIT_OBJECT               SCN
USER_AUDIT_OBJECT              SCN
DBA_AUDIT_EXISTS               SCN
DBA_FGA_AUDIT_TRAIL            SCN
DBA_COMMON_AUDIT_TRAIL         SCN
DBA_CAPTURE_PREPARED_TABLES    SCN
ALL_CAPTURE_PREPARED_TABLES    SCN
DBA_FILE_GROUP_TABLES          SCN
ALL_FILE_GROUP_TABLES          SCN
USER_FILE_GROUP_TABLES         SCN
V$RESTORE_POINT                SCN
V$RECOVERY_STATUS              SCN_NEEDED
V$LOGMNR_CONTENTS              SCN
V$XML_AUDIT_TRAIL              SCN
GV$RESTORE_POINT               SCN

 

         

 

这里回到正题:清空共享池(OLTP系统运行时不要去操作,这个过程很影响整体运行)

SQL>ALTER SYSTEM FLUSH SHARED_POOL;

如果要查询某过程或包的源码,可以看一下系统的资源包:

SQL>SELECT * FROM USER_SOURCE t WHERE t.name = '过程或包的名字' ORDER BY LINE;

这些源码信息在首次是不会装入内存的,因为共享池的大小有限,调用时再装入内存,而且也不会逃脱LRU的命运,若一些写的很烂的SQL,就有可能把它替换出去,这个时候我们想做到的是启动时直接装入内存并不会被替换,ORACLE给我们一个KEEP方法,但是并非默认的,也就是安装ORACLE后并不是默认就提供的这个包,如果你用具有DBA权限的人进去,不能使用DBMS_SHARED_POOL这个包(报:这个包不存在),说明还没有创建,此时需要做一下操作,才能创建:

 

1、首先定位ORACLE_HOME的位置,我们没有直接定位ORACLE_HOME的方式,除非是你自己安装的,如果不知道,用下面一个办法:

SELECT * FROM V$PARAMETER P1
WHERE P1.NAME = 'spfile';

 

2、若没有该目录,使用CREATE SPFILE FROM PFILE;执行一下重启OK就有了,得到该目录后,假如得到如下:

D:/ORACLE10/PRODUCT/10.2.0/DB_1/DATABASE/SPFILEORCL102.ORA

那么ORACLE_HOME上相推两层得到:

D:/ORACLE10/PRODUCT/10.2.0/DB_1/

那么要得到那个包的创建脚本就在:

D:/oracle10/product/10.2.0/db_1/RDBMS/ADMIN/DBMSPOOL.SQL

此时需要到安装数据库的机器上去执行,如果你本地有脚本当然也可以执行,但是注意:这个执行必须是在SQLPLUS中,PL/SQL中执行该脚本不好用。

 

3、执行方式:进入到安装该数据库的SQLPLUS下用SYS用户登录,该包需要创建在SYS用户下。

SQL> @D:/oracle10/product/10.2.0/db_1/RDBMS/ADMIN/DBMSPOOL.SQL

程序包已创建。


授权成功。


视图已创建。


程序包体已创建。

 

 

 

4、对于系统的大过程,可能第一次装载比较缓慢,而且如果使用频率较高,可以将其脱离LRU算法,并直接装入内存,如果可以的话,做ORACLE启动时触发器,如果不行,就手动执行一下代码:

BEGIN

   SYS.DBMS_SHARED_POOL.KEEP('存储过程或包的名字');

END;

 

 

若想将某过程从内存中去除掉:

BEGIN

   SYS.DBMS_SHARED_POOL.UNKEEP('存储过程或包的名字');

END;

 

 

5、此时查看缓冲池中是否装载改对象:

SELECT name,owner,type
FROM v$db_object_cache where kept = 'YES'
AND NAME ='
过程或包的名字';//这个地方也可以用SQL片段来LIKE

 

 

查看共享池中执行的一些SQL包头:

SELECT * FROM V$SQLAREA;

 

 

通不过上述的SQL得到HASH_VALUE或者ADDREDSS或者SQL_ID都可以通过以下视图得到对应执行SQL的全部内容(当SQL较长的时候,V$SQLAREA只保存前面一部分,全部内容在该视图中):

SELECT * FROM V$SQLTEXT_WITH_NEWLINES;

 

 

得到SQL的执行计划:

SELECT * FROM V$SQL_PLAN;

 

 

得到对共享池设置的建议值,ORACLE根据实际运行情况,推荐值:

SELECT * FROM V$SHARED_POOL_ADVANCE;

 

 

还有些不是很常用的:

得到SQL绑定变量信息:

SELECT * FROM V$SQL_BIND_CAPTURE;

 

 

SQL占用共享池内存:

SELECT * FROM V$SQL_SHARED_MEMORY;

 

 

SQL消耗调用的统计信息:

SELECT * FROM V$SQLSTATS;

 

 

这里只是由预编译->共享池->SGA的过程,对于SGA的内核只是阐述了共享池的部分,下次说明SGA的另一大块,Data Buffer,数据缓冲区,该区域在OLTP系统中非常重要。

 

最后补充话题,本来这个想在后面说的,因为涉及一些其他内容,不过既然说到,就提一下吧,我们在OLTP要求使用绑定参数方式执行SQL如:

用应用程序的SQL应当是

String sql = “SELECT * FROM A WHERE ID=?”;

而不是

String sql = “SELECT * FROM A WHERE ID=”+id;

 

那么这样的情况我们该怎么办呢?当要查询多个ID,使用IN的情况,或者同时修改多条记录的操作,我们无疑想出最常规的三种办法(我们先介绍常规方法,再介绍解决问题的方法):

 

方法1(拼串,放弃预编译):

String sql = “SELECT * FROM A WHERE ID IN(”+keys+”)”;

付:该方法放弃预编译,但是也是常规方法中的无奈之举。

 

方法2(将参数个数动态化去预编译)

StringBuffer sql = new StringBuffer(256);

sql.append(“SEELCT * FROM A WHERE ID IN(”);

for(….) {

   sql.append(“?”).append(“,”);

}

sql.deleteCharAt(sql.length()-1);

付:该方法比上一种稍微好一点,OLTP下一般情况下,我们常规方法中最少要这样去完成,大家可以把ibatis的执行SQL日志拿出来看下即可发现,ibatis对于动态参数个数也是这样去完成的,对于并发度不算高的代码段我们可以这样使用,如果并发度高的代码段,这样使用我们也不会考虑。

 

方法3(循环提取。循环修改)

for(….) {

    ptmt.setInt(1,ID[i]);

    ptmt.executeQuery(“SELECT * FROM A WHERE ID=?”);

}

付:这在执行过程中往往是最“不应该使用”的办法了;如果执行插入操作,我们会适当考虑携带批处理这样去完成也是可以的,不过对于UPDATE和SELECT这类操作我们不该这样使用的。

 

在这里上述三种办法,只有第二种方法OLTP并发量不大的情况下可以使用,若并发量较大,且参数个数的动态性比较大,也应该考虑使用其他方法去实现,因为大家通过上述试验和反向查询后发现,问号个数的变化也会产生不同的SQL,共享池中仍然会造成很多的垃圾,只是相对第一种方法概率降低了很多,而第三种方法基本是我们不考虑的。

 

我们说一下如果对于这样的情况,批量执行过程中,我们该如何转换,利用ORACLE的数组进行转换,为此我们先在ORACLE内部提供一个函数,和数组类型,前序工作:

步骤1

创建数据类型(表格类型,也类似数组):

CREATE OR REPLACE TYPE MY_TABLE_TYPE IS TABLE OF VARCHAR2(8000);

 

步骤2

创建转换函数(在网上很多地方可以找到类似代码,我这只是一个参考):

CREATE OR REPLACE FUNCTION SPLIT(SRC_STR IN VARCHAR2,

SPLIT_STR VARCHAR2) RETURN MY_TABLE_TYPE IS
  V_TABLE_STR MY_TABLE_TYPE := MY_TABLE_TYPE();
  V_TEMP_STR  VARCHAR2(
8000) := SRC_STR;
  V_SPLIT_STR VARCHAR2(
20) := SPLIT_STR;
  I           NUMBER :=
1;
  J           NUMBER :=
1;
BEGIN

  IF V_SPLIT_STR IS NULL THEN
      V_SPLIT_STR :=
',';--我们默认用逗号分隔  
  END IF;

  IF SRC_STR IS NULL OR SRC_STR = V_SPLIT_STR THEN
       RETURN V_TABLE_STR;
  END IF;

  V_TEMP_STR := LTRIM(V_TEMP_STR, V_SPLIT_STR);

  LOOP
        I := INSTR(V_TEMP_STR, V_SPLIT_STR, J);
        EXIT WHEN I =
0 OR J > LENGTH(V_TEMP_STR);
        V_TABLE_STR.EXTEND;
        V_TABLE_STR(V_TABLE_STR.COUNT) := SUBSTR(V_TEMP_STR, J, I - J);
        J := I + LENGTH(V_SPLIT_STR);
  END LOOP;

  IF J < LENGTH(V_TEMP_STR) THEN
        V_TABLE_STR.EXTEND;
        V_TABLE_STR(V_TABLE_STR.COUNT) := SUBSTR(V_TEMP_STR, J, LENGTH(V_TEMP_STR) - J +
1);
  END IF;

  RETURN V_TABLE_STR;

END SPLIT;

 

 

步骤3:(测试可用性,这里假如数据都是按照逗号分隔的)

较高版本支持这样的写法:

SQL> SELECT * FROM TABLE(SPLIT('123,321',','));

 

COLUMN_VALUE

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

123

321

较低版本可以这样写:

SQL> SELECT * FROM TABLE(CAST(SPLIT('123,321,456', ',') AS MY_TABLE_TYPE));

 

COLUMN_VALUE

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

123

321

456

 

步骤4(程序应用):

String sql = “SELECT * FROM A WHERE ID IN(SELECT * FROM TABLE(SPLIT(?,',')))”;//当然对于低版本的数据库,相应修改即可。

但是ORACLE有些时候会很傻的去使用HASH JOIN,因为他们他不知道你里面返回多少数据,而我们通过转换回来的ID往往数据量很少,最多就是几十行上百行,若目标表为一个大表,使用HASH JOIN的确是一件很浪费的事情,此时我们不愿意这样去做,因为很浪费CPU和临时表空间(这其实是后面要说的),我们一般需要强制指定查询的方式来控制他走嵌套循环,让大表根据小表去走索引,使用ORACLEHint来强制告诉它应该由小表引导大表执行,来保证SQL执行计划的稳定性:

String sql = “SELECT /*+ordered use_nl(a2,a1)*/a2.* FROM A a1,(SELECT COLUMN_VALUE FROM TABLE(SPLIT(?,',')) a2 WHERE a1.ID = A2.COLUMN_VALUE”;

 

此时可能会问,这样转一次会不会很慢,是的,这不难会想想一个拆开字符串的过程,我们必然会消耗一点,不过要想到一次执行就是拆开一个字符串而已,而且我们传入的字符串也不会太长,这个解析过程对于ORACLE来说还是没啥问题的,不必担心这个,而更加重要的提高了共享池的应用。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: