9 Replies Latest reply: May 28, 2014 8:41 AM by Matperry-Oracle RSS

    Why does my query do full scan on table ...?

    Angelina84

      I have a query with sub-query that should narrow down a search of triples:

       

      DELETE FROM XPROCESS_TPL  t WHERE t.triple.rdf_s_id

      IN

      ( SELECT r.value_id FROM mdsys.rdf_value$ r

        WHERE  r.value_type='UR'   AND  r.vname_suffix LIKE  'ProcessAggregate_Lens Fab%' )

       

      the inner SELECT returns 122707 rows in 41.914 seconds

       

      However, the outer query takes ~6 hours all together...

      What I thought would happen was it should first do the sub-query,get all value_id's and then use index to find data in XPROCESS_TPL

       

      Why is that not happening and how can I optimize this query?

       

      I created index on rdf_s_id:

      CREATE INDEX "SEMANTIC"."XPROCESS_SUB_IDX" ON "SEMANTIC"."XPROCESS_TPL" ("TRIPLE"."RDF_S_ID")

        • 1. Re: Why does my query do full scan on table ...?
          Matperry-Oracle

          Hi,

           

          Can you please post the query plan you get for this query. It will also be helpful to know how many total rows are in the XPROCESS_TPL table. In addition, please make sure to gather stats on the application table (dbms_stats.gather_table_stats()) and semantic network (sem_perf.gather_stats() as sysdba).

           

          Thanks,
          Matt

          • 2. Re: Why does my query do full scan on table ...?
            Angelina84

            What do you mean by query plan?

             

            select count(*) from XPROCESS_TPL ;

            50718080

             

            I asked dba to gathered stats for both and he did.

            • 3. Re: Why does my query do full scan on table ...?
              Matperry-Oracle

              The query plan is the execution plan generated by the query optimizer. One way of viewing the query plan is using explain plan through SQL*Plus:

               

              SQL> explain plan for

              select empno, ename, dname, loc

              from emp e, dept d

              where e.deptno = d.deptno;

                2    3    4

              Explained.

               

              Elapsed: 00:00:00.05

              SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic,parallel,partition,predicate'));

               

               

              PLAN_TABLE_OUTPUT

              ----------------------------------------------------------------------------------------------------------------------------------------------------------------

              Plan hash value: 2219294842

               

              -----------------------------------

              | Id  | Operation          | Name |

              -----------------------------------

              |   0 | SELECT STATEMENT   |      |

              |*  1 |  HASH JOIN         |      |

              |   2 |   TABLE ACCESS FULL| DEPT |

              |   3 |   TABLE ACCESS FULL| EMP  |

              -----------------------------------

               

              Predicate Information (identified by operation id):

              ---------------------------------------------------

               

                 1 - access("E"."DEPTNO"="D"."DEPTNO")

               

              15 rows selected.

               

              Elapsed: 00:00:00.47

              SQL>

              • 4. Re: Why does my query do full scan on table ...?
                Angelina84

                Explained.

                 

                 

                SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic,parallel,partition,predicate'));

                 

                 

                PLAN_TABLE_OUTPUT

                --------------------------------------------------------------------------------

                Plan hash value: 3197374508

                 

                 

                ------------------------------------------------------------------------

                | Id  |         Operation               |        Name         |

                ------------------------------------------------------------------------

                |   0 |   SELECT STATEMENT  |                          |

                |*  1 |   HASH JOIN                 |                          |

                |*  2 |   TABLE ACCESS FULL| RDF_VALUE$     |

                |*  3 |   TABLE ACCESS FULL| XPROCESS_TPL |

                -------------------------------------------

                 

                 

                 

                 

                PLAN_TABLE_OUTPUT

                --------------------------------------------------------------------------------

                Predicate Information (identified by operation id):

                --------------------------------------------------------------------------------

                 

                 

                   1 - access("T"."SYS_NC00004$"="R"."VALUE_ID")

                   2 - filter("R"."VALUE_TYPE"='UR' AND "R"."VNAME_SUFFIX" LIKE

                              'ProcessAggregate_Lens Fab%')

                   3 - filter("T"."SYS_NC00004$">0)

                 

                 

                18 rows selected.

                • 5. Re: Why does my query do full scan on table ...?
                  Matperry-Oracle

                  If you run the query as a SELECT instead of a DELETE, how long does it take and how many rows are returned?

                   

                  SELECT COUNT(*)

                  FROM XPROCESS_TPL  t

                  WHERE t.triple.rdf_s_id

                  IN

                  ( SELECT r.value_id FROM mdsys.rdf_value$ r

                    WHERE  r.value_type='UR'   AND  r.vname_suffix LIKE  'ProcessAggregate_Lens Fab%' )

                  • 6. Re: Why does my query do full scan on table ...?
                    Angelina84

                    Yes...That's what I did yesterday.

                    Someone killed it after 8 hours of running.

                    It never returned anything.

                    • 7. Re: Why does my query do full scan on table ...?
                      Matperry-Oracle

                      Ok. I see.

                       

                      I think the query plan is reasonable. 120,000 rows is a bit too much for index-based nested loop join, so hash join is probably the best approach in this case. Let's try with a smaller result for the inner query:

                       

                      SELECT COUNT(*)

                      FROM XPROCESS_TPL  t

                      WHERE t.triple.rdf_s_id

                      IN

                      ( SELECT r.value_id FROM mdsys.rdf_value$ r

                        WHERE  r.value_type='UR'   AND  r.vname_suffix LIKE  'ProcessAggregate_Lens Fab%'

                                      AND rownum <= 100)

                      • 8. Re: Why does my query do full scan on table ...?
                        Angelina84

                        Yes, that instantly returned  3200 rows.

                        • 9. Re: Why does my query do full scan on table ...?
                          Matperry-Oracle

                          Thanks. Please try increasingly larger values for the inner query and see where it blows up (e.g. 1K, 10K, 25K, etc.), and note any query plan changes for slow vs fast queries.

                           

                          It's also a good idea to check the speed of a full table scan on your application table to see if there are any obvious performance problems there:

                           

                          select max(t.triple.rdf_s_id), max(t.triple.rdf_p_id), max(t.triple.rdf_c_id)

                          from XPROCESS_TPL t;

                           

                          I get about 25 seconds for a 138M row application table on an older desktop machine.