The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
Oracle performance question - why does a query filter of "where DAYS_BETWEEN between -10 and 10" take so long?
When I run a medium complex query with one inner join (and 2 unions), data returns in under 5s, 483 rows. One of the key pieces of info is "Days Between" (the end of one trip and the beginning of another). (The entire data set is small, as are the base tables)
But when I query the whole thing and say "where days_between_trips between -10 and 10", the query never returns !
The problem is a person taking four trips with a short break between trips, trying to get days_between each trip.
So this works great, 483 rows, neat and tidy
select Trip #, Trip_Info, Next_Trip_Info, days_between from (
--
select Trip_#, Trip_1_Info, Trip_2_Info,
date_2_start - date_1_end as days_between
from table_1_vw a
inner join table_2_vw b on a.user_id = b.user_id
--
union
--
select Trip_#, Trip_2_Info, Trip_3_Info,
date_3_start - date_2_end as days_between
from table_2_vw b
inner join table_3_vw c on b.user_id = c.user_id
--
union
--
select Trip_#, Trip_3_Info, Trip_4_Info,
date_4_start - date_3_end as days_between
from table_3_vw c
inner join table_4_vw d on d.user_id = c.user_id
)
--
where trip_year = [current_year]
and trip_status = 'ACTIVE
***
-------------
but when I add
*** --> "and days_between between -10 and 10", the query never returns.
I figured the data would be pulled into memory (483 rows) and then it's a simple matter to take this and filter out those rows not meeting the days_between criteria.
Why am I seeing this scenario ?
It's a enterprise system, everything seems good, etc.
But when I query the whole thing and say "where days_between_trips between -10 and 10", the query never returns !
The problem is a person taking four trips with a short break between trips, trying to get days_between each trip.
So this works great, 483 rows, neat and tidy
select Trip #, Trip_Info, Next_Trip_Info, days_between from (
--
select Trip_#, Trip_1_Info, Trip_2_Info,
date_2_start - date_1_end as days_between
from table_1_vw a
inner join table_2_vw b on a.user_id = b.user_id
--
union
--
select Trip_#, Trip_2_Info, Trip_3_Info,
date_3_start - date_2_end as days_between
from table_2_vw b
inner join table_3_vw c on b.user_id = c.user_id
--
union
--
select Trip_#, Trip_3_Info, Trip_4_Info,
date_4_start - date_3_end as days_between
from table_3_vw c
inner join table_4_vw d on d.user_id = c.user_id
)
--
where trip_year = [current_year]
and trip_status = 'ACTIVE
***
-------------
but when I add
*** --> "and days_between between -10 and 10", the query never returns.
I figured the data would be pulled into memory (483 rows) and then it's a simple matter to take this and filter out those rows not meeting the days_between criteria.
Why am I seeing this scenario ?
It's a enterprise system, everything seems good, etc.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
If the above scenario is not what you are seeing, then please post dbms_xplan.display output (text, not screen capture images) of the plans of both versions of the query.
sdstuber, thanks !
it's been a while since I've done Explain Plan, so I will have to dust that off Monday . . . :-)
I was just curious on this, it does look like it's being pushed back to the view, which calls a function which does this and that.
I was thinking it get the data set first and then apply the criteria, but apparently not !
I do need to get our performance awareness upgraded, but with our primary table having 30,000 records, no one bothers about it . . .
it's been a while since I've done Explain Plan, so I will have to dust that off Monday . . . :-)
I was just curious on this, it does look like it's being pushed back to the view, which calls a function which does this and that.
I was thinking it get the data set first and then apply the criteria, but apparently not !
I do need to get our performance awareness upgraded, but with our primary table having 30,000 records, no one bothers about it . . .
you might be able to use a WITH clause an materialize hint
that might look something like this...
that might look something like this...
WITH x
AS (SELECT /*+ MATERIALIZE */
*
FROM (SELECT trip_#,
trip_1_info,
trip_2_info,
date_2_start - date_1_end AS days_between
FROM table_1_vw a INNER JOIN table_2_vw b ON a.user_id = b.user_id
--
UNION
--
SELECT trip_#,
trip_2_info,
trip_3_info,
date_3_start - date_2_end AS days_between
FROM table_2_vw b INNER JOIN table_3_vw c ON b.user_id = c.user_id
--
UNION
--
SELECT trip_#,
trip_3_info,
trip_4_info,
date_4_start - date_3_end AS days_between
FROM table_3_vw c INNER JOIN table_4_vw d ON d.user_id = c.user_id))
SELECT trip #,
trip_info,
next_trip_info,
days_between
FROM x
WHERE trip_year = :current_year AND trip_status = 'ACTIVE' AND days_between BETWEEN -10 AND 10
I tried the /*+ MATERIALIZE */ today and it seems to work ! I'll have to talk the whole thing over with the DBA.
I did the EXPLAIN Plan with and without "days_between between -10 and 10". They're somewhat the same but WITH "days_between" it actually looks cleaner, see attachments.
What I DON'T see is the EXPLAIN PLAN for a function that is called from the view(s). The function is used for the "end date trip 1" and "start date trip 2" which is where days_between comes from. The function does a complex query that calls 3 other tables that are not listed, PLUS 2 tables also in the main query, BUT it sure looks like the function is NOT included in the Explain Plan. And so for every record it's jumping through hoops via the function to get the days_between.
In the EXPLAIN PLAN there is one Table Access Full (ST_ASSIGNMENTS) although there's also a line that uses RowId Index for ST_ASSIGNMENTS. ST_ASSIGNMENTS has 150,000.
I just ran the query again with the "days_between" criteria, it didn't come back after 30 minutes.
Explain-Plan-1.png
Explain-Plan-2.png
I did the EXPLAIN Plan with and without "days_between between -10 and 10". They're somewhat the same but WITH "days_between" it actually looks cleaner, see attachments.
What I DON'T see is the EXPLAIN PLAN for a function that is called from the view(s). The function is used for the "end date trip 1" and "start date trip 2" which is where days_between comes from. The function does a complex query that calls 3 other tables that are not listed, PLUS 2 tables also in the main query, BUT it sure looks like the function is NOT included in the Explain Plan. And so for every record it's jumping through hoops via the function to get the days_between.
In the EXPLAIN PLAN there is one Table Access Full (ST_ASSIGNMENTS) although there's also a line that uses RowId Index for ST_ASSIGNMENTS. ST_ASSIGNMENTS has 150,000.
I just ran the query again with the "days_between" criteria, it didn't come back after 30 minutes.
Explain-Plan-1.png
Explain-Plan-2.png
Attached is the Explain Plan for the function. It looks clean but it never returns with the "days_between" criteria.
Explain-Plan-3.PNG
Explain-Plan-3.PNG
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - PowerPoint 2016… 224 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Word 2016 Part … 125 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I think what you'll find is the new condition is being pushed into at least one of the unions of the inline view, possibly all of them.
The optimizer "thinks" it will be a helpful condition to prune the results.
But instead, it's taking a longer path. This could be due to invalid/stale statistics.