在10g/11g中如何查看SQL Profiles信息
2021/4/10 2:28:25
本文主要是介绍在10g/11g中如何查看SQL Profiles信息,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
在10g/11g中如何查看SQL Profiles的信息?
在Oracle 10g中,可以查询以下的数据字段表来获取信息:
SQLPROF$ATTR、SQLPROF$、SQLPROF$DESC
在Oracle 11g中,上面的数据字典表被取而代之,可以访问下面的数据字段表来获取信息:
SQLOBJ$、SQLOBJ$AUXDATA、SQLOBJ$DATA
示例如下:
Oracle 10g中:
SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b
2 where a.signature = b.signature
3 and a.name='SYS_SQLPROF_0158d954d210000';
ATTR_VAL
----------------------------------------------------------------------------------------------------
FULL(@"SEL$1" "T1"@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_NL(@"SEL$1" "T2"@"SEL$1")
SQL> select object_name,object_type from dba_objects where object_Name like 'SQLPROF%';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SQLPROF$ TABLE
SQLPROF$ATTR TABLE
SQLPROF$DESC TABLE
SQLPROF_ATTR TYPE
SQLPROF_ATTR SYNONYM
这些表的创建语句可以从sql.bsq中获取。
Oracle 11g中:
SQL> select name from dba_sql_profiles;
NAME
------------------------------
SYS_SQLPROF_0141d9f2f2a60001
SYS_SQLPROF_0141d9e54d180000
SQL> set autotrace on
SQL> SELECT extractValue(value(h),'.') AS hint
2 FROM sys.sqlobj$data od, sys.sqlobj$ so,
3 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
4 WHERE so.name = 'SYS_SQLPROF_0141d9e54d180000'
5 AND so.signature = od.signature
6 AND so.category = od.category
7 AND so.obj_type = od.obj_type
8 AND so.plan_id = od.plan_id;
HINT
----------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.01440028801)
OPTIMIZER_FEATURES_ENABLE(default)
SQL> select object_name,object_type from dba_objects where object_name like 'SQLOBJ%';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SQLOBJ$ TABLE
SQLOBJ$AUXDATA TABLE
SQLOBJ$DATA TABLE
SQLOBJ$DATA_PKEY INDEX
SQLOBJ$_PKEY INDEX
在Oracle 11g中,这些创建表的语句被从sql.bsq中剥离了出来,在11g中,被剥离到了dmanage.bsq中,并通过sql.bsq中调用来进行表的创建。
$cat $ORACLE_HOME/rdbms/admin/sql.bsq
........
rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! IMPORTANT !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
rem Whenever new column is created to store internal, user or kernel column
rem number, be sure to update the structure adtDT in atb.c so that those
rem columns will be updated properly during drop column.
rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
rem
dcore.bsq
dsqlddl.bsq
dmanage.bsq
dplsql.bsq
dtxnspc.bsq
dfmap.bsq
denv.bsq
drac.bsq
dsec.bsq
doptim.bsq
dobj.bsq
djava.bsq
dpart.bsq
drep.bsq
daw.bsq
dsummgt.bsq
dtools.bsq
dexttab.bsq
ddm.bsq
dlmnr.bsq
ddst.bsq
这篇关于在10g/11g中如何查看SQL Profiles信息的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-01后台管理开发学习:新手入门指南
- 2024-11-01后台管理系统开发学习:新手入门教程
- 2024-11-01后台开发学习:从入门到实践的简单教程
- 2024-11-01后台综合解决方案学习:从入门到初级实战教程
- 2024-11-01接口模块封装学习入门教程
- 2024-11-01请求动作封装学习:新手入门教程
- 2024-11-01登录鉴权入门:新手必读指南
- 2024-11-01动态面包屑入门:轻松掌握导航设计技巧
- 2024-11-01动态权限入门:新手必读指南
- 2024-11-01动态主题处理入门:新手必读指南