Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003
    Posts
    107

    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?

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by dotolee
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •