[20160121]調式PL SQL.txt

--一上班，看到一個帖子，裡面提到一些技巧，才想起來許多東西視乎在忘記。
--連結：

--我自己重複測試看看。

1.環境：
SCOTT@book> @&r/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

create or replace procedure P is
x int;
begin
--
-- this is my proc, and it has one very poor SQL
--
select count(*)
into   x
from   dba_views;

select count(*)
into   x
from   dba_tables;

select count(*)
into   x
from   dba_objects;

select count(*)
into   x
from   dba_objects, dba_objects;

end;
/

--要以上指令碼正常執行，必須顯式授權select許可權。

SYS@book> GRANT SELECT ON SYS.dba_views TO SCOTT;
Grant succeeded.

SYS@book> GRANT SELECT ON SYS.dba_tables TO SCOTT;
Grant succeeded.

SYS@book> GRANT SELECT ON SYS.dba_objects TO SCOTT;
Grant succeeded.

2.測試：
SCOTT@book> @ &r/spid

SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
90 589 51753 32 216 alter system kill session '90,589' immediate;

SCOTT@book> exec p
--很慢....

--開啟另外的會話。
SCOTT@book> select sid,username, sql_id from   v$session where status = 'ACTIVE' and    last_call_et > 10 and    username is not null;
       SID USERNAME             SQL_ID
---------- -------------------- -------------
        90 SCOTT                ff35fbgz27513

SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00 1650815232 1 0 104 1031 53 SQL*Net message to client WAITED SHORT TIME 3 0

SCOTT@book> select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait,status from v$session where sid=90 ;
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT STATUS
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- --------------- --------
0000000062657100 0000000000000001 00 1650815232 1 0 90 589 68 SQL*Net message from client WAITED KNOWN TIME 97590648 153 ACTIVE

--從這裡看僅僅SQL*Net message from client，實際上後臺執行那條sql語句。具有一定的欺騙性，自己以後要注意。

SCOTT@book> @ &r/sqlid ff35fbgz27513
old   1: select sql_id,to_char(replace(sql_fulltext,chr(13),'')) sqltext from v$sql where sql_id = '&&1' and rownum<=1
new   1: select sql_id,to_char(replace(sql_fulltext,chr(13),'')) sqltext from v$sql where sql_id = 'ff35fbgz27513' and rownum<=1
old   3: select sql_id,to_char(replace(sql_text,chr(13),'')) sqltext from dba_hist_sqltext where sql_id = '&&1' and rownum<=1
new   3: select sql_id,to_char(replace(sql_text,chr(13),'')) sqltext from dba_hist_sqltext where sql_id = 'ff35fbgz27513' and rownum<=1
SQL_ID        SQLTEXT
------------- ----------------------------------------------
ff35fbgz27513 SELECT COUNT(*) FROM DBA_OBJECTS, DBA_OBJECTS

--可以找到這條sql語句。

SCOTT@book> select PROGRAM_ID, PROGRAM_LINE# from v$sql where sql_id = 'ff35fbgz27513';
PROGRAM_ID PROGRAM_LINE#
---------- -------------
89889 19

--透過這裡可以知道在那個儲存過程以及哪一行.

SCOTT@book> select * from dba_objects where object_id=89889;
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ ----------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT P 89889 PROCEDURE 2016-01-21 08:46:24 2016-01-21 08:51:56 2016-01-21:08:51:56 VALID N N N 1

SCOTT@book> select * from dba_source where owner=user and name='P';
OWNER NAME TYPE       LINE TEXT
------ ----- ---------- ---- --------------------------------------------------
SCOTT P     PROCEDURE     1 procedure P is
SCOTT P     PROCEDURE     2   x int;
SCOTT P     PROCEDURE     3 begin
SCOTT P     PROCEDURE     4 --
SCOTT P     PROCEDURE     5 -- this is my proc, and it has one very poor SQL
SCOTT P     PROCEDURE     6 --
SCOTT P     PROCEDURE     7   select count(*)
SCOTT P     PROCEDURE     8   into   x
SCOTT P     PROCEDURE     9   from   dba_views;
SCOTT P     PROCEDURE    10
SCOTT P     PROCEDURE    11   select count(*)
SCOTT P     PROCEDURE    12   into   x
SCOTT P     PROCEDURE    13   from   dba_tables;
SCOTT P     PROCEDURE    14
SCOTT P     PROCEDURE    15   select count(*)
SCOTT P     PROCEDURE    16   into   x
SCOTT P     PROCEDURE    17   from   dba_objects;
SCOTT P     PROCEDURE    18
SCOTT P     PROCEDURE    19   select count(*)
SCOTT P     PROCEDURE    20   into   x
SCOTT P     PROCEDURE    21   from   dba_objects, dba_objects;
SCOTT P     PROCEDURE    22
SCOTT P     PROCEDURE    23 end;
23 rows selected.

--從以上的測試，都能對上。

3.如何最佳化那條sql語句呢？

--當然這種笛卡爾積本來不是最佳化的可能。不過我想起以前好像可以透過no_merge不融合來獲得好的效果。上面的笛卡爾結果太大。效果不明顯。
--換一個人檢視看看。

SCOTT@book> set timing on
SCOTT@book> SELECT COUNT (*) FROM dba_tables a , dba_tables b;
COUNT(*)
----------
8294400
Elapsed: 00:00:26.30

SCOTT@book> SELECT /*+ NO_MERGE(A) NO_MERGE(B) */ COUNT (*) FROM dba_tables a , dba_tables b;
COUNT(*)
----------
8294400
Elapsed: 00:00:00.48

--如果沒有使用別名a，b。

SCOTT@book> SELECT /*+ NO_MERGE(DBA_TABLES_0002@SEL$1) NO_MERGE(DBA_TABLES_0001@SEL$1) */ COUNT (*) FROM dba_tables , dba_tables ;
COUNT(*)
----------
8294400
Elapsed: 00:00:00.68

[20160121]調式PL SQL.txt

相關文章