[20170625]12c Extended statistics.txt--//別人的系統12c,awr報表出現大量調用執行如下sql語句.select default$ from col$ where rowid=:1;--//google看了一下,問題出在Extended statistics ...
[20170625]12c Extended statistics.txt
--//別人的系統12c,awr報表出現大量調用執行如下sql語句.
select default$ from col$ where rowid=:1;
--//google看了一下,問題出在Extended statistics的問題,12c 會自動收集擴展統計信息.找到如下鏈接:
--//https://blog.dbi-services.com/sql-plan-directives-strike-again/
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
--//特別在應用不使用綁定變數的情況下,12c這個問題會更嚴重,據說12.2已經解決這個問題.
select default$ from col$ where rowid=:1;
--//sql_id=''47r1y8yn34jmj'.
--//僅僅記錄作者的結論:
--//https://blog.dbi-services.com/sql-plan-directives-strike-again/
Conclusion
In that case, the solution is both to change the application to use bind variable (it's perl, very easy) and apply the
patch.
I'm sharing that because it's a good illustration of what side effects can do, as well as a good example of methodical
troubleshooting. When you know the reason without blind guesses, you can address the root cause. If you don't, the risk
is that you add even more side effects.
By the way, please use bind variables…
--//我也google,也許設置"_optimizer_enable_extended_stats"=FALSE,也可以.不過這樣估計就不能使用建立與使用這樣的統計.
alter system set "_optimizer_enable_extended_stats"=FALSE scope=both;
SYS@test> @ hide _optimizer_enable_extended_stats
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
-------------------------------- -------------------------------------------------- ------------- ------------- ------------
_optimizer_enable_extended_stats use extended statistics for selectivity estimation TRUE TRUE TRUE
--//再或者設置如下,這也是我使用的方法:
alter session set cursor_sharing="force" scope=both;
--//刪除不需要的擴展統計,例子:
exec dbms_stats.drop_extended_stats(ownname => 'SCOTT',tabname => 'EMP',extension => '("ENAME","JOB")');
2.自己手工建立刪除看看.
SCOTT@test01p> select DBMS_STATS.create_extended_stats(ownname => 'SCOTT',tabname => 'EMP',extension =>'("ENAME","DEPTNO")') c30 from dual ;
C30
------------------------------
SYS_STUU2WA2Z3E__3QS4G4E5M$QPA
--//註不能exec 來執行.
SCOTT@test01p> column EXTENSION_NAME format a30
SCOTT@test01p> select * from dba_stat_extensions where owner='SCOTT' ;
OWNER TABLE_NAME EXTENSION_NAME EXTENSION CREATOR DRO
---------- ---------- ------------------------------ ------------------------ ------- ---
SCOTT EMP SYS_STUU2WA2Z3E__3QS4G4E5M$QPA ("ENAME","DEPTNO") USER YES
EMP SYS_NC00009$ (STANDARD_HASH("ENAME")) SYSTEM NO
SCOTT@test01p> exec dbms_stats.drop_extended_stats(ownname => 'SCOTT',tabname => 'EMP',extension => '("ENAME","DEPTNO")');
PL/SQL procedure successfully completed.
SCOTT@test01p> select * from dba_stat_extensions where owner='SCOTT' ;
OWNER TABLE_NAME EXTENSION_NAME EXTENSION CREATOR DRO
---------- ---------- ------------------------------ ------------------------ ------- ---
SCOTT EMP SYS_NC00009$ (STANDARD_HASH("ENAME")) SYSTEM NO
--//可以發現已經刪除.
--//補充:https://smarttechways.com/2017/01/06/extended-stats-in-oracle/
SQL plan directives can be used by Oracle to determine if extended statistics specifically column groups, are missing
and would resolve the cardinality misestimates. After a SQL directive is used the optimizer decides if the cardinality
not be estimate could be resolved with a column group. If so, the database can automatically create that column group
the next time statistics are gathered on the appropriate table.
This step is "always on" in Oracle Database 12c Release 1, but from Oracle Database 12c Release 2, it is controlled by
the DBMS_STATS preference AUTO_STAT_EXTENSIONS. Note that the default is OFF, so to enable automatic column group
creation the following.
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_STAT_EXTENSIONS', 'ON');
--//我的環境沒有這個參數.
總結:
1.我個人的建議不要過早的使用XX.1的版本,這樣就是人家試驗品.
2.國內應用大量不使用綁定變數,這個也是國內使用oracle資料庫的通病.
