-----Original Message-----
From: Chalavadi, Durgarao [mailto:ChalavaD@qwest.net]
Sent: Monday, October 05, 1998 10:11 AM
To: 'ari'
Subject:
Hi Ari,
I received your mail on friday. Thanks a lot for sending me reply.
How can i know which optimizer is being used by oracle session?
Thanks in advance for your help.
Bi,
Durga
---- Reply -------------
The best way is to do
SELECT * FROM V$PARAMETER WHERE NAME = 'optimizer_mode';
This will tell you the mode for the database. If the value is CHOOSE than it the
database is in Cost-Based. However, if there are no statistics on the tables
that a query is based upon, then it is in RULE mode.
To tell for a particular query, you will need to do an EXPLAIN PLAN on the
query. If you see costs associated with the query, then the query is in
COST-BASED mode. Otherwise it is in RULE-BASED mode.
Don't forget that you can use the RULE hint to force rule-based mode on any
query.
Best regards,
-Ari Kaplan
www.arikaplan.com
Back to Ari Kaplan's Home Page