Re: Wierd performance impact
From: Ron Reidy <rereidy_at_indra.com>
Date: Wed, 30 Apr 2003 05:22:43 -0600
Message-ID: <3EAFB203.6040801_at_indra.com>
Date: Wed, 30 Apr 2003 05:22:43 -0600
Message-ID: <3EAFB203.6040801_at_indra.com>
Do you look at explain plan output or get a trace of the session (set event 10046)? These two things will tell you exactly what is going on and where the problem is.
-- Ron Reidy Oracle DBA shankar wrote:Received on Wed Apr 30 2003 - 13:22:43 CEST
> Hello all,
> 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....