[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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

連線到:

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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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 ;

--//透過查詢執行計劃,確定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 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的特性.

也說明一點,做事情不能評感覺,還是以測試為準,當然理論的理解也很重要.

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

相關文章