OraFAQ Forum: Performance Tuning » The time of 1 Query is more than 1 hour, HELP Â¡Â¡Â¡

Home » RDBMS Server » Performance Tuning » The time of 1 Query is more than 1 hour, HELP Â¡Â¡Â¡

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

The time of 1 Query is more than 1 hour, HELP Â¡Â¡Â¡ [message #137977]

Mon, 19 September 2005 04:24

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.

Report message to a moderator

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

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?

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 17:34:37 CST 2024