Re: Tuning Issue or Redevelopment Issue ???

"Tony Rees" <antonie.rees_at_btinternet.com> wrote in message news:<bbndoq$4br$1$830fa7a5_at_news.demon.co.uk>...
> Dear Oracle Gurus,
>
> I have in the last couple of months been working on a project to trap all
> techniciam test data onto an Oracle 817 database. I am using an AMDOCS
> formerly Clarify application. Here is the problem I am now facing:-
>
> When doing a simple select from a table it is taking ages to return the data
> back. Here is the statement
>
> select /*+rule*/
> * from table_x_audit_hdr where x_detail_number = 'RMA-61214-1'
>
> I have created relevant indexes and analysed both tables and indexes and by
> looking at the plan for the above statement it is deffinately using the
> indexes which have been refreshed. There are only 1200 records in the above
> table so this statement should be working really quickly.
>
> The Table that I had to create has just over 600 fields in it !!!. My DBA's
> in the States (I am in the UK) have said that I should redesign the
> application and try to get the size of the table right down which i have
> come up with a plan that will take 6 weeks to redevelop that will bring the
> size of the table down to just over a 100 records.
>
> If I do a similar select from another table with only 100 fields in it but
> with 116256 rows of data the select statement is coming back really quickly
> ie
>
> select /*+rule*/
> * from table_demand)dtl where detail_number = 'RMA-61214-1'
>
> Are there any memory parameters I should be looking at to get this to work
> quicker or would I be better in taking my DBA's advise and redeveloping the
> whole app to have only 100 fields in it ?????? Or is there some other thing
> I can not think of to fix this.
>
> Any help and advise would be great as I have been trying to come up with a
> resolve to this for weeeks now
>
> regards
>
> Frustrated developer
>
> Tony

Tony, if you remove the rule hint and let the CBO at the SQL does the plan change? Is there more than one index that has detail_number as the leading column? How many rows are there per detail number?

Since the rows have more than 255 column Oracle is going to store the rows in multiple segments. So a row with more than (about) 510 columns will take 3 logical IO to access. If the rows are physically long and the block size is small there is a good chance that row migration and possibly chaining has taken place. This could greatly increase the number of IO necessary to access the data. What are the avg_row_len, chain_cnt, and num_rows values for this table?

If you populate index_stats for the index being used what percentage of the
index entries are held by deleted rows?

There is a chance that if the entire row data is not inserted at one time that significant row chaining has occurred over time and a reorg might actually have a noticable effect on query performance. Normally a reorg does not have a noticable performance impact, but depending on the answers to the above questions it might be worth a shot.

HTH -- Mark D Powell -- Received on Fri Jun 06 2003 - 08:18:51 CDT