[20140102]ORA-00600 [kkocxj pjpCtx] error is reported when running之补充.txt

[20140102]ORA-00600 [kkocxj  pjpCtx] error is reported when running之补充.txt

去年我写一篇blog,链接如下:
http://blog.itpub.net/267265/viewspace-1065675/

有人问sql profiles如何建立,以前我一直是手工编写命令执行,不是很方面,正好放假看了.
Apress.Pro.Oracle.SQL.2nd.Edition.Nov.2013.pdf

我修改脚本其中的脚本move_sql_profiles.sql,从以下站点下载,实现建立sql profiles的目的.

URL http://www.apress.com/downloadable/download/sample/sample_id/1482/

1.确定sql_id.
SYSTEM> @ver
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

select /*+
      OPT_PARAM('_optimizer_push_pred_cost_based' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$9AACC4F0")
      OUTLINE_LEAF(@"SEL$693A5C0E")
      OUTLINE_LEAF(@"SET$7BE537C4")
      OLD_PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" ("T22"."ID"))
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SET$2")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$1" "V_T1"@"SEL$1")
      NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
      LEADING(@"SEL$1" "V_T1"@"SEL$1" "V_T2"@"SEL$1")
      USE_NL(@"SEL$1" "V_T2"@"SEL$1")
      INDEX_RS_ASC(@"SEL$693A5C0E" "T22"@"SEL$5" ("T22"."ID"))
      INDEX_RS_ASC(@"SEL$9AACC4F0" "T21"@"SEL$4" ("T21"."ID"))
      INDEX_RS_ASC(@"SEL$3" "T12"@"SEL$3" ("T12"."IDX"))
      INDEX_RS_ASC(@"SEL$2" "T11"@"SEL$2" ("T11"."IDX"))
*/
v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=42;

--sql_id= 4qfu642abbt7j,作为good sql 语句的执行计划.取出执行计划提示.

select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=42;
--sql_id=5vzmydwgadm36,作为原始的sql语句.使用上面的执行计划提示建立sql profiles.

--我以前的做法是手工执行dbms_sqltune.import_sql_profile命令,一般执行如下:
begin
dbms_sqltune.import_sql_profile(
   name => 'profile_test1',
   description => 'SQL profile created manually',
-- category => 'TEST',
   sql_text => q'[select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=42]',
   profile => sqlprof_attr(
     q'[OPT_PARAM('_optimizer_push_pred_cost_based' 'false')]',
     q'[ALL_ROWS]',
     q'[OUTLINE_LEAF(@"SEL$2")]',
     q'[OUTLINE_LEAF(@"SEL$3")]',
     q'[OUTLINE_LEAF(@"SET$1")]',
     q'[OUTLINE_LEAF(@"SEL$9AACC4F0")]',
     q'[OUTLINE_LEAF(@"SEL$693A5C0E")]',
     q'[OUTLINE_LEAF(@"SET$7BE537C4")]',
     q'[OLD_PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" ("T22"."ID"))]',
     q'[OUTLINE_LEAF(@"SEL$1")]',
     q'[OUTLINE(@"SEL$4")]',
     q'[OUTLINE(@"SEL$5")]',
     q'[OUTLINE(@"SET$2")]',
     q'[OUTLINE(@"SEL$1")]',
     q'[NO_ACCESS(@"SEL$1" "V_T1"@"SEL$1")]',
     q'[NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")]',
     q'[LEADING(@"SEL$1" "V_T1"@"SEL$1" "V_T2"@"SEL$1")]',
     q'[USE_NL(@"SEL$1" "V_T2"@"SEL$1")]',
     q'[INDEX_RS_ASC(@"SEL$693A5C0E" "T22"@"SEL$5" ("T22"."ID"))]',
     q'[INDEX_RS_ASC(@"SEL$9AACC4F0" "T21"@"SEL$4" ("T21"."ID"))]',
     q'[INDEX_RS_ASC(@"SEL$3" "T12"@"SEL$3" ("T12"."IDX"))]',
     q'[INDEX_RS_ASC(@"SEL$2" "T11"@"SEL$2" ("T11"."IDX"))]'
   ),
   replace => FALSE,
   force_match => TRUE
);
end;
/

--使用q作为引号前导,主要避免里面存在单引号.删除sql profiles执行如下.
-- exec dbms_sqltune.drop_sql_profile('profile_test1');

2.执行cr_sql_profiles.sql:

SYSTEM@his> @r:\cr_sql_profile
Enter good sql statment of value for sql_id1: 4qfu642abbt7j
Enter good sql of value for child_no1 (0):
Enter original sql statment of value for sql_id2: 5vzmydwgadm36
Enter original value for child_no2 (0):
Enter value for profile_name (PROF_sqlid_planhash):
Enter value for category (DEFAULT):
Enter value for force_matching (FALSE): true

--附录脚本如下:
----------------------------------------------------------------------------------------
--
-- File name:   create_sql_profile.sql
--
-- Purpose:     Create SQL Profile based on Outline hints in V$SQL.OTHER_XML.
--
-- Usage:       This scripts prompts for four values.
--
--              sql_id: the sql_id of the statement to attach the profile to (must be in the shared pool)
--
--              child_no: the child_no of the statement from v$sql
--
--              profile_name: the name of the profile to be generated
--
--              category: the name of the category for the profile
--
--              force_macthing: a toggle to turn on or off the force_matching feature
--
-- Description:
--
--              Based on a script by Randolf Giest.
--
---------------------------------------------------------------------------------------
--

set feedback off
set sqlblanklines on

accept sql_id1 -
       prompt 'Enter good sql statment of value for sql_id1: ' -
       default 'X0X0X0X0'
accept child_no1 -
       prompt 'Enter good sql of value for child_no1 (0): ' -
       default '0'
accept sql_id2 -
       prompt 'Enter original sql statment of value for sql_id2: ' -
       default 'X0X0X0X0'
accept child_no2 -
       prompt 'Enter original value for child_no2 (0): ' -
       default '0'
accept profile_name -
       prompt 'Enter value for profile_name (PROF_sqlid_planhash): ' -
       default 'X0X0X0X0'
accept category -
       prompt 'Enter value for category (DEFAULT): ' -
       default 'DEFAULT'
accept force_matching -
       prompt 'Enter value for force_matching (FALSE): ' -
       default 'false'

declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
l_profile_name varchar2(30);
begin
select
extractvalue(value(d), '/hint') as outline_hints
bulk collect
into
ar_profile_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
sql_id = '&&sql_id1'
and child_number = &&child_no1
and other_xml is not null
)
) d;

select
sql_fulltext,
decode('&&profile_name','X0X0X0X0','PROF_&&sql_id2'||'_'||plan_hash_value,'&&profile_name')
into
cl_sql_text, l_profile_name
from
v$sql
where
sql_id = '&&sql_id2'
and child_number = &&child_no2;

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
category => '&&category',
name => l_profile_name,
force_match => &&force_matching
-- replace => true
);

  dbms_output.put_line(' ');
  dbms_output.put_line('SQL Profile '||l_profile_name||' created.');
  dbms_output.put_line(' ');

exception
when NO_DATA_FOUND then
  dbms_output.put_line(' ');
  dbms_output.put_line('ERROR: sql_id: '||'&&sql_id1'||' Child: '||'&&child_no1'||' not found in v$sql.');
  dbms_output.put_line(' ');
end;
/

undef sql_id
undef child_no
undef profile_name
undef category
undef force_matching

set sqlblanklines off
set feedback on
----

3.验证是否正确,我特地修改变量v_t1.idx=43.我前面使用force_matching =true,这样对不同的文字变量有效.

SYSTEM> select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20 from v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=43;
        ID C10        C20
---------- ---------- --------------------
        43 t11aaaaaaa t21ccccccc
        43 t11aaaaaaa t22ddddddd
        43 t12bbbbbbb t21ccccccc
        43 t12bbbbbbb t22ddddddd

4 rows selected.

SYSTEM> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0uf4ntm7x3mbr, child number 0
-------------------------------------
select v_t1.id,substr(v_t1.name,1,10) c10 ,substr(v_t2.name,1,10) c20
from v_t1,v_t2 where v_t1.id=v_t2.id and v_t1.idx=43

Plan hash value: 637972453

--------------------------------------------------------------------------
| Id  | Operation                      | Name      | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------
|   1 |  NESTED LOOPS                  |           |      4 |    10   (0)|
|   2 |   VIEW                         | V_T1      |      2 |     4   (0)|
|   3 |    UNION-ALL                   |           |        |            |
|   4 |     TABLE ACCESS BY INDEX ROWID| T11       |      1 |     2   (0)|
|*  5 |      INDEX RANGE SCAN          | I_T11_IDX |      1 |     1   (0)|
|   6 |     TABLE ACCESS BY INDEX ROWID| T12       |      1 |     2   (0)|
|*  7 |      INDEX RANGE SCAN          | I_T12_IDX |      1 |     1   (0)|
|   8 |   VIEW                         | V_T2      |      2 |     3   (0)|
|   9 |    UNION-ALL PARTITION         |           |        |            |
|  10 |     TABLE ACCESS BY INDEX ROWID| T21       |      1 |     2   (0)|
|* 11 |      INDEX RANGE SCAN          | I_T21_ID  |      1 |     1   (0)|
|  12 |     TABLE ACCESS BY INDEX ROWID| T22       |      1 |     2   (0)|
|* 13 |      INDEX RANGE SCAN          | I_T22_ID  |      1 |     1   (0)|
--------------------------------------------------------------------------

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

   5 - access("IDX"=43)
   7 - access("IDX"=43)
  11 - access("ID"="V_T1"."ID")
  13 - access("ID"="V_T1"."ID")

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

--可以发现使用sql profile,并且选择好的执行计划.