Home » RDBMS Server » Performance Tuning » The time of 1 Query is more than 1 hour, HELP ¡¡¡
icon9.gif  The time of 1 Query is more than 1 hour, HELP ¡¡¡ [message #137977] Mon, 19 September 2005 04:24 Go to next message
IngRMP
Messages: 7
Registered: September 2005
Location: Spain
Junior Member
Hi everyone,

We have some problems with the next query:

explain plan for
SELECT distinct
b.serverid,
a.VisitID,
a.Views,
a.FirstViewDate,
date_to_nttime(a.FirstViewDateTime) as FirstViewDateTime,
a.FirstViewDOW,a.EntryPageID,
a.EntryPagebreakdownid,
a.ExitPageID,
a.exitpagebreakdownid,
a.FirstVisit
FROM reports_Visits a, l_reports_views_orden b
WHERE a.VisitID NOT IN (SELECT VisitID FROM F_PATHS)
and a.VISITID = b.VISITID(+)

Indexes:

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."VISITID"="B"."VISITID"(+))
5 - access("A"."VISITID"="F_PATHS"."VISITID")

The performance is worst, because Oracle decided to use a FULL SCAN instead of our INDEXES. The result of the explain plan is the next.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 682K| 39M| | 6107 |
| 1 | SORT UNIQUE | | 682K| 39M| 93M| 6107 |
|* 2 | HASH JOIN OUTER | | 682K| 39M| 6976K| 2673 |
| 3 | NESTED LOOPS ANTI | | 111K| 5661K| | 953 |
| 4 | TABLE ACCESS FULL | REPORTS_VISITS | 1259K| 55M| | 953 |
|* 5 | INDEX UNIQUE SCAN | SYS_C007940 | 1046K| 6130K| | |
| 6 | INDEX FAST FULL SCAN| LORD_VW_SEVIVIDIDX | 6330K| 54M| | 4 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------

Please help us, we want to improve the functioning.

Thanks.
Re: The time of 1 Query is more than 1 hour, HELP ¡¡¡ [message #138027 is a reply to message #137977] Mon, 19 September 2005 08:05 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Have statistics for the three tables involved been collected recently?

What happens if you use NOT EXISTS instead of NOT IN?
SELECT
DISTINCT b.serverid
,        a.visitid
,        a.views
,        a.firstviewdate
,        date_to_nttime(a.firstviewdatetime) AS firstviewdatetime
,        a.firstviewdow
,        a.entrypageid
,        a.entrypagebreakdownid
,        a.exitpageid
,        a.exitpagebreakdownid
,        a.firstvisit
FROM     reports_visits         a
,        l_reports_views_orden  b
WHERE    NOT EXISTS (SELECT NULL
                     FROM   f_paths         fp
                     WHERE  fp.visitid = a.visitid)
AND      a.visitid = b.visitid (+)
/
You may also see performance improvements if you express the logic in the DATE_TO_NTTIME function as straight SQL.

Have you analyzed waits? What is it you're waiting for?

Previous Topic: Difference in retrieving number of rows between two queries
Next Topic: how to increase search in the database
Goto Forum:
  


Current Time: Fri Dec 09 13:39:49 CST 2022