[20181105]再论12c set feedback only.txt

来源：这里教程网时间：2026-03-03 12:11:10 作者：

[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的特性. 也说明一点,做事情不能评感觉,还是以测试为准,当然理论的理解也很重要.