Unanswered: understanding / proving SQL statement is inefficient
Hi there. just an oracle newbie. have an application that I have to document - built by someone else. it is very very slow so I started to review the sql statements that are being used.
Ran some reports in oracle but I don't understand what I'm reading...
The particular sql statement looks like:
SELECT DTAADMIN.BD_VIEW.SHAPE, DTAADMIN.BD_VIEW.SE_SDO_ROWID FROM DTAADMIN.BD_VIEW WHERE mdsys.sdo_filter(DTAADMIN.BD_VIEW.SHAPE, MDSYS.SDO_GEOMETRY(:gtype1,:srid1,NULL,:elem_info1 ,rdinates1),'querytype=window') = 'TRUE'
Disk Reads Per Execution = 8.136612
Disk Reads = 1489
Buffer Gets = 2304428
These numbers seem pretty large. All that I've done is requested a list of businesses that start with a particular letter...
Can someone confirm that these numbers are pretty high? Is there a good URL that you can point me to regarding Oracle reports / interpreting them?
All that I've done is requested a list of businesses that start with a particular letter...
Not in that query you didn't. You have requested all rows where a complex Oracle Spatial function invocation returns the string 'TRUE'. Unless you have a function-based index corresponding to that function invocation (and I'm not even sure that would be possible in this case), or unless Oracle Spatial does some fancy indexing of its own, that implies a full scan of the table, i.e. as many disk reads as the table has blocks.