ITPub博客

首页 > 数据库 > Oracle > [20181105]再论12c set feedback only.txt

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

原创 Oracle 作者:lfree 时间:2018-11-05 11:07:34 0 删除 编辑

[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的特性.

  也说明一点,做事情不能评感觉,还是以测试为准,当然理论的理解也很重要.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2218706/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2852
  • 访问量
    6641573