Forum Stats

  • 3,784,163 Users
  • 2,254,899 Discussions
  • 7,880,717 Comments

Discussions

My function in select statement taking time and have multiple entries in v$session

Uday_N
Uday_N Member Posts: 302 Bronze Badge

Hi All,

I am calling a function for my select query . for one query it works fine but for another query similar function taking time . the query which takes time has additional two tables than the less time query but it doesn't related to the function . Another important information is when i run the function , it has multiples entries in my v$session . I am really sorry for not able to publish my code as code is very big .

Why a same function takes time for one query while it doesn't take time for another similar query . Also why it is having lot of entries in V$SESSION . Am i missing something here? Apart from code if you need any details please apprise me . I am really sorry as i thought sharing a sample code wont looks good so i didnt write my sample code .


Regards,

Udayasoorian

Tagged:

Best Answer

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,674 Red Diamond
    edited Jan 12, 2021 5:54AM

    The only reason why your query will spanning multiple sessions, is Oracle Parallel Query (PQ) - assuming you diagnosis that these are the same query sessions is correct.

    Have a look at the query's execution plan, and at v$px_session, to confirm parallel execution.

    Uday_N
  • Uday_N
    Uday_N Member Posts: 302 Bronze Badge

    Hi Bill,

    Thanks for your answer . You are right. i checked the plan there is parallel execution happening . How is it possible when we didn't give any parallel command and if i pass a particular value it throws


    Error : 'error signalled in parallel query server '

    Cause : A parallel query server reached an expection condition .


    Also if i pass the same value for eg :


    select pkg_samp.func_sample(1) from dual;

    it gives the result but while i pass the same value with my query ex


    with Q as

    (select pkg_samp.func_sample(1) , a.emp_id ,b.dept_name from emp a , dept b where a.emp_id = b.emp_id )


    select * from Q


    The above error and parallel processing occurs . Why it is happening . if you need further details i will give it .Please kind your advice.

  • Uday_N
    Uday_N Member Posts: 302 Bronze Badge

    Hi Bill,

    Thanks for your advise and sharing the doc . I am using NO_PARALLEL hint . It worked . Thanks once again for your help .


    Regards,

    Udayasoorian