Slow query on SDE 9.3 spatial view

[MichaelGinzburg]

Hello,

I have a spatial view created with sdtable -o create_view.

The view joins multiple tables, all join fields in all tables are indexed.

For some reason Identify on the view is very slow.

Digging down to ADF code, I found that query.Identify is very slow.

Can't find the reason.

The server has a plenty of resources.

Oracle has 4Gb of memory.

ArcGIS Sever 9.3.1, ArcSDE 9.3.1, ADF

Oracle 11g

Thanks for any insight.

[BirajaNayak]

Hi Michael,

How you are accessing SDE view. Is it through ArcMAP 9.3.1 or any other application.

Can you share sde client intercept and database trace to show the slow query and sde command for performance.

Is performance observed during "identify" operation only. How the view performance during loading, pan and Zoom.

Is there any other view having same behavior or only specific to the view in the question. Is the performance observed on a particular client box or particular user access or observed at a particular time of the day.

Is this view newly created. If not, then when you started observing performance.

Is this view content any geometry. If yes, what type of geometry.  Also provide how many attributes fields and total number of rows this view has. How many tables are involved in creation of this view. 

Thanks,

Biraja