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  |
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 #663386 is a reply to message #663374] |
Thu, 01 June 2017 11:43  |
 |
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 |
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 #663631 is a reply to message #663472] |
Sun, 11 June 2017 22:59 |
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?
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 16:41:30 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
