[20211210]優化遇到的奇怪問題.txt

[20211210]優化遇到的奇怪問題.txt

--//上午優化一條sql語句,遇到一些問題浪費許多時間,直到上午快下班才定位解決,在測試環境做一個小演示,避免在這些細節上犯錯.

1.環境:
SCOTT@book> @ 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

2.建立測試:
$ cat t1.txt
alter session set statistics_level = all;
set term off
select * from dept where REGEXP_LIKE(dname, '00', '00');
set term on
@ dpc '' ''

--//簡單說明:我們生產系統設定cursor_sharing=force,這樣裡面的常量變成:"SYS_B_03", :"SYS_B_04"之類的.如果寫在where裡面的,
--//可以檢視v$sql_bind_capture檢視獲得.而在select裡面的值無法抓取，我一般選擇'00'字元.
--//我的測試REGEXP_LIKE出現在where中,而我除錯的sql語句REGEXP_LIKE出現在select 裡面.

SCOTT@book> @ t1.txt
Session altered.
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fy92msh4xvx6u, child number 0
-------------------------------------
select * from dept where REGEXP_LIKE(dname, '00', '00')
Plan hash value: 3383998547
----------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      0 |00:00:00.01 |
|*  1 |  TABLE ACCESS FULL| DEPT |      1 |      1 |    20 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |
----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( REGEXP_LIKE ("DNAME",'00','00',<not feasible>)
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--//可以發現實際上居然錯誤的執行語句,可以看到執行計劃.由於語句異常中止,我自己測試執行的sql語句非常快返回,而生產系統的語
--//句執行緩慢,非常不好理解.

--//我開始註解一些where條件,最後發現問題在REGEXP_LIKE(dname, '00', '00'),我猜測第3個引數應該是i.

select * from dept where REGEXP_LIKE(dname, '00', 'i');

SCOTT@book> @ t1.txt
Session altered.
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2qwr8pxwdp0qu, child number 0
-------------------------------------
select * from dept where REGEXP_LIKE(dname, '00', 'i')
Plan hash value: 3383998547
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      0 |00:00:00.01 |       6 |
|*  1 |  TABLE ACCESS FULL| DEPT |      1 |      1 |    20 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       6 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( REGEXP_LIKE ("DNAME",'00','i',HEXTORAW('50B1FE7B0000000004D90402000000000000000000000000C883D
              A060000000000000000000000000000000000000000120000000000000080B1FE7B0000000002000000000000000000000085000000'
              ) ))
--//oracle過濾條件很奇特，以前沒注意還有第4個引數.

--//再次測試,這樣才能正常還原生產系統遇到的情況.
--//實際上我當時還犯了幾個錯誤,當時註解set term off,執行是沒有仔細看前面的提示,實際上執行語句已經報錯.我自己沒有想到這樣也
--//能看到執行計劃.實際上如果當時執行set echo on,也許就很注意到問題在那裡了.

--//浪費差不多一個小時,做一個記錄,避免以後在這些細節上犯錯.
--//如果當時我開啟set echo on，關閉set term off，這個錯誤很快能發現：

SCOTT@book> @ t1.txt
SCOTT@book> alter session set statistics_level = all;
Session altered.

SCOTT@book> --set term off
SCOTT@book> select * from dept where REGEXP_LIKE(dname, '00', '00');
select * from dept where REGEXP_LIKE(dname, '00', '00')
                                            *
ERROR at line 1:
ORA-01760: illegal argument for function

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fy92msh4xvx6u, child number 0
-------------------------------------
select * from dept where REGEXP_LIKE(dname, '00', '00')
Plan hash value: 3383998547
----------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      0 |00:00:00.01 |
|*  1 |  TABLE ACCESS FULL| DEPT |      1 |      1 |    20 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |
----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( REGEXP_LIKE ("DNAME",'00','00',<not feasible>)

--//主要問題在於我沒想到oracle sql語句執行報錯，也能生成執行計劃。