-----Original Message-----
From: Christopher Spence [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, August 08, 2001 10:27 AM
To:
Multiple recipients of list ORACLE-L
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
Fax: (707) 885-2275
Fuelspot
73 Princeton Street
North,
Chelmsford 01863
-----Original Message-----
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
>>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
have a benefit whether the database selects to use the RBO or
CBO...
IMHO.
Kev.
__________________
Kevin Thomas
Technical Analyst
Deregulation
Services
Calanais Ltd.
Tel: 0141 568 2314
Fax: 0141 568 2366
http://www.calanais.com
-----Original Message-----
Sent: 07 August 2001 18:36
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
Fax: (707) 885-2275
Fuelspot
73 Princeton Street
North,
Chelmsford 01863
-----Original Message-----
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
code, it's an absolute nightmare for the DBAs and it
doesn't look good on
the
developers.
Cheers,
Kev.
(a cheesed off
developer...not a DBA!)
__________________
Kevin Thomas
Technical Analyst
Deregulation
Services
Calanais Ltd.
Tel: 0141 568 2314
Fax: 0141 568 2366
http://www.calanais.com
-----Original Message-----
Sent: 07 August 2001 17:06
To: Multiple recipients of list ORACLE-L
Morning listers!
I am having performance problems with this
database, transactions ar running
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
db_writers = 4
db_file_multiblock_read_count = 16
db_block_buffers = 57600
db_block_size = 8192
shared_pool_size = 157286400
processes = 200
dml_locks =
1500
log_buffer = 655360
sequence_cache_entries = 800
sequence_cache_hash_buckets = 89
log_checkpoint_interval = 80000
optimizer_mode=CHOOSE
session_cached_cursors = 300
sort_area_size=1048576
hash_area_size=262144
hash_multiblock_io_count=4
hash_join_enabled=TRUE
always_anti_join=HASH
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,
Veronica Levin Enriquez
Administrador AIX
Compañía
Cervecera de Nicaragua
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Veronica
Levin
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: Thomas,
Kevin
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:
Christopher Spence
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: Thomas,
Kevin
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:
Christopher Spence
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).
