对sql加gather_plan_statistics提示，可以收集该sql的plan statistics，从而有助于对问题sql进行分析，纠正错误的执行计划，但如果是生产库的sql，往往是无法加提示的，
通过手动的为sql生成sql profile，可以解决此问题：

sys@EBANK>l
1* select /*+ cardinality(b 100000) */ a.object_name,a.object_type from a,b where a.object_id=b.object_id and b.object_id=1000
sys@EBANK>/

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
V_$BUFFER_POOL_STATISTICS      VIEW
V_$BUFFER_POOL_STATISTICS      VIEW
V_$BUFFER_POOL_STATISTICS      VIEW
V_$BUFFER_POOL_STATISTICS      VIEW
V_$BUFFER_POOL_STATISTICS      VIEW
V_$BUFFER_POOL_STATISTICS      VIEW
V_$BUFFER_POOL_STATISTICS      VIEW
V_$BUFFER_POOL_STATISTICS      VIEW

8 rows selected.

sys@EBANK>select sql_text,sql_id,child_number from v$sql where sql_text like 'select /*+ cardinality%';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID CHILD_NUMBER
------------- ------------
select /*+ cardinality(b 100000) */ a.object_name,a.object_type from a,b where a.object_id=b.object_id and b.object_id=1000
8c38nsyg36bs8 0

---获取sql的sql_id后，可以通过create_1_hint_sql_profile.sql这个脚本来对该sql加一个提示gather_plan_statistics

sys@EBANK>@create_1_hint_sql_profile.sql
Enter value for sql_id: 8c38nsyg36bs8
Enter value for profile_name (PROFILE_sqlid_MANUAL): prof_8c38nsyg36bs8
Enter value for category (DEFAULT):
Enter value for force_matching (false): true
old 16: sql_id = '&&sql_id';
new 16: sql_id = '8c38nsyg36bs8';
old 18: select decode('&&profile_name','X0X0X0X0','PROFILE_'||'&&sql_id'||'_MANUAL','&&profile_name')
new 18: select decode('prof_8c38nsyg36bs8','X0X0X0X0','PROFILE_'||'8c38nsyg36bs8'||'_MANUAL','prof_8c38nsyg36bs8')
Enter value for hint: gather_plan_statistics
old 33: profile => sqlprof_attr('&hint'),
new 33: profile => sqlprof_attr('gather_plan_statistics'),
old 34: category => '&&category',
new 34: category => 'DEFAULT',
old 40: force_match => &&force_matching
new 40: force_match => true
Profile prof_8c38nsyg36bs8 created.

PL/SQL procedure successfully completed.

执行sql_profile_hints脚本来查看sql profile的hints：

sys@EBANK>@sql_profile_hints
Enter value for profile_name: prof_8c38nsyg36bs8
old 19:    'and name like (''&&profile_name'') '||
new 19:    'and name like (''prof_8c38nsyg36bs8'') '||
old 38:    'and p.name like (''&&profile_name'')) '||
new 38:    'and p.name like (''prof_8c38nsyg36bs8'')) '||
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
gather_plan_statistics
1 rows selected.

重新执行该sql，确认sql profile 已生效：

sys@EBANK>select /*+ cardinality(b 100000) */ a.object_name,a.object_type from a,b where a.object_id=b.object_id and b.object_id=1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 4182542269

----------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |   800K|    24M|   279   (1)| 00:00:04 |
|* 1 | HASH JOIN         |       |   800K|    24M|   279   (1)| 00:00:04 |
|* 2 |   TABLE ACCESS FULL| A     |     8 |   232 |   276   (0)| 00:00:04 |
|* 3 |   INDEX RANGE SCAN | IDX_B |   100K|   292K|     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("A"."OBJECT_ID"=1000)
   3 - access("B"."OBJECT_ID"=1000)

Note
-----
- SQL profile "prof_8c38nsyg36bs8" used for this statement

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        994 consistent gets
          0 physical reads
          0 redo size
        569 bytes sent via SQL*Net to client
        380 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          8 rows processed

到此，可以检查该sql的plan_statistics:

sys@EBANK>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('8c38nsyg36bs8',0,'ALL IOSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8c38nsyg36bs8, child number 0
-------------------------------------
select /*+ cardinality(b 100000) */ a.object_name,a.object_type from a,b where a.object_id=b.object_id and
b.object_id=1000

Plan hash value: 4182542269

----------------------------------------------------------------------------------------------------------------------
| Id | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN         |       |      1 |    800K|    24M|   279   (1)| 00:00:04 |      8 |00:00:00.01 |     994 |
|* 2 |   TABLE ACCESS FULL| A     |      1 |      8 |   232 |   276   (0)| 00:00:04 |      8 |00:00:00.01 |     991 |
|* 3 |   INDEX RANGE SCAN | IDX_B |      1 |    100K|   292K|     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / A@SEL$1
   3 - SEL$1 / B@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("A"."OBJECT_ID"=1000)
   3 - access("B"."OBJECT_ID"=1000)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "A"."OBJECT_NAME"[VARCHAR2,128], "A"."OBJECT_TYPE"[VARCHAR2,19]
   2 - "A"."OBJECT_NAME"[VARCHAR2,128], "A"."OBJECT_ID"[NUMBER,22], "A"."OBJECT_TYPE"[VARCHAR2,19]
   3 - "B"."OBJECT_ID"[NUMBER,22]

Note
-----
- SQL profile "prof_8c38nsyg36bs8" used for this statement

ITPub博客

用sql profile来获取sql的plan statistics

最新文章