Home » RDBMS Server » Performance Tuning » Tune a select query which contains a pl/sql table type
Tune a select query which contains a pl/sql table type [message #387091] Tue, 17 February 2009 21:48 Go to next message
mnair
Messages: 5
Registered: January 2009
Junior Member
Hi,

I have created a table type t_ref as
CREATE OR REPLACE TYPE "T_REF" IS TABLE OF VARCHAR2(35);

Now I have a procedure which uses this table type...

PROCEDURE c4_test_prc(iv_bgln IN c4_edi_tb.b_gln%TYPE,
iv_docreftype IN c4_edi_tb.doc_type_name%TYPE,
it_ref_num IN t_ref,
ov_error OUT VARCHAR2) AS


n_count NUMBER(3) := 0;
begin

SELECT COUNT(tdoc.column_value)
INTO n_count
FROM c4_edi_tb edi,
c4_gln_tb gln,
(SELECT column_value
FROM TABLE(CAST(it_ref_num AS
t_ref))) tdoc
WHERE edi.doc_num = tdoc.column_value
AND edi.b_gln = gln.n_number
AND edi.b_gln = iv_bgln
AND edi.doc_type_name = iv_docreftype;

....
....
end;

I need to tune the select query in the procedure ...How can I get the explain plan for this query, as it is using a table type?

Also if I have to execute just this query and see how much time it is taking, how can I do that? I can give the values for the variables iv_bgln and iv_docreftype. But how can I give the values for it_ref_num , if it has the values say '123455' and '23456'?
Re: Tune a select query which contains a pl/sql table type [message #387095 is a reply to message #387091] Tue, 17 February 2009 22:09 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
mnair wrote on Wed, 18 February 2009 04:48
I need to tune the select query in the procedure ...How can I get the explain plan for this query, as it is using a table type?

Possible by using SQL Trace + TKProf.
mnair wrote on Wed, 18 February 2009 04:48
Also if I have to execute just this query and see how much time it is taking, how can I do that?

Also possible to obtain by the above way.
mnair wrote on Wed, 18 February 2009 04:48
I can give the values for the variables iv_bgln and iv_docreftype. But how can I give the values for it_ref_num , if it has the values say '123455' and '23456'?

Time to consult PL/SQL User's Guide and Reference, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/.
Have a look at Chapter 12 Using PL/SQL With Object Types.
Previous Topic: Not using Index
Next Topic: Partition table selfjoin for Time series
Goto Forum:
  


Current Time: Tue Dec 06 02:33:28 CST 2016

Total time taken to generate the page: 0.10066 seconds