-
1. Re: Why does my query do full scan on table ...?
Matperry-Oracle May 27, 2014 2:34 PM (in response to Angelina84)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 May 27, 2014 3:45 PM (in response to Matperry-Oracle)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 May 27, 2014 5:35 PM (in response to Angelina84)1 person found this helpfulThe 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 May 27, 2014 6:26 PM (in response to Matperry-Oracle)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 May 27, 2014 8:03 PM (in response to Angelina84)1 person found this helpfulIf 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 May 28, 2014 12:06 PM (in response to Matperry-Oracle)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 May 28, 2014 12:57 PM (in response to Angelina84)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 May 28, 2014 1:22 PM (in response to Matperry-Oracle)Yes, that instantly returned 3200 rows.
-
9. Re: Why does my query do full scan on table ...?
Matperry-Oracle May 28, 2014 1:41 PM (in response to Angelina84)1 person found this helpfulThanks. 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.