|
写函数不是目的,主要是为研究sql为什么不能共享,查看v$sql_shared_cursor更方便一些!
函数有些冗长,但是50多段都是重复的意思,一下没想到太好的办法,所以就先这样写了,谁有更好的办法可以再改装一下。
每一小段都是处理v$sql_shared_cursor中的一个mismatch的列,共53列
[php]
create or replace type my_type as object
(
sql_id varchar2(13),
address raw(4),
child_address raw(4),
child_number number,
mismatch_reason_name varchar2(30),
mismatch_reason varchar2(1)
);
--==============================
create or replace type my_type_table is table of my_type;
--==============================
create or replace function fun_table_column_to_row
(p_cursor sys_refcursor)
return my_type_table pipelined
is
type my_rec_type is record(
SQL_ID VARCHAR2(13) ,
ADDRESS RAW(4) ,
CHILD_ADDRESS RAW(4) ,
CHILD_NUMBER NUMBER ,
UNBOUND_CURSOR VARCHAR2(1) ,
SQL_TYPE_MISMATCH VARCHAR2(1) ,
OPTIMIZER_MISMATCH VARCHAR2(1) ,
OUTLINE_MISMATCH VARCHAR2(1) ,
STATS_ROW_MISMATCH VARCHAR2(1) ,
LITERAL_MISMATCH VARCHAR2(1) ,
SEC_DEPTH_MISMATCH VARCHAR2(1) ,
EXPLAIN_PLAN_CURSOR VARCHAR2(1) ,
BUFFERED_DML_MISMATCH VARCHAR2(1) ,
PDML_ENV_MISMATCH VARCHAR2(1) ,
INST_DRTLD_MISMATCH VARCHAR2(1) ,
SLAVE_QC_MISMATCH VARCHAR2(1) ,
TYPECHECK_MISMATCH VARCHAR2(1) ,
AUTH_CHECK_MISMATCH VARCHAR2(1) ,
BIND_MISMATCH VARCHAR2(1) ,
DESCRIBE_MISMATCH VARCHAR2(1) ,
LANGUAGE_MISMATCH VARCHAR2(1) ,
TRANSLATION_MISMATCH VARCHAR2(1) ,
ROW_LEVEL_SEC_MISMATCH VARCHAR2(1) ,
INSUFF_PRIVS VARCHAR2(1) ,
INSUFF_PRIVS_REM VARCHAR2(1) ,
REMOTE_TRANS_MISMATCH VARCHAR2(1) ,
LOGMINER_SESSION_MISMATCH VARCHAR2(1) ,
INCOMP_LTRL_MISMATCH VARCHAR2(1) ,
OVERLAP_TIME_MISMATCH VARCHAR2(1) ,
SQL_REDIRECT_MISMATCH VARCHAR2(1) ,
MV_QUERY_GEN_MISMATCH VARCHAR2(1) ,
USER_BIND_PEEK_MISMATCH VARCHAR2(1) ,
TYPCHK_DEP_MISMATCH VARCHAR2(1) ,
NO_TRIGGER_MISMATCH VARCHAR2(1) ,
FLASHBACK_CURSOR VARCHAR2(1) ,
ANYDATA_TRANSFORMATION VARCHAR2(1) ,
INCOMPLETE_CURSOR VARCHAR2(1) ,
TOP_LEVEL_RPI_CURSOR VARCHAR2(1) ,
DIFFERENT_LONG_LENGTH VARCHAR2(1) ,
LOGICAL_STANDBY_APPLY VARCHAR2(1) ,
DIFF_CALL_DURN VARCHAR2(1) ,
BIND_UACS_DIFF VARCHAR2(1) ,
PLSQL_CMP_SWITCHS_DIFF VARCHAR2(1) ,
CURSOR_PARTS_MISMATCH VARCHAR2(1) ,
STB_OBJECT_MISMATCH VARCHAR2(1) ,
ROW_SHIP_MISMATCH VARCHAR2(1) ,
PQ_SLAVE_MISMATCH VARCHAR2(1) ,
TOP_LEVEL_DDL_MISMATCH VARCHAR2(1) ,
MULTI_PX_MISMATCH VARCHAR2(1) ,
BIND_PEEKED_PQ_MISMATCH VARCHAR2(1) ,
MV_REWRITE_MISMATCH VARCHAR2(1) ,
ROLL_INVALID_MISMATCH VARCHAR2(1) ,
OPTIMIZER_MODE_MISMATCH VARCHAR2(1) ,
PX_MISMATCH VARCHAR2(1) ,
MV_STALEOBJ_MISMATCH VARCHAR2(1) ,
FLASHBACK_TABLE_MISMATCH VARCHAR2(1) ,
LITREP_COMP_MISMATCH VARCHAR2(1)
);
in_rec my_rec_type ;
out_rec my_type;
begin
loop
fetch p_cursor into in_rec;
exit when p_cursor%NOTFOUND;
-- first row
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'UNBOUND_CURSOR',
in_rec.UNBOUND_CURSOR);
pipe row(out_rec);
-- second row
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'SQL_TYPE_MISMATCH',
in_rec.SQL_TYPE_MISMATCH);
pipe row(out_rec);
-- 3
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'OPTIMIZER_MISMATCH ',
in_rec.OPTIMIZER_MISMATCH) ;
pipe row(out_rec);
-- 4
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'OUTLINE_MISMATCH',
in_rec.OUTLINE_MISMATCH);
pipe row(out_rec);
-- 5
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'STATS_ROW_MISMATCH',
in_rec.STATS_ROW_MISMATCH);
pipe row(out_rec);
-- 6
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'LITERAL_MISMATCH',
in_rec.LITERAL_MISMATCH);
pipe row(out_rec);
-- 7
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'SEC_DEPTH_MISMATCH ',
in_rec.SEC_DEPTH_MISMATCH) ;
pipe row(out_rec);
-- 8
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'EXPLAIN_PLAN_CURSOR',
in_rec.EXPLAIN_PLAN_CURSOR);
pipe row(out_rec);
-- 9
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'BUFFERED_DML_MISMATCH',
in_rec.BUFFERED_DML_MISMATCH);
pipe row(out_rec);
-- 10
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'PDML_ENV_MISMATCH ',
in_rec.PDML_ENV_MISMATCH );
pipe row(out_rec);
-- 11
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'INST_DRTLD_MISMATCH ',
in_rec.INST_DRTLD_MISMATCH) ;
pipe row(out_rec);
-- 12
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'SLAVE_QC_MISMATCH',
in_rec.SLAVE_QC_MISMATCH);
pipe row(out_rec);
-- 13
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'TYPECHECK_MISMATCH',
in_rec.TYPECHECK_MISMATCH);
pipe row(out_rec);
-- 14
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'AUTH_CHECK_MISMATCH',
in_rec.AUTH_CHECK_MISMATCH);
pipe row(out_rec);
-- 15
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'BIND_MISMATCH ',
in_rec.BIND_MISMATCH) ;
pipe row(out_rec);
-- 16
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'DESCRIBE_MISMATCH',
in_rec.DESCRIBE_MISMATCH);
pipe row(out_rec);
--==============================================================
-- 17
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'LANGUAGE_MISMATCH',
in_rec.LANGUAGE_MISMATCH);
pipe row(out_rec);
-- 18
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'TRANSLATION_MISMATCH',
in_rec.TRANSLATION_MISMATCH);
pipe row(out_rec);
-- 19
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'ROW_LEVEL_SEC_MISMATCH ',
in_rec.ROW_LEVEL_SEC_MISMATCH) ;
pipe row(out_rec);
-- 20
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'INSUFF_PRIVS',
in_rec.INSUFF_PRIVS);
pipe row(out_rec);
-- 21
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'INSUFF_PRIVS_REM',
in_rec.INSUFF_PRIVS_REM);
pipe row(out_rec);
-- 22
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'REMOTE_TRANS_MISMATCH',
in_rec.REMOTE_TRANS_MISMATCH);
pipe row(out_rec);
-- 23
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'LOGMINER_SESSION_MISMATCH ',
in_rec.LOGMINER_SESSION_MISMATCH) ;
pipe row(out_rec);
-- 24
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'INCOMP_LTRL_MISMATCH',
in_rec.INCOMP_LTRL_MISMATCH);
pipe row(out_rec);
--===========================================================================
-- 25
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'OVERLAP_TIME_MISMATCH',
in_rec.OVERLAP_TIME_MISMATCH);
pipe row(out_rec);
-- 26
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'SQL_REDIRECT_MISMATCH ',
in_rec.SQL_REDIRECT_MISMATCH );
pipe row(out_rec);
-- 27
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'MV_QUERY_GEN_MISMATCH ',
in_rec.MV_QUERY_GEN_MISMATCH) ;
pipe row(out_rec);
-- 28
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'USER_BIND_PEEK_MISMATCH',
in_rec.USER_BIND_PEEK_MISMATCH);
pipe row(out_rec);
-- 29
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'TYPECHECK_MISMATCH',
in_rec.TYPECHECK_MISMATCH);
pipe row(out_rec);
-- 30
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'NO_TRIGGER_MISMATCH',
in_rec.NO_TRIGGER_MISMATCH);
pipe row(out_rec);
-- 31
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'FLASHBACK_CURSOR ',
in_rec.FLASHBACK_CURSOR) ;
pipe row(out_rec);
-- 32
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'ANYDATA_TRANSFORMATION',
in_rec.ANYDATA_TRANSFORMATION);
pipe row(out_rec);
--=============================================================================
-- 33
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'INCOMPLETE_CURSOR',
in_rec.INCOMPLETE_CURSOR);
pipe row(out_rec);
-- 34
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'TOP_LEVEL_RPI_CURSOR ',
in_rec.TOP_LEVEL_RPI_CURSOR );
pipe row(out_rec);
-- 35
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'DIFFERENT_LONG_LENGTH ',
in_rec.DIFFERENT_LONG_LENGTH) ;
pipe row(out_rec);
-- 36
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'LOGICAL_STANDBY_APPLY',
in_rec.LOGICAL_STANDBY_APPLY);
pipe row(out_rec);
-- 37
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'DIFF_CALL_DURN',
in_rec.DIFF_CALL_DURN);
pipe row(out_rec);
-- 38
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'BIND_UACS_DIFF',
in_rec.BIND_UACS_DIFF);
pipe row(out_rec);
-- 39
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'PLSQL_CMP_SWITCHS_DIFF ',
in_rec.PLSQL_CMP_SWITCHS_DIFF) ;
pipe row(out_rec);
-- 40
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'CURSOR_PARTS_MISMATCH',
in_rec.CURSOR_PARTS_MISMATCH);
pipe row(out_rec);
--=============================================================================
-- 41
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'STB_OBJECT_MISMATCH',
in_rec.STB_OBJECT_MISMATCH);
pipe row(out_rec);
-- 42
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'ROW_SHIP_MISMATCH ',
in_rec.ROW_SHIP_MISMATCH );
pipe row(out_rec);
-- 43
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'PQ_SLAVE_MISMATCH ',
in_rec.PQ_SLAVE_MISMATCH) ;
pipe row(out_rec);
-- 44
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'TOP_LEVEL_DDL_MISMATCH',
in_rec.TOP_LEVEL_DDL_MISMATCH);
pipe row(out_rec);
-- 45
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'MULTI_PX_MISMATCH',
in_rec.MULTI_PX_MISMATCH);
pipe row(out_rec);
-- 46
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'BIND_PEEKED_PQ_MISMATCH',
in_rec.BIND_PEEKED_PQ_MISMATCH);
pipe row(out_rec);
-- 47
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'MV_REWRITE_MISMATCH ',
in_rec.MV_REWRITE_MISMATCH) ;
pipe row(out_rec);
-- 48
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'ROLL_INVALID_MISMATCH',
in_rec.ROLL_INVALID_MISMATCH);
pipe row(out_rec);
--==========================================
-- 49
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'OPTIMIZER_MODE_MISMATCH',
in_rec.OPTIMIZER_MODE_MISMATCH);
pipe row(out_rec);
-- 50
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'PX_MISMATCH',
in_rec.PX_MISMATCH);
pipe row(out_rec);
-- 51
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'MV_STALEOBJ_MISMATCH',
in_rec.MV_STALEOBJ_MISMATCH);
pipe row(out_rec);
-- 52
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'FLASHBACK_TABLE_MISMATCH ',
in_rec.FLASHBACK_TABLE_MISMATCH) ;
pipe row(out_rec);
-- 53
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'LITREP_COMP_MISMATCH',
in_rec.LITREP_COMP_MISMATCH);
pipe row(out_rec);
end loop ;
close p_cursor ;
return ;
end fun_table_column_to_row;
.
下面简单测试一下:
用户xys和sys下都存在完样一样的两个表t1
USER 为 "XYS"
SQL> select * from t1 where id=1;
ID
----------
1
另开一个sqlplus窗口:
SQL> show user
USER 为 "SYS"
SQL> select * from t1 where id=1;
ID
----------
1
SQL> select sql_id from v$sql where sql_text like 'select * from t1 where id=1%'
;
SQL_ID
-------------
5ag8kthgnvjk2
5ag8kthgnvjk2
--上面结果显示很显然这两个sql没有共享
SQL> select * from v$sql_shared_cursor
2 where sql_id='5ag8kthgnvjk2';
SQL_ID ADDRESS CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T
------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - -
R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
5ag8kthgnvjk2 6CC627C4 69C90804 0 N N N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
5ag8kthgnvjk2 6CC627C4 69C9063C 1 N N N N N N N N N N N N N Y N N N Y
N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
--上面查询显示了两条sql不能共享的原因,每次看起来都感觉很难受,找'Y'感觉很费劲,于是想转换一下看看是不是更方便一些。
调用上面的函数看看:
SQL> SELECT * FROM (select * from table(fun_table_row_to_column(cursor(select *
from v$sql_shared_cursor
2 where sql_id='5ag8kthgnvjk2')))) a
3 where mismatch_reason_name in
4 (select mismatch_reason_name from table(fun_table_row_to_column(cursor(sele
ct * from v$sql_shared_cursor
5 where sql_id='5ag8kthgnvjk2')))
6 where mismatch_reason='Y')
7 order by mismatch_reason_name , child_number;
SQL_ID ADDRESS CHILD_AD CHILD_NUMBER MISMATCH_REASON_NAME M
------------- -------- -------- ------------ ------------------------------ -
5ag8kthgnvjk2 6CC627C4 69C90804 0 AUTH_CHECK_MISMATCH N
5ag8kthgnvjk2 6CC627C4 69C9063C 1 AUTH_CHECK_MISMATCH Y
5ag8kthgnvjk2 6CC627C4 69C90804 0 TRANSLATION_MISMATCH N
5ag8kthgnvjk2 6CC627C4 69C9063C 1 TRANSLATION_MISMATCH Y
SQL>
.
[/php]
看起来函数效果还不错,不过总觉得有些累赘,大家也可以看看函数是否可以进一步简化。
[ 本帖最后由 warehouse 于 2008-1-9 23:52 编辑 ] |
|