 Group By Behaviors [message #407720] |
Thu, 11 June 2009 05:08  |
ykathirv
Messages: 7
Registered: June 2009
|
Junior Member |
|
|
Query - 1
=======
SELECT PRODUCT_TERM_ID,COUNT(1)
FROM MV_GES VG
WHERE ORG_ID = 5
AND GUIDELINE_DEF_NAME = 'Segments'
AND 1 IN (SELECT 1 FROM TMP_PRODUCT STP WHERE STP.PRODUCT_TERM_ID=VG.PRODUCT_TERM_ID)
AND 1 IN (SELECT 1 FROM TMP_SEGMENTATION STS WHERE STS.SEGMENTATION_ID =VG.SEGMENTATION_ID )
group by PRODUCT_TERM_ID
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 2591
SORT GROUP BY NOSORT 1 28 2591
TABLE ACCESS BY INDEX ROWID GMUSR.MV_GES 19 532 23
INDEX RANGE SCAN GMUSR.MV_GES_GUIDELINE_M_PRD_SEG_IND 19 13
TABLE ACCESS FULL GMUSR.TMP_PRODUCT 1 13 2
TABLE ACCESS FULL GMUSR.TMP_SEGMENTATION 1 13 2
-----------------------------------------------------------------
Query - 2
=======
SELECT PRODUCT_TERM_ID
FROM MV_GES VG
WHERE ORG_ID = 5
AND GUIDELINE_DEF_NAME = 'Segments'
AND 1 IN (SELECT 1 FROM TMP_PRODUCT STP WHERE STP.PRODUCT_TERM_ID=VG.PRODUCT_TERM_ID)
AND 1 IN (SELECT 1 FROM TMP_SEGMENTATION STS WHERE STS.SEGMENTATION_ID =VG.SEGMENTATION_ID )
group by PRODUCT_TERM_ID
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 6
HASH GROUP BY 1 54 6
TABLE ACCESS BY INDEX ROWID GMUSR.MV_GES 1 28 1
NESTED LOOPS 1 54 5
MERGE JOIN CARTESIAN 1 26 4
TABLE ACCESS FULL GMUSR.TMP_SEGMENTATION 1 13 2
BUFFER SORT 1 13 2
TABLE ACCESS FULL GMUSR.TMP_PRODUCT 1 13 2
INDEX RANGE SCAN GMUSR.MV_GES_M_PRD_SEG_IND 1 1
------------------------------------------------------
Query - 3
=======
SELECT DISTINCT PRODUCT_TERM_ID
FROM MV_GES_GUIDELINE VG
WHERE ORG_ID = 5
AND GUIDELINE_DEF_NAME = 'Segmented_Guideline'
AND 1 IN (SELECT 1 FROM TMP_PRODUCT STP WHERE STP.PRODUCT_TERM_ID=VG.PRODUCT_TERM_ID)
AND 1 IN (SELECT 1 FROM TMP_SEGMENTATION STS WHERE STS.SEGMENTATION_ID =VG.SEGMENTATION_ID )
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 6
HASH UNIQUE 1 54 6
TABLE ACCESS BY INDEX ROWID GMUSR.MV_GES 1 28 1
NESTED LOOPS 1 54 5
MERGE JOIN CARTESIAN 1 26 4
TABLE ACCESS FULL GMUSR.TMP_SEGMENTATION 1 13 2
BUFFER SORT 1 13 2
TABLE ACCESS FULL GMUSR.TMP_PRODUCT 1 13 2
INDEX RANGE SCAN GMUSR.MV_GES_M_PRD_SEG_IND 1 1
I have tried the three different Queries to get unique Product_Id's for a request.
The Query - 1's Elapsed time was 00:25:29.00
The Query - 2 & Query - 3 is not getting executed and ends up with "User requested cancellation of Current operations"
when you are looking at the Query -1 & Query -2 it is same query only difference is i removed the aggregate function "Count(1)" from the Query.
I am expecting both should behave in the same way. but when i am executing the Queries Quer -1 turns the result set within 2 secs Query - 2 is not yielding any result after hours of execution. why is this behaving like this.
Please help me to find the cause of this issue.
Yoga K.
|
|
|
|
| Re: Group By Behaviors [message #407727 is a reply to message #407720] |
Thu, 11 June 2009 05:42  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Try this:
SELECT PRODUCT_TERM_ID,COUNT(1)
FROM MV_GES VG
JOIN (
SELECT DISTINCT PRODUCT_TERM_ID
FROM TMP_PRODUCT STP
) STP ON STP.PRODUCT_TERM_ID = VG.PRODUCT_TERM_ID
JOIN (
SELECT SEGMENTATION_ID
FROM TMP_SEGMENTATION
) STS ON STS.SEGMENTATION_ID = VG.SEGMENTATION_ID
WHERE ORG_ID = 5
AND GUIDELINE_DEF_NAME = 'Segments'
group by PRODUCT_TERM_ID
Ross Leishman
|
|
|
|
| Re: Group By Behaviors [message #407745 is a reply to message #407727] |
Thu, 11 June 2009 06:47 |
ykathirv
Messages: 7
Registered: June 2009
|
Junior Member |
|
|
Hi,
For the given query the explain plan is
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 8
SORT GROUP BY NOSORT 1 4 8
VIEW 1 4 8
SORT UNIQUE 1 78 8
TABLE ACCESS BY INDEX ROWID GEXPROB3.MV_GES_GUIDELINE 1 40 3
NESTED LOOPS 1 78 7
MERGE JOIN CARTESIAN 1 38 4
TABLE ACCESS FULL GEXPROB3.TMP_PRODUCT 1 13 2
BUFFER SORT 1 25 2
TABLE ACCESS FULL GEXPROB3.TMP_SEGMENTATION 1 25 2
INDEX RANGE SCAN GEXPROB3.MV_GES_GUIDELINE_PROD_SEG_IND 1 2
it is also not yielding any results it is hanging.
My Problem over here is
the below query responds with the result set within minutes but to fetch the entire data set it took 00:25:29.00 secs.
I am ready to live with this timing.
But my actual requirement is to get only unique Product_term_ID's from the table.
There is no need of Count(1) to be included over there. As a work around to resolve the issue i have included. Because then only i am getting the result set soon. In my Java code i am omitting the Count(1) column.
SELECT PRODUCT_TERM_ID,COUNT(1)
FROM MV_GES VG
WHERE ORG_ID = 5
AND GUIDELINE_DEF_NAME = 'Segments'
AND 1 IN (SELECT 1 FROM TMP_PRODUCT STP WHERE STP.PRODUCT_TERM_ID=VG.PRODUCT_TERM_ID)
AND 1 IN (SELECT 1 FROM TMP_SEGMENTATION STS WHERE STS.SEGMENTATION_ID =VG.SEGMENTATION_ID )
group by PRODUCT_TERM_ID
But the below query is not at all responding after hours of execution.
Finally it fails with the message "user requested cancel of current operation "
if you are seeing these queries it is exactly similar, expect the exclusion of Aggregate Function "Count(1)".
SELECT PRODUCT_TERM_ID
FROM MV_GES VG
WHERE ORG_ID = 5
AND GUIDELINE_DEF_NAME = 'Segments'
AND 1 IN (SELECT 1 FROM TMP_PRODUCT STP WHERE STP.PRODUCT_TERM_ID=VG.PRODUCT_TERM_ID)
AND 1 IN (SELECT 1 FROM TMP_SEGMENTATION STS WHERE STS.SEGMENTATION_ID =VG.SEGMENTATION_ID )
group by PRODUCT_TERM_ID
So i would like to know why i am getting this behavior.
Is there anything wrong the way i written the SQL or this is the expected behavior from Oracle when we don't include the Aggregate functions in Group by queries.
If you are seeing the third Query instead of group by i am using Distinct directly on the select statement.
This query is also not yielding any result after hours of execution fails with the message [U]"user requested cancel of current operation " .
SELECT distinct PRODUCT_TERM_ID
FROM MV_GES VG
WHERE ORG_ID = 5
AND GUIDELINE_DEF_NAME = 'Segments'
AND 1 IN (SELECT 1 FROM TMP_PRODUCT STP WHERE STP.PRODUCT_TERM_ID=VG.PRODUCT_TERM_ID)
AND 1 IN (SELECT 1 FROM TMP_SEGMENTATION STS WHERE STS.SEGMENTATION_ID =VG.SEGMENTATION_ID )
[Updated on: Thu, 11 June 2009 06:53] Report message to a moderator |
|
|
|
|
|
| Re: Group By Behaviors [message #407748 is a reply to message #407747] |
Thu, 11 June 2009 06:59 |
ykathirv
Messages: 7
Registered: June 2009
|
Junior Member |
|
|
I have analyzed the table today as part of my Tuning.
And for your information the other two tables(TMP_SEGMENTATION, TMP_PRODUCT) are Global temporary tables.
LAST_ANALYZED - NUM_ROWS - TABLE_NAME
11-JUN-2009 - 2428337 - MV_GES
|
|
|
|
| Re: Group By Behaviors [message #407757 is a reply to message #407748] |
Thu, 11 June 2009 07:40 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Ahhh -
1) how many rows are there in the Temporary tables
2) do they have any indexes on them
3) How selective are the Product_Term_id and Segmentation_id in the Temporary tables
|
|
|
|
| Re: Group By Behaviors [message #407759 is a reply to message #407757] |
Thu, 11 June 2009 07:50 |
ykathirv
Messages: 7
Registered: June 2009
|
Junior Member |
|
|
TMP_PRODUCT table has around 2.4 Lac records and TMP_SEGMENTATION table has 5K records.
MV_GES table has an index on ORG_ID,PRODUCT_TERM_ID,SEGMENTATION_ID. Temporary table doesn't have any index defined as it is a single column table.
Both the tables are having only one column PRODUCT_TERM_ID and SEGMENTATION_ID.
TMP_PRODUCT is @ the grain of PRODUCT_TERM_ID
TMP_SEGMENTATION is @ the grain of SEGMENTATION_ID.
|
|
|
|
| Re: Group By Behaviors [message #407762 is a reply to message #407759] |
Thu, 11 June 2009 08:06 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Try sticking indexes on the two temporary table - that way the CBO will be able to access each row as an index scan, or possibly an index unique scan, rather than a full table scan.
| Quote: | TMP_PRODUCT is @ the grain of PRODUCT_TERM_ID
TMP_SEGMENTATION is @ the grain of SEGMENTATION_ID.
|
This is a bit of a tautology.
Let's rephrase the question - will there be multiple rows in the temporary tables with the same segmentation/product_term id, or are the Ids unique.
If the ids are unique, then make the indexes on the tables unique, and add a unique constraint as well.
|
|
|
|
| Re: Group By Behaviors [message #407864 is a reply to message #407762] |
Fri, 12 June 2009 01:15 |
ykathirv
Messages: 7
Registered: June 2009
|
Junior Member |
|
|
TMP_PRODUCT table has only one column called product_term_ID,
TMP_SEGMENTATION table has only one column called segmentation_ID.
Even we tried the query by creating the Index on temporary tables that didn't work.
I am having only one question over here why are these queries behaving indifferently even though it is same
Query -1
========
SELECT PRODUCT_TERM_ID,COUNT(1)
FROM MV_GES VG
WHERE ORG_ID = 5
AND GUIDELINE_DEF_NAME = 'Segments'
AND 1 IN (SELECT 1 FROM TMP_PRODUCT STP WHERE STP.PRODUCT_TERM_ID=VG.PRODUCT_TERM_ID)
AND 1 IN (SELECT 1 FROM TMP_SEGMENTATION STS WHERE STS.SEGMENTATION_ID =VG.SEGMENTATION_ID )
group by PRODUCT_TERM_ID
Query -2
=========
SELECT PRODUCT_TERM_ID
FROM MV_GES VG
WHERE ORG_ID = 5
AND GUIDELINE_DEF_NAME = 'Segments'
AND 1 IN (SELECT 1 FROM TMP_PRODUCT STP WHERE STP.PRODUCT_TERM_ID=VG.PRODUCT_TERM_ID)
AND 1 IN (SELECT 1 FROM TMP_SEGMENTATION STS WHERE STS.SEGMENTATION_ID =VG.SEGMENTATION_ID )
group by PRODUCT_TERM_ID |
|
|
|
| Re: Group By Behaviors [message #407886 is a reply to message #407864] |
Fri, 12 June 2009 03:55 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The queries are different because they aren't the same.
One of them requires a list of product ids, and the other requires a list of product ids and a count of how many of each there were - these are different processes.
What is OPTIMIZER_DYNAMIC_SAMPLING & OPTIMIZER_FEATURES_ENABLE set to on your system.
What does this query return:
select table_name
,last_analyzed
,num_rows
,blocks
from all_tables
where table_name in ('TMP_PRODUCT','TMP_SEGMENTATION');
And if you don't want help in tuning the queries, then why did you spend time complaining about how long they take?
|
|
|
|
| Re: Group By Behaviors [message #407895 is a reply to message #407886] |
Fri, 12 June 2009 04:23 |
ykathirv
Messages: 7
Registered: June 2009
|
Junior Member |
|
|
starting point itself i have told that the below query
SELECT PRODUCT_TERM_ID
FROM MV_GES VG
WHERE ORG_ID = 5
AND GUIDELINE_DEF_NAME = 'Segments'
AND 1 IN (SELECT 1 FROM TMP_PRODUCT STP WHERE STP.PRODUCT_TERM_ID=VG.PRODUCT_TERM_ID)
AND 1 IN (SELECT 1 FROM TMP_SEGMENTATION STS WHERE STS.SEGMENTATION_ID =VG.SEGMENTATION_ID )
group by PRODUCT_TERM_ID
is not returning the result set.
As a Workaround I have added the Count(1) then it is responding very Quickly.
SELECT PRODUCT_TERM_ID,COUNT(1)
FROM MV_GES VG
WHERE ORG_ID = 5
AND GUIDELINE_DEF_NAME = 'Segments'
AND 1 IN (SELECT 1 FROM TMP_PRODUCT STP WHERE STP.PRODUCT_TERM_ID=VG.PRODUCT_TERM_ID)
AND 1 IN (SELECT 1 FROM TMP_SEGMENTATION STS WHERE STS.SEGMENTATION_ID =VG.SEGMENTATION_ID )
group by PRODUCT_TERM_ID
when you are seeing the Queries both are grouped by PRODUCT_TERM_ID. in the second query i am giving the additional task of counting based on the product termID.
but in the first query i am giving only one task that group it based on PRODUCT TERM ID.
if you are seeing the requirement wise i accept that it is 2 different queries. but the purpose of those 2 queries is to get the Unique PRODUCT TERM ID's in my case.
Hope it clarifies.
I want to know why group by is behaving this way.
Even though it is grouping based on the same column why performance wise it is differing.
|
|
|
|
| Re: Group By Behaviors [message #407896 is a reply to message #407895] |
Fri, 12 June 2009 04:37 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Tautology time:
The query execution time is different because the execution paths are different.
The Execution paths are different because the queries are different.
| Quote: | What is OPTIMIZER_DYNAMIC_SAMPLING & OPTIMIZER_FEATURES_ENABLE set to on your system.
What does this query return:
select table_name
,last_analyzed
,num_rows
,blocks
from all_tables
where table_name in ('TMP_PRODUCT','TMP_SEGMENTATION');
|
|
|
|
|
| Re: Group By Behaviors [message #407903 is a reply to message #407896] |
Fri, 12 June 2009 04:53 |
ykathirv
Messages: 7
Registered: June 2009
|
Junior Member |
|
|
The result of the given query is
| Quote: |
TABLE_NAME LAST_ANALYZED NUM_ROWS BLOCKS
TMP_PRODUCT 6/11/2009 3:12:40 PM 0 0
TMP_SEGMENTATION 6/11/2009 3:12:41 PM 0 0
MV_GES 6/11/2009 3:12:40 PM 2428337 54977
|
both the table are global temporary table with ON COMMIT DELETE ROWS option enabled.
SELECT NAME||' - '||VALUE FROM v$parameter WHERE UPPER(NAME) LIKE 'OPTIMIZER%'
following is the result of the above Query
| Quote: | NAME - VALUE
optimizer_features_enable - 10.2.0.4
optimizer_mode - ALL_ROWS
optimizer_index_cost_adj - 100
optimizer_index_caching - 0
optimizer_dynamic_sampling - 2
optimizer_secure_view_merging - TRUE
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
