sql profile使用说明

sql profile实验：
create table binbin (id number);
insert into binbin values('1');
commit;
SQL>  begin
  2  for i in 1..1000
  3  loop
  4  insert into binbin values('2');
  5  end loop
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select count(1) from binbin;

  COUNT(1)
----------
      1001

create index i_binbin on binbin(id);
select * from binbin where id=1;
SQL> EXEC DBMS_STATS.gather_table_stats(user,'binbin',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> set autot traceonly
SQL> select * from binbin where id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1220076337

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_BINBIN |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("ID"=1)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         11  consistent gets
          1  physical reads
          0  redo size
        509  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

select * from binbin where id=2;
1000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3911455480

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |  1000 | 13000 |     2   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| I_BINBIN |  1000 | 13000 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter("ID"=2)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         80  consistent gets
          1  physical reads
          0  redo size
      17629  bytes sent via SQL*Net to client
       1218  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed
使用hint实现全表扫描
SQL>     select /*+ FULL(binbin)*/ * from binbin where id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3353578345

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     3 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BINBIN |     1 |     3 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("ID"=1)

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

SQL> SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(binbin)*/%'; －－获取sql_id

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2ngjyqw7r531f
    select /*+ FULL(binbin)*/ * from binbin where id=1

f9zbvphj3u2r5
select /*+ FULL(binbin)*/ * from binbin where id=1

g0wpr4gx7kznb
EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR     select /*+ FULL(binbin)*/ * from binbin where id=1

5u7cbg1pv7wm7
SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(binbin)*/%'
获得对应Outline

SQL> select * from table(dbms_xplan.display_cursor('2ngjyqw7r531f',null,'outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2ngjyqw7r531f, child number 0
-------------------------------------
    select /*+ FULL(binbin)*/ * from binbin where id=1

Plan hash value: 3353578345

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| BINBIN |     1 |     3 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "BINBIN"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - filter("ID"=1)

31 rows selected.

创建sql profile：
declare
v_hints sys.sqlprof_attr;
begin
v_hints:=sys.sqlprof_attr(
'BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE(''10.2.0.4'')',
'DB_VERSION(''10.2.0.4'')',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'FULL(@"SEL$1" "BINBIN"@"SEL$1")',--这个是由于hint产生,需要的就是这个
'END_OUTLINE_DATA');
dbms_sqltune.import_sql_profile(
'select * from binbin where id=1',
v_hints,'SQLPROFILE_binbin',--sql profile 名称
force_match=>true,replace=>true);
end;
/
PL/SQL procedure successfully completed.
验证sql profile
SQL> set autot traceonly
SQL> select * from binbin where id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3353578345

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     3 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BINBIN |     1 |     3 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("ID"=1)

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

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        509  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
select * from binbin where id=2;
SQL> select * from binbin where id=2;

1000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3353578345

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |  1000 |  3000 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BINBIN |  1000 |  3000 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("ID"=2)

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

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         71  consistent gets
          0  physical reads
          0  redo size
      17629  bytes sent via SQL*Net to client
       1218  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed
 删除sql profile
exec dbms_sqltune.drop_sql_profile(name =>’SQLPROFILE_binbin’ );