Poorly Performing Queries 1

[ddrillich]

Good Day,

The software vendor asked us the following - "What I need to know is whether there is a query or a set of queries that is taking a long time to process. ... I need to you to profile the performance of your database server to see if there are any poorly performing queries that are "smoking guns". "

Where can I find this information in 8.17?

Regards,
Dan

 

[SantaMufasa]

Actually, Dan, Oracle 10g is outfitted to automatically gather statistics for your tables. To ensure that your instance is "on board" for automatic statistics gathering, issue this query:

SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

Also, ensure that the database parameter, "STATISTICS_LEVEL" has a value of either "TYPICAL" or "ALL". You can use this query to check that issue:

col parameter format a20
col value format a10
select name parameter, value from v$parameter
where name = 'statistics_level';

PARAMETER            VALUE
-------------------- ----------
statistics_level     TYPICAL

To manually gather statistics, here is a good invocation from your SQL*Plus prompt:

exec dbms_stats.gather_schema_stats('[i]schema_name[/i]', -
options=>'GATHER', -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
cascade => TRUE)

Let us know about findings and improvement.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Oops...I just noticed that we are in the Oracle 8i forum for this thread. Dan, can you please confirm what version of Oracle we're talking about for your statistics gathering? The version determines what code to use for gathering statistics (...and you certainly don't want to use the syntax for Oracle 8i statistics on an Oracle 10g installation...it works, but it will badly goof up 10g executions.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ddrillich]

Mufasa,

SQL> SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB'
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select name parameter, value from v$parameter where name = 'statistics_level';

no rows selected

I'll try to gather the statistics at night, when usage is low. BTW, we are on 8.1.7.0.

Regards,
Dan

 

[SantaMufasa]

Sorry about the false start...my suggestions, above, are 10g compliant and not available on 8i.

For 8i, you must manually gather statistics. A valid command to gather 8i statistics for a schema (without bringing the instance to its virtual knees) is:

exec dbms_utility.analyze_schema('[i]schema_name[/i]','ESTIMATE',estimate_percent => 10)

Let us know how this goes and how it affects your performance.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

Check your optimizer settings on your database. If you are running rule based, which your application might require, then you do NOT want to gather statistics. Run the following query and paste the results.

select value
from v$parameter
where name = 'optimizer_mode';


Bill
Oracle DBA/Developer
New York State, USA

 

[ddrillich]

SQL> select value from v$parameter where name = 'optimizer_mode';

VALUE
-------------------------------------------------------------------------------

CHOOSE

SQL>

 

[Beilstwh]

What that means is that if you do NOT have statistics, the optimizer will use RULE and if you do have statistics, the optimizer will use Cost Based. Have you gathered your stats yet?

Bill
Oracle DBA/Developer
New York State, USA

 

[ddrillich]

Bill,

So, is it safe to gather the stats? If it is, I'll run it tonight.

Regards,
Dan

 

[Beilstwh]

Absolutely, As a matter of fact, I would setup the stats job as a DBMS_JOB so that it would automatically run every day in the late evening or early morning. There is only one gotcha. If the application is written for rule based optimization, you can bring it to it's knees if you switch to cost based optimizer. If after the statistics are gathered, your response gets worst then use


exec DBMS_STATS.DELETE_SCHEMA_STATS ('SCHEMA_NAME');

to go back to RULE based.

Bill
Oracle DBA/Developer
New York State, USA

 

[ddrillich]

Guys,

You managed to scare me ;-) Now, after applying the indexes, the issue with upload content is resolved. I'm not sure how to proceed....

Regards,
Dan

 

[SantaMufasa]

Before we inadvertenly introduce more fear into the equation, let's first ask you what your fears are and what do you see as your options for proceeding?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[engineer2100]

Finally some light at the end of the tunnel!!!

anyways, since now it is in the safe hands of Santa and Bill, I think i will take a walk out.. Mexico here i come!!!

 

[ddrillich]

Engi,

Thanks a lot and enjoy Mexico!

Cheers,
Dan

 

[ddrillich]

Mufasa,

My fear is that by running the following command, performance might be degraded -

exec dbms_utility.analyze_schema('schema_name','ESTIMATE',estimate_percent => 10)

The options for proceeding – after applying the two indexes, the upload issue got resolved. Therefore, one option is to leave things as they are. Another option is to look closely at the schema as long as it's not experimental performance-wise.


Cheers,
Dan

 

[ddrillich]

Let me ask the vendor whether the application is written for rule based optimization....

 

[ddrillich]

They just got back to me and said that it's not written for rule based optimization. I guess I can run it now.

 

[Beilstwh]

Just a suggestion, gather the statistics only on the schema holding the application. Do not attempt to gather stats in system or sys. They are optimized for rule based in 8i.

Bill
Oracle DBA/Developer
New York State, USA