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
>
>
>

I have been working with Clarify in the past. It was developed by people with 0 Oracle experience.
If you say your table has 600 columns, chances are your record doesn't fit in a physical block.
You can easily check this by looking at the statistics. If your db_block_size is lower than 8k you might want to rebuild your database with a 8k block size.
Otherwise I would try to split up this table.

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address