首页 » ORACLE 9i-23c » What’s the ORA_TEMP_%_DS_% table, eg. SYS.ORA_TEMP_1_DS_%

What’s the ORA_TEMP_%_DS_% table, eg. SYS.ORA_TEMP_1_DS_%

  • 2019/09/22
  • ORACLE 9i-23c
  • 517 views
  • What’s the ORA_TEMP_%_DS_% table, eg. SYS.ORA_TEMP_1_DS_%已关闭评论

AWR TOP SQL:

dscp4xmkphqb9 insert /*+ append */ into sys.ora_temp_2_ds_262354 
SELECT /*+  parallel(t, 320) parallel_index(t, 320) dbms_stats cursor_sharing_exact use_weak_name_resl 
dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') 
no_substrb_pad  */"LOCATION_AREA", "LOCATION_CITY", "LOCATION_PROVINCE", "LOGIC_POISTION", 
"LOGIN_TYPE", "LONGITUDE", "MAINTEN_MAN", "MAINTEN_OVER_DATES", "MAINTEN_OVER_TIME", 
...
"LABEL_CN", "LABEL_DEV", "LAST_MODIFY_TIME", "LATITUDE"  from "SA_OMP"."WLAN_AP璁惧_SHOT" sample ( 10.0000000000)  t   xxx

SYS.ORA_TEMP_1_DS_% are global temporary tables created by dbms_stats to generate histograms for tables.

Further investigation revealed that a SQL which queried against ORA_TEMP_%_DS_% uses too much temporary space; however before explicitly setting ESTIMATE_PERCENT, this error didn’t occur (With AUTO sample size, it trends to use 100% sample size, so it is very curious that problem didn’t occur when using a higher sample size).

This is expected behavior.

ORA_TEMP_%_DS_% is only used when gathering histogram. Although it trends to use 100% sample size for AUTO sample size when gathering table stats, but gathering histogram activity does not honor that rule. It uses a different sample size.

User a even smaller ESTIMATE_PERCENT
OR
Do NOT explicitly set ESTIMATE_PERCENT

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：