This discussion is archived
5 Replies Latest reply: Jan 22, 2013 5:47 AM by Stefan Jager RSS

slow SDO_RELATE operation on a spatial view

Luis A. Paolini Newbie
Currently Being Moderated
Hi Folks,

I need some help to understand what's going on with a spatial query that is taking so long to be executed (about 7 minutes).

I have a spatial view as below:

//////////////////////////////////////////////////////////////////////////////////
create or replace view v_lote_fiscal_relac as
select
lf.objectid,lf.numbloco,lf.setor,lf.quarteirao,lf.origem,lf.tipo,lf.nome,lf.superquadra,lf.quadra,lf.area,lf.observacao,lf.motivo,lf.data,lf.matricula,
vlf.idf_lote,vlf.ind_tipo_lote,vlf.num_lote,vlf.num_seq_lote,vlf.num_setor,vlf.num_quarteirao,vlf.mtr_area_real,vlf.des_tipo_categoria,
lf.geom
from
GEOSMFAGP.Lote_Fiscal lf, IMO_VW_LOTE_FISCAL@POAGEO vlf
where
retorna_num_lote(lf.numbloco) = vlf.num_lote AND
retorna_num_seq_lote(lf.numbloco) = vlf.num_seq_lote;
//////////////////////////////////////////////////////////////////////////////////

The functions retorna_num_lote and retorna_num_seq_lote just do a substr to extract information from lf.numbloco column

//////////////////////////////////////////////////////////////////////////////////
This is the spatial query created by a mapping application

SELECT
OBJECTID
FROM
GEOPONTO.V_LOTE_FISCAL_RELAC T
WHERE
SDO_RELATE(T.GEOM,
SDO_GEOMETRY(2003,1010101,NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(180514.7833451195,1663508.6932339652,
180514.84948680276,1663504.0633161366,
180517.6274375,1663503.9971744534,
180517.69357918325,1663508.4948089155,
180514.7833451195,1663508.6932339652)), 'mask=ANYINTERACT') = 'TRUE'


Please, correct me if I'm wrong, but it looks like the query will first get all the records that satisfy the where clause and then apply the spatial filter on this resultset.
How can it be improved?
I did some tests using SQL Developer (SQL Profiler) and it was possible to create a better execution plan to this query. The time was reduced to 4 seconds.
But the SQL Profile seems to have effect only to the query analyzed. I mean, if a change the sdo_geometry parameter, the query takes 7 minutes again.
Any ideas?

Regards,
Luis
  • 1. Re: slow SDO_RELATE operation on a spatial view
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Luis,
    it looks like the query will first get all the records that satisfy the where clause and then apply the spatial filter on this resultset
    WHich WHERE-clause are you talking about? Your query only has an SDO_RELATE in it's WHERE-clause, and SDO_RELATE will indeed first create a subset, using the MBR's of your records, then have a closer look at the filtering geometry you give it. [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_index_query.htm#i1005329]That is how Oracle´s spatial querying works.
    SDO_GEOMETRY(2003,1010101,NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(180514.7833451195,1663508.6932339652,
    180514.84948680276,1663504.0633161366,
    180517.6274375,1663503.9971744534,
    180517.69357918325,1663508.4948089155,
    180514.7833451195,1663508.6932339652))
    You are using a custom SRID (1010101 - it does not exist in my DB). Is the table using the same SRID? If not, transformation takes place and will use up time.
    I did some tests using SQL Developer (SQL Profiler) and it was possible to create a better execution plan to this query. The time was reduced to 4 seconds.
    But the SQL Profile seems to have effect only to the query analyzed. I mean, if a change the sdo_geometry parameter, the query takes 7 minutes again.>
    What did you change, and how did you improve the query? This would be interesting to know because that is what is slowing down your query :-)
    Have you tried this query using SDO_FILTER? And what was the performance there?

    Lastly, in your view I would not use functions but split what looks like parcel numbers out in the view itself, but that should not have too much of an adverse effect on your query. How many records in your tables? I find 4 seconds quite long still, especially if this query is executed more than once (unless we're talking large tables here: millions of rows).

    Regards,
    Stefan
  • 2. Re: slow SDO_RELATE operation on a spatial view
    Luis A. Paolini Newbie
    Currently Being Moderated
    Hi Stefan,

    1. When I say that the query will first get all the records that satisfy the where clause, i mean the where clause in the View.
    Thus, the view is executed and all the records that satisfy the where clause are returned and then the spatial query is executed upon these records.
    Am I right?

    2. Yes, I'm using a custom SRID but no transformation is taking place, once the geometries stored on the table are based in the same srid.

    3. About SQL Developer -> SQL Profiler, what I did was select the query and ask to SQL Developer to run the SQL Tuning Advisor.
    Applying the SQL Profiler generated by the Tuning Advisor, the query time is improved.
    Tje point is that the Tuning Advisor just shows the new execution plan versus the original one. It does not show how to change the query to
    have the optimized execution plan.

    4. I did not tried the SDO_FILTER, but I'll give it a try.

    Regards,
    Luis
  • 3. Re: slow SDO_RELATE operation on a spatial view
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Luis,
    Luis A. Paolini wrote:
    1. When I say that the query will first get all the records that satisfy the where clause, i mean the where clause in the View.
    Thus, the view is executed and all the records that satisfy the where clause are returned and then the spatial query is executed upon these records.
    Am I right?
    Yes that is correct. If you query a view, you only query the records that the view is giving you. You can't change that, unless you create a query directly on the tables that view is getting it's data from. Which is entirely possible, of course (in this case at least).
    It's probalbly not a bad idea to do an EXPLAIN PLAN on the view, and also on the query. That should give you some hints as to what you can improve. I assume the connection to the other database (the view uses a database link on the second table) is not the problem?
    3. About SQL Developer -> SQL Profiler, what I did was select the query and ask to SQL Developer to run the SQL Tuning Advisor.
    Applying the SQL Profiler generated by the Tuning Advisor, the query time is improved.
    Tje point is that the Tuning Advisor just shows the new execution plan versus the original one. It does not show how to change the query to
    have the optimized execution plan.
    Do you have access to the view? I mean, can you change it? You may try to use UNION ALL or a JOIN, that might improve the performance of the view. Also: do the columns from the where-clause have the correct and valid indexes on them?

    4. I did not tried the SDO_FILTER, but I'll give it a try.
    Be aware that SDO_FILTER does not give as accurate results as SDO_RELATE. SDO_FILTER only looks at the MBR's of the records, not at the actual geometry. This can sometimes cause surprising results, but it is much faster.
  • 4. Re: slow SDO_RELATE operation on a spatial view
    Luis A. Paolini Newbie
    Currently Being Moderated
    Hi Stefan,

    Please, see below the Explain Plan that I mentioned and the SQL Profiler command that makes the query be executed very fast.

    *1- Original*
    -----------
    Plan hash value: 2780585567


    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
    -------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 4808 | 1596K | 25 (0)| 00:00:01 | | |
    | 1 | NESTED LOOPS | | 4808 | 1596K| 25 (0)| 00:00:01 | | |
    | 2 | REMOTE | IMO_VW_LOTE_FISCAL | 25553 | 648K | 25 (0)| 00:00:01 | POAGEO | R->S |
    |* 3 | TABLE ACCESS BY INDEX ROWID| LOTE_FISCAL | 1 | 314 | 25 (0)| 00:00:01 | | |
    |* 4 | DOMAIN INDEX | XSPLOTE_FISCAL_GEO | | | 0 (0)| 00:00:01 | | |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    uery Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------

    1 - SEL$F5BB74E1
    2 - SEL$F5BB74E1 / VLF@SEL$2
    3 - SEL$F5BB74E1 / LF@SEL$2
    4 - SEL$F5BB74E1 / LF@SEL$2

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    3 - filter("VLF"."NUM_LOTE"="RETORNA_NUM_LOTE"("LF"."NUMBLOCO") AND
    "VLF"."NUM_SEQ_LOTE"="RETORNA_NUM_SEQ_LOTE"("LF"."NUMBLOCO"))
    4 - access("MDSYS"."SDO_RELATE"("LF"."GEOM","MDSYS"."SDO_GEOMETRY"(2003,1010101,NULL,"SDO_ELEM_INFO_ARRA
    Y"(1,1003,1),"SDO_ORDINATE_ARRAY"(180514.7833451195,1663508.6932339652,180514.84948680276,1663504.063316136
    6,180517.6274375,1663503.9971744534,180517.69357918325,1663508.4948089155,180514.7833451195,1663508.6932339
    652)),'mask=ANYINTERACT')='TRUE')

    *2- Using SQL Profile*
    --------------------
    Plan hash value: 3617866586

    -------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
    -------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 4808 | 1596K| 485 (1) | 00:00:06 | | |
    |* 1 | HASH JOIN | | 4808 | 1596K| 485 (1) | 00:00:06 | | |
    | 2 | TABLE ACCESS BY INDEX ROWID| LOTE_FISCAL | 1882 | 577K| 459 (1) | 00:00:06 | | |
    |* 3 | DOMAIN INDEX | XSPLOTE_FISCAL_GEO | | | 0 (0) | 00:00:01 | | |
    | 4 | REMOTE | IMO_VW_LOTE_FISCAL | 25553 | 648K| 25 (0) | 00:00:01 | POAGEO | R->S |
    -------------------------------------------------------------------------------------------------------------------

    uery Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------

    1 - SEL$F5BB74E1
    2 - SEL$F5BB74E1 / LF@SEL$2
    3 - SEL$F5BB74E1 / LF@SEL$2
    4 - SEL$F5BB74E1 / VLF@SEL$2

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - access("VLF"."NUM_LOTE"="RETORNA_NUM_LOTE"("LF"."NUMBLOCO") AND
    "VLF"."NUM_SEQ_LOTE"="RETORNA_NUM_SEQ_LOTE"("LF"."NUMBLOCO"))
    3 - access("MDSYS"."SDO_RELATE"("LF"."GEOM","MDSYS"."SDO_GEOMETRY"(2003,1010101,NULL,"SDO_ELEM_INFO_ARRA
    Y"(1,1003,1),"SDO_ORDINATE_ARRAY"(180514.7833451195,1663508.6932339652,180514.84948680276,1663504.063316136
    6,180517.6274375,1663503.9971744534,180517.69357918325,1663508.4948089155,180514.7833451195,1663508.6932339
    652)),'mask=ANYINTERACT')='TRUE')


    Recommendation (estimated benefit: 99.96%)
    ------------------------------------------
    - Consider this SQL Profile:.
    execute dbms_sqltune.accept_sql_profile(task_name => 'staName68255',
    task_owner => 'GEOPONTO', replace => TRUE);

    After the execution o the command above, the same query is executed in 3 seconds.

    P.S: I Cannot change the view, the column is indexed and SDO_FILTER will not work for me, because I need an exact match.

    Regards,
    Luis
  • 5. Re: slow SDO_RELATE operation on a spatial view
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Luis,

    Well, in that case I would recommend using the SQL Profile. If you want to know more about SQL Profiles, I would [url http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:61313086268493]suggest this article from AskTom. If you read this, you'll see why it's a good thing to use an SQL Profile, especially if you have no influence over the views and tables your data is coming from.

    Personally I would consider recreating the view (not using PL/SQL functions - thecontext switching will make it slow), and maybe using a Materialized View for the data from the database link to improve things (aside from using the SQL Profile - but it would need to be re-evaluated after these changes). Since you said you can't change them, the SQL Profile is the best way.

    HTH,
    Stefan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points