Analyzing queries

[Virginia]

We have a product that runs on Oracle. The version of Oracle I'm
working with is 8.1.7. I should also note that I'm relatively new to
Oracle.

I'm troubleshooting one particular database that is having performance
problems when we try to run various quireies and peform inserts or
updates.

Unfortunately this particular product "requires" that "OPTIMIZER_ MODE
= RULE".

Of the research I've done on analyzing tables, it seems that analyzing
queries and tables only effect a database if you're running a Cost
Basis system vs. Rule.

Given that we have to run RULE, are there any options that will assist
us in troubleshooting and tuning this database?

Thank you for your time!

Virgina
Field Engineer
vleandr@nai.com
***NO SPAM PLEASE****

[Frank van Bortel]

Re: Analyzing queries

Virginia wrote:

We have a product that runs on Oracle. The version of Oracle I'm
working with is 8.1.7. I should also note that I'm relatively new to
Oracle.
>
I'm troubleshooting one particular database that is having performance
problems when we try to run various quireies and peform inserts or
updates.
>
Unfortunately this particular product "requires" that "OPTIMIZER_ MODE
= RULE".
>
Of the research I've done on analyzing tables, it seems that analyzing
queries and tables only effect a database if you're running a Cost
Basis system vs. Rule.
>
Given that we have to run RULE, are there any options that will assist
us in troubleshooting and tuning this database?
>
Thank you for your time!
>
Virgina
Field Engineer
vleandr@nai.com
***NO SPAM PLEASE****

Plenty.
No offense, but I'd try to get an expert in; would probably be done
within a day. See claims from hotsos.com
--

Regards,
Frank van Bortel

[Ana C. Dent]

Re: Analyzing queries

Virginia wrote:

We have a product that runs on Oracle. The version of Oracle I'm
working with is 8.1.7. I should also note that I'm relatively new to
Oracle.
>
I'm troubleshooting one particular database that is having performance
problems when we try to run various quireies and peform inserts or
updates.
>
Unfortunately this particular product "requires" that "OPTIMIZER_ MODE
= RULE".

This sounds like Siebel.

>
Of the research I've done on analyzing tables, it seems that analyzing
queries and tables only effect a database if you're running a Cost
Basis system vs. Rule.
>
Given that we have to run RULE, are there any options that will assist
us in troubleshooting and tuning this database?

Enable SQL_TRACE & use TKPROF for troubleshooting .
You may have limited options for "tuning".

[Ryan]

Re: Analyzing queries

see my replies inline...


"Virginia" <vleandr@nai.co mwrote in message
news:84c080d3.0 401061022.614d8 a20@posting.goo gle.com...

We have a product that runs on Oracle. The version of Oracle I'm
working with is 8.1.7. I should also note that I'm relatively new to
Oracle.
>
I'm troubleshooting one particular database that is having performance
problems when we try to run various quireies and peform inserts or
updates.
>
Unfortunately this particular product "requires" that "OPTIMIZER_ MODE
= RULE".
>

common with off the shelf products. they were made a while ago and never
tested against CBO. so they tell you not to use it.

Of the research I've done on analyzing tables, it seems that analyzing
queries and tables only effect a database if you're running a Cost
Basis system vs. Rule.

dont analyze in rule mode.

>
Given that we have to run RULE, are there any options that will assist
us in troubleshooting and tuning this database?
>

yes, learn how to use the explain plan. go to metalink and otn.oracle.com
and do a search on that and 'autotrace'. this gives you a chart of your
query.

you can install it by running the script in
ORACLE_HOME/rdbms/admin/utlxplan.sql

Thank you for your time!
>
Virgina
Field Engineer
vleandr@nai.com
***NO SPAM PLEASE****

[Daniel Roy]

Re: Analyzing queries

I have to deal every day with such a ------ product (Siebel). What I
found is that pretty much the only "tuning" option that these bastards
agree to let us perform is to create new indices when that helps. They
won't even allow us to remove the indices containing one single value,
that the RBO thinks is useful! Post the query and the execution plan,
and we might be able to give more advice.

Daniel

We have a product that runs on Oracle. The version of Oracle I'm
working with is 8.1.7. I should also note that I'm relatively new to
Oracle.
>
I'm troubleshooting one particular database that is having performance
problems when we try to run various quireies and peform inserts or
updates.
>
Unfortunately this particular product "requires" that "OPTIMIZER_ MODE
= RULE".
>
Of the research I've done on analyzing tables, it seems that analyzing
queries and tables only effect a database if you're running a Cost
Basis system vs. Rule.
>
Given that we have to run RULE, are there any options that will assist
us in troubleshooting and tuning this database?
>
Thank you for your time!
>
Virgina
Field Engineer
vleandr@nai.com
***NO SPAM PLEASE****

[support@DBtune.com]

Re: Analyzing queries

danielroy10junk @hotmail.com (Daniel Roy) wrote in message news:<3722db.04 01070725.5ebb3c 7a@posting.goog le.com>...

I have to deal every day with such a ------ product (Siebel). What I
found is that pretty much the only "tuning" option that these bastards
agree to let us perform is to create new indices when that helps. They
won't even allow us to remove the indices containing one single value,
that the RBO thinks is useful! Post the query and the execution plan,
and we might be able to give more advice.
>
Daniel
>

Having attended the Siebel Core Consultants Course and worked closely
with Siebel since Nov-2002, recent discussions with them have revealed
there is an option that allows Siebel session to be MT on the Siebel
server. If so this should reduce the number of sessions to the DB and
hence reduce the amount of resources used on the DB server, as well as
not having to resort to using dedicated sessions because MTS reaches
the maximum number of connections that a dispatcher can handle (max
sessions reached). Basically increasing DB performance.

https://www.DBtune.com

support@DBtune. com

[Mark D Powell]

Re: Analyzing queries

danielroy10junk @hotmail.com (Daniel Roy) wrote in message news:<3722db.04 01070725.5ebb3c 7a@posting.goog le.com>...

I have to deal every day with such a ------ product (Siebel). What I
found is that pretty much the only "tuning" option that these bastards
agree to let us perform is to create new indices when that helps. They
won't even allow us to remove the indices containing one single value,
that the RBO thinks is useful! Post the query and the execution plan,
and we might be able to give more advice.
>
Daniel
>
>
>

We have a product that runs on Oracle. The version of Oracle I'm
working with is 8.1.7. I should also note that I'm relatively new to
Oracle.

I'm troubleshooting one particular database that is having performance
problems when we try to run various quireies and peform inserts or
updates.

Unfortunately this particular product "requires" that "OPTIMIZER_ MODE
= RULE".

Of the research I've done on analyzing tables, it seems that analyzing
queries and tables only effect a database if you're running a Cost
Basis system vs. Rule.

Given that we have to run RULE, are there any options that will assist
us in troubleshooting and tuning this database?

Thank you for your time!

Virgina
Field Engineer
vleandr@nai.com
***NO SPAM PLEASE****

Daniel, what about using the outline feature to provide optimizer
hints without touching the vendor code?

HTH -- Mark D Powell --

[Daniel Roy]

Re: Analyzing queries

>

Daniel, what about using the outline feature to provide optimizer
hints without touching the vendor code?
>
HTH -- Mark D Powell --

I heard of a Siebel project where they implemented this suggestion,
and it worked OK. I'm not sure if it was "approved" by Siebel or not.
The problem with Siebel is that for any database change, if the Siebel
guys have not heard of this feature, then instead of saying "That
sounds great, let's try it!", they become defensive and threaten to
not support our Siebel if we implement that change. That creates a
very closed-minded environment, where everyone is scared to do
anything. The only time that they agree to make any change is when the
application is so slow that all the users are complaining non-stop.

Daniel

[Ryan]

Re: Analyzing queries

if Siebel is this hostile, why do people buy their product? Are there
competing products?
"Daniel Roy" <danielroy10jun k@hotmail.comwr ote in message
news:3722db.040 1081343.4d88014 8@posting.googl e.com...

Daniel, what about using the outline feature to provide optimizer
hints without touching the vendor code?

HTH -- Mark D Powell --

>
I heard of a Siebel project where they implemented this suggestion,
and it worked OK. I'm not sure if it was "approved" by Siebel or not.
The problem with Siebel is that for any database change, if the Siebel
guys have not heard of this feature, then instead of saying "That
sounds great, let's try it!", they become defensive and threaten to
not support our Siebel if we implement that change. That creates a
very closed-minded environment, where everyone is scared to do
anything. The only time that they agree to make any change is when the
application is so slow that all the users are complaining non-stop.
>
Daniel

[Daniel Roy]

Re: Analyzing queries

if Siebel is this hostile, why do people buy their product? Are there

competing products?

They're a good marketing machine, who don't show their real color
until the customers are "locked in". They do some very good pre-sales
presentations (the product performance on the sales guy's laptop is
always acceptable, as opposed to many customers' implementations !).
During pre-sales, they don't say anything about the constraints
(database and OS-wise) they will put on the customer afterwards. And
after a company invested millions of dollars in Siebel, they won't get
rid of it because of Siebel's stubberness. Possibly other CRM products
are doing the same, I don't know (I'm a Siebel "specialist ", and don't
know other CRM products).

Daniel

[Hans Forbrich]

Re: Analyzing queries

Daniel Roy wrote:

>

Daniel, what about using the outline feature to provide optimizer
hints without touching the vendor code?

HTH -- Mark D Powell --

>
I heard of a Siebel project where they implemented this suggestion,
and it worked OK. I'm not sure if it was "approved" by Siebel or not.
The problem with Siebel is that for any database change, if the Siebel
guys have not heard of this feature, then instead of saying "That
sounds great, let's try it!", they become defensive and threaten to
not support our Siebel if we implement that change. That creates a
very closed-minded environment, where everyone is scared to do
anything. The only time that they agree to make any change is when the
application is so slow that all the users are complaining non-stop.
>
Daniel

Sounds like they took a page out of the SAP book. "You can't change it
without our permission; you can't discuss it without out permission;
you can't ..."