欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-14 13:00:46
[20181105]再论12c set feedback only.txt--//前一阵子的测试,链接:http://blog.itpub.net/267265/viewspace-2216290/--//12CR2 ,增强了set FEEDBACK功能,加入only选项可以禁止输出信息显示,仅仅显 ......

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