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