【sql調優】繫結變數與CBO
SQL> var x1 number;
SQL> exec :x1 :=1;
PL/SQL 過程已成功完成。
SQL> set autot on
SQL> set autot traceonly
SQL> select count(*) from t where object_id =:x1 or :x1=0;
執行計劃
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 278 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T | 761 | 2283 | 278 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER(:X1)=0 OR "OBJECT_ID"=TO_NUMBER(:X1))
統計資訊
----------------------------------------------------------
223 recursive calls
0 db block gets
1050 consistent gets
1014 physical reads
0 redo size
418 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(object_id) from t where object_id =:x1 or :x1=0;
執行計劃
----------------------------------------------------------
Plan hash value: 3028837625
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 108 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FAST FULL SCAN| I_ID | 761 | 2283 | 108 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER(:X1)=0 OR "OBJECT_ID"=TO_NUMBER(:X1))
統計資訊
----------------------------------------------------------
99 recursive calls
0 db block gets
406 consistent gets
389 physical reads
0 redo size
1 rows processed
SQL> select object_id from t where object_id =:x1 or :x1=0;
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 761 | 2283 | 278 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 761 | 2283 | 278 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:X1)=0 OR "OBJECT_ID"=TO_NUMBER(:X1))
SQL> select object_id from t where object_id =:x1 and :x1=0;
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 845274062
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73 | 219 | 1 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | INDEX RANGE SCAN| I_ID | 73 | 219 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:X1)=0)
2 - access("OBJECT_ID"=TO_NUMBER(:X1))
SQL> select object_id from t where object_id =:x1
2 union
3 select object_id from t where 0 =:x1;
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 2897536294
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68940 | 201K| | 467 (100)| 00:00:06 |
| 1 | SORT UNIQUE | | 68940 | 201K| 1640K| 467 (100)| 00:00:06 |
| 2 | UNION-ALL | | | | | | |
|* 3 | INDEX RANGE SCAN | I_ID | 73 | 219 | | 1 (0)| 00:00:01 |
|* 4 | FILTER | | | | | | |
| 5 | TABLE ACCESS FULL| T | 68867 | 201K| | 277 (1)| 00:00:04 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=TO_NUMBER(:X1))
4 - filter(TO_NUMBER(:X1)=0)
SQL> set linesize 999
SQL> select object_id from t where object_id =:x1
2 union
3 select object_id from t where 0 =:x1;
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 2897536294
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68940 | 201K| | 467 (100)| 00:00:06 |
| 1 | SORT UNIQUE | | 68940 | 201K| 1640K| 467 (100)| 00:00:06 |
| 2 | UNION-ALL | | | | | | |
|* 3 | INDEX RANGE SCAN | I_ID | 73 | 219 | | 1 (0)| 00:00:01 |
|* 4 | FILTER | | | | | | |
| 5 | TABLE ACCESS FULL| T | 68867 | 201K| | 277 (1)| 00:00:04 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=TO_NUMBER(:X1))
4 - filter(TO_NUMBER(:X1)=0)
SQL> set linesize 130
SQL> select object_id from t where object_id =:x1
2 union
3 select object_id from t where 0 =:x1;
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 2897536294
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68940 | 201K| | 467 (100)| 00:00:06 |
| 1 | SORT UNIQUE | | 68940 | 201K| 1640K| 467 (100)| 00:00:06 |
| 2 | UNION-ALL | | | | | | |
|* 3 | INDEX RANGE SCAN | I_ID | 73 | 219 | | 1 (0)| 00:00:01 |
|* 4 | FILTER | | | | | | |
| 5 | TABLE ACCESS FULL| T | 68867 | 201K| | 277 (1)| 00:00:04 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=TO_NUMBER(:X1))
4 - filter(TO_NUMBER(:X1)=0)
SQL> var x2 number;
SQL> exec :x2 :=4;
PL/SQL 過程已成功完成。
SQL> select object_id from t where object_id= :x1 or object_id =:x2;
執行計劃
----------------------------------------------------------
Plan hash value: 547095368
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 146 | 438 | 3 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
|* 2 | INDEX RANGE SCAN| I_ID | 146 | 438 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:X1) OR "OBJECT_ID"=TO_NUMBER(:X2))
SQL> select object_id from t where object_id= :x1 or 3 =:x2;
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 761 | 2283 | 278 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 761 | 2283 | 278 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:X2)=3 OR "OBJECT_ID"=TO_NUMBER(:X1))
SQL> select object_id from t where object_id= :x1 or :x2=4;
已選擇68867行。
執行計劃
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 761 | 2283 | 278 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 761 | 2283 | 278 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:X2)=4 OR "OBJECT_ID"=TO_NUMBER(:X1))
SQL> select object_id from t where object_id= :x1 or :x1=0;
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 761 | 2283 | 278 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 761 | 2283 | 278 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:X1)=0 OR "OBJECT_ID"=TO_NUMBER(:X1)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-673846/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【SQL 調優】繫結變數窺測SQL變數
- 【sql調優】使用繫結變數（二）SQL變數
- 【sql調優】使用繫結變數（一）SQL變數
- SQL Server動態SQL，繫結變數SQLServer變數
- 【效能優化】查詢繫結變數的sql語句優化變數SQL
- 【優化】使用繫結變數 OR 不使用繫結變數，這不是問題！優化變數
- SQL Developer中使用繫結變數SQLDeveloper變數
- 繫結變數及其優缺點變數
- 關於繫結變數的SQL繫結什麼值變數SQL
- Oracle 變數繫結與變數窺視合集Oracle變數
- 檢視未繫結變數的sql變數SQL
- 獲取sql繫結變數的值SQL變數
- 統計沒有繫結變數SQL變數SQL
- ORACLE優化實戰（繫結變數）Oracle優化變數
- 繫結變數變數
- 關於pl/sql中的繫結變數SQL變數
- [SQL]oracle 繫結變數(bind variable)[轉載]SQLOracle變數
- SQL使用繫結變數，測試例項。SQL變數
- 繫結變數優缺點、使用、繫結變數窺探、 Oracle自適應共享遊標變數Oracle
- 【調優】CBO基礎
- 【原創】Oracle 變數繫結與變數窺視合集Oracle變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- 繫結變數，組合查詢方式，導致CBO錯誤一例變數
- 繫結變數在靜態sql和動態sql中變數SQL
- Oracle 繫結變數Oracle變數
- 關於sql_profile中的繫結變數SQL變數
- 統計未用繫結變數的sql語句變數SQL
- oracle 查詢未使用繫結變數的sqlOracle變數SQL
- v$sql_bind_capture 獲取繫結變數SQLAPT變數
- v$sql_bind_capture與timestamp型別的繫結變數的數值SQLAPT型別變數
- 【調優】CBO基礎（八）
- 【調優】CBO基礎（六）
- 【調優】CBO基礎（五）
- 【調優】CBO基礎（四）
- 【調優】CBO基礎（三）
- 【調優】CBO基礎（二）
- 從不繫結變數與繫結變數兩種情況討論柱狀圖的作用變數
- SQL*Plus中使用DATE型別的繫結變數SQL型別變數