Re: Database Tuning Issue or Redesign ??

Hi Mark,

thanks for taking the time to read and try to help me out with this issue. Here is a little more background information to this issue.

We are using Clarify Version 6 here at Stratex to do the job of managing our repair centres globally. I recently developed and put live quite a large extention to this application to capture all of the technicians repair comments and to audit the tests they do whilst repairing all incoming equipment. Once the forms are filled in and saved to the database (No performance issues here) the technicians require to print the forms off. How Clarify achieves this is to pass a number or parameters to an external application (Called Actuate ) using a number of windows API calls.

What I discovered through a little bit of digging through SQL trace files is that ACTUATE currently selects all 600 plus fields from the database (Was a surprise to me !!!) but only displays as many as 80 fields on the largest form.

From this discussion thread it is clear that this may not be an easy scenario to resolve based on the width of the table. So I had a long think about this on the weekend and decided that the best way to resolve this initially was to create a VIEW for each of the 40 or so forms rather than doing a direct select from the table.
Using this view within Actuate to select all of the valid fields rather than using the complete table.I tested this idea out this morning and everything is looking good. The select statement returns the result really quickly and the response issue at the ACTUATE end seems to have no gone away.

I would like to read up a little bit more in this area so that I will not make the same mistakes again in the future. Can you provide me any URL's or pointers so I can better understanding what you were saying in your email concerning multiple segments,chaining multiple I/O's etc.

Thanks once again for your help in this matter

regards

Tony Rees
Stratex Networks

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:2687bb95.0306060518.5bc64c0b_at_posting.google.com...
> "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 --

"Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message news:5450ev8rob0ptvqr512aevdagk45kv7t3t_at_4ax.com...
> On Fri, 06 Jun 2003 00:34:05 GMT, "Jim Kennedy"
> <kennedy-down_with_spammers_at_attbi.com> wrote:
>
> >Sorry, can you run it through tkprof so we can see the more human
readable
> >output.
> >Jim
> >
> >--
>
>
> Jim, there is nothing wrong with the query. He would either better run
> a 10046 trace or following my suggestion and check whether a single
> record fits in 1 physical block. My guess is it doesn't, so row
> chaining will explain his performance problem.
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Mon Jun 09 2003 - 08:57:28 CDT