RE: Win2000/8.1.7.3.0/SQL
John - It is available at http://www.hotsos.com/catalog/ Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, August 20, 2002 7:09 PM To: Multiple recipients of list ORACLE-L Mike, Just one more thing to check before you close this: Can you run a 10053 trace? This is a 'CBO trace' and should provide some interesting reading. There was an excellent paper from Wolfgang Breitling on the 10053 at the recent IOUG and should be available somewhere... Inquiring minds want to know! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointments are inevitable in Life, but discouragement is optional. You decide! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 20, 2002 4:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: Win2000/8.1.7.3.0/SQL John: That makes perfect sense in view of the fact that only the /*+ RULE */ hint makes the query run. I'm not gonna try and find the root cause any more; I've modified my queries and I get answers again. My Thanks to all who have helped me with this issue. Cheers, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
Title: RE: Win2000/8.1.7.3.0/SQL I think you are on to something Jared. We had this happen at a production site last year. For some reason, the site began reporting performance problems after upgrading from 7.3.4 to 8.1.6. I finally tracked it down to the fact that a majority of our tables had 'degree' set to 'DEFAULT', which forces the use of the CBO. We fixed this by doing 'alter table table noparallel' for all the tables which set degree to 1 and solved the problem. Dan. -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 7:38 PM To: Multiple recipients of list ORACLE-L Subject: Re: Win2000/8.1.7.3.0/SQL Mike, Is the DEGREEE on the table 1? You can see this in DBA_TABLES. If the degree of parallelism is 1 then CBO will be used regardless of OPTIMIZER_MODE or OPTIMIZER_GOAL. Using a RULE hint will override the optimizer settings. I'm guessing that degree is 1 and you have old statistics. Jared On Monday 19 August 2002 17:58, Vergara, Michael (TEM) wrote: Ok...this is strange. The optimizer_mode is set to RULE, but if I put a RULE hint into the SQL it runs OK. I tried increasing the sort_area_size, but no joy. The segment with the largest number of extents is the silly TEMP segment with 985. After that is IDL_UB1$ with 739, and it tapers off quickly after that. On the other hand, this query runs fine on our SAP system, and it has thousands of tables and lots of extents. Well, the RULE hint in a RULE system seems to have worked. Weird. I never woulda tried that. Thanks, Mike -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 4:28 PM To: Multiple recipients of list ORACLE-L or is that 1 db set to first_rows? If so try the query with a rule hint? Bruce Reardon -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
Anand: This is one of four queries in a script I call my 'space_critical' script. Every night, an automated process scans all the DBs that I am responsible for, and sends me an e-mailif there is a problem, such as not enough space in a tablespace for a table to add an anotherextent. When I get this e-mail, I log in to this database with a maintenance ID, and run 'space_critical'. The entire report shows space by tablespace, by datafile, and by 'troubled object'. Trouble Objects are tables, indexes, whatever, that cannot allocate another extent. My script also creates a script in the /tmp directory that will change the next_extent size to a number that will fit in the remaining space. This is useful when the vendor is late delivering the disk you need. I published this script last year to www.searchDatabase.com. The script can be found at http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci778766,00.html. I hope you find it useful. Cheers, Mike -Original Message-From: Anand Kumar N [mailto:[EMAIL PROTECTED]]Sent: Monday, August 19, 2002 9:05 PMTo: Vergara, Michael (TEM)Subject: Re: Win2000/8.1.7.3.0/SQL U'r prompting in this SQL as Generating first troubled objects. What does this mean, actually what is the output of this sql. Pl. reply me. RegardsAnand KumarITW Signode India Ltd
RE: Win2000/8.1.7.3.0/SQL
Jared: The query is selecting from DBA_xxx views. The tables under these views are some of the X$ tables, and tables like user$, ts$, seg$, etc. I can verify that everything but the X$ tables have a degree of 1. How can I check those X$ tables...or should I decide to be content with a resolution? Thanks, Mike -Original Message- Sent: Monday, August 19, 2002 7:38 PM To: Multiple recipients of list ORACLE-L Mike, Is the DEGREEE on the table 1? You can see this in DBA_TABLES. If the degree of parallelism is 1 then CBO will be used regardless of OPTIMIZER_MODE or OPTIMIZER_GOAL. Using a RULE hint will override the optimizer settings. I'm guessing that degree is 1 and you have old statistics. Jared On Monday 19 August 2002 17:58, Vergara, Michael (TEM) wrote: Ok...this is strange. The optimizer_mode is set to RULE, but if I put a RULE hint into the SQL it runs OK. I tried increasing the sort_area_size, but no joy. The segment with the largest number of extents is the silly TEMP segment with 985. After that is IDL_UB1$ with 739, and it tapers off quickly after that. On the other hand, this query runs fine on our SAP system, and it has thousands of tables and lots of extents. Well, the RULE hint in a RULE system seems to have worked. Weird. I never woulda tried that. Thanks, Mike -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 4:28 PM To: Multiple recipients of list ORACLE-L or is that 1 db set to first_rows? If so try the query with a rule hint? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
Title: RE: Win2000/8.1.7.3.0/SQL Dan: When you did a 'select degree from dba_tables', did you get '1' or 'DEFAULT'? I get '1's for all my tables. Is there someplace else I should look? Thanks, Mike -Original Message-From: Straub, Dan [mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 20, 2002 9:03 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Win2000/8.1.7.3.0/SQL I think you are on to something Jared. We had this happen at a production site last year. For some reason, the site began reporting performance problems after upgrading from 7.3.4 to 8.1.6. I finally tracked it down to the fact that a majority of our tables had 'degree' set to 'DEFAULT', which forces the use of the CBO. We fixed this by doing 'alter table table noparallel' for all the tables which set degree to 1 and solved the problem. Dan. -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 7:38 PM To: Multiple recipients of list ORACLE-L Subject: Re: Win2000/8.1.7.3.0/SQL Mike, Is the DEGREEE on the table 1? You can see this in DBA_TABLES. If the degree of parallelism is 1 then CBO will be used regardless of OPTIMIZER_MODE or OPTIMIZER_GOAL. Using a RULE hint will override the optimizer settings. I'm guessing that degree is 1 and you have old statistics. Jared On Monday 19 August 2002 17:58, Vergara, Michael (TEM) wrote: Ok...this is strange. The optimizer_mode is set to RULE, but if I put a RULE hint into the SQL it runs OK. I tried increasing the sort_area_size, but no joy. The segment with the largest number of extents is the silly TEMP segment with 985. After that is IDL_UB1$ with 739, and it tapers off quickly after that. On the other hand, this query runs fine on our SAP system, and it has thousands of tables and lots of extents. Well, the RULE hint in a RULE system seems to have worked. Weird. I never woulda tried that. Thanks, Mike -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 4:28 PM To: Multiple recipients of list ORACLE-L or is that 1 db set to first_rows? If so try the query with a rule hint? Bruce Reardon -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
Mike, The message I replied to didn't include the SQL. So, I found your original message and actually looked at the SQL. :) Make sure that your system tables have not been analyzed, as the kernel still relies on RBO. You can't change the degree on x$ tables, as they are not actually tables. They are memory structures with a SQL interface. You might want to investigate using the ORDERED hint and changing the order of your driving tables. If your execution plan is using NESTED LOOPS, put the expensive one first ( driving table ). HTH Jared Vergara, Michael (TEM) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/20/2002 09:38 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Win2000/8.1.7.3.0/SQL Jared: The query is selecting from DBA_xxx views. The tables under these views are some of the X$ tables, and tables like user$, ts$, seg$, etc. I can verify that everything but the X$ tables have a degree of 1. How can I check those X$ tables...or should I decide to be content with a resolution? Thanks, Mike -Original Message- Sent: Monday, August 19, 2002 7:38 PM To: Multiple recipients of list ORACLE-L Mike, Is the DEGREEE on the table 1? You can see this in DBA_TABLES. If the degree of parallelism is 1 then CBO will be used regardless of OPTIMIZER_MODE or OPTIMIZER_GOAL. Using a RULE hint will override the optimizer settings. I'm guessing that degree is 1 and you have old statistics. Jared On Monday 19 August 2002 17:58, Vergara, Michael (TEM) wrote: Ok...this is strange. The optimizer_mode is set to RULE, but if I put a RULE hint into the SQL it runs OK. I tried increasing the sort_area_size, but no joy. The segment with the largest number of extents is the silly TEMP segment with 985. After that is IDL_UB1$ with 739, and it tapers off quickly after that. On the other hand, this query runs fine on our SAP system, and it has thousands of tables and lots of extents. Well, the RULE hint in a RULE system seems to have worked. Weird. I never woulda tried that. Thanks, Mike -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 4:28 PM To: Multiple recipients of list ORACLE-L or is that 1 db set to first_rows? If so try the query with a rule hint? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Win2000/8.1.7.3.0/SQL
Michael, Not that this has anything to do with your specific question... but...looking at the SQL statement you posted, I don't think it needs the table with the alias b. It already has the information from the inline view. Also, the object's pctincrease isn't used in the calculation although it's printed, and although the sql uses an outer join with dba_free_space (in case there is no free_space) it doesn't nvl the maxbytes column. I think the SQL should be something like this SELECT d.tablespace_name,owner,segment_name, segment_type,next_extent/1048576 next_ext FROMdba_segments d, (SELECT tablespace_name, max(bytes) maxbytes FROM dba_free_space GROUP BY tablespace_name) fs WHERE d.tablespace_name = fs.tablespace_name (+) and next_extent*(1 + pct_increase/100) nvl(fs.maxbytes,0) ORDER BY ext_extent desc, owner, segment_name BTW, will local managed tablespaces have this type of problem of a segment not being able to acquire a next extent? How does one monitor local tbs? Chaim Vergara, Michael (TEM) [EMAIL PROTECTED]@fatcity.com on 08/19/2002 06:23:34 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi Gurus: I have this SQL, see... col segment_name format a30 heading Segment Name col maxseg format 9,999,999.9 heading Largest|Segment (MB) col segment_type format a15 heading Segment|Type col pct_increase format 999 heading Pct|Incr col next_ext format 9,999,999.9 heading Next|Extent (MB) col ownerformat a12 prompt Generating First Troubled Objects Listing... select a.owner, a.tablespace_name, a.segment_name, a.segment_type, a.next_extent/1048576 next_ext, a.pct_increase, max(b.bytes)/1048576 MAXSEG from sys.dba_segments a, sys.dba_free_space b, (select max(fs.bytes) maxbytes, fs.tablespace_name tsname from sys.dba_free_space fs group by fs.tablespace_name) wherea.tablespace_name = b.tablespace_name and a.tablespace_name = tsname (+) and a.next_extent maxbytes group by a.owner, a.tablespace_name, a.segment_name, a.segment_type, a.next_extent, a.pct_increase order by a.next_extent desc, owner, segment_name / ...and it works great everywhere. Well, almost everywhere, and there's the rub. Thie query works on my 7.3, 8.0, 8i, 8iR2, and most 8iR3 databases. In this one database, on Windows 2000, it blows out the TEMP tablespace. Every time. And this is the only system where that happens. There are only 12 tablespaces, and 15 datafiles. Any ideas why this would fill up the TEMP space? Thanks, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
Title: RE: Win2000/8.1.7.3.0/SQL Hi Mike, I got 'DEFAULT'. After 'alter table ... noparallel', it was '1'. From Metalink 66484.1: (3) If any table in a query has a parallel degreee greater than one (including the DEFAULT degree), Oracle uses the cost-based optimizer for that query--even if OPTIMIZER_MODE = RULE, or if there is a RULE hint in the query itself So, since you said that a RULE hint makes it work, this may not be the problem. Dan. -Original Message- From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 20, 2002 10:05 AM To: Multiple recipients of list ORACLE-L Subject: RE: Win2000/8.1.7.3.0/SQL Dan: When you did a 'select degree from dba_tables', did you get '1' or 'DEFAULT'? I get '1's for all my tables. Is there someplace else I should look? Thanks, Mike
RE: Win2000/8.1.7.3.0/SQL
Title: RE: Win2000/8.1.7.3.0/SQL Dan: I don't think this is the problem. See query... SQL select degree, count(*) 2 from dba_tables 3 group by degree; DEGREE COUNT(*)-- -- 1 2652 And I have not changed anything. Thanks (and keep those ideas coming in, folks!), Mike -Original Message-From: Straub, Dan [mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 20, 2002 11:03 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Win2000/8.1.7.3.0/SQL Hi Mike, I got 'DEFAULT'. After 'alter table ... noparallel', it was '1'. From Metalink 66484.1: (3) If any table in a query has a parallel degreee greater than one (including the DEFAULT degree), Oracle uses the cost-based optimizer for that query--even if OPTIMIZER_MODE = RULE, or if there is a RULE hint in the query itself So, since you said that a RULE hint makes it work, this may not be the problem. Dan. -Original Message- From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 20, 2002 10:05 AM To: Multiple recipients of list ORACLE-L Subject: RE: Win2000/8.1.7.3.0/SQL Dan: When you did a 'select degree from dba_tables', did you get '1' or 'DEFAULT'? I get '1's for all my tables. Is there someplace else I should look? Thanks, Mike
RE: Win2000/8.1.7.3.0/SQL
Jared: I validated that there were no stats with this query: SQL SQL l 1 select owner, trunc(last_analyzed), count(*) 2 from dba_tables 3* group by owner, trunc(last_analyzed) SQL / OWNER TRUNC(LAS COUNT(*) -- - -- AURORA$JIS$UTILITY$ 14 OSE$HTTP$ADMIN3 OUTLN 2 PERFSTAT 28 SIEBEL 12-JUL-02 2357 SYS 219 SYSTEM 15 7 rows selected. SQL So I'm confident it's not analyzed SYS objects. But then I wondered what else Siebel may have done...so I did this... SQL 1 select name, value from v$parameter 2* where name like '%opt%' SQL / NAMEVALUE --- optimizer_features_enable 8.1.7 optimizer_mode RULE optimizer_max_permutations 8 optimizer_index_cost_adj100 optimizer_index_caching 0 optimizer_percent_parallel 0 object_cache_optimal_size 102400 7 rows selected. SQL I confess to not knowing what the 'optimizer_features_enable' parameter does, so I'll have to look that up. Thanks, Mike -Original Message- Sent: Tuesday, August 20, 2002 10:49 AM To: Multiple recipients of list ORACLE-L Mike, The message I replied to didn't include the SQL. So, I found your original message and actually looked at the SQL. :) Make sure that your system tables have not been analyzed, as the kernel still relies on RBO. You can't change the degree on x$ tables, as they are not actually tables. They are memory structures with a SQL interface. You might want to investigate using the ORDERED hint and changing the order of your driving tables. If your execution plan is using NESTED LOOPS, put the expensive one first ( driving table ). HTH Jared Vergara, Michael (TEM) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/20/2002 09:38 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Win2000/8.1.7.3.0/SQL Jared: The query is selecting from DBA_xxx views. The tables under these views are some of the X$ tables, and tables like user$, ts$, seg$, etc. I can verify that everything but the X$ tables have a degree of 1. How can I check those X$ tables...or should I decide to be content with a resolution? Thanks, Mike -Original Message- Sent: Monday, August 19, 2002 7:38 PM To: Multiple recipients of list ORACLE-L Mike, Is the DEGREEE on the table 1? You can see this in DBA_TABLES. If the degree of parallelism is 1 then CBO will be used regardless of OPTIMIZER_MODE or OPTIMIZER_GOAL. Using a RULE hint will override the optimizer settings. I'm guessing that degree is 1 and you have old statistics. Jared On Monday 19 August 2002 17:58, Vergara, Michael (TEM) wrote: Ok...this is strange. The optimizer_mode is set to RULE, but if I put a RULE hint into the SQL it runs OK. I tried increasing the sort_area_size, but no joy. The segment with the largest number of extents is the silly TEMP segment with 985. After that is IDL_UB1$ with 739, and it tapers off quickly after that. On the other hand, this query runs fine on our SAP system, and it has thousands of tables and lots of extents. Well, the RULE hint in a RULE system seems to have worked. Weird. I never woulda tried that. Thanks, Mike -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 4:28 PM To: Multiple recipients of list ORACLE-L or is that 1 db set to first_rows? If so try the query with a rule hint? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line
RE: Win2000/8.1.7.3.0/SQL
Mike, Just a thought - could you check DBA_TAB_COLUMNS too for LAST_ANALYZED in the SYS schema? John 1 select owner, trunc(last_analyzed), count(*) 2 from dba_tables 3* group by owner, trunc(last_analyzed) SQL / So I'm confident it's not analyzed SYS objects. But then I wondered what else Siebel may have done...so I did this... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
Mike, Ok...this is strange. The optimizer_mode is set to RULE, but if I put a RULE hint into the SQL it runs OK. I tried increasing This is almost clearly a case of the CBO being forced for whatever reason. And there are many :( Cut and paste from the Fine Manual (Oracle 8i Designing and Tuning for Performance). See the last 'Note:' :) The Optimizer 4-19 Features that Require the CBO The use of any of the following features requires the use of the CBO: n Partitioned tables n Index-organized tables n Reverse key indexes n Function-based indexes n SAMPLE clauses in a SELECT statement n Parallel execution and parallel DML n Star transformations n Star joins n Extensible optimizer n Query rewrite (materialized views) n Progress meter n Hash joins n Bitmap indexes n Partition views (release 7.3) Note: Even if the parameter OPTIMIZER_MODE is set to RULE, the use of these features enables the CBO. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointments are inevitable in Life, but discouragement is optional. You decide! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
John: I checked it out with this... SQL select owner, trunc(last_analyzed), count(*) 2 from dba_tab_columns 3 group by owner, trunc(last_analyzed); OWNER TRUNC(LAS COUNT(*) -- - -- AURORA$JIS$UTILITY$ 69 OSE$HTTP$ADMIN 16 OUTLN21 PERFSTAT293 SIEBEL 12-JUL-02 17737 SIEBEL71877 SYS 15831 SYSTEM 172 8 rows selected. SQL ...and it looks like there's nothing here, either! Thanks, Mike -Original Message- Sent: Tuesday, August 20, 2002 2:48 PM To: Multiple recipients of list ORACLE-L Mike, Just a thought - could you check DBA_TAB_COLUMNS too for LAST_ANALYZED in the SYS schema? John 1 select owner, trunc(last_analyzed), count(*) 2 from dba_tables 3* group by owner, trunc(last_analyzed) SQL / So I'm confident it's not analyzed SYS objects. But then I wondered what else Siebel may have done...so I did this... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
John: That makes perfect sense in view of the fact that only the /*+ RULE */ hint makes the query run. I'm not gonna try and find the root cause any more; I've modified my queries and I get answers again. My Thanks to all who have helped me with this issue. Cheers, Mike -Original Message- Sent: Tuesday, August 20, 2002 1:24 PM To: Multiple recipients of list ORACLE-L Mike, Ok...this is strange. The optimizer_mode is set to RULE, but if I put a RULE hint into the SQL it runs OK. I tried increasing This is almost clearly a case of the CBO being forced for whatever reason. And there are many :( Cut and paste from the Fine Manual (Oracle 8i Designing and Tuning for Performance). See the last 'Note:' :) The Optimizer 4-19 Features that Require the CBO The use of any of the following features requires the use of the CBO: n Partitioned tables n Index-organized tables n Reverse key indexes n Function-based indexes n SAMPLE clauses in a SELECT statement n Parallel execution and parallel DML n Star transformations n Star joins n Extensible optimizer n Query rewrite (materialized views) n Progress meter n Hash joins n Bitmap indexes n Partition views (release 7.3) Note: Even if the parameter OPTIMIZER_MODE is set to RULE, the use of these features enables the CBO. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
Did you compare the explain plans between a database where it works and where is doesn't. Anything interesting there? -Original Message- Sent: Tuesday, August 20, 2002 7:28 PM To: Multiple recipients of list ORACLE-L John: I checked it out with this... SQL select owner, trunc(last_analyzed), count(*) 2 from dba_tab_columns 3 group by owner, trunc(last_analyzed); OWNER TRUNC(LAS COUNT(*) -- - -- AURORA$JIS$UTILITY$ 69 OSE$HTTP$ADMIN 16 OUTLN21 PERFSTAT293 SIEBEL 12-JUL-02 17737 SIEBEL71877 SYS 15831 SYSTEM 172 8 rows selected. SQL ...and it looks like there's nothing here, either! Thanks, Mike -Original Message- Sent: Tuesday, August 20, 2002 2:48 PM To: Multiple recipients of list ORACLE-L Mike, Just a thought - could you check DBA_TAB_COLUMNS too for LAST_ANALYZED in the SYS schema? John 1 select owner, trunc(last_analyzed), count(*) 2 from dba_tables 3* group by owner, trunc(last_analyzed) SQL / So I'm confident it's not analyzed SYS objects. But then I wondered what else Siebel may have done...so I did this... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
Mike, Just one more thing to check before you close this: Can you run a 10053 trace? This is a 'CBO trace' and should provide some interesting reading. There was an excellent paper from Wolfgang Breitling on the 10053 at the recent IOUG and should be available somewhere... Inquiring minds want to know! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointments are inevitable in Life, but discouragement is optional. You decide! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 20, 2002 4:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: Win2000/8.1.7.3.0/SQL John: That makes perfect sense in view of the fact that only the /*+ RULE */ hint makes the query run. I'm not gonna try and find the root cause any more; I've modified my queries and I get answers again. My Thanks to all who have helped me with this issue. Cheers, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
Now you've piqued my curiosity again, too. I'm running it without the hints and with a 10053 and we'll see what it says. Tomorrow. At the plantation... No...no...that's a movie... Cheers, Mike -Original Message- Sent: Tuesday, August 20, 2002 5:09 PM To: Multiple recipients of list ORACLE-L Mike, Just one more thing to check before you close this: Can you run a 10053 trace? This is a 'CBO trace' and should provide some interesting reading. There was an excellent paper from Wolfgang Breitling on the 10053 at the recent IOUG and should be available somewhere... Inquiring minds want to know! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointments are inevitable in Life, but discouragement is optional. You decide! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 20, 2002 4:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: Win2000/8.1.7.3.0/SQL John: That makes perfect sense in view of the fact that only the /*+ RULE */ hint makes the query run. I'm not gonna try and find the root cause any more; I've modified my queries and I get answers again. My Thanks to all who have helped me with this issue. Cheers, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
Mike, ...and it works great everywhere. Well, almost everywhere, and there's the rub. Thie query works on my 7.3, 8.0, 8i, 8iR2, and most 8iR3 databases. In this one database, on Windows 2000, it blows out the TEMP tablespace. Every time. And this is the only system where that happens. There are only 12 tablespaces, and 15 datafiles. Any ideas why this would fill up the TEMP space? Does that particular Db have it's SYS schema ANALYZED? This should return no rows - if it does, then you can drop the stats for SYS and run this query again. select table_name from dba_tables where last_analyzed is not null and owner = 'SYS'; The problem could be that partial/complete ANALYZE of the SYS objects is resulting in Queries-from-Hell. Queries against the DD are written for Rule and the stats forces the CBO to be used instead.. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointments are inevitable in Life, but discouragement is optional. You decide! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Win2000/8.1.7.3.0/SQL
The order by is dong a sort and you have insufficient sort space configured so it is doing it to disk and that is the temp tablespace or whatever is allocated as the users temporary tablespace. I suspect that this database has the smallest SGA for any of the databases you have tested. You might like to investigate the SORT_AREA_SIZE parameter or maybe just look at increasing the available memory to the instance. Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. Vergara, Michael (TEM) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 20-08-2002 08:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Fax to: Subject:Win2000/8.1.7.3.0/SQL Hi Gurus: I have this SQL, see... col segment_name format a30 heading Segment Name col maxseg format 9,999,999.9 heading Largest|Segment (MB) col segment_type format a15 heading Segment|Type col pct_increase format 999 heading Pct|Incr col next_ext format 9,999,999.9 heading Next|Extent (MB) col ownerformat a12 prompt Generating First Troubled Objects Listing... select a.owner, a.tablespace_name, a.segment_name, a.segment_type, a.next_extent/1048576 next_ext, a.pct_increase, max(b.bytes)/1048576 MAXSEG from sys.dba_segments a, sys.dba_free_space b, (select max(fs.bytes) maxbytes, fs.tablespace_name tsname from sys.dba_free_space fs group by fs.tablespace_name) wherea.tablespace_name = b.tablespace_name and a.tablespace_name = tsname (+) and a.next_extent maxbytes group by a.owner, a.tablespace_name, a.segment_name, a.segment_type, a.next_extent, a.pct_increase order by a.next_extent desc, owner, segment_name / ...and it works great everywhere. Well, almost everywhere, and there's the rub. Thie query works on my 7.3, 8.0, 8i, 8iR2, and most 8iR3 databases. In this one database, on Windows 2000, it blows out the TEMP tablespace. Every time. And this is the only system where that happens. There are only 12 tablespaces, and 15 datafiles. Any ideas why this would fill up the TEMP space? Thanks, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
John: Does that particular Db have it's SYS schema ANALYZED? This should return no rows - if it does, then you can drop the stats for SYS and run this query again. Nope. Checked that already, but I checked it again. Any other suggestions? Thanks, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
or is that 1 db set to first_rows? If so try the query with a rule hint? Bruce Reardon -Original Message- Sent: Tuesday, 20 August 2002 8:49 Mike, ...and it works great everywhere. Well, almost everywhere, and there's the rub. Thie query works on my 7.3, 8.0, 8i, 8iR2, and most 8iR3 databases. In this one database, on Windows 2000, it blows out the TEMP tablespace. Every time. And this is the only system where that happens. There are only 12 tablespaces, and 15 datafiles. Any ideas why this would fill up the TEMP space? Does that particular Db have it's SYS schema ANALYZED? This should return no rows - if it does, then you can drop the stats for SYS and run this query again. select table_name from dba_tables where last_analyzed is not null and owner = 'SYS'; The problem could be that partial/complete ANALYZE of the SYS objects is resulting in Queries-from-Hell. Queries against the DD are written for Rule and the stats forces the CBO to be used instead.. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
Hmmm...that's a possibility. However, the query is blowing out a 2GB temp space that is empty when the query starts. I'll check and play with this field anyway. Thanks, Mike -Original Message- Sent: Monday, August 19, 2002 4:18 PM To: Multiple recipients of list ORACLE-L The order by is dong a sort and you have insufficient sort space configured so it is doing it to disk and that is the temp tablespace or whatever is allocated as the users temporary tablespace. I suspect that this database has the smallest SGA for any of the databases you have tested. You might like to investigate the SORT_AREA_SIZE parameter or maybe just look at increasing the available memory to the instance. Cheers -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
Title: RE: Win2000/8.1.7.3.0/SQL Just a WAG, but do any of your objects have a huge number of extents? Dan. -Original Message- From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 4:23 PM To: Multiple recipients of list ORACLE-L Subject: RE: Win2000/8.1.7.3.0/SQL John: Does that particular Db have it's SYS schema ANALYZED? This should return no rows - if it does, then you can drop the stats for SYS and run this query again. Nope. Checked that already, but I checked it again. Any other suggestions? Thanks, Mike
RE: Win2000/8.1.7.3.0/SQL
Mike, Hmmm...that's a possibility. However, the query is blowing out a 2GB temp space that is empty when the query starts. I'll check and play with this field anyway. A 100046 trace would be a good tool to use in the case... Alternately, just use the 'alter session set sql_trace = TRUE' and tkprof the output with explain=sys/password (even if the trace file is partial). John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointments are inevitable in Life, but discouragement is optional. You decide! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Win2000/8.1.7.3.0/SQL
Ok...this is strange. The optimizer_mode is set to RULE, but if I put a RULE hint into the SQL it runs OK. I tried increasing the sort_area_size, but no joy. The segment with the largest number of extents is the silly TEMP segment with 985. After that is IDL_UB1$ with 739, and it tapers off quickly after that. On the other hand, this query runs fine on our SAP system, and it has thousands of tables and lots of extents. Well, the RULE hint in a RULE system seems to have worked. Weird. I never woulda tried that. Thanks, Mike -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 4:28 PM To: Multiple recipients of list ORACLE-L or is that 1 db set to first_rows? If so try the query with a rule hint? Bruce Reardon -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Win2000/8.1.7.3.0/SQL
Mike, Is the DEGREEE on the table 1? You can see this in DBA_TABLES. If the degree of parallelism is 1 then CBO will be used regardless of OPTIMIZER_MODE or OPTIMIZER_GOAL. Using a RULE hint will override the optimizer settings. I'm guessing that degree is 1 and you have old statistics. Jared On Monday 19 August 2002 17:58, Vergara, Michael (TEM) wrote: Ok...this is strange. The optimizer_mode is set to RULE, but if I put a RULE hint into the SQL it runs OK. I tried increasing the sort_area_size, but no joy. The segment with the largest number of extents is the silly TEMP segment with 985. After that is IDL_UB1$ with 739, and it tapers off quickly after that. On the other hand, this query runs fine on our SAP system, and it has thousands of tables and lots of extents. Well, the RULE hint in a RULE system seems to have worked. Weird. I never woulda tried that. Thanks, Mike -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 4:28 PM To: Multiple recipients of list ORACLE-L or is that 1 db set to first_rows? If so try the query with a rule hint? Bruce Reardon -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
