sql_profile的使用(一)
今天看了老熊關於sql_profile的講解，受益匪淺，自己在本機也做了一通,感覺好記性不如爛筆頭還是得多總結總測試才能真正理解。
準備的資料如下，建立兩個表，一個大，一個小，然後做表分析
SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000;
Table created.
SQL> create table t2 as select * from dba_objects;
Table created.
SQL> create index t2_idx on t2(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
不加任何hint,檢視執行計劃,可以看到兩個表都走了全表掃描。
SQL> set autot trace exp stat
SQL> set linesize 200
SQL> set pages 100
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
26 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 112K| 122 |
|* 1 | HASH JOIN | | 2500 | 112K| 122 |
|* 2 | TABLE ACCESS FULL| T1 | 2500 | 77500 | 8 |
| 3 | TABLE ACCESS FULL| T2 | 269K| 3952K| 107 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4477 consistent gets
0 physical reads
0 redo size
1669 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26 rows processed
如果手動調優，加入Hint，可以參考如下的形式，可以看到效能有了成倍的提升。
SQL> select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
and t1.object_id=t2.object_id; 4
26 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 112K| 258 |
| 1 | NESTED LOOPS | | | | |
| 2 | NESTED LOOPS | | 2500 | 112K| 258 |
|* 3 | TABLE ACCESS FULL | T1 | 2500 | 77500 | 8 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 1 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
323 consistent gets
0 physical reads
0 redo size
1669 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26 rows processed
下面來根據sql_id來進行調優，試試sql_profile給出的見解。先從快取中查出剛才執行的sql語句。
SQL> select sql_id,sql_text from v$sql where sql_text like '%t1.object_name%'
2 /
SQL_ID
。。。。
4zbqykx89yc8v
select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
2pxr40u2zm0ja
select sql_id,sql_text from v$sql where sql_text like '%t1.object_name%'
然後執行下面的儲存過程，執行sqltune task.
SQL> var tuning_task varchar2(100);
SQL> DECLARE
l_sql_id v$session.prev_sql_id%TYPE;
l_tuning_task VARCHAR2(30);
BEGIN
l_sql_id:='4zbqykx89yc8v';
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
:tuning_task:=l_tuning_task;
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);
END;
/
PL/SQL procedure successfully completed.
檢視task的name
SQL> print tuning_task;
TUNING_TASK
--------------------------------------------------------------------------------------------------------------------------------
TASK_12352
如果sql語句本身不復雜，涉及的表不大的話，執行是很快的。如下檢視報告。
SQL> set long 99999
SQL> col comments format a200
SQL> SELECT dbms_sqltune.report_tuning_task(:tuning_task)COMMENTS FROM dual;
COMMENTS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_12352
Tuning Task Owner : N1
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 07/10/2014 15:04:18
Completed at : 07/10/2014 15:04:20
Schema Name: N1
SQL ID : 4zbqykx89yc8v
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
and t1.object_id=t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 92.9%)
-----------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_12352',task_owner => 'N1', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .042304 .006329 85.03 %
CPU Time (s): .042293 .006399 84.86 %
User I/O Time (s): 0 0
Buffer Gets: 4475 317 92.91 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 26 26
Fetches: 26 26
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 1196 | 796 (1)| 00:00:10 |
|* 1 | HASH JOIN | | 26 | 1196 | 796 (1)| 00:00:10 |
|* 2 | TABLE ACCESS FULL| T1 | 26 | 806 | 47 (3)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 269K| 3952K| 748 (1)| 00:00:09 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
2- Using SQL Profile
--------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 1196 | 49 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 26 | 1196 | 49 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 26 | 806 | 47 (3)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------
可以從報告看出，改進確實是很客觀的，提升了90%以上。
來簡單驗證一下，先得accept 一下。
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_12352',task_owner => 'N1', replace => TRUE);
PL/SQL procedure successfully completed.
再來查詢一下，看看是否啟用了profile
SQL> set autot trace exp stat
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
/
26 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 1196 | 49 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 26 | 1196 | 49 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 26 | 806 | 47 (3)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_01471f52938e0000" used for this statement
準備的資料如下，建立兩個表，一個大，一個小，然後做表分析
SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000;
Table created.
SQL> create table t2 as select * from dba_objects;
Table created.
SQL> create index t2_idx on t2(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
不加任何hint,檢視執行計劃,可以看到兩個表都走了全表掃描。
SQL> set autot trace exp stat
SQL> set linesize 200
SQL> set pages 100
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
26 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 112K| 122 |
|* 1 | HASH JOIN | | 2500 | 112K| 122 |
|* 2 | TABLE ACCESS FULL| T1 | 2500 | 77500 | 8 |
| 3 | TABLE ACCESS FULL| T2 | 269K| 3952K| 107 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4477 consistent gets
0 physical reads
0 redo size
1669 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26 rows processed
如果手動調優，加入Hint，可以參考如下的形式，可以看到效能有了成倍的提升。
SQL> select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner
2 from t1,t2
3 where t1.object_name like '%T1%'
and t1.object_id=t2.object_id; 4
26 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 112K| 258 |
| 1 | NESTED LOOPS | | | | |
| 2 | NESTED LOOPS | | 2500 | 112K| 258 |
|* 3 | TABLE ACCESS FULL | T1 | 2500 | 77500 | 8 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 1 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
323 consistent gets
0 physical reads
0 redo size
1669 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26 rows processed
下面來根據sql_id來進行調優，試試sql_profile給出的見解。先從快取中查出剛才執行的sql語句。
SQL> select sql_id,sql_text from v$sql where sql_text like '%t1.object_name%'
2 /
SQL_ID
。。。。
4zbqykx89yc8v
select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
2pxr40u2zm0ja
select sql_id,sql_text from v$sql where sql_text like '%t1.object_name%'
然後執行下面的儲存過程，執行sqltune task.
SQL> var tuning_task varchar2(100);
SQL> DECLARE
l_sql_id v$session.prev_sql_id%TYPE;
l_tuning_task VARCHAR2(30);
BEGIN
l_sql_id:='4zbqykx89yc8v';
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
:tuning_task:=l_tuning_task;
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);
END;
/
PL/SQL procedure successfully completed.
檢視task的name
SQL> print tuning_task;
TUNING_TASK
--------------------------------------------------------------------------------------------------------------------------------
TASK_12352
如果sql語句本身不復雜，涉及的表不大的話，執行是很快的。如下檢視報告。
SQL> set long 99999
SQL> col comments format a200
SQL> SELECT dbms_sqltune.report_tuning_task(:tuning_task)COMMENTS FROM dual;
COMMENTS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_12352
Tuning Task Owner : N1
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 07/10/2014 15:04:18
Completed at : 07/10/2014 15:04:20
Schema Name: N1
SQL ID : 4zbqykx89yc8v
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
and t1.object_id=t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 92.9%)
-----------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_12352',task_owner => 'N1', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .042304 .006329 85.03 %
CPU Time (s): .042293 .006399 84.86 %
User I/O Time (s): 0 0
Buffer Gets: 4475 317 92.91 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 26 26
Fetches: 26 26
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 1196 | 796 (1)| 00:00:10 |
|* 1 | HASH JOIN | | 26 | 1196 | 796 (1)| 00:00:10 |
|* 2 | TABLE ACCESS FULL| T1 | 26 | 806 | 47 (3)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 269K| 3952K| 748 (1)| 00:00:09 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
2- Using SQL Profile
--------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 1196 | 49 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 26 | 1196 | 49 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 26 | 806 | 47 (3)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------
可以從報告看出，改進確實是很客觀的，提升了90%以上。
來簡單驗證一下，先得accept 一下。
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_12352',task_owner => 'N1', replace => TRUE);
PL/SQL procedure successfully completed.
再來查詢一下，看看是否啟用了profile
SQL> set autot trace exp stat
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
/
26 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 1196 | 49 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 26 | 1196 | 49 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 26 | 806 | 47 (3)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_01471f52938e0000" used for this statement
Statistics
----------------------------------------------------------
34 recursive calls
1 db block gets
338 consistent gets
3 physical reads
196 redo size
1669 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
26 rows processed
再來看看如果改動了sql語句，多加了些空格，看看profile還能不能正常啟用。
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id= t2.object_id
2 /
26 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 1196 | 49 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 26 | 1196 | 49 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 26 | 806 | 47 (3)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_01471f52938e0000" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
323 consistent gets
0 physical reads
0 redo size
1669 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26 rows processed
可以看到，還是正常啟用了。另外，庫裡的cursor_sharing引數如下。
SQL> show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination string memory+disk
cursor_sharing string EXACT
所以在使用中，對於sql調優來說還是可以嘗試使用sql_profile的，確實提供了不少的知識集。----------------------------------------------------------
34 recursive calls
1 db block gets
338 consistent gets
3 physical reads
196 redo size
1669 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
26 rows processed
再來看看如果改動了sql語句，多加了些空格，看看profile還能不能正常啟用。
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id= t2.object_id
2 /
26 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 1196 | 49 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 26 | 1196 | 49 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 26 | 806 | 47 (3)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_01471f52938e0000" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
323 consistent gets
0 physical reads
0 redo size
1669 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26 rows processed
可以看到，還是正常啟用了。另外，庫裡的cursor_sharing引數如下。
SQL> show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination string memory+disk
cursor_sharing string EXACT
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26224278/viewspace-1797421/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於sql_profile中的繫結變數SQL變數
- 效能下降的不定時炸彈_過舊的sql_profileSQL
- DBMS_SQLTUNE詳解(2)---sql_profileSQL
- [20141119]使用指令碼完成sql_profile工作指令碼SQL
- Oracle SQL_Profile手動生成及繫結sql執行計劃OracleSQL
- 自動/手動型別sql_profile(dbms_sqltune/coe_xfr_sql_profile.sql)型別SQL
- synchronized的使用（一）synchronized
- Urllib庫的使用一---基本使用
- glom模組的使用(一)
- ViewDragHelper 的基本使用（一）View
- sql monitor的使用(一)SQL
- In和exists使用及效能分析(一)：in的使用
- git使用之一——git的基本使用Git
- Elasticsearch的PHP的API使用（一）ElasticsearchPHPAPI
- zookeeper的原理和使用（一）
- HttpUnit的基礎使用(一)HTTP
- 一、errno perror strerror的使用Error
- docker 的一些使用Docker
- sqlalchemy在python中的使用(基本使用)一SQLPython
- iOS開發之使用Git的基本使用(一)iOSGit
- 使用一個Oracle MySQL的理念OracleMySql
- clickhouse使用的一點總結
- 反射的概念 和基本使用（一）反射
- pycharm使用的一些技巧PyCharm
- ECMAscript一些方法的使用
- 我的一次RxJava使用。RxJava
- sql trace的使用說明一SQL
- 初學VR（一）：Unity的使用VRUnity
- asynchttpclient 使用的一點疑問HTTPclient
- orion使用的一些理解
- BBED工具的安裝使用（一）
- 使用PACKAGE的一點經驗Package
- 使用者使用git常用的基本命令（一）Git
- 嘗試說一說事件的使用事件
- 使用 Laravel 時的一些小技巧Laravel
- CSS的偽元素使用例子之一CSS
- 使用Immutable js的一點體會JS
- 【Go】slice的一些使用技巧Go