dbms_stats hint?
From: Chuck <chuckh1958_nospam_at_gmail.com>
Date: Mon, 25 Jan 2010 17:24:24 -0500
Message-ID: <hjl5mq$qd9$1_at_news.eternal-september.org>
While tracking down an ora-1652 error today I noticed two very similar statements that were running just prior to the error. They contained a dbms_stats hint and looked something like the one below. What is the dbms_stats hint and can I safely assume it was the stats job that generated these statements? BTW these statements have blown up a 4g temp tablespace a couple of times now. Why would dbms_stats need so much temp space for an insert into what's probably a GTT? Any way to prevent this seeing this is probably coming from an internal stats job?
Date: Mon, 25 Jan 2010 17:24:24 -0500
Message-ID: <hjl5mq$qd9$1_at_news.eternal-september.org>
While tracking down an ora-1652 error today I noticed two very similar statements that were running just prior to the error. They contained a dbms_stats hint and looked something like the one below. What is the dbms_stats hint and can I safely assume it was the stats job that generated these statements? BTW these statements have blown up a 4g temp tablespace a couple of times now. Why would dbms_stats need so much temp space for an insert into what's probably a GTT? Any way to prevent this seeing this is probably coming from an internal stats job?
insert /*+ append */ into sys.ora_temp_1_ds_1662 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */"MESSAGELOBID","MESSAGEID","NETWORKORIGINALMESSAGE","KEYALIAS" from "PROTECT"."MESSAGELOB" sample ( 18.6510224151) t Received on Mon Jan 25 2010 - 16:24:24 CST