Hello Team,

Database users are complaining that the database is slow.I observed that The select queries are taking time when they are issued by users and most of the queries are spending time in reading index blocks.Also observed "db file sequenial read" as one the top events in awr report.

Almost every query is using the indexes in their execution plan.
All the datafiles are located on the same Drive on windows 2003 R2 server.

Attached the AWR report for 30 min duration.

please suggest options for me to improve the performance.


Vamsi

Hi,

Please take your AWR report and paste it into the analyzer:

http://www.statspackanalyzer.com

Then, report back the results. . . .

what changed between when it was working fine and now?

Sometime back,As it's a documentum application, I have changed optimizer_index_cost_adj to 5 and Cursor_sharing to FORCE from default values as per the recommndations of the EMC to improve the performance.It worked for quite sometime and for the last one week,users are complaining about the slow performance again.Most of the queries are in the format "select count(*)....."....These queries are using the indexes as the above parameter favors for indexes,but taking considerable amount of time.

Some of the tables consist of around 10 million records.I observed the queries which are fired on these tables are dead slow ..taking around 45 min..

Attached the statspack analyzer recommndations.

Vamsi.

Hi Vamsi,

>> It worked for quite sometime and for the last one week,users are complaining about the slow performance again.

So, what changed? SOMETHING HAS CHANGED!

Did you re-analyze your statistics with dbms_stats?

To find the root cause, go back to before the problem and examine stats$sql_plan to see if the execution plans changed:

http://www.rampant-books.com/book_0301_perf.htm

********************************************************
>> I have changed optimizer_index_cost_adj to 5

EMC said this? What release are you on?

Please read this: http://www.dba-oracle.com/oracle_tips_cost_adj.htm

I do not see any attachment with the statspackaalyzer recommendation . ..

Hi Burlson,

-->Did you re-analyze your statistics with dbms_stats?

A weekly job is running from Documentum application itself to analyze the tables.I have not schedlued any separate job for this task.

--->I have changed optimizer_index_cost_adj to 5

Yes. I contacted EMC and they recommnded to set it for 5. The release was Documentum 6.5

Hi,

Your database has 68% direct path reads, parallelized large-table full-table scans, and you are performing more than 816 disk reads per second.

This only hapends when you use parallel query and bypass index access and the SGA:

http://www.dba-oracle.com/m_direct_path_read.htm



Have you accidentially turned-on parallel query?

Hi,

No.I have not turned on parallel query option and also There are no parallel hints in the sql statements that I captured.If parallel query option is enabled,then Oracle should favor full tablescans.Is it correct? It it is correct,then i should see more no of FTS in the execution plans.But I can see index access for almost 95% of statements.

>> But I can see index access for almost 95% of statements.

That is not consistent with the direct path reads.

Index access is "db file sequential reads", and the report does not show this as your isssue.

Your database shows 68% of wait times on direct path reads.

Read this carefully, it explains it all:

http://download-west.oracle.com/docs/cd/B1...e.htm#sthref955

Aha!

Check MOSC for bugs!

There is a big for this exact issue, strange direct path reads, fixed in 11.2 . . .

Hi Burlson,

According to the bug,The db file scattered read events will be refelected as "direct path reads" in 11.1.0.7.It's fine.

how can we confirm this as the reason to the slow behaviour of the database?If it is,the how can I improve performance?

The query which uses existing indices also taking too much time and I can see the "db file sequential read" event in Trace file of the query.

I thought of the below options.

1.Rebuild indexes

2.Move the indexes to tablespace with block size 16K as the db_File_multiblock_read_count is 16k.

I would like to know your suggestion.

Vamsi.

Hi Vamsi,


>> I thought of the below options.

What evidence do you have that your indexs require rebuilding?

*********************************************
>> I would like to know your suggestion.

I already gave you my suggestions! Twice!

Find out why 68% of your waits are on direct path reads!

Did you read the suggestions from statspackanalyzer?

Tuning is neither simple nor trivial, and it could take many hours to disgnosea performance problem on a complex database.

If you want to learn it yourself, I would start with my book:

http://www.rampant-books.com/book_0501_awr...tive_tuning.htm

If you want expert assistance today, just call my staff 800-766-1884 and we can assign a tuning expert to fix this for you.

Good Luck!