Home » RDBMS Server » Performance Tuning » How To Check Performace Tuning
How To Check Performace Tuning [message #630771] Mon, 05 January 2015 05:26 Go to next message
mastansky@gmail.com
Messages: 13
Registered: July 2013
Location: INDIA
Junior Member

Hi to everyone please let me know how to check the following procedure's performance and in the following three methods how to know which is the best method among three methods .

Thanks in Advance.

CREATE OR REPLACE PACKAGE PKG_P
AS
	G_SAL NUMBER(7,2):=3000;
END;
/
-----------------------------------


Method:1.)NORMAL CURSOR FOR LOOP METHOD :
----------------------------------
CREATE OR REPLACE PROCEDURE P(v_deptno NUMBER,v_dname VARCHAR2)
AS
CURSOR c_emp(c_deptno NUMBER,c_dname VARCHAR2)
IS
	SELECT E.Ename,D.Dname,E.Sal 
	FROM Emp E,Dept D
	WHERE E.Deptno=D.Deptno 
	AND
	E.Deptno=c_deptno 
	AND
	D.Dname=c_dname
	AND
	E.Sal=PKG_P.G_SAL ;
BEGIN
FOR i IN c_emp(v_deptno,v_dname)
LOOP
	DBMS_OUTPUT.PUT_LINE(i.Ename||' '||i.Dname||' '||i.Sal);
END LOOP;
END;


-----------------------------------------------
method :2)NORMAL CURSOR FOR LOOP WITH IF CONDITION :
-----------------------------------------------

CREATE OR REPLACE PROCEDURE P(v_deptno NUMBER,v_dname VARCHAR2)
AS
CURSOR c_emp(c_deptno NUMBER,c_dname VARCHAR2)
IS
	SELECT E.Ename,D.Dname,E.Sal 
	FROM Emp E,Dept D
	WHERE E.deptno=D.deptno 
	AND 
	E.Deptno=c_deptno 
	AND
	D.Dname=c_dname;
BEGIN
FOR i IN c_emp(v_deptno,v_dname)
LOOP
	IF i.sal=PKG_P.G_SAL THEN
		DBMS_OUTPUT.PUT_LINE(i.Ename||' '||i.Dname||' '||i.Sal);
	END IF;
END LOOP;
END;

--------------------------
method :3)USING ASSCOCIATE ARRAY:
--------------------------


CREATE OR REPLACE PROCEDURE P(v_deptno NUMBER,v_dname VARCHAR2)
AS
CURSOR c_emp(c_deptno NUMBER,c_dname VARCHAR2)
IS
	SELECT E.Ename,D.Dname,E.Sal 
	FROM Emp E,Dept D
	WHERE E.deptno=D.deptno 
	AND 
	E.Deptno=c_deptno 
	AND
	D.Dname=c_dname;
TYPE t is RECORD
(
	v_ename VARCHAR2(30),
	v_dname VARCHAR2(30),
	v_sal NUMBER(7,2)
);
TYPE t1 IS TABLE OF t;
t2 t1;

BEGIN
OPEN c_emp(v_deptno,v_dname);
FETCH c_emp BULK COLLECT INTO t2;
FOR i in t2.FIRST..t2.LAST
LOOP
	IF t2(i).V_sal=PKG_P.G_SAL THEN
		DBMS_OUTPUT.PUT_LINE(t2(i).v_ename||' '||t2(i).V_dname||' '||t2(i).V_sal);
	END IF;
END LOOP;
END;
---------------



Mod edit: Added code tags

[Updated on: Mon, 05 January 2015 05:44] by Moderator

Report message to a moderator

Re: How To Check Performace Tuning [message #630776 is a reply to message #630771] Mon, 05 January 2015 06:15 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
For something like that I'd check the performance by timing it, but unless you've got a lot of records in those tables you aren't going to notice a difference either way.
Also given the parameters the chances are it'll only every find one emp per run anyway, in which case performance checking the differences is really pointless. It helps to have a meaningful example to test performance differences.

I will say, as a general principle, you should never write a select that returns data you're not interested in - so never do the 2nd approach.
Re: How To Check Performace Tuning [message #630777 is a reply to message #630776] Mon, 05 January 2015 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65385
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and as a general principle you should not use PL/SQL when something can be done in a single SQL statement.

Re: How To Check Performace Tuning [message #630782 is a reply to message #630777] Mon, 05 January 2015 06:31 Go to previous messageGo to next message
mastansky@gmail.com
Messages: 13
Registered: July 2013
Location: INDIA
Junior Member

My doubt is keeping extra filter in where clause casues the poor performace over 2 method using IF condition
Re: How To Check Performace Tuning [message #630783 is a reply to message #630782] Mon, 05 January 2015 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65385
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said, do as much as possible in SQL.

A general SQL principle is: "restrict (the number of rows) as much as possible sooner as possible".

[Updated on: Mon, 05 January 2015 06:35]

Report message to a moderator

Re: How To Check Performace Tuning [message #630864 is a reply to message #630783] Mon, 05 January 2015 18:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Michel's advice is excellent (as usual).

To add to it, here are my thoughts.

1. bulk fetch is always better for 10 to 1000 rows, over just one row at a time.  Indeed, there have been automatic PL/SQL optimizations added to recent versions of the PL/SQL compiler that will translate a FOR LOOP into its BULK VERSION automatically.

2. which brings us to style of coding.  Generally I prefer FOR LOOP to independent cursors.  I think they read better, and as is noted in #1, there is the automatic optimization in its favor now too.

3. it seems strange to me that you need both the DEPTNO and DNAME values at the same time.  I am going to assume this is just a fluke of your test case.

4. as Michel points out, putting it all in SQL gives the optimizer the most options so you want the value of the package variable in the SQL code, not tested as an IF statement.  However...

5. calling PL/SQL from SQL is expensive in many ways.  It would be vastly better for you to have passed the value of the package variable into the routine as another parameter.


In the end I would have coded this, and then let PL/SQL optimize it into the bulk form behind the scenes. This leaves me with both the most readable and the fastest code.

create or replace procedure p (dept_no_p in number, dname_p in varchar2, sal_p in number)
as
begin
   for r1 in (
                SELECT E.Ename,D.Dname,E.Sal 
                FROM Emp E,Dept D
                WHERE E.Deptno=D.Deptno 
                AND E.Deptno=deptno_p
                AND D.Dname=dname_p
                AND E.Sal=sal_p
             ) loop
      DBMS_OUTPUT.PUT_LINE(i.Ename||' '||i.Dname||' '||i.Sal);
   end loop;
end;
/


At least I think it is the most readable, others may disagree. Kevin

Since your question appears related to SQL Tuning, here is the promotional chapter of my book on SQL Tuning. I have attached the free promotional chapter and the free scripts from the book. This is the complete first chapter and it will teach you about the role of Cardinality in query plan generation and give you some tools to make tuning a little easier. This chapter and the scripts are free and you do NOT need to buy the book in order to use them and share them with others so please do so. If you do decide to get the book make sure to use the coupon codes noted in the back of the promotional chapter document so you can get it for 1/2 price. I see a few people buying from the noted website, but then forgetting to use the coupon which is a shame.
Re: How To Check Performace Tuning [message #630880 is a reply to message #630864] Tue, 06 January 2015 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65385
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
5. calling PL/SQL from SQL is expensive in many ways. It would be vastly better for you to have passed the value of the package variable into the routine as another parameter.


Agree but here it is not a call to a procedure but a package variable which is in the session UGA and so is like a bind variable, even better than that as there is no communication with the client to get the value.

Re: How To Check Performace Tuning [message #630898 is a reply to message #630864] Tue, 06 January 2015 03:22 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Kevin Meade wrote on Tue, 06 January 2015 00:19

Generally I prefer FOR LOOP to independent cursors.


For Loops and Independent (explicit) cursors aren't mutually exclusive.
I think you mean that when you use a For Loop you prefer to do it with an implicit cursor (select statement is right there in the for loop command).
Re: How To Check Performace Tuning [message #630948 is a reply to message #630898] Tue, 06 January 2015 09:39 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes Cookiemonster, that is what I mean, keeps the sql code close to where it is relevant to other code.

Michel, as usual, you demonstrate knowledge superior to mine.

I still caution the OP to keep in mind that calling pl/sql from sql is expensive and so avoid it when it is reasonable to do so.

Kevin
Previous Topic: Optimizing Dynamic SQL Usage
Next Topic: max and min function taking lot of time
Goto Forum:
  


Current Time: Tue Feb 20 20:28:49 CST 2018

Total time taken to generate the page: 0.14514 seconds