实验目的:理解sql语句如何存储在内存中
1.不使用绑定变量的例子
sec@ora10g> select * from emp where empno=7900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
sec@ora10g> select * from emp where empno= 7900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
sec@ora10g> select * from emp where empno= 7900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
sec@ora10g> select * from emp where empno=7902;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
sec@ora10g> select * from emp where empno= 7902;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
2.使用绑定变量的例子
sec@ora10g> var v1 number
sec@ora10g> begin
2 :v1:=7900;
3 end;
4 /
PL/SQL procedure successfully completed.
sec@ora10g> select * from emp where empno=:v1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
sec@ora10g> var v1 number
sec@ora10g> begin
2 :v1:=7902;
3 end;
4 /
PL/SQL procedure successfully completed.
sec@ora10g> select * from emp where empno=:v1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
3.验证是否能够共享到shared_pool中解析过的sql语句
sec@ora10g> select sql_text from v$sqlarea where sql_text like 'select * from emp where %';
SQL_TEXT
-------------------------------------------------------------
select * from emp where empno=7900
select * from emp where empno=:v1
select * from emp where empno= 7902
select * from emp where empno=7902
select * from emp where empno= 7900
select * from emp where empno= 7900
6 rows selected.
4.查看shared_pool剩余大小,若不足需要对共享池进行优化
sec@ora10g> select * from v$sgastat where name = 'free memory' and pool = 'shared pool';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool free memory 15717892
5.共享sql语句的条件
1).内存中有相同的文本串
2).Hash值相同,比较两个语句的文本是否相同
3).对象的owner必须相同,不同账号的同名表不能共享
4).使用绑定变量时,变量名称必须相同
5).运行语句的环境相同,比如优化模式等参数
6.总结
使用绑定变量可以大大的减少sql被反复的解析,提高sql的运行效率。
给开发同学们的建议:
1).尽量使用绑定变量,并且统一绑定变量名字
2).规范sql语句和pl/sql语句的格式
3).规范在sql语句中出现的空格个数
-- The End --
1.不使用绑定变量的例子
sec@ora10g> select * from emp where empno=7900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
sec@ora10g> select * from emp where empno= 7900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
sec@ora10g> select * from emp where empno= 7900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
sec@ora10g> select * from emp where empno=7902;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
sec@ora10g> select * from emp where empno= 7902;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
2.使用绑定变量的例子
sec@ora10g> var v1 number
sec@ora10g> begin
2 :v1:=7900;
3 end;
4 /
PL/SQL procedure successfully completed.
sec@ora10g> select * from emp where empno=:v1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
sec@ora10g> var v1 number
sec@ora10g> begin
2 :v1:=7902;
3 end;
4 /
PL/SQL procedure successfully completed.
sec@ora10g> select * from emp where empno=:v1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
3.验证是否能够共享到shared_pool中解析过的sql语句
sec@ora10g> select sql_text from v$sqlarea where sql_text like 'select * from emp where %';
SQL_TEXT
-------------------------------------------------------------
select * from emp where empno=7900
select * from emp where empno=:v1
select * from emp where empno= 7902
select * from emp where empno=7902
select * from emp where empno= 7900
select * from emp where empno= 7900
6 rows selected.
4.查看shared_pool剩余大小,若不足需要对共享池进行优化
sec@ora10g> select * from v$sgastat where name = 'free memory' and pool = 'shared pool';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool free memory 15717892
5.共享sql语句的条件
1).内存中有相同的文本串
2).Hash值相同,比较两个语句的文本是否相同
3).对象的owner必须相同,不同账号的同名表不能共享
4).使用绑定变量时,变量名称必须相同
5).运行语句的环境相同,比如优化模式等参数
6.总结
使用绑定变量可以大大的减少sql被反复的解析,提高sql的运行效率。
给开发同学们的建议:
1).尽量使用绑定变量,并且统一绑定变量名字
2).规范sql语句和pl/sql语句的格式
3).规范在sql语句中出现的空格个数
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-561359/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-561359/