| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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$ADMIN 3 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> /
NAME VALUE --------------------------- ---------------------------------------- optimizer_features_enable 8.1.7 optimizer_mode RULE optimizer_max_permutations 80000 optimizer_index_cost_adj 100 optimizer_index_caching 0
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)" <mvergara_at_guidant.com>
Sent by: root_at_fatcity.com
08/20/2002 09:38 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
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:Bruce.Reardon_at_comalco.riotinto.com.au]
> 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: mvergara_at_guidant.com 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: ListGuru_at_fatcity.com (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: Jared.Still_at_radisys.com 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: ListGuru_at_fatcity.com (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: mvergara_at_guidant.com 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: ListGuru_at_fatcity.com (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).Received on Tue Aug 20 2002 - 14:28:44 CDT
 |
 |