如何验证SQL PROFILE的性能?(2)

　　Physical Read Requests: 0 0

　　Physical Write Requests: 0 0

　　Physical Read Bytes: 0 0

　　Physical Write Bytes: 0 0

　　Rows Processed: 1 1

　　Fetches: 1 1

　　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: 663678050

　　----------------------------------------------------------------------------------

　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

　　----------------------------------------------------------------------------------

　　| 0 | SELECT STATEMENT | | 1 | 113 | 408 (1)| 00:00:01 |

　　|* 1 | TABLE ACCESS FULL| PROFILE_TEST | 1 | 113 | 408 (1)| 00:00:01 |

　　----------------------------------------------------------------------------------

　　Predicate Information (identified by operation id):

　　---------------------------------------------------

　　1 - filter("OBJECT_ID"=5060)

　　2- Using SQL Profile

　　--------------------

　　Plan hash value: 2974300728

　　----------------------------------------------------------------------------------------------------

　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

　　----------------------------------------------------------------------------------------------------

　　| 0 | SELECT STATEMENT | | 1 | 113 | 2 (0)| 00:00:01 |

　　| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PROFILE_TEST | 1 | 113 | 2 (0)| 00:00:01 |

　　|* 2 | INDEX RANGE SCAN | IX_OBJD | 1 | | 1 (0)| 00:00:01 |

　　----------------------------------------------------------------------------------------------------

　　Predicate Information (identified by operation id):

　　---------------------------------------------------

　　2 - access("OBJECT_ID"=5060)

　　-------------------------------------------------------------------------------

　　execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_226',task_owner => 'SYS', replace => TRUE,category=>'MACLEAN_TEST');

　　SQL> set autotrace on;

　　SQL> select /*+ FULL( profile_test) */ * from profile_test where

　　2 object_id=5060;

　　1 row selected.

　　Execution Plan

　　----------------------------------------------------------

　　Plan hash value: 2974300728

　　----------------------------------------------------------------------------------------------------

　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

　　----------------------------------------------------------------------------------------------------

　　| 0 | SELECT STATEMENT | | 1 | 113 | 2 (0)| 00:00:01 |

　　| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PROFILE_TEST | 1 | 113 | 2 (0)| 00:00:01 |

　　|* 2 | INDEX RANGE SCAN | IX_OBJD | 1 | | 1 (0)| 00:00:01 |

　　----------------------------------------------------------------------------------------------------

　　Predicate Information (identified by operation id):

　　---------------------------------------------------

　　2 - access("OBJECT_ID"=5060)

　　Note

　　-----

　　- SQL profile "SYS_SQLPROF_013b5177cf260000" used for this statement

　　Statistics

　　----------------------------------------------------------

　　275 recursive calls

　　0 db block gets

　　130 consistent gets

　　1 physical reads

　　0 redo size

　　1783 bytes sent via SQL*Net to client

　　543 bytes received via SQL*Net from client

　　2 SQL*Net roundtrips to/from client

　　27 sorts (memory)

　　0 sorts (disk)

　　1 rows processed

　　SQL>

　　SQL> alter session set sqltune_category=DEFAULT;

　　Session altered.

　　SQL> select /*+ FULL( profile_test) */ * from profile_test where object_id=5060;

　　1 row selected.

　　Execution Plan

　　----------------------------------------------------------

　　Plan hash value: 663678050

　　----------------------------------------------------------------------------------

原文转自：http://blogread.cn/it/article/6041