Home » RDBMS Server » Performance Tuning » Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g.
Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #299773] Wed, 13 February 2008 02:58 Go to next message
suneelchetty
Messages: 5
Registered: February 2008
Location: bangalore
Junior Member
I am firing a query which uses a table hint /*+ USE_CONCAT */ on a table which 250 million data with 13 indexes on it. The problem is query is taking a lot of time to execute i.e. more than 3 minutes.

Query :

SELECT /*+ USE_CONCAT */ COUNT(*) FROM DI_MATCH_KEY
WHERE NORM_COUNTRY_CD = 'US'
AND ((( NORM_CONAME_KEY1 ='WILM I' OR
NORM_CONAME_KEY2 = 'WILM I' OR NORM_CONAME_KEY23 = 'WILM I'
OR NORM_CONAME_KEYFIRST ='WILLIAM' )
AND NORM_STATE_PROVINCE = 'CA' ) OR NORM_ADDR_KEY2 = 'CALMN 12 3 OSAI')

Indexes for columns on this table are :

1.ADDRESS_SOURCE_CD
2.DUNS_NBR
3.AGN_ID(Primary Key)
4.SOURCE_SYSTEM
5.NORM_ADDR_KEY2, NORM_COUNTRY_CD
6.NORM_CITY, NORM_COUNTRY_CD, NORM_STATE_PROVINCE
7.NORM_CONAME_KEY23, NORM_COUNTRY_CD, NORM_STATE_PROVINCE
8.NORM_CONAME_KEY1, 9.NORM_COUNTRY_CD, .NORM_STATE_PROVINCE
10.NORM_COUNTRY_CD, NORM_STATE_PROVINCE
11.NORM_CONAME_KEYFIRST, NORM_COUNTRY_CD, NORM_STATE_PROVINCE
12.NORM_CONAME_KEY2, NORM_COUNTRY_CD, NORM_STATE_PROVINCE
13.NORM_PHONE_NBR, NORM_COUNTRY_CD

Can anyone help me in this regard to know why it is taking time and what is the use of this table hint
Re: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #299782 is a reply to message #299773] Wed, 13 February 2008 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First remove the hint.

Then read and follow OraFAQ Forum Guide and How to Identify Performance Problem and Bottleneck and provide the requested information.

Also format your post as specified in "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #299965 is a reply to message #299782] Wed, 13 February 2008 20:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post the Explain Plan.

How many rows are returned?

Ross Leishman
Re: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #300182 is a reply to message #299773] Thu, 14 February 2008 08:57 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try rewriting it as UNION:
SELECT SUM(CNTR) FROM
(
SELECT COUNT(*) CNTR FROM DI_MATCH_KEY 
WHERE NORM_COUNTRY_CD = 'US' AND NORM_CONAME_KEY1 ='WILM I' 
 AND NORM_STATE_PROVINCE = 'CA' 
UNION ALL
SELECT COUNT(*) CNTR FROM DI_MATCH_KEY 
WHERE NORM_COUNTRY_CD = 'US' AND NORM_CONAME_KEY2 = 'WILM I' 
 AND NORM_STATE_PROVINCE = 'CA' 
UNION ALL
SELECT COUNT(*) CNTR FROM DI_MATCH_KEY 
WHERE NORM_COUNTRY_CD = 'US' AND NORM_CONAME_KEY23 = 'WILM I'
 AND NORM_STATE_PROVINCE = 'CA' 
UNION ALL
SELECT COUNT(*) CNTR FROM DI_MATCH_KEY 
WHERE NORM_COUNTRY_CD = 'US' AND NORM_CONAME_KEYFIRST ='WILLIAM' 
 AND NORM_STATE_PROVINCE = 'CA' 
UNION ALL
SELECT COUNT(*) CNTR FROM DI_MATCH_KEY 
WHERE NORM_COUNTRY_CD = 'US' AND
   NORM_ADDR_KEY2 = 'CALMN 12 3 OSAI'
) XX


Look at explain.
As I understand - each branch of union is supposed to use a different index.

HTH.
Michael



Re: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #300203 is a reply to message #300182] Thu, 14 February 2008 10:38 Go to previous messageGo to next message
suneelchetty
Messages: 5
Registered: February 2008
Location: bangalore
Junior Member
I have observed the query which u have sent but my concern is that doing a sum of the count giving by union all will not be the result i am expecting b'coz the result will have duplicates so I want the result with out duplicates and the query which u sent ran in a second. Thanks a lot and if u could suggest me the result with out duplicates.

I want the query to give the count with out dupilcates.

Explain plan of this I am enclosing here. Smile

[Updated on: Thu, 14 February 2008 10:47]

Report message to a moderator

Re: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #300210 is a reply to message #299965] Thu, 14 February 2008 10:48 Go to previous messageGo to next message
suneelchetty
Messages: 5
Registered: February 2008
Location: bangalore
Junior Member
I am enclosing explain plan in a zip file.
Re: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #300277 is a reply to message #300210] Thu, 14 February 2008 20:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What columns are in the MK_CY_KEY2_AGN index? Don't guess, look it up in USER_IND_COLUMNS or a GUI schema browser.

And for the second time, how many rows does the SQL return?

Ross Leishman
Re: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #300302 is a reply to message #300277] Thu, 14 February 2008 23:15 Go to previous messageGo to next message
suneelchetty
Messages: 5
Registered: February 2008
Location: bangalore
Junior Member
The column names involved in this index are

MK_CY_KEY2_AGN - NORM_CONAME_KEYFIRST, NORM_COUNTRY_CD, NORM_STATE_PROVINCE.

The explain plan is attached below.
Re: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #300303 is a reply to message #299965] Thu, 14 February 2008 23:16 Go to previous messageGo to next message
suneelchetty
Messages: 5
Registered: February 2008
Location: bangalore
Junior Member
The table contains 260 million data and the number of rows returned is 73914.
Re: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g. [message #300989 is a reply to message #300303] Mon, 18 February 2008 22:42 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So it's returning 74,000 rows in about 3 minutes. How did you measure that? Did you fetch them all in SQL*Plus? Did you process them in a PL/SQL loop? Is that very slow in your environment? Depending on load and capacity, that sort of performance may be "normal".

Tell me how long this takes:

SELECT * FROM (
  SELECT /*+ USE_CONCAT */ COUNT(*) FROM DI_MATCH_KEY 
  WHERE NORM_COUNTRY_CD = 'US' 
  AND ((( NORM_CONAME_KEY1 ='WILM I' OR 
  NORM_CONAME_KEY2 = 'WILM I' OR NORM_CONAME_KEY23 = 'WILM I'
  OR NORM_CONAME_KEYFIRST ='WILLIAM' ) 
  AND NORM_STATE_PROVINCE = 'CA' ) OR NORM_ADDR_KEY2 = 'CALMN 12 3 OSAI')
)
WHERE ROWNUM > 1


This will ACCESS all of the data but return NONE of it. This gives you a benchmark best performance on top of which you will have to add time to fetch, transport, and render/process the rows.

If this is still slow, we tune the SQL. If it is fast, we look at other factors.

Ross Leishman
Previous Topic: Query tunning required
Next Topic: Improving data loading into tables
Goto Forum:
  


Current Time: Sun Dec 04 16:41:36 CST 2016

Total time taken to generate the page: 0.06682 seconds