Home » RDBMS Server » Performance Tuning » query issue with execute immediate (11.2.0.4)
query issue with execute immediate [message #663374] Thu, 01 June 2017 04:36 Go to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Hi,

I have a insert query in the procedure with execute immediate and it is taking time. Due to this my procedure is taking time. Please help me on this. My sample code as below.

procedure test(p_qp_req_id        VARCHAR2,
    p_parent_qp_req_id VARCHAR2,
    p_avail_check      VARCHAR2,
    p_threshold        NUMBER,
    p_poolid           VARCHAR2,
    p_continueavail    VARCHAR2,
    p_rescount OUT NUMBER,
    p_ret_code OUT VARCHAR2,
    p_ret_msg OUT VARCHAR2)

begin

v_poolsql:= 'insert into pc_eri_erimatch_matchres_gtt(qp_req_id,corpid,personid,pool_id,positionid,workschedulerule) '||
            ' select distinct   '|| P_QP_REQ_ID||'  P_QP_REQ_ID ,res.corpid, res.personid,rmap.pool_id,res.positionid ,res.workschedulerule '||
            ' from pc_eri_erimatch_res_info res ,pr_eri_data_pool_res_mapping rmap '||
               q'[  where res.resourcestatus='Active' and res.personid=rmap.personid(+)  ]';
if p_poolid is not null
then


v_poolsql:=v_poolsql || ' and rmap.pool_id in  ('''||p_poolid||''' )' ;

end if;

EXECUTE IMMEDIATE v_poolsql;


end test;

Re: query issue with execute immediate [message #663376 is a reply to message #663374] Thu, 01 June 2017 05:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
How about showing us an actual insert statement generated along with the explain plan.
Re: query issue with execute immediate [message #663377 is a reply to message #663376] Thu, 01 June 2017 05:26 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
select distinct

This is almost always bad.

It strongly indicates that either your data model is flawed or the query logic.
Re: query issue with execute immediate [message #663381 is a reply to message #663377] Thu, 01 June 2017 06:12 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
You are right. Here data model is old. We have proposed the new data model to avoid outer joins. So we are using outer joins in query logic.
Re: query issue with execute immediate [message #663382 is a reply to message #663381] Thu, 01 June 2017 06:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: query issue with execute immediate [message #663386 is a reply to message #663374] Thu, 01 June 2017 11:43 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Depending on the value of "p_poolid" it appears that both of the following Select Statements
will require Explain Plans. Some column vales were unknown and the SQL has '???' for thoes items.

Also previously requested Information for the tables would be helpful in the Analysis.

SELECT DISTINCT
       '???'                 p_qp_req_id
     , res.corpid
     , res.personid
     , rmap.pool_id
     , res.positionid
     , res.workschedulerule
FROM   pc_eri_erimatch_res_info     res
     , pr_eri_data_pool_res_mapping rmap
WHERE  res.resourcestatus = 'Active'
AND    res.personid       = rmap.personid(+);

OR

SELECT DISTINCT
       '???'                 p_qp_req_id
     , res.corpid
     , res.personid
     , rmap.pool_id
     , res.positionid
     , res.workschedulerule
FROM   pc_eri_erimatch_res_info     res
     , pr_eri_data_pool_res_mapping rmap
WHERE  res.resourcestatus = 'Active'
AND    res.personid       = rmap.personid(+)
AND    rmap.pool_id IN  ('???' )
Re: query issue with execute immediate [message #663470 is a reply to message #663386] Mon, 05 June 2017 07:11 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you very much for your response.

Please find the explain plans as below.
SELECT /*query1*/ DISTINCT
       '1496333585'                 p_qp_req_id
     , res.corpid
     , res.personid
     , rmap.pool_id
     , res.positionid
     , res.workschedulerule
FROM   pc_eri_erimatch_res_info     res
     , pr_eri_data_pool_res_mapping rmap
WHERE  res.resourcestatus = 'Active'
AND    res.personid       = rmap.personid(+);

select * from gv$sql where lower(sql_text) like '%query1%'; --2gmpyvypp7f34
select * from table(dbms_xplan.display_cursor(sql_id=>'2gmpyvypp7f34',format=>'ALLSTATS LAST'));

SQL_ID  2gmpyvypp7f34, child number 0
-------------------------------------
SELECT /*query1*/ DISTINCT        '1496333585'                 
p_qp_req_id      , res.corpid      , res.personid      , rmap.pool_id   
   , res.positionid      , res.workschedulerule FROM   
pc_eri_erimatch_res_info     res      , pr_eri_data_pool_res_mapping 
rmap WHERE  res.resourcestatus = 'Active' AND    res.personid       = 
rmap.personid(+)
 
Plan hash value: 768156733
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                         | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                              |        |       |       |          |
|   1 |  HASH UNIQUE                |                              |  74848 |     9M|  2560K|          |
|*  2 |   HASH JOIN RIGHT OUTER     |                              |  80845 |  6594K|  3768K| 6666K (0)|
|   3 |    TABLE ACCESS STORAGE FULL| PR_ERI_DATA_POOL_RES_MAPPING |  74963 |  1025K|  1025K|          |
|*  4 |    TABLE ACCESS STORAGE FULL| PC_ERI_ERIMATCH_RES_INFO     |  80845 |  1025K|  1025K|          |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("RES"."PERSONID"="RMAP"."PERSONID")
   4 - storage("RES"."RESOURCESTATUS"='Active')
       filter("RES"."RESOURCESTATUS"='Active')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
   - 3 Sql Plan Directives used for this statement

SELECT /*query3*/ DISTINCT
       '1496333585'                 p_qp_req_id
     , res.corpid
     , res.personid
     , rmap.pool_id
     , res.positionid
     , res.workschedulerule
FROM   pc_eri_erimatch_res_info     res
     , pr_eri_data_pool_res_mapping rmap
WHERE  res.resourcestatus = 'Active'
AND    res.personid       = rmap.personid(+)
AND    rmap.pool_id is null;

select * from gv$sql where lower(sql_text) like '%query3%'; --6y0fkt275nyhw
select * from table(dbms_xplan.display_cursor(sql_id=>'6y0fkt275nyhw',format=>'ALLSTATS LAST'));

SQL_ID  6y0fkt275nyhw, child number 0
-------------------------------------
SELECT /*query3*/ DISTINCT        '1496333585'                 
p_qp_req_id      , res.corpid      , res.personid      , rmap.pool_id   
   , res.positionid      , res.workschedulerule FROM   
pc_eri_erimatch_res_info     res      , pr_eri_data_pool_res_mapping 
rmap WHERE  res.resourcestatus = 'Active' AND    res.personid       = 
rmap.personid(+) AND    rmap.pool_id is null
 
Plan hash value: 3630449908
 
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                              |      1 |        |      0 |00:00:00.15 |   27053 |       |       |          |
|   1 |  HASH UNIQUE                 |                              |      1 |      1 |      0 |00:00:00.15 |   27053 |  1920K|  1920K|          |
|*  2 |   FILTER                     |                              |      1 |        |      0 |00:00:00.15 |   27053 |       |       |          |
|*  3 |    HASH JOIN RIGHT OUTER     |                              |      1 |  80845 |  74848 |00:00:00.15 |   27053 |  6594K|  3768K| 6668K (0)|
|   4 |     TABLE ACCESS STORAGE FULL| PR_ERI_DATA_POOL_RES_MAPPING |      1 |  74963 |  74963 |00:00:00.01 |    2088 |  1025K|  1025K|          |
|*  5 |     TABLE ACCESS STORAGE FULL| PC_ERI_ERIMATCH_RES_INFO     |      1 |  80845 |  74848 |00:00:00.07 |   24965 |  1025K|  1025K|          |
--------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("RMAP"."POOL_ID" IS NULL)
   3 - access("RES"."PERSONID"="RMAP"."PERSONID")
   5 - storage("RES"."RESOURCESTATUS"='Active')
       filter("RES"."RESOURCESTATUS"='Active')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 2 Sql Plan Directives used for this statement
 

There are no indexes in pc_eri_erimatch_res_info table.
There are unique index on personid in pr_eri_data_pool_res_mapping table
Re: query issue with execute immediate [message #663472 is a reply to message #663470] Mon, 05 June 2017 07:16 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Sorry

There are index on personid in pc_eri_erimatch_res_info table.
There are unique index on personid in pr_eri_data_pool_res_mapping table
Re: query issue with execute immediate [message #663631 is a reply to message #663472] Sun, 11 June 2017 22:59 Go to previous message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Your SQL said:

SELECT /*query3*/ DISTINCT
       '1496333585'                 p_qp_req_id
     , res.corpid
     , res.personid
     , rmap.pool_id
     , res.positionid
     , res.workschedulerule
FROM   pc_eri_erimatch_res_info     res
     , pr_eri_data_pool_res_mapping rmap
WHERE  res.resourcestatus = 'Active'
AND    res.personid       = rmap.personid(+)
AND    rmap.pool_id is null;

First time: The distinct is always bad, including simple query or in small partitioned table. You should avoid, of course, some time, you cannot.
Second time: Place to Left outer join in near where clause but only limitation with "Active" value from pc_eri_erimatch_res_info(resourcestatus) column. Did this column associate to index?
Previous Topic: PLS-00201: identifier 'DBMS_AWR.ENABLE_AWR' must be declared
Next Topic: Gathering Statistics of Partitioned table
Goto Forum:
  


Current Time: Thu Mar 28 09:24:18 CDT 2024