Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Yousef.

Asked: April 29, 2019 - 1:40 pm UTC

Last updated: May 02, 2019 - 6:48 am UTC

Version: 11G

Viewed 1000+ times

You Asked

Hi Tom
I have Stored procedure return some data,

create  PROCEDURE "SP1" 
(CV_1   IN OUT SYS_REFCURSOR)

/*CREATE GLOBAL TEMPORARY table TT_TABL2
      (
        ORDER_ID NUMBER,
        REQ_ID NUMBER,
        TXN_ID NUMBER,
        TYPE NUMBER,
        ROW_NUM NUMBER,

        CONSTRAINT TT_TABL2_PK PRIMARY KEY (ROW_NUM,ORDER_PROCESS_TYPE)
      )  
      ON COMMIT DELETE ROWS NOPARALLEL; */

AS



BEGIN 

INSERT INTO TT_MSR_ORDER 
SELECT  
       T1.ID ORDER_ID,
       T2.ID REQ_ID,
       T3.ID TXN_ID ,
       25 AS ORDER_PROCESS_TYPE,
       ROWNUM
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.ID = T2.TABLE1_ID
INNER JOIN Table3 T3 ON T2.ID = T3.TABLE2_ID >;
--
--
--
--

END;





the above insert some time take a long time (more than minutes), to insert less than 50 records into GTT, and I go to drop the GTT and create it again.
when I recreate GTT the stored procedure return to running quickly and return more than 1000000 records in less than one minute.
I know my action is not correct (re-create GTT) so if there any other action please advice.

and Connor said...

It is unlikely to be your GTT but the cost of running the query.

The first time you run:
INSERT INTO TT_MSR_ORDER 
SELECT  
       T1.ID ORDER_ID,
       T2.ID REQ_ID,
       T3.ID TXN_ID ,
       25 AS ORDER_PROCESS_TYPE,
       ROWNUM
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.ID = T2.TABLE1_ID
INNER JOIN Table3 T3 ON T2.ID = T3.TABLE2_ID >;


you will probably be reading all that data from *disk*. Once its been run, the data is now in memory and subsequent executions are much faster.

Try this in SQL Plus

set autotrace on stat
[run your query]

You'll see physical reads vs consistent gets. Then run it again and check how physical reads is. My hypothesis is that this is a query tuning issue not a GTT issue.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Yousef Abdelnabi, April 30, 2019 - 8:36 am UTC

thank you
I will try it in SQL*plus and will back to you with the result.
but can you explain to me why it's working when I drop the GTT and create it again?
we call the SP every day and it's worked smoothly my case happened one time monthly (around), after that, I go to re-create GGT and its back to work,
my query has some condition I wasn't attached it, these conditions have indexes to avoid full scan table.


thanks again

Connor McDonald
May 02, 2019 - 6:48 am UTC

To get detailed specific information, you can always trace it

create  PROCEDURE "SP1" 
(CV_1   IN OUT SYS_REFCURSOR)
AS
BEGIN 
  dbms_monitor.session_trace_enable(waits=>true);
  ..
  ..
  ..
  dbms_monitor.session_trace_disable;
end;


and then run tkprof on the trace file to get a detailed analysis.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.