Performance issues arcsde 10.1 on ST_GEOMETRY selects

[irishadar]

Hi  we'v installed arcsde 10.1 in solaris 10 with oracle 11.2.0.3.
The machine has 2 cpu and 16 G memory using netapp for storage.
We run a simple select using 2 polygon layers one has 800k features and the other 30k features.
Both layer are well indexed. We use to get 0.068sec for this select but now we gets 1.5sec, in any case there is a distinct time difference between the first and the second time 0.5 sec.

These are the trace from both times, can someone see the problem?

First time (line256):

select a.ata_id,a.ata_tat_id, b.gush_num, b.gush_suffix,b.parcel,b.status
FROM gis_atar1 a, gis_parcel1 b WHERE sde.ST_Intersects(b.shape,
  sde.ST_Buffer(a.shape,0)) =1 and a.ata_id=567

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.26       0.25          0       1297          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.03       0.06          1         38          0          26
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.29       0.32          1       1335          0          26

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100 
Number of plan statistics captured: 1

Second time(line 43):

select a.ata_id,a.ata_tat_id, b.gush_num, b.gush_suffix,b.parcel,b.status
FROM gis_atar1 a, gis_parcel1 b WHERE sde.ST_Intersects(b.shape,
  sde.ST_Buffer(a.shape,0)) =1 and a.ata_id=567

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.03       0.05          0         38          0          26
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.03       0.05          0         38          0          26

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 100 
Number of plan statistics captured: 1

thanks for the help
iris hadar

[anthonysanchez]

Can you please post the execution plans as well?

[irishadar]

couldn't find the execution plan part.
should I run something else beside the trace 10046 level8?

[irishadar]

Sorry about the slowness I'm not DBA I'm GIS ADMINISTRATOR.
In any case this the EXPLAIN PLAN and is identical both time i run it, it even gets the same Plan Hash Value

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3387874830                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                            
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                  
| Id  | Operation                                | Name              | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                           
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                  
|   0 | SELECT STATEMENT                         |                   |  8564 |    21M|     4   (0)| 00:00:01 |                                                                                                                                                            
|   1 |  NESTED LOOPS                            |                   |  8564 |    21M|     4   (0)| 00:00:01 |                                                                                                                                                                  
|   2 |   TABLE ACCESS BY INDEX ROWID            | GIS_ATAR1         |     1 |   240 |     2   (0)| 00:00:01 |                                                                                                                                      
|*  3 |    INDEX RANGE SCAN                      | GIS_ATAR1_ATA_IDX |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                           
|   4 |   TABLE ACCESS BY INDEX ROWID            | GIS_PARCEL1       |  8348 |    18M|     4   (0)| 00:00:01 |                                                                                                                                 
|*  5 |    DOMAIN INDEX (Sel: Default - No Stats)| A11_IX1           |       |       |    18E  (0)|          |                                                                                                                                          
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                  
                                                                                                                                                                                                                                                                                                            
Predicate Information (identified by operation id):                                                                                                                                                                                        
---------------------------------------------------                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                            
   3 - access("A"."ATA_ID"=567)                                                                                                                                                                                                                                                  
   5 - access("SDE"."ST_INTERSECTS"("B"."SHAPE","SDE"."ST_BUFFER"("A"."SHAPE",0))=1)                                                                                                                                   
                                                                                                                                                                                                                                                                                                            
Note                                                                                                                                                                                                                                                                                                  
-----                                                                                                                                                                                                                                                                                                     
   - dynamic sampling used for this statement (level=2)                                                                                                                                                                                      

22 rows selected