暗黙的な変換による実行計画エラー
25240 ワード
awrレポートの表示プロセスでは、暗黙的な変換によるインデックスの失効の問題を発見し、記録します.
awrにおける文の実際の消費量は40以上w論理読み
変数を代入するとテストが速くなります
文sqlidによる履歴実行計画の表示
user_sessは明らかに実行計画を間違え、全テーブルスキャンを使用した.
ふと思い出すuser_sessテーブルフィールドorder_No varchar 2は暗黙的な変換のために単一引用符テストを削除すると推測します
開発者を探して卵の引用符を戻して暗黙的な変換を避けて回復します
注意:暗黙型変換numberをvarchar 2に変換すると、インデックスが無効になります.varchar 2がnumberに変換された場合、インデックスは失効しません.
awrにおける文の実際の消費量は40以上w論理読み
変数を代入するとテストが速くなります
SQL> SELECT a.order_prod_amount, b.prod_cost_price FROM user_sess a, PR_DO_T b WHERE a.prod_id = b.prod_id AND a.order_no = '125924325287';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4287492176
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 4 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS | | 1 | 30 | 4 (0)| 00:00:01 | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| user_sess | 1 | 21 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX UNIQUE SCAN | SYS_C008504 | 1 | | 2 (0)| 00:00:01 | | |
| 4 | TABLE ACCESS BY INDEX ROWID | PR_DO_T | 2983 | 26847 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | SYS_C008459 | 1 | | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ORDER_NO"='125924325287')
5 - access("A"."PROD_ID"="B"."PROD_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
2 physical reads
0 redo size
489 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed文sqlidによる履歴実行計画の表示
SQL> SELECT a.order_prod_amount, b.prod_cost_price FROM user_sess a, PR_DO_T b WHERE a.prod_id = b.prod_id AND a.order_no = '871314
SQL> set linesize 200
SQL> select * from table(dbms_xplan.display_cursor('88f4paj5t26xb'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 88f4paj5t26xb, child number 0
-------------------------------------
SELECT a.order_prod_amount,b.prod_cost_price FROM user_sess a, PR_DO_T b
WHERE a.prod_id = b.prod_id AND a.order_no = :"SYS_B_0"
Plan hash value: 4105848458
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 106K(100)| | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 30 | 106K (1)| 00:21:14 | | |
| 2 | PARTITION RANGE ALL | | 1 | 21 | 106K (1)| 00:21:14 | 1 | 53 |
|* 3 | TABLE ACCESS FULL | user_sess | 1 | 21 | 106K (1)| 00:21:14 | 1 | 53 |
| 4 | TABLE ACCESS BY INDEX ROWID| PR_DO_T | 1 | 9 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | SYS_C008459 | 1 | | 0 (0)| | | |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_NUMBER("A"."ORDER_NO")=:SYS_B_0)
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
5 - access("A"."PROD_ID"="B"."PROD_ID")
24 rows selected.user_sessは明らかに実行計画を間違え、全テーブルスキャンを使用した.
ふと思い出すuser_sessテーブルフィールドorder_No varchar 2は暗黙的な変換のために単一引用符テストを削除すると推測します
SQL> SELECT a.order_prod_amount, b.prod_cost_price FROM user_sess a, PR_DO_T b WHERE a.prod_id = b.prod_id AND a.order_no = 125924325287;
Elapsed: 00:00:18.46
Execution Plan
----------------------------------------------------------
Plan hash value: 4105848458
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 106K (1)| 00:21:14 | | |
| 1 | NESTED LOOPS | | 1 | 30 | 106K (1)| 00:21:14 | | |
| 2 | PARTITION RANGE ALL | | 1 | 21 | 106K (1)| 00:21:14 | 1 | 53 |
|* 3 | TABLE ACCESS FULL | user_sess | 1 | 21 | 106K (1)| 00:21:14 | 1 | 53 |
| 4 | TABLE ACCESS BY INDEX ROWID| PR_DO_T | 1 | 9 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | SYS_C008459 | 1 | | 0 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_NUMBER("A"."ORDER_NO")=125924325287)
5 - access("A"."PROD_ID"="B"."PROD_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
484974 consistent gets
477021 physical reads
0 redo size
489 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed開発者を探して卵の引用符を戻して暗黙的な変換を避けて回復します
注意:暗黙型変換numberをvarchar 2に変換すると、インデックスが無効になります.varchar 2がnumberに変換された場合、インデックスは失効しません.