| reducing the parse-execution ration [message #460876] |
Tue, 15 June 2010 05:43  |
akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai
|
Junior Member |
|
|
Hi,
I have a procedure which has a select query with two 'Union All' used in it.
The structure of the query is as follows
select sum(col1) from (select count(distinct col1) col1
from a inner join b on
a.col2=b.col1
inner join c on c.col1 = b.col3
inner join d on d.col1 = c.col2
where ( (a.int_status_id =7)
OR ( (a.int_status_id = 6)
and (sysdate-a.date) =10))
and a.txn_type = 's'
union all
select count(distinct col1) col1
from a inner join b on
a.col2=b.col1
inner join c on c.col1 = b.col3
inner join d on d.col1 = c.col2
where ( (a.int_status_id =7)
OR ( (a.int_status_id = 6)
and (sysdate-a.date) =10))
and a.txn_type = 'x'
union all
select count(distinct col1) col1
from a where a.txn_type = 'Y')
my goal is to reduce the elapse time for this query and this is executed at least 10 each day.
When i rewrite the same query using 'With Clause' the disk read is increased.
While analyzing the explain plan i found parallel executions being taking place and because of it for each execution, the parse_call is 4
that is the parse calls are more than the number of executions.
I tried using the NO_Parallel hint but even then the explain plan uses parallel execution.
Kindly let me know what should i do to reduce the parse calls/avoid parallel execution.
Thanks,
Akilam.
|
|
|
|
| Re: reducing the parse-execution ration [message #460886 is a reply to message #460876] |
Tue, 15 June 2010 06:36  |
akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai
|
Junior Member |
|
|
Formatted the code
SELECT SUM(col1)
FROM (SELECT COUNT(DISTINCT col1) col1
FROM a
inner join b
ON a.col2 = b.col1
inner join c
ON c.col1 = b.col3
inner join d
ON d.col1 = c.col2
WHERE ( ( a.int_status_id = 7 )
OR ( ( a.int_status_id = 6 )
AND ( SYSDATE - a.DATE ) = 10 ) )
AND a.txn_type = 's'
UNION ALL
SELECT COUNT(DISTINCT col1) col1
FROM a
inner join b
ON a.col2 = b.col1
inner join c
ON c.col1 = b.col3
inner join d
ON d.col1 = c.col2
WHERE ( ( a.int_status_id = 7 )
OR ( ( a.int_status_id = 6 )
AND ( SYSDATE - a.DATE ) = 10 ) )
AND a.txn_type = 'x'
UNION ALL
SELECT COUNT(DISTINCT col1) col1
FROM a
WHERE a.txn_type = 'Y') [Updated on: Tue, 15 June 2010 06:49] Report message to a moderator |
|
|
|
| Re: reducing the parse-execution ration [message #460894 is a reply to message #460886] |
Tue, 15 June 2010 07:08 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Is a.col1 unique?
And are you sure this bit is right:
AND ( SYSDATE - a.DATE ) = 10
Since sysdate includes a time component that's going to struggle to find any matches, are you sure you shouldn't be using trunc in there?
|
|
|
|
|
|
| Re: reducing the parse-execution ration [message #460898 is a reply to message #460896] |
Tue, 15 June 2010 07:29 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
In that case can't you rewrite it as:
SELECT SUM(col1)
FROM (SELECT COUNT(DISTINCT col1) col1
FROM a
inner join b
ON a.col2 = b.col1
inner join c
ON c.col1 = b.col3
inner join d
ON d.col1 = c.col2
WHERE (a.int_status_id = 7
OR a.int_status_id = 6 AND ( SYSDATE - a.DATE ) = 10 )
AND a.txn_type IN ('s', 'x')
UNION ALL
SELECT COUNT(*) col1
FROM a
WHERE a.txn_type = 'Y')
Doesn't fix your parallel problem but should reduce the execution time anyway.
|
|
|
|
| Re: reducing the parse-execution ration [message #460901 is a reply to message #460898] |
Tue, 15 June 2010 07:43 |
akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai
|
Junior Member |
|
|
Hi,
I appreciate your effort and sorry for not posting the actual code . i have rwritten the complete code.
Kindly give me your suggestions for this code
SELECT SUM(col1)
FROM (SELECT COUNT(DISTINCT col1) col1
FROM a inner join b ON a.col2 = b.col1
inner join c ON c.col1 = b.col3
inner join d ON d.col1 = c.col2
WHERE ( ( a.int_status_id = 7 )
OR ( ( a.int_status_id = 6 )
AND ( (SYSDATE - a.date) * 24 * 60) >= 10))
AND a.txn_type = 's'
AND ( (c.status IN ('ORDER TAKEN', 'AUTHORISE', 'AUTHORISING')
AND d.status = 'APPROVED')
OR c.status NOT IN ('ORDER TAKEN', 'AUTHORISE', 'AUTHORISING'))
and a.col7 is not null
UNION ALL
SELECT COUNT(DISTINCT col1) col1
FROM a inner join b ON a.col2 = b.col1
inner join c ON c.col1 = b.col3
inner join d ON d.col1 = c.col2
WHERE ( ( a.int_status_id = 7 )
OR ( ( a.int_status_id = 6 ) and
( (SYSDATE - a.date) * 24 * 60) >= 10))
AND a.txn_type = 's'
and d.status = 'APPROVED'
and a.col7 is null
UNION ALL
SELECT COUNT(DISTINCT col1) col1
FROM a
WHERE a.txn_type = 'Y' ( a.int_status_id = 7 ) d [Updated on: Tue, 15 June 2010 07:45] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: reducing the parse-execution ration [message #460907 is a reply to message #460906] |
Tue, 15 June 2010 08:04 |
akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai
|
Junior Member |
|
|
SELECT SUM(col1)
FROM (SELECT COUNT(DISTINCT col1) col1
FROM a inner join b ON a.col2 = b.col1
inner join c ON c.col1 = b.col3
inner join d ON d.col1 = c.col2
WHERE ( ( a.int_status_id = 7 )
OR ( ( a.int_status_id = 6 )
AND ( (SYSDATE - a.date) * 24 * 60) >= 10))
AND a.txn_type = 's'
AND ( (c.status IN ('ORDER TAKEN', 'AUTHORISE', 'AUTHORISING')
AND d.status = 'APPROVED')
OR c.status NOT IN ('ORDER TAKEN', 'AUTHORISE', 'AUTHORISING'))
and a.col7 is not null
UNION ALL
SELECT COUNT(DISTINCT col1) col1
FROM a inner join b ON a.col2 = b.col1
inner join c ON c.col1 = b.col3
inner join d ON d.col1 = c.col2
WHERE ( ( a.int_status_id = 7 )
OR ( ( a.int_status_id = 6 ) and
( (SYSDATE - a.date) * 24 * 60) >= 10))
AND a.txn_type = 's'
and d.status = 'APPROVED'
and a.col7 is null
UNION ALL
SELECT COUNT(DISTINCT col1) col1
FROM a
WHERE a.txn_type = 'Y' and a.int_status_id = 7 ) d |
|
|
|
| Re: reducing the parse-execution ration [message #460912 is a reply to message #460907] |
Tue, 15 June 2010 08:39 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I'm not sure if you're trying to hide your table structure from me or simplify the code in attempt to make my life easier but either way your rewrites keep changing the meaning of the select so I can't meaningfully rewrite it.
For example your first version has a different value for txn_type in each of the 1st two selects (s and x).
Your later rewrites only have one value for txn_type in each of the 1st two selects (s).
That changes the meaning of the select.
If you want me, or anyone else here to have a go at it you need to copy and paste the original select in full and unaltered.
I can tell you the approach I was thinking of so you can have a go yourself:
Basically the first two selects can be combined into one. They're accessing the same tables, with the same joins and very similar where clauses.
The rows from each appear to be mutually exclusive so the COUNT(DISTINCT(col1)) will give the same result when they are combined.
You just need to OR the bits of the where clause that differ between the two.
Past that you can try replacing the joins to b, c and d with an exists sub-query, this may or may not help.
If you do do this then you can replace the COUNT(DISTINCT(col1)) with COUNT(*).
By the way - a select in the from clause isn't referred to as a sub-query, it's called an inline view.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
