1. 首页
  2. IT资讯

[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,并且选择好的执行计划.  

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1066674/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/182942.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code