在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

參考：
http://www.antognini.ch/2008/08/sql-profiles-in-data-dictionary/
http://www.unyoug.com/forums/viewtopic.php?p=366

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2106743/，如需轉載，請註明出處，否則將追究法律責任。