| 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: Optimizer Mode......how to choose the right one?
Only
if you hit a tie with the rank of predicates.
"Do not criticize someone until you walked a
mile in their shoes, that way when you criticize them, you are a mile a way and
have their shoes."
Christopher R. Spence <FONT
face="Bell MT" size=1>OCP MCSE MCP A+ RAPTOR CNA <FONT face="Comic Sans MS" size=2>Oracle DBA <FONT face="Comic Sans MS" size=2>Phone: (978) 322-5744 <FONT face="Comic Sans MS" size=2>Fax: (707) 885-2275
face="Comic Sans MS" size=2>73 Princeton Street <FONT face="Comic Sans MS" size=2>North, Chelmsford 01863 <FONT face="Comic Sans MS" size=2>
<FONT
face=Tahoma size=2>-----Original Message-----From: Steve Haas
[mailto:steven.haas_at_snet.net] Sent: Thursday, August 09, 2001 12:55
PMTo: Multiple recipients of list ORACLE-LSubject: Re:
Optimizer Mode......how to choose the right one?
Does the order of tables also make a
difference with a RULE hint?
Steven HaasOpus Consultants, LLC
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
Sent: Wednesday, August 08, 2001 12: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.
<FONT face=Arial color=#0000ff
size=2>
<FONT face=Arial color=#0000ff
size=2>There is nothing wrong with getting it in a good order, I just wanted
to stress that it only truely matters during those two conditions.
That's all, not trying to be adversary.
<FONT face=Arial color=#0000ff
size=2>
<FONT face=Arial color=#0000ff
size=2>CNA is Novell Admin, I haven't done it in a while, I originally
started as a Novell Admin.
<FONT face=Arial color=#0000ff
size=2>Raptor is a firewall product I spent about 4 years working
with.
<FONT face=Arial color=#0000ff
size=2>
"Do not criticize someone until you walked
a mile in their shoes, that way when you criticize them, you are a mile a
way and have their shoes."
Christopher R. Spence <FONT
face="Bell MT" size=1>OCP MCSE MCP A+ RAPTOR CNA <FONT
face="Comic Sans MS" size=2>Oracle DBA <FONT face="Comic Sans MS"
size=2>Phone: (978) 322-5744 <FONT face="Comic Sans MS"
size=2>Fax: (707) 885-2275
face="Comic Sans MS" size=2>73 Princeton Street <FONT
face="Comic Sans MS" size=2>North, Chelmsford 01863 <FONT
face="Comic Sans MS" size=2>
<FONT
face=Tahoma size=2>-----Original Message-----From: Koivu, Lisa
[mailto:lisa.koivu_at_efairfield.com] Sent: Wednesday, August 08,
2001 11:08 AMTo: 'ORACLE-L_at_fatcity.com';
'cspence_at_FuelSpot.com'Subject: 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 that was failing
miserably.
Love the sig. You are a
CNA? A Raptor? Am I missing something?
Lisa Koivu <FONT
face=Arial color=#0000ff size=2>Vikings Fan (and DBA) <FONT
face=Arial color=#0000ff size=2>Ft. Lauderdale, FL, USA
-----Original Message-----
From: <FONT
face=Arial size=1>Christopher Spence [SMTP:cspence_at_FuelSpot.com]
Sent: <FONT
face=Arial size=1>Wednesday, August 08, 2001 10:27 AM
To:
Multiple recipients of list ORACLE-L
<FONT face=Arial
size=1>Subject:
RE: Optimizer Mode......how to choose the right
one?
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 someone until you walked a
mile in their shoes, that way when
you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE
MCP A+ RAPTOR CNA Oracle DBA
Phone: (978) 322-5744 <FONT
face=Arial size=2>Fax: (707) 885-2275
Fuelspot <FONT face=Arial
size=2>73 Princeton Street North,
Chelmsford 01863
-----Original Message----- <FONT
face=Arial size=2>Sent: Wednesday, August 08, 2001 4:30 AM
To: Multiple recipients of list
ORACLE-L
>>Order of the tables only matters when
using the ORDERED hint or using <FONT face=Arial
size=2>>>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 correct place within the query will <FONT face=Arial
size=2>have a benefit whether the database selects to use the RBO or
CBO...
IMHO. <FONT face=Arial
size=2>Kev.
__________________
Kevin Thomas <FONT face=Arial
size=2>Technical Analyst Deregulation
Services Calanais Ltd.
Tel: 0141 568 2314 <FONT
face=Arial size=2>Fax: 0141 568 2366 <FONT face=Arial
size=2><A target=_blank
href="http://www.calanais.com">http://www.calanais.com
-----Original Message----- <FONT
face=Arial size=2>Sent: 07 August 2001 18:36 <FONT face=Arial
size=2>To: Multiple recipients of list ORACLE-L
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 criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE
MCP A+ RAPTOR CNA Oracle DBA
Phone: (978) 322-5744 <FONT
face=Arial size=2>Fax: (707) 885-2275
Fuelspot <FONT face=Arial
size=2>73 Princeton Street North,
Chelmsford 01863
-----Original Message----- <FONT
face=Arial size=2>Sent: Tuesday, August 07, 2001 12:37 PM
To: Multiple recipients of list
ORACLE-L
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 statements through explain plan will
show you where the bottlenecks are...failing
that TKPROF will identify potential
problem areas with your database. c)
Size of queries, joins etc.
Too many developers do not write code to be
efficient, as long as it returns the
correct values it doesn't matter whether it takes 2 minutes or
2 hours...
I've spent a number of
hours/days/weeks/months tidying up poorly optimised <FONT
face=Arial size=2>code, it's an absolute nightmare for the DBAs and it
doesn't look good on the
developers.
Cheers, <FONT face=Arial
size=2>Kev. (a cheesed off
developer...not a DBA!)
__________________
Kevin Thomas <FONT face=Arial
size=2>Technical Analyst Deregulation
Services Calanais Ltd.
Tel: 0141 568 2314 <FONT
face=Arial size=2>Fax: 0141 568 2366 <FONT face=Arial
size=2><A target=_blank
href="http://www.calanais.com">http://www.calanais.com
-----Original Message----- <FONT
face=Arial size=2>Sent: 07 August 2001 17:06 <FONT face=Arial
size=2>To: Multiple recipients of list ORACLE-L
Morning listers!
I am having performance problems with this
database, transactions ar running <FONT face=Arial
size=2>very slow and I am not sure if I have choose the right optimizer
mode...
AIX 4.2.1, Oracle 7.3.4, 30 GB Database, 1 GB
Real Memory, 500 MB SGA, 70
concurrent users, mostly OLTP transactions.
I have tunned init parameters the best I can
we the resources I have:
db_files = 70 <FONT face=Arial
size=2>db_writers = 4 <FONT face=Arial
size=2>db_file_multiblock_read_count = 16 <FONT face=Arial
size=2>db_block_buffers = 57600 <FONT face=Arial
size=2>db_block_size = 8192 <FONT face=Arial
size=2>shared_pool_size = 157286400 <FONT face=Arial
size=2>processes = 200 dml_locks =
1500 log_buffer = 655360
sequence_cache_entries = 800
sequence_cache_hash_buckets = 89
log_checkpoint_interval = 80000
optimizer_mode=CHOOSE <FONT
face=Arial size=2>session_cached_cursors = 300 <FONT
face=Arial size=2>sort_area_size=1048576 <FONT face=Arial
size=2>hash_area_size=262144 <FONT face=Arial
size=2>hash_multiblock_io_count=4 <FONT face=Arial
size=2>hash_join_enabled=TRUE <FONT face=Arial
size=2>always_anti_join=HASH <FONT face=Arial
size=2>job_queue_processes=8
35 rollback segments, inital 1MB, next 1MB,
optimal 30 MB 8 multiplexed redologs,
30 MB each
I tried FIRST_ROWS, analyzing the tables, but
users claimed that performance was
getting worse, so I chaged it to Choose. Always analyzing the
tables....
but, everytime I analyze the tables,
performance gets worse. Is this a
normal behavior?
Any advice will be welcome!
thanks is advance,
Saludos, <FONT face=Arial
size=2>Veronica Levin Enriquez <FONT face=Arial
size=2>Administrador AIX Compańía
Cervecera de Nicaragua --
Please see the official ORACLE-L FAQ:
<A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com <FONT
face=Arial size=2>-- Author: Veronica
Levin INET:
vlevin_at_victoria.com.ni
Fat City Network Services
-- (858) 538-5051 FAX: (858) 538-5051 <FONT face=Arial
size=2>San Diego, California
-- Public Internet access / Mailing Lists <FONT face=Arial
size=2>--------------------------------------------------------------------
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 <FONT face=Arial
size=2>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:
<A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com <FONT
face=Arial size=2>-- Author: Thomas,
Kevin INET:
Kevin.Thomas_at_calanais.com
Fat City Network Services
-- (858) 538-5051 FAX: (858) 538-5051 <FONT face=Arial
size=2>San Diego, California
-- Public Internet access / Mailing Lists <FONT face=Arial
size=2>--------------------------------------------------------------------
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 <FONT face=Arial
size=2>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:
<A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com <FONT
face=Arial size=2>-- Author:
Christopher Spence INET:
cspence_at_FuelSpot.com
Fat City Network Services
-- (858) 538-5051 FAX: (858) 538-5051 <FONT face=Arial
size=2>San Diego, California
-- Public Internet access / Mailing Lists <FONT face=Arial
size=2>--------------------------------------------------------------------
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 <FONT face=Arial
size=2>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:
<A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com <FONT
face=Arial size=2>-- Author: Thomas,
Kevin INET:
Kevin.Thomas_at_calanais.com
Fat City Network Services
-- (858) 538-5051 FAX: (858) 538-5051 <FONT face=Arial
size=2>San Diego, California
-- Public Internet access / Mailing Lists <FONT face=Arial
size=2>--------------------------------------------------------------------
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 <FONT face=Arial
size=2>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:
<A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com <FONT
face=Arial size=2>-- Author:
Christopher Spence INET:
cspence_at_FuelSpot.com
Fat City Network Services
-- (858) 538-5051 FAX: (858) 538-5051 <FONT face=Arial
size=2>San Diego, California
-- Public Internet access / Mailing Lists <FONT face=Arial
size=2>--------------------------------------------------------------------
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 Thu Aug 09 2001 - 15:34:27 CDT
 |
 |