Home » RDBMS Server » Performance Tuning » Help in tuning query
Help in tuning query [message #344858] Mon, 01 September 2008 07:55 Go to next message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
SELECT ah.root_agent_id,
SUM (CASE WHEN ld_status_id = 1 THEN 1 ELSE 0 END) stat_new,
SUM (CASE WHEN ld_status_id = 2 THEN 1 ELSE 0 END) stat_open,
SUM (CASE WHEN ld_status_id = 3 THEN 1 ELSE 0 END) stat_fftd,
SUM (CASE WHEN ld_status_id = 4 THEN 1 ELSE 0 END) stat_pres,
SUM (CASE WHEN ld_status_id = 5 THEN 1 ELSE 0 END) stat_posi,
SUM (CASE WHEN ld_status_id = 6 THEN 1 ELSE 0 END) stat_hibe,
SUM (CASE WHEN ld_status_id = 7 THEN 1 ELSE 0 END) stat_nega, SUM (CASE WHEN ld_status_id = 8 THEN 1 ELSE 0 END)stat_esc_new,
SUM (CASE WHEN ld_status_id = 9 THEN 1 ELSE 0 END) stat_esc_open,
SUM (CASE WHEN ld_status_id = 10 THEN 1 ELSE 0 END) stat_esc_fftd,
SUM (CASE WHEN ld_status_id = 11 THEN 1 ELSE 0 END) stat_esc_pres,
SUM (CASE WHEN ld_status_id = 12 THEN 1 ELSE 0 END) stat_ffd,
SUM (CASE WHEN ld_status_id = 13 THEN 1 ELSE 0 END) stat_esc_ffd

FROM (SELECT fn_get_nth_agent_id(hierarchy_path,'.',9) AS root_agent_id,a.channel, a.agent_id
FROM ams_agent_hierarchy a, ams_agent_master b
WHERE a.agent_id = b.agent_id
and a.hierarchy_level >= 9
and b.active = 'A') ah
LEFT OUTER JOIN
ams_lts_lead_details pl
ON ah.agent_id = pl.ld_allocate_to_id
GROUP BY ah.root_agent_id


Explain plan

SELECT STATEMENT CHOOSECost: 1 K Bytes: 5 K Cardinality: 48
6 HASH GROUP BY Cost: 1 K Bytes: 5 K Cardinality: 48
5 HASH JOIN OUTER Cost: 1 K Bytes: 14 M Cardinality: 146 K
3 HASH JOIN Cost: 1 K Bytes: 564 K Cardinality: 6 K
1 TABLE ACCESS FULL TABLE AMS.AMS_AGENT_HIERARCHY Cost: 807 Bytes: 494 K Cardinality: 6 K
2 INDEX FAST FULL SCAN INDEX AMS.IDX_AGENTMASTER_DESIGNATION Cost: 361 Bytes: 2 M Cardinality: 227 K
4 INDEX FAST FULL SCAN INDEX AMS.IDX_LEAD_DTL_ALLOC_TO Cost: 123 Bytes: 2 M Cardinality: 145 K

[Updated on: Mon, 01 September 2008 07:56]

Report message to a moderator

Re: Help in tuning query [message #344859 is a reply to message #344858] Mon, 01 September 2008 07:58 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Please format your code before Posting
http://www.orafaq.com/forum/t/88153/0/

Regards,
Dwarak
Re: Help in tuning query [message #344864 is a reply to message #344859] Mon, 01 September 2008 08:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try it without the user-defined function. If it is much faster, then that function is the problem.

Ross Leishman
Re: Help in tuning query [message #344873 is a reply to message #344858] Mon, 01 September 2008 08:31 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Also review the index on the table ams_agent_hierarchy.
Check if there is scope of adding an index on a.hierarchy_level
so that your search might opt for a index range scan instead of a full table scan.

Regards,
Dwarak.K

Re: Help in tuning query [message #345109 is a reply to message #344873] Tue, 02 September 2008 06:01 Go to previous messageGo to next message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
function is not taking much time...this sql query is called inside loop in plsql procedure for near about 10 times
Re: Help in tuning query [message #345110 is a reply to message #345109] Tue, 02 September 2008 06:04 Go to previous messageGo to next message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
and i have created index on hierarchy_level which helped in reducing cost
Re: Help in tuning query [message #345627 is a reply to message #345110] Thu, 04 September 2008 05:40 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Does that mean now the qry is working fine ? Razz

If not please post the explain plan
Re: Help in tuning query [message #345854 is a reply to message #345627] Fri, 05 September 2008 02:11 Go to previous messageGo to next message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
functon is taking total 12 sec to show the output......can i write it in different way.......

[Updated on: Fri, 05 September 2008 02:24]

Report message to a moderator

Re: Help in tuning query [message #348382 is a reply to message #344858] Tue, 16 September 2008 10:27 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
please listen to rleishman, he seems to be one of the smartest members there.
If he sed to rewrite query without UDF, so do it.
Because using event simple UDF in select it slow down it minimum 10 times.
you can use option PREDICATED in UDF (if UDF is predicated for true) so then performance loss can be minimum.

other thing, please post server details, tables stats, and time of execution.

Re: Help in tuning query [message #348467 is a reply to message #344858] Tue, 16 September 2008 17:43 Go to previous messageGo to next message
satm2008
Messages: 10
Registered: September 2008
Location: Toronto, Canada
Junior Member
Can you post the code of fn_get_nth_agent_id() function?
Just curious, how you wrote that.
Re: Help in tuning query [message #348520 is a reply to message #348467] Wed, 17 September 2008 01:57 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Hi Trupti111,

When you say the function is taking 12 sec to display output
do you mean that the select statement, with the function, now
takes 12 secs - what does this represent/how was it isolated?

I'd shoot for divide-and-conquer here- how does the SELECT within the ah dataset execute alone without the outer join?

I'd like to see the output (and alias for each field); my instinct says to
(1) test the subselect for dataset AH alone, with the function
(2) test the subselect for AH with the outer join without
the function

Just making sure you've truly benchmarked the function's performance separate from AH & The outer join, and separate from the SUM/Group by of the top select.

Does that function just parse out a piece of a string with a position relative to a delimiter? Im not sold on it being the culprit without knowing how its performance was isolated/tested.

How long does this take vs the whole select:
SELECT fn_get_nth_agent_id(hierarchy_path,'.',9) AS root_agent_id,a.channel, a.agent_id
FROM ams_agent_hierarchy a, ams_agent_master b
WHERE a.agent_id = b.agent_id 
and a.hierarchy_level >= 9
and b.active = 'A')
 


I'm wondering about DECODING the id_status_id for your stat fields and summing those results; i'd need to see the output
and know the origin of the non-aliased fields in the query.

Best Regards
Harry

Re: Help in tuning query [message #348782 is a reply to message #348520] Wed, 17 September 2008 23:23 Go to previous message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
hi .... thanks for the concern
that task has been completed....i got new task now
if any problem occurs i will ask for help
thank you

[Updated on: Wed, 17 September 2008 23:25]

Report message to a moderator

Previous Topic: Performance Test: IS NOT NULL check
Next Topic: query performance related
Goto Forum:
  


Current Time: Fri Dec 02 22:53:10 CST 2016

Total time taken to generate the page: 0.08256 seconds