开发成功的Oracle应用_Oracle的硬解析与软解析2

五、编码硬解析的改进方法

1、更改参数cursor_sharing

参数cursor_sharing决定了何种类型的SQL能够使用相同的SQL area

CURSOR_SHARING={ SIMILAR | EXACT | FORCE }

  EXACT --只有当发布的SQL语句与缓存中的语句完全相同时才用已有的执行计划

  FORCE --如果SQL语句是字面量，则迫使Optimizer始终使用已有的执行计划，无论已有的执行计划是不是最佳的

  SIMILAR --如果SQL语句是字面量，则只有当已有的执行计划是最佳时才使用它，如果已有执行计划不是最佳则重

 

新对这个SQL语句进行分析来制定最佳执行计划。可以基于不同的级别来设定该参数，如ALTER SESSION，ALTER SYSTEM

sys@ORCL>show parameter cursor_shar

NAME                                 TYPE

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

VALUE

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

cursor_sharing                       string

EXACT

sys@ORCL>alter system set cursor_sharing='similar';

System altered.

 

sys@ORCL>select name,class,value from v$sysstat where statistic#=584;

NAME

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

     CLASS      VALUE

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

parse count (hard)

        64      35077

 

sys@ORCL>show parameter cursor_sharing;

NAME                                 TYPE

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

VALUE

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

cursor_sharing                       string

similar

sys@ORCL>select name,class,value from v$sysstat where statistic#=584;

NAME

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

     CLASS      VALUE

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

parse count (hard)

        64      35077

 

sys@ORCL>select * from scott.dept where deptno=10;

    DEPTNO DNAME                                      LOC

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

      10 ACCOUNTING                                 NEW YORK

 

sys@ORCL>select name,class,value from v$sysstat where statistic#=584;

NAME

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

     CLASS      VALUE

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

parse count (hard)

        64      35078

 

sys@ORCL>select * from scott.dept where deptno=20;

    DEPTNO DNAME                                      LOC

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

      20 RESEARCH                                   DALLAS

 

sys@ORCL>select name,class,value from v$sysstat where statistic#=584;

NAME

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

     CLASS      VALUE

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

parse count (hard)

        64      35078

 

sys@ORCL>select sql_text,child_number from v$sql

  2  where sql_text like 'select * from scott.dept where deptno%';

SQL_TEXT

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

CHILD_NUMBER

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

select * from scott.dept where deptno=:"SYS_B_0"

           0

sys@ORCL>alter system set cursor_sharing='exact';

System altered.

sys@ORCL>show parameter cursor_sharing;

NAME                                 TYPE

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

VALUE

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

cursor_sharing                       string

exact

sys@ORCL>select sql_text,child_number from v$sql

  2  where sql_text like 'select * from scott.dept where deptno%';

SQL_TEXT

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

CHILD_NUMBER

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

select * from scott.dept where deptno=:"SYS_B_0"

           0

select * from scott.dept where deptno=40

           0

 

sys@ORCL>/

SQL_TEXT

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

CHILD_NUMBER

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

select * from scott.dept where deptno=:"SYS_B_0"

           0

select * from scott.dept where deptno=40

           0

select * from scott.dept where deptno=50

           0

 

______________________________________________________________________________________________

Oracle字符串连接的方法

 

和其他数据库系统类似，Oracle字符串连接使用"||"进行字符串拼接，其使用方式和MYSQLServer中的加号"+"一样。

sys@ORCL>desc scott.emp;

 Name                                                  Null?    Type

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

 EMPNO                                                 NOT NULL NUMBER(4)

 ENAME                                                          VARCHAR2(10)

 JOB                                                            VARCHAR2(9)

 MGR                                                            NUMBER(4)

 HIREDATE                                                       DATE

 SAL                                                            NUMBER(7,2)

 COMM                                                           NUMBER(7,2)

 DEPTNO                                                         NUMBER(2)

sys@ORCL>select 'EMPNO IS'||EMPNO||',ENAME IS '||ENAME FROM scott.emp where ename is not null

'EMPNOIS'||EMPNO||',ENAMEIS'||ENAME

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

EMPNO IS7369,ENAME IS SMITH

EMPNO IS7499,ENAME IS ALLEN

EMPNO IS7521,ENAME IS WARD

EMPNO IS7566,ENAME IS JONES

EMPNO IS7654,ENAME IS MARTIN

EMPNO IS7698,ENAME IS BLAKE

EMPNO IS7782,ENAME IS CLARK

EMPNO IS7788,ENAME IS SCOTT

EMPNO IS7839,ENAME IS KING

EMPNO IS7844,ENAME IS TURNER

EMPNO IS7876,ENAME IS ADAMS

EMPNO IS7900,ENAME IS JAMES

EMPNO IS7902,ENAME IS FORD

EMPNO IS7934,ENAME IS MILLER

 

14 rows selected.

除了"||",Oracle还支持使用CONCAT()函数进行字符串拼接，比如

sys@ORCL>select concat('ENAME IS ',ENAME) FROM SCOTT.EMP;

 

CONCAT('ENAMEIS',ENAME)

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

ENAME IS SMITH

ENAME IS ALLEN

ENAME IS WARD

ENAME IS JONES

ENAME IS MARTIN

ENAME IS BLAKE

ENAME IS CLARK

ENAME IS SCOTT

ENAME IS KING

ENAME IS TURNER

ENAME IS ADAMS

ENAME IS JAMES

ENAME IS FORD

ENAME IS MILLER

14 rows selected.

如果CONCAT中连接的值不是字符串，Oracle会尝试将其转换为字符串，与MYSQL的CONCAT()函数不同，Oracle的CONCAT()函数只支持两个函数，不支持两个以上字符串的拼接，比如下面的SQL语句在Oracle中是错误的

sys@ORCL>SELECT CONCAT('EMPNO',EMPNO,'ENAME',ENAME) FROM SCOTT.EMP WHERE ENAME IS NOT NULL;

SELECT CONCAT('EMPNO',EMPNO,'ENAME',ENAME) FROM SCOTT.EMP WHERE ENAME IS NOT NULL

       *

ERROR at line 1:

ORA-00909: invalid number of arguments

 

如果要进行多个字符串的拼接的话，可以使用多个CONCAT()函数嵌套使用

sys@ORCL>SELECT CONCAT(CONCAT(CONCAT('EMPNO IS ',EMPNO),'ENAME IS '),ENAME) FROM SCOTT.EMP WHERE ENAME IS NOT NULL;

CONCAT(CONCAT(CONCAT('EMPNOIS',EMPNO),'ENAMEIS'),ENAME)

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

EMPNO IS 7369ENAME IS SMITH

EMPNO IS 7499ENAME IS ALLEN

EMPNO IS 7521ENAME IS WARD

EMPNO IS 7566ENAME IS JONES

EMPNO IS 7654ENAME IS MARTIN

EMPNO IS 7698ENAME IS BLAKE

EMPNO IS 7782ENAME IS CLARK

EMPNO IS 7788ENAME IS SCOTT

EMPNO IS 7839ENAME IS KING

EMPNO IS 7844ENAME IS TURNER

EMPNO IS 7876ENAME IS ADAMS

EMPNO IS 7900ENAME IS JAMES

EMPNO IS 7902ENAME IS FORD

EMPNO IS 7934ENAME IS MILLER

14 rows selected.

 

______________________________________________________________________________________________

参考：

http://blog.csdn.net/robinson_0612/article/details/6195483

Oracle 硬解析与软解析

http://blog.csdn.net/wanghai__/article/details/4778343

ORACLE绑定变量用法总结

http://database.51cto.com/art/201011/232267.htm

Oracle字符串连接的方法

http://www.52ij.com/jishu/5133.html

开发成功的Oracle应用_Oracle 硬解析与软解析1

本文来源 我爱IT技术网 http://www.52ij.com/jishu/5134.html 转载请保留链接。