Home » RDBMS Server » Performance Tuning » Group By Behaviors (Oracle Database 10g 10.2.0.4.0 - 64bit, UNIX)
icon5.gif  Group By Behaviors [message #407720] Thu, 11 June 2009 05:08 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #407747 is a reply to message #407745] Thu, 11 June 2009 06:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just a thought - how up to date are the statistics on the tables?
Re: Group By Behaviors [message #407748 is a reply to message #407747] Thu, 11 June 2009 06:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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



Previous Topic: HINT INDEX Usage
Next Topic: How to tune latch: cache buffers chains
Goto Forum:
  


Current Time: Sun Dec 04 16:43:06 CST 2016

Total time taken to generate the page: 0.09013 seconds