[20181105]再论12c set feedback only.txt
–//前一阵子的测试,链接:http://blog.itpub.net/267265/viewspace-2216290/
–//12cr2 ,增强了set feedback功能,加入only选项可以禁止输出信息显示,仅仅显示返回几行,有利于dba集中精力调试
–//sql语句,18c还增加返回执行语句的sql_id的功能,简单测试.
–//我一直以为这种功能是针对特定版本的比如仅仅对12c有效,实际上如果你使用12c的客户端连接11g,执行set feedback only应该不支持.
–//通过测试说明问题.
1.环境:
d:\temp>sqlplus scott/book@78
sql*plus: release 12.2.0.1.0 production on 星期一 11月 5 10:47:35 2018
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
copyright (c) 1982, 2016, oracle. all rights reserved.
连接到:
oracle database 11g enterprise edition release 11.2.0.4.0 – 64bit production
with the partitioning, olap, data mining and real application testing options
scott@78> @ ver1
port_string version banner
—————————— ————– ——————————————————————————–
x86_64/linux 2.4.xx 11.2.0.4.0 oracle database 11g enterprise edition release 11.2.0.4.0 – 64bit production
–//我刚刚在办法环境重新安装12cr2版本.使用sqlplus连接11g.
scott@78> set feedback only
scott@78> select * from emp;
empno ename job mgr hiredate sal comm deptno
———- ———- ——— ———- ——————- ———- ———- ———-
已选择 14 行。
–//没有多余的显示.
scott@78> set feedback 6
scott@78> @ dpc ” ”
plan_table_output
————————————-
sql_id a2dk8bdn0ujx7, child number 0
————————————-
select * from emp
plan hash value: 3956160932
—————————————————————————
| id | operation | name | e-rows |e-bytes| cost (%cpu)| e-time |
—————————————————————————
| 0 | select statement | | | | 3 (100)| |
| 1 | table access full| emp | 14 | 532 | 3 (0)| 00:00:01 |
—————————————————————————
query block name / object alias (identified by operation id):
————————————————————-
1 – sel$1 / emp@sel$1
2.补充测试连接的http://blog.itpub.net/267265/viewspace-2216326/特性是否有效:
scott@78> set history 10
scott@78> select sysdate from dual ;
sysdate
——————-
2018-11-05 10:51:00
scott@78> select user from dual ;
user
——————————
scott
scott@78> history
1 select sysdate from dual ;
2 select user from dual ;
–//可以发现一样有效.
scott@78> variable b number = 20;
scott@78> select * from dept where deptno = :b ;
deptno dname loc
———- ————– ————-
20 research dallas
–//这样定义变量也没有问题.
3.最后测试statement caching:
–//statement caching有点像软软解析.
scott@78> @ spid
sid serial# process server spid pid p_serial# c50
———- ———- ———————— ——— —— ——- ———- ———————————————-
41 7 5512:516 dedicated 62921 27 3 alter system kill session ‘41,7’ immediate;
variable b number = 20;
select * from dept where deptno = :b ;
select * from dept where deptno = :b ;
–//通过查询执行计划,确定sql_id=2b073tss4h1f3.
scott@78> select sql_id,executions,parse_calls,sql_text from v$sql where sql_id=’2b073tss4h1f3′;
sql_id executions parse_calls sql_text
————- ———- ———– ————————————————————
2b073tss4h1f3 2 2 select * from dept where deptno = :b
–//执行2次,分析2次.
–//如果采用statement caching
–//sesson 1:
set statementcache 100
variable c number = 10;
select * from dept where deptno = :c ;
select * from dept where deptno = :c ;
select sysdate from dual;
–//通过查询执行计划,确定sql_id=’abzxwsyzmsu8h’,另外打开会话:
–//sesson 2:
sys@book> select sql_id,executions,parse_calls,sql_text from v$sql where sql_id=’abzxwsyzmsu8h’;
sql_id executions parse_calls sql_text
————- ———- ———– ————————————————————
abzxwsyzmsu8h 2 1 select * from dept where deptno = :c
–//执行2次,分析1次.
sys@book> @ sharepool/shp4 abzxwsyzmsu8h 0
text kglhdadr kglhdpar c40 kglhdlmd kglhdpmd kglhdivc kglobhd0 kglobhd6 kglobhs0 kglobhs6 kglobt16 n0_6_16 n20 kglnahsh kglobt03 kglobt09
————– —————- —————- —————————————- ———- ———- ———- —————- —————- ———- ———- ———- ——— ———- ———- ————- ———-
子游标句柄地址 000000007e344808 000000007e0f7020 select * from dept where deptno = :c 1 0 0 000000007e373578 000000007c5d06c8 8600 12144 3086 23830 23830 3208407312 abzxwsyzmsu8h 0
父游标句柄地址 000000007e0f7020 000000007e0f7020 select * from dept where deptno = :c 1 0 0 000000007e33c8a8 00 4720 0 0 4720 4720 3208407312 abzxwsyzmsu8h 65535
–//kglhdlmd=1.session 1最后执行的是select sysdate from dual;
sys@book> select * from v$open_cursor where sql_id=’abzxwsyzmsu8h’;
saddr sid user_name address hash_value sql_id sql_text last_sql_active_tim sql_exec_id cursor_type
—————- — ——— —————- ———- ————- ———————————— ——————- ———– ———–
000000008631ddf0 41 scott 000000007e0f7020 3208407312 abzxwsyzmsu8h select * from dept where deptno = :c open
–//可以发现光标状态已经是open,仅仅执行2次.
–//session 1:
scott@78> variable c number = 30;
scott@78> select * from dept where deptno = :c ;
deptno dname loc
———- ————– ————-
30 sales chicago
scott@book> select sql_id,executions,parse_calls,sql_text from v$sql where sql_id=’abzxwsyzmsu8h’;
sql_id executions parse_calls sql_text
————- ———- ———– ————————————————————
abzxwsyzmsu8h 3 1 select * from dept where deptno = :c
–//执行3次,分析1次.
4.支持csv数据格式显示:
scott@78> set markup csv on
scott@78> select * from emp ;
“empno”,”ename”,”job”,”mgr”,”hiredate”,”sal”,”comm”,”deptno”
7369,”smith”,”clerk”,7902,”1980-12-17 00:00:00″,800,,20
7499,”allen”,”salesman”,7698,”1981-02-20 00:00:00″,1600,300,30
7521,”ward”,”salesman”,7698,”1981-02-22 00:00:00″,1250,500,30
7566,”jones”,”manager”,7839,”1981-04-02 00:00:00″,2975,,20
7654,”martin”,”salesman”,7698,”1981-09-28 00:00:00″,1250,1400,30
7698,”blake”,”manager”,7839,”1981-05-01 00:00:00″,2850,,30
7782,”clark”,”manager”,7839,”1981-06-09 00:00:00″,2450,,10
7788,”scott”,”analyst”,7566,”1987-04-19 00:00:00″,3000,,20
7839,”king”,”president”,,”1981-11-17 00:00:00″,5000,,10
7844,”turner”,”salesman”,7698,”1981-09-08 00:00:00″,1500,0,30
7876,”adams”,”clerk”,7788,”1987-05-23 00:00:00″,1100,,20
7900,”james”,”clerk”,7698,”1981-12-03 00:00:00″,950,,30
7902,”ford”,”analyst”,7566,”1981-12-03 00:00:00″,3000,,20
7934,”miller”,”clerk”,7782,”1982-01-23 00:00:00″,1300,,10
已选择 14 行。
–//ok.剩下的不在测试了,剩下大家自行测试.
set markup csv on delimiter ‘|’
select * from dept;
set markup csv on delimiter ‘|’ quote off
select * from dept;
set markup csv off
select * from dept;
5.看来自己以前感觉错误,sqlplus的特性与oracle版本无关,你只要安装12cr2版本的client端,就可以使用新版本的sqlplus的特性.
也说明一点,做事情不能评感觉,还是以测试为准,当然理论的理解也很重要.