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