10G可以通过如下查询来获取SQL PROFILE使用的HINT
SQL> SELECT attr_val 2 FROM sys.sqlprof$ p, sys.sqlprof$attr a 3 WHERE p.sp_name = 'opt_estimate' 4 AND p.signature = a.signature 5 AND p.category = a.category; ATTR_VAL --------------------------------------------------------------------------------- OPT_ESTIMATE(@"SEL$1", JOIN, ("T"@"SEL$1", "CH"@"SEL$1"), SCALE_ROWS=276.7754079) OPT_ESTIMATE(@"SEL$1", TABLE, "CH"@"SEL$1", SCALE_ROWS=40.15499105)
11G后这个查询不再有效,实际上涉及到的底层表也已经改变。可以通过如下查询获取:
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 = 'opt_estimate' 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, "CH"@"SEL$1", SCALE_ROWS=39.20843548) OPT_ESTIMATE(@"SEL$1", JOIN, ("T"@"SEL$1", "CH"@"SEL$1"), SCALE_ROWS=281.2054138) OPTIMIZER_FEATURES_ENABLE(default)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-773590/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-773590/