dbms_stats hint?
in [Oracle]
|
From: Chuck on 25 Jan 2010 17:24 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
From: Mark D Powell on 26 Jan 2010 14:31 On Jan 25, 5:24 pm, Chuck <chuckh1958_nos...(a)gmail.com> wrote: > 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 Back a few releases Oracle support had said that the analyze command could take 4 times the table size to generate a full compute. What dbms_stats options were in effect and how large was the table that statistics were being generated for? What other tasks that require temp tablespace were also running? This specific SQL may not be responsible or at least not responsible by itself. You may wish to compare the allocated size of your temp tablespace to the larger indexes and result set sizes you know your applications will pull to be sure you do in fact have enough temp space allocated. HTH -- Mark D Powell --
From: Jonathan Lewis on 26 Jan 2010 14:59 "Chuck" <chuckh1958_nospam(a)gmail.com> wrote in message news:hjl5mq$qd9$1(a)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 This statement does come from dbms_stats; it's part of the job of gathering histograms on a table. Since each histogram requires Oracle to run a complex analytic query, Oracle creates a copy of the sample data - i.e.restricted to a subset of the rows and columns - rather than running the analytic query against the full table for each column. In your case Oracle is using an 18.6% sample of the table, and plans to collect histograms only on the four columns listed. So you could work out (roughly) the size of the temporary table needed. (average column length x num_rows + 12 x sample size for each column) plus about 10%. Once the temporary table is created, it's possible that the query with the analytic function (with all its sorting) has a further large-scale impact on the temporary tablespace. The dbms_stats hint isn't really a hint - it's just a label that the developers have put into the code to make it easier to identify the fact that the statement was generated by dbms_stats. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com
|
Pages: 1 Prev: Radiohead lyrics in Oracle RDBMS code Next: 40 Fast Facts about Oracle |