Title: Message
Hi Steven,
it depends on what optimizer will actually
work with your particular statement.
Yes for RBO (in fact, in some
cases)
No for CBO (except 'hard' cases when default
number of permutations (80.000) is not enough)
Regards,
Ed
Does the order of tables also make
15
PM
Subject: RE: Optimizer Mode..how to
choose the right one?
I
by no means say CBO doesn't make mistakes. It by far does, but the
original statement stays accurate, order of tables only matters with RBO (if
comes up with tie) and ordered hint.
) 885-2275
Fuelspot 73 Princeton Street North, Chelmsford 01863
-Original Message-From: Koivu, Lisa
[mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 08,
2001 11:08 AMTo: '[EMAIL PROTECTED]';
'[EMAIL PROTECTED]'Subject: RE: Optimizer Mode..h
Increase the "sort_area_size" parameter in the init.ora file
to have more memory to play with. It is much quickere as it uses
RAM, rather than a hard disk, but then again, your server might not
have enough RAM to spare.
-Original Message-
Sent: Tuesday, August 07, 2001 1:43 PM
To: Multi
uelspot 73 Princeton Street North, Chelmsford 01863
-Original Message-From: Vadim Gorbounov
[mailto:[EMAIL PROTECTED]] Sent: Wednesday, August
08, 2001 12:09 PMTo: '[EMAIL PROTECTED]'Subject: RE:
Optimizer Mode......how to choose the right one?
Chris,
Sorry for t
-Original Message-From: Koivu, Lisa
[mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 08, 2001
11:08 AMTo: '[EMAIL PROTECTED]';
'[EMAIL PROTECTED]'Subject: RE: Optimizer Mode..how to
choose the right one?
Ahh, but Christopher, therein lies
the problem
Title: RE: Optimizer Mode..how to choose the right one?
Ahh, but Christopher, therein lies the problem. The CBO makes mistakes and sometimes needs the gentle nudging of the ORDERED hint. I've seen a query go from hours to 2 seconds after some analysis of the execution plan tha
The driving table order does not matter when using CBO, it will detirmine
the order based on cost, not placement.
The order only matters when dealing with the RBO (when it hits a tie,
otherwise it will change it, but this happens often), or when using the
ordered hint.
"Do not criticize someon
>>Order of the tables only matters when using the ORDERED hint or using
>>rbo AND there is a tie ranking.
But surely the very nature of the CHOOSE hint is to select the best method
to use whether it's to follow the RBO or the CBO. Therefor ensuring that you
have the driving table defined in the c
: Multiple recipients of list ORACLE-L
Asunto: RE: Optimizer Mode..how to choose the right one?
try analyzing using the "for all indexed columns"
clause ..
--- "Miller, Jay" <[EMAIL PROTECTED]> wrote:
> You probably already thought of this, but I hope you
> aren
try analyzing using the "for all indexed columns"
clause ..
--- "Miller, Jay" <[EMAIL PROTECTED]> wrote:
> You probably already thought of this, but I hope you
> aren't analyzing the
> SYS schema? This can cause terrible performance
> problems.
>
> Jay Miller
>
> -Original Message-
>
tes, 07 de Agosto de 2001 04:21 p.m.
Para: Multiple recipients of list ORACLE-L
Asunto: RE: Optimizer Mode......how to choose the right one?
You probably already thought of this, but I hope you aren't analyzing the
SYS schema? This can cause terrible performance problems.
Jay Miller
-O
You probably already thought of this, but I hope you aren't analyzing the
SYS schema? This can cause terrible performance problems.
Jay Miller
-Original Message-
Sent: Tuesday, August 07, 2001 12:06 PM
To: Multiple recipients of list ORACLE-L
...
I tried FIRST_ROWS, analyzing the tabl
--
> De: Christian Trassens [mailto:[EMAIL PROTECTED]]
> Enviado el: Martes, 07 de Agosto de 2001 01:26 p.m.
> Para: Multiple recipients of list ORACLE-L
> Asunto: Re: Optimizer Mode..how to choose the
> right one?
>
>
> NEVER change an optimizer since the application have
ecera de Nicaragua
-Mensaje original-
De: Christian Trassens [mailto:[EMAIL PROTECTED]]
Enviado el: Martes, 07 de Agosto de 2001 01:26 p.m.
Para: Multiple recipients of list ORACLE-L
Asunto: Re: Optimizer Mode......how to choose the right one?
NEVER change an optimizer since the applic
NEVER change an optimizer since the application have
some time working with it. I should leave it in CHOOSE
and then analyze what are doing the transactions.
Look up the worst events through v$system_Event or
from time to time through v$session_wait. Issue an
utlbstat/utlestat or statspack. Then
ORACLE-L
Asunto: RE: Optimizer Mode..how to choose the right one?
What type of transactions are running, update, insert, delete, or select?
-Original Message-
Sent: Tuesday, August 07, 2001 9:56 AM
To: Multiple recipients of list ORACLE-L
Take a look at index_caching parameters, they
If the transaction having problem is a SELECT statement and does SORT, then
I think the sort_area_size is too small.
Can you show us the SQL?
-Original Message-
Sent: Tuesday, August 07, 2001 9:37 AM
To: Multiple recipients of list ORACLE-L
Hi there,
Unfortunately you can't just
ients of list ORACLE-L
Asunto: RE: Optimizer Mode......how to choose the right one?
Take a look at index_caching parameters, they can make a big difference when
using cost mode, Oracle's settings are very poor.
I have a good article on my site www.vampired.net under Performance which
What type of transactions are running, update, insert, delete, or select?
-Original Message-
Sent: Tuesday, August 07, 2001 9:56 AM
To: Multiple recipients of list ORACLE-L
Take a look at index_caching parameters, they can make a big difference when
using cost mode, Oracle's settings ar
If you are using 8.x + I would recommend looking at Statspack over
utilbstat.
It comes installed with 816+ and available for download on 8.x+.
It is fairly easy to configure and a great utility.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, y
When using first rows, you force the cost based optimizer, in which the
order of the tables does not matter.
Order of the tables only matters when using the ORDERED hint or using rbo
AND there is a tie ranking.
"Do not criticize someone until you walked a mile in their shoes, that way
when you c
z
Administrador AIX
Compañía Cervecera de Nicaragua
-Mensaje original-
De: Thomas, Kevin [mailto:[EMAIL PROTECTED]]
Enviado el: Martes, 07 de Agosto de 2001 10:37 a.m.
Para: Multiple recipients of list ORACLE-L
Asunto: RE: Optimizer Mode..how to choose the right one?
Hi there,
Unfortunately you
Take a look at index_caching parameters, they can make a big difference when
using cost mode, Oracle's settings are very poor.
I have a good article on my site www.vampired.net under Performance which is
a great discussion about making the CBO work for you and not against you.
"Do not criticize
1) How often do you rebuild your indexes?
2) What does your utlb and utle report say?
3) Have you done any explain plans or tkprofs on your sql code?
Sql may not be using indexes.
4) Try using a hint of rule on your sql code to see if it runs better that way
5) Do you have a lot of chai
Hi there,
Unfortunately you can't just throw things like FIRST_ROWS at a select
statement to make it run faster. The person who wrote the statement should
have taken into account such things as:
a) Size of tables, ordering, which is the driving table
b) Indexes, are the being used, running state
26 matches
Mail list logo