Home » SQL & PL/SQL » SQL & PL/SQL » Identify bottlenecks in queries (Oracle, Oracle Database 11g Express Edition Release 11.2.0.2.0 ,Win 7 Pro)
Identify bottlenecks in queries [message #637764] Sun, 24 May 2015 02:44 Go to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Hi All
I did my recent interview few days back and they asked me one of the question and I'm, curious to know the answer for my learning purpose.

Question

You have a package name abc and this package contained 10 procedures.
You execute that package and it freeze or stuck in the middle. 
Tell me how do you know that your package stuck on which procedure or on which select statement...


I really appreciate if someone answer this question for my learning purpose.

Regards
Shu
Re: Identify bottlenecks in queries [message #637765 is a reply to message #637764] Sun, 24 May 2015 02:47 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
Before answering a question, you need to understand it. What does "execute that package" mean?
Re: Identify bottlenecks in queries [message #637766 is a reply to message #637765] Sun, 24 May 2015 02:49 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
I think execute package means running the package. please let me know if you find me wrong...
Re: Identify bottlenecks in queries [message #637767 is a reply to message #637766] Sun, 24 May 2015 02:54 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
shumail wrote on Sun, 24 May 2015 08:49
I think execute package means running the package. please let me know if you find me wrong...
What does "running the package" mean? If you think that it actually means anything, you are showing that you have no knowledge of PL/SQL. Which may be why they asked such a question. How would you invoke something written in PL/SQL?
Re: Identify bottlenecks in queries [message #637768 is a reply to message #637767] Sun, 24 May 2015 03:11 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
For procedure
we need to call it, like below:

like below:

Procedure

CREATE OR REPLACE
PROCEDURE increase_salary(
    dept_id_in IN VARCHAR2)
AS
TYPE emp_id_t
IS
  TABLE OF employees%rowtype;
TYPE emp_sal_t
IS
  TABLE OF employees.salary%TYPE;
  l_employee_id emp_id_t :=emp_id_t ();
  l_salary emp_sal_t     :=emp_sal_t ();
BEGIN
  SELECT * BULK COLLECT
  INTO l_employee_id --,l_salary
  FROM employees
  WHERE department_id=dept_id_in;
  FOR indx IN 1..l_employee_id.count
  LOOP
    dbms_output.put_line('EMPLOYEE_ID='||l_employee_id(indx).employee_id||'  salary='||l_employee_id(indx).salary);
    IF l_employee_id(indx).salary>1 THEN
      l_salary.EXTEND;
      l_salary(l_salary.count):=l_employee_id(indx).salary;
      dbms_output.put_line('Salary ='||l_salary(l_salary.count));
    END IF;
  END LOOP;
END;



Call or invoke procedure from SQL PLUS

SQL> set serveroutput on
SQL> exec increase_salary(10);
EMPLOYEE_ID=200 salary=4400
Salary =4400

PL/SQL procedure successfully completed.

SQL>
Re: Identify bottlenecks in queries [message #637769 is a reply to message #637768] Sun, 24 May 2015 03:36 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
I'm wondering that we cannot run package but we can access its procedure, functions, cursor, pl/sql types and other stuff if it is exist in package specification.

In short package is a collection of logical related pl/sql types, function, procedures etc..


Please correct me if you find me wrong...

Re: Identify bottlenecks in queries [message #637770 is a reply to message #637769] Sun, 24 May 2015 04:20 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
OK. So go back your original question:
Quote:
Tell me how do you know that your package stuck on which procedure or on which select statement...

I think you can re-word it now so that it makes sense. THen you need to look at what tools are available. Try researching dbms_profiler, dbms_hprof, and dbms_monitor. Also statspack and dbms_workload_repository.

update: Also v$session_longops and v$session_wait.

[Updated on: Sun, 24 May 2015 04:22]

Report message to a moderator

Re: Identify bottlenecks in queries [message #637771 is a reply to message #637770] Sun, 24 May 2015 04:36 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Hi John

Thanks for correcting me, Can you please tell me one more thing, which of the tools in your suggested list is most commonly use and simple to understand if you don't have any DBA background...
Re: Identify bottlenecks in queries [message #637775 is a reply to message #637771] Sun, 24 May 2015 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

All of them, they point to different issues and when you want to diagnose the problem.

Re: Identify bottlenecks in queries [message #637782 is a reply to message #637775] Mon, 25 May 2015 00:19 Go to previous messageGo to next message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Thanks for you replies... I really appreciate your help specially John
Re: Identify bottlenecks in queries [message #637916 is a reply to message #637782] Fri, 29 May 2015 01:44 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Dear Friend,

I Hope you should have knowledge for exception handling.

In your case you have to handle exceptions and need to maintain log table for tracking your package procedure/function etc..So,you can identify issues in your code easily by checking log table.

Please find below mentioned url for getting information how to use exception handling in pl/sql code:

http://www.exforsys.com/tutorials/oracle-11g/oracle-11g-exception-handling.html

[Updated on: Fri, 29 May 2015 01:46]

Report message to a moderator

Re: Identify bottlenecks in queries [message #637924 is a reply to message #637916] Fri, 29 May 2015 02:45 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
This looks like a performance problem question, not an error handling one.
Re: Identify bottlenecks in queries [message #637938 is a reply to message #637764] Fri, 29 May 2015 06:35 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Instrumenting the package via dbms_application_info could also be a basic alternative
Re: Identify bottlenecks in queries [message #637941 is a reply to message #637938] Fri, 29 May 2015 11:10 Go to previous message
shumail
Messages: 149
Registered: September 2012
Location: Canada
Senior Member
Thanks to all
Previous Topic: better query other than multiple for loop's?
Next Topic: Displaying calculated field with selected rows and Adding row according to date condition
Goto Forum:
  


Current Time: Mon Mar 18 21:43:31 CDT 2024