CBO influences
in [Oracle]
|
From: DA Morgan on 9 Sep 2005 18:52 Jonathan Lewis wrote: > "EdStevens" <quetico_man(a)yahoo.com> wrote in message > news:1126294364.813025.287860(a)o13g2000cwo.googlegroups.com... > >>Bingo! My partner had assured me that he had checked the >>initialization parms and they were all identical. When I looked at the >>10053 trace where all the optimizer related parms were grouped together >>I discovered that OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, >>and OPTIMIZER_MAX_PERMUTATIONS were different. >> > > > There's a nice little feature in 10g > that helps with that, the parameter > list is split into the parameters > with altered values and parameters > with default values. > > Such a small change, but such a > big help. Didn't the ISDEFAULT column exist in previous versions? -- Daniel A. Morgan http://www.psoug.org damorgan(a)x.washington.edu (replace x with u to respond)
From: Jonathan Lewis on 10 Sep 2005 00:55 "DA Morgan" <damorgan(a)psoug.org> wrote in message news:1126306293.634107(a)yasure... > Jonathan Lewis wrote: >> "EdStevens" <quetico_man(a)yahoo.com> wrote in message >> news:1126294364.813025.287860(a)o13g2000cwo.googlegroups.com... >> >>>Bingo! My partner had assured me that he had checked the >>>initialization parms and they were all identical. When I looked at the >>>10053 trace where all the optimizer related parms were grouped together >>>I discovered that OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, >>>and OPTIMIZER_MAX_PERMUTATIONS were different. >>> >> There's a nice little feature in 10g >> that helps with that, the parameter >> list is split into the parameters >> with altered values and parameters >> with default values. >> >> Such a small change, but such a >> big help. > > Didn't the ISDEFAULT column exist in previous versions? Indeed, But we are talking about the 10053 trace file, and the current values for your session's parameters may not match the environment under which the trace was generated. -- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle - Volume 1: Fundamentals On-shelf date: Nov 2005 http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 4th Sept 2005
From: DA Morgan on 10 Sep 2005 01:25 Jonathan Lewis wrote: > "DA Morgan" <damorgan(a)psoug.org> wrote in message > news:1126306293.634107(a)yasure... > >>Jonathan Lewis wrote: >> >>>"EdStevens" <quetico_man(a)yahoo.com> wrote in message >>>news:1126294364.813025.287860(a)o13g2000cwo.googlegroups.com... >>> >>> >>>>Bingo! My partner had assured me that he had checked the >>>>initialization parms and they were all identical. When I looked at the >>>>10053 trace where all the optimizer related parms were grouped together >>>>I discovered that OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, >>>>and OPTIMIZER_MAX_PERMUTATIONS were different. >>>> >>> >>>There's a nice little feature in 10g >>>that helps with that, the parameter >>>list is split into the parameters >>>with altered values and parameters >>>with default values. >>> >>>Such a small change, but such a >>>big help. >> >>Didn't the ISDEFAULT column exist in previous versions? > > > > > Indeed, > > But we are talking about the 10053 trace file, > and the current values for your session's > parameters may not match the environment > under which the trace was generated. Thanks for the clarification. One more, slightly related question, if I may. Gathering system statistics I do not seem to be able to force a demo database to gather statistics for MBRC, MREADTIM, and SLAVETHR. I understand the last might be a bit difficult on a notebook used for classroom demo. But any help on any of this would be appreciated. Thanks. -- Daniel A. Morgan http://www.psoug.org damorgan(a)x.washington.edu (replace x with u to respond)
From: Jonathan Lewis on 10 Sep 2005 02:22 "DA Morgan" <damorgan(a)psoug.org> wrote in message news:1126329859.958172(a)yasure... > > One more, slightly related question, if I may. > > Gathering system statistics I do not seem to be able to force > a demo database to gather statistics for MBRC, MREADTIM, and > SLAVETHR. > > I understand the last might be a bit difficult on a notebook > used for classroom demo. But any help on any of this would > be appreciated. > > Thanks. > -- > Daniel A. Morgan > http://www.psoug.org > damorgan(a)x.washington.edu > (replace x with u to respond) Perhaps your sample data sets and are too small, and your db_cache_size too big to generate any physical multiblock reads. I just create a table that I know is larger than the cache, then do a few count(*) on it. (Or if the cache is small, I do a few scans of source$ and a few forced indexed accesses of source$) The SLAVETHR is a bit of a puzzle, since you haven't mentioned MAXTHR as not being set - and these both apply to parallel queries: MAXTHR is the observation of throughput the coordinator can take, SLAVETHR is the throughput the slaves can supply - I wouldn't have thought you could get one set without the other. (Guess - maybe the gather goes wrong if you have had only one ongoing parallel query that didn't terminate when you are trying to gather system stats). -- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle - Volume 1: Fundamentals On-shelf date: Nov 2005 http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 4th Sept 2005
From: DA Morgan on 10 Sep 2005 13:46 Jonathan Lewis wrote: > "DA Morgan" <damorgan(a)psoug.org> wrote in message > news:1126329859.958172(a)yasure... > >>One more, slightly related question, if I may. >> >>Gathering system statistics I do not seem to be able to force >>a demo database to gather statistics for MBRC, MREADTIM, and >>SLAVETHR. >> >>I understand the last might be a bit difficult on a notebook >>used for classroom demo. But any help on any of this would >>be appreciated. >> >>Thanks. >>-- >>Daniel A. Morgan >>http://www.psoug.org >>damorgan(a)x.washington.edu >>(replace x with u to respond) > > > > > Perhaps your sample data sets and > are too small, and your db_cache_size > too big to generate any physical > multiblock reads. I just create a table > that I know is larger than the cache, > then do a few count(*) on it. > > (Or if the cache is small, I do a few > scans of source$ and a few forced > indexed accesses of source$) > > The SLAVETHR is a bit of a puzzle, > since you haven't mentioned MAXTHR > as not being set - and these both apply > to parallel queries: MAXTHR is the > observation of throughput the coordinator > can take, SLAVETHR is the throughput > the slaves can supply - I wouldn't have > thought you could get one set without > the other. (Guess - maybe the gather > goes wrong if you have had only one > ongoing parallel query that didn't terminate > when you are trying to gather system stats). Thanks. What I ended up doing was running some ROLLUP and CUBE queries on the demo Sales History schema ... worked like a charm. But here's what I get (10gR1). Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production With the Partitioning, OLAP and Data Mining options SQL> SELECT pname, pval1 2 FROM aux_stats$ 3 WHERE sname = 'SYSSTATS_MAIN'; PNAME PVAL1 ------------------------------ ---------- CPUSPEED 413 CPUSPEEDNW 450.609 IOSEEKTIM 10 IOTFRSPEED 4096 MAXTHR 43008 MBRC 8 MREADTIM 5.121 SLAVETHR SREADTIM 6.636 9 rows selected. So it is possible. Again ... thanks. -- Daniel A. Morgan http://www.psoug.org damorgan(a)x.washington.edu (replace x with u to respond)
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: TIMEOUT ON CONTROL FILE ENQUEUE Next: ORA-12518 Listener could not hand off connection |