Wierd performance impact

From: shankar <sasubram_at_cisco.com>
Date: 29 Apr 2003 18:53:47 -0700
Message-ID: <b7616278.0304291753.779d8f5f_at_posting.google.com>

Hello all,
[Quoted] I have a function that accepts a parameter (email_id). Then there is a cursor which has a select using the email_id in the where clause. In this case, it takes 1 minutes to run the function for certain scenario.

But if I hard code the email in the select, it runs in mili-secs.

Why?

---

First scenario:
CREATE OR REPLACE function san_test (p_email IN varchar2) return VA_share_Assign_For_Scheduler Is Cursor shr_Assign is
Select * from (

	Select e.node_Id, e.status_Name, e.email,
	 e.structure_Node_Name||' '||e.email position_Name,
	to_number(null) team_Agent_Id, null teamAgent
	from  shr_Emp_Assignments_V e
	where e.status_name in ('ACTIVE', 'CONFIRMED')
	and e.assignment_obsolete_Flag = 'N'
	and e.job_category in (


						'Account Manager',

						'Global Account Mgr',

						'Major Account Mgr',

						'Service account manager',

						'Channel Account Manager',

						'Sales Manager',

						'Sales Representatives',					

						'Temporary',

						'Area & Regional Mgr',


						'Services Mgr')


	and ( (e.assignment_End_date Is null) OR 
		  (trunc(e.assignment_end_Date) > trunc(sysdate-1))
		 ) 
	and e.email = p_email--'chwoodfi'

UNION ALL
Select team.node_id, team.status_Name, team_member.email,

	team_member.name||' ('||team.structure_node_Name||')' position_Name,
	team_member.team_Agent_Id, team_member.name teamAgent....

second scenario:
CREATE OR REPLACE function san_test (p_email IN varchar2) return VA_share_Assign_For_Scheduler Is Cursor shr_Assign is
Select * from (

	Select e.node_Id, e.status_Name, e.email,
	 e.structure_Node_Name||' '||e.email position_Name,
	to_number(null) team_Agent_Id, null teamAgent
	from  shr_Emp_Assignments_V e
	where e.status_name in ('ACTIVE', 'CONFIRMED')
	and e.assignment_obsolete_Flag = 'N'
	and e.job_category in (


						'Account Manager',

						'Global Account Mgr',

						'Major Account Mgr',

						'Service account manager',

						'Channel Account Manager',

						'Sales Manager',

						'Sales Representatives',					

						'Temporary',

						'Area & Regional Mgr',


						'Services Mgr')


	and ( (e.assignment_End_date Is null) OR 
		  (trunc(e.assignment_end_Date) > trunc(sysdate-1))
		 ) 
	and e.email = 'chwoodfi'

UNION ALL
Select team.node_id, team.status_Name, team_member.email,

	team_member.name||' ('||team.structure_node_Name||')' position_Name,
	team_member.team_Agent_Id, team_member.name teamAgent....

Received on Wed Apr 30 2003 - 03:53:47 CEST