Home » SQL & PL/SQL » SQL & PL/SQL » know the execution time without running the query (10.0 win xp)
|
|
|
|
| Re: know the execution time without running the query [message #333597 is a reply to message #333590] |
Sun, 13 July 2008 07:54   |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
In my 25 years of working with Oracle I have never seen such a thing. I am loathe to say no for amything, because I can't know everything and there are plenty of people smarter than me in this business. Still, this time I am pretty sure the answer is no.
Though one might think that the optimizer would be able to tell us this, the optimizer does not really care how long anything takes. It only cares to be able to rate various alternative query plans against each other in a way that it can predict correctly which ones of them are least expensive. Whether a query takes one second to run or one day to run, as long as the optimizer has found the cheapest of plans, it has done what it set out to do. Thus Oracle will tell you every time you ask; that the optimizer numbers don't say how long anything takes, only that at the time of query plan optimization, these numbers allow for relative comparison of alternative plans.
They will even go so far as to say that the numbers in the query plan cannot even be reused to compare different queries against each other, or even the same query against itself over time. You can check asktomhome for a discussion of this. In short Oracle wants us to remember that the optimizer's numbers are strictly for "internal use only". At least this is the party line. I am sure there are people who have become creative in this area. Maybe some of them will offer up some comments today.
Sorry, but the short answer is no, there is no way to tell what resources a query will actually consume when run, with running it. Kevin
|
|
|
|
| Re: know the execution time without running the query [message #333625 is a reply to message #333597] |
Sun, 13 July 2008 13:09 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I remember back when using Oracle 8i that Oracle Discoverer could produce such estimates before running the query, so I knew the algorithm or method was there, just not visible. Now using dbms_resource_manager, a query can be canceled before it starts if execution will take longer than the allowable time. We can use this to get the estimated time by setting the allowable time (max_est_exec_time) to 1 second, attempting to run the query, then getting the estimated time within the error message. We can set things up so that it only takes a one-line command to switch between 1 second allowable time to get the estimate and normal to actually run the query. Please see the demonstration below.
-- one-time setup:
SYS@orcl_11g> BEGIN
2 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE
3 ('scott', 'ADMINISTER_RESOURCE_MANAGER', FALSE);
4 END;
5 /
PL/SQL procedure successfully completed.
SYS@orcl_11g> CONNECT scott/tiger
Connected.
SCOTT@orcl_11g>
SCOTT@orcl_11g> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
3 DBMS_RESOURCE_MANAGER.CREATE_PLAN
4 ('estimated_execution_time', 'estimated_execution_time plan');
5 DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
6 ('time_estimate', 'time_estimate group');
7 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
8 (PLAN => 'estimated_execution_time',
9 GROUP_OR_SUBPLAN => 'time_estimate',
10 COMMENT => 'estimated_execution_time time_estimate directive',
11 MAX_EST_EXEC_TIME => 1 );
12 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
13 ('estimated_execution_time','OTHER_GROUPS', 'estimated_execution_time other_groups directive' );
14 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
15 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP
16 ('scott', 'time_estimate', TRUE );
17 DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP
18 ('scott', 'time_estimate');
19 END;
20 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g>
-- test:
SCOTT@orcl_11g> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = estimated_execution_time scope = memory
2 /
System altered.
SCOTT@orcl_11g> SELECT COUNT(*) FROM all_objects
2 /
SELECT COUNT(*) FROM all_objects
*
ERROR at line 1:
ORA-07455: estimated execution time (3 secs), exceeds limit (1 secs)
SCOTT@orcl_11g> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ''
2 /
System altered.
SCOTT@orcl_11g> SELECT COUNT(*) FROM all_objects
2 /
COUNT(*)
----------
68760
SCOTT@orcl_11g>
-- cleanup in case you don't want to leave it on your system:
SCOTT@orcl_11g> BEGIN
2 DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
3 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
4 DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE ('estimated_execution_time');
5 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
6 END;
7 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g>
|
|
|
|
|
|
| Re: know the execution time without running the query [message #588320 is a reply to message #333590] |
Mon, 24 June 2013 19:38 |
 |
klb1953
Messages: 5
Registered: January 2013
|
Junior Member |
|
|
I know it has been some time since this thread was active, but I am trying to do the same thing with 11g (11.2.0.3.0). The query uses Oracle text but the estimate seems to be way out of the actual execution time (10secs to 2 mims & 10 secs). Any thoughts on why?
TIA
Ken
09:52:43 OPS$PP2@DPR01>set timing on
09:59:46 OPS$PP2@DPR01>
09:59:47 OPS$PP2@DPR01>
09:59:47 OPS$PP2@DPR01>
09:59:47 OPS$PP2@DPR01>declare
10:00:17 2 vGroup varchar2(40);
10:00:17 3 begin
10:00:17 4 dbms_session.switch_current_consumer_group('time_estimate',vGroup,TRUE);
10:00:17 5 dbms_output.put_line('vGroup=' || vGroup);
10:00:17 6 end;
10:00:17 7 /
vGroup=
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
10:00:17 OPS$PP2@DPR01>
10:00:20 OPS$PP2@DPR01>
10:00:20 OPS$PP2@DPR01>
10:00:20 OPS$PP2@DPR01>SELECT score, bd.bd_id, bd_empl_no, bd_claim_suffix_no, bd_temp_claim_no,bd_prov_abn, bd_pr
ov_nme, bd_prov_inv_no, bd_td_id, td_desc, bd_trim_identifier,bd_empl_sur_nme, bd_empl_given_nme, bd_empl_fo
rmer_sur_nme, bd_empl_birth_dt,bd_empl_ags_no, bd_inj_desc, bd_inj_dt, null pracsys_claim_status, null pracsys_dete
rm_status, is_desc, bd_prov_inv_dt, bd_prov_inv_amt, null pracsys_claim_inv_no,bd_dc_id, bd_ic_id, bd_doc_ti
tle, bd_dl_id, dl_desc, bd_comment, null pracsys_rec_type, null pracsys_inv_status FROM ( SELECT * FROM (
SELECT /* FIRST_ROWS(100) */ SCORE(1)*10000 score, bis_tbl_rowid bd_rowid FROM bpu_index_store bis WHERE CONTAI
NS(bis_text, parser.progrelax('+21852'), 1) > 0 AND bis_tbl_nme = 'BPU_DOCUMENT' ORDER BY SCORE(1) DESC ) WHERE ROWNUM < 10
1 ) mbd INNER JOIN bpu_document bd ON (bd.ROWID = mbd.bd_rowid)INNER JOIN trim_destination ON (td_id = bd_td_id) INNER JOIN documen
t_location dl ON (dl_id = bd_dl_id)LEFT OUTER JOIN invoice_status ON (is_id = bd_is_id)
10:00:28 2 UNION
10:00:28 3 SELECT score, null bd_id, pcv.empl_no,pcv.claim_suffix_no, null temp_claim_no, null prov_abn, null prov
_nme, null prov_inv_no,null bd_td_id, null td_desc, null bd_trim_identifier, pcv.empl_sur_nme, pcv.empl_given_nme,p
cv.empl_former_sur_nme, pcv.birth_dte, pcv.ags_no, pcv.inj_desc, pcv.inj_dte,pcv.pracsys_claim_status, pcv.pracsys_
determ_status, null is_desc, null ext_inv_dt, null inv_tot_amt, null pracsys_claim_inv_no, null bd_dc_id, nu
ll bd_ic_id, null bd_doc_title,dl_id, dl_desc, null comm, 'Claim' pracsys_rec_type, null pracsys_inv_status FROM
(SELECT * FROM ( SELECT /* FIRST_ROWS(100) */ SCORE(1)*10000 score,bis_tbl_rowid claims_rowid FROM bpu_index_s
tore bis WHERE CONTAINS(bis_text,parser.progrelax('+21852'), 1) > 0 AND bis_tbl_nme = 'CLAIMS' ORDER BY SCORE(1) DESC
)WHERE ROWNUM < 101 ) mc INNER JOIN pracsys_claims_v pcv ON (pcv.claims_rowid = mc.claims_rowid) INNER JOIN document_location dl
ON (dl_id = 1)
10:00:28 4 UNION
10:00:28 5 SELECT score, null bd_id, pciv.empl_no,pciv.claim_suffix_no, null temp_claim_no, abn_no, prov_nme,
null prov_inv_no, null bd_td_id, null td_desc, null bd_trim_identifier, pciv.empl_sur_nme, pciv.empl_given_nm
e,pciv.empl_former_sur_nme, pciv.birth_dte, pciv.ags_no, pciv.inj_desc, pciv.inj_dte,pciv.pracsys_claim_status, pci
v.pracsys_determ_status, null is_desc, ext_inv_dt,inv_tot_amt, pciv.claim_inv_no, null bd_dc_id, null bd_ic_id,
null bd_doc_title,dl_id, dl_desc, comm, 'Invoice' pracsys_rec_type, pciv.pracsys_inv_status FROM ( SELECT *
FROM ( SELECT /* FIRST_ROWS(100) */ SCORE(1)*10000 score, bis_tbl_rowid claim_invoice_rowid FROM bpu_ind
ex_store bis WHERE CONTAINS(bis_text, parser.progrelax('+21852'), 1)> 0 AND bis_tbl_nme = 'CLAIM_INVOICE' ORDER BY SCORE(
1) DESC ) WHERE ROWNUM < 101 ) mi INNER JOIN pracsys_claim_invoice_v pciv ON (pciv.claim_invoice_rowid = mi.claim_invoice_rowid)
INNER JOIN document_location dl ON (dl_id = 1) ORDER BY 1 desc;
SELECT score, bd.bd_id, bd_empl_no, bd_claim_suffix_no, bd_temp_claim_no,bd_prov_abn, bd_prov_nme, bd_prov_
inv_no, bd_td_id, td_desc, bd_trim_identifier,bd_empl_sur_nme, bd_empl_given_nme, bd_empl_former_sur_nme, bd_e
mpl_birth_dt,bd_empl_ags_no, bd_inj_desc, bd_inj_dt, null pracsys_claim_status, null pracsys_determ_status, is_desc,
bd_prov_inv_dt, bd_prov_inv_amt, null pracsys_claim_inv_no,bd_dc_id, bd_ic_id, bd_doc_title, bd_dl_id,
dl_desc, bd_comment, null pracsys_rec_type, null pracsys_inv_status FROM ( SELECT * FROM ( SELECT /* FIRST
_ROWS(100) */ SCORE(1)*10000 score, bis_tbl_rowid bd_rowid FROM bpu_index_store bis WHERE CONTAINS(bis_text, parser.progr
elax('+21852'), 1) > 0 AND bis_tbl_nme = 'BPU_DOCUMENT' ORDER BY SCORE(1) DESC ) WHERE ROWNUM < 101 ) mbd INNER JOIN bp
u_document bd ON (bd.ROWID = mbd.bd_rowid)INNER JOIN trim_destination ON (td_id = bd_td_id) INNER JOIN document_location dl ON (dl_id
= bd_dl_id)LEFT OUTER JOIN invoice_status ON (is_id = bd_is_id)
*
ERROR at line 1:
ORA-07455: estimated execution time (10 secs), exceeds limit (1 secs)
Elapsed: 00:00:00.01
10:00:28 OPS$PP2@DPR01>declare
10:00:45 2 vGroup varchar2(40);
10:00:45 3 begin
10:00:45 4 dbms_session.switch_current_consumer_group('',vGroup,TRUE);
10:00:45 5 dbms_output.put_line('vGroup=' || vGroup);
10:00:45 6 end;
10:00:45 7 /
vGroup=TIME_ESTIMATE
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
10:00:45 OPS$PP2@DPR01>SELECT score, bd.bd_id, bd_empl_no, bd_claim_suffix_no, bd_temp_claim_no,bd_prov_abn, bd_pr
ov_nme, bd_prov_inv_no, bd_td_id, td_desc, bd_trim_identifier,bd_empl_sur_nme, bd_empl_given_nme, bd_empl_fo
rmer_sur_nme, bd_empl_birth_dt,bd_empl_ags_no, bd_inj_desc, bd_inj_dt, null pracsys_claim_status, null pracsys_dete
rm_status, is_desc, bd_prov_inv_dt, bd_prov_inv_amt, null pracsys_claim_inv_no,bd_dc_id, bd_ic_id, bd_doc_ti
tle, bd_dl_id, dl_desc, bd_comment, null pracsys_rec_type, null pracsys_inv_status FROM ( SELECT * FROM (
SELECT /* FIRST_ROWS(100) */ SCORE(1)*10000 score, bis_tbl_rowid bd_rowid FROM bpu_index_store bis WHERE CONTAI
NS(bis_text, parser.progrelax('+21852'), 1) > 0 AND bis_tbl_nme = 'BPU_DOCUMENT' ORDER BY SCORE(1) DESC ) WHERE ROWNUM < 10
1 ) mbd INNER JOIN bpu_document bd ON (bd.ROWID = mbd.bd_rowid)INNER JOIN trim_destination ON (td_id = bd_td_id) INNER JOIN documen
t_location dl ON (dl_id = bd_dl_id)LEFT OUTER JOIN invoice_status ON (is_id = bd_is_id)
10:00:51 2 UNION
10:00:51 3 SELECT score, null bd_id, pcv.empl_no,pcv.claim_suffix_no, null temp_claim_no, null prov_abn, null prov
_nme, null prov_inv_no,null bd_td_id, null td_desc, null bd_trim_identifier, pcv.empl_sur_nme, pcv.empl_given_nme,p
cv.empl_former_sur_nme, pcv.birth_dte, pcv.ags_no, pcv.inj_desc, pcv.inj_dte,pcv.pracsys_claim_status, pcv.pracsys_
determ_status, null is_desc, null ext_inv_dt, null inv_tot_amt, null pracsys_claim_inv_no, null bd_dc_id, nu
ll bd_ic_id, null bd_doc_title,dl_id, dl_desc, null comm, 'Claim' pracsys_rec_type, null pracsys_inv_status FROM
(SELECT * FROM ( SELECT /* FIRST_ROWS(100) */ SCORE(1)*10000 score,bis_tbl_rowid claims_rowid FROM bpu_index_s
tore bis WHERE CONTAINS(bis_text,parser.progrelax('+21852'), 1) > 0 AND bis_tbl_nme = 'CLAIMS' ORDER BY SCORE(1) DESC
)WHERE ROWNUM < 101 ) mc INNER JOIN pracsys_claims_v pcv ON (pcv.claims_rowid = mc.claims_rowid) INNER JOIN document_location dl
ON (dl_id = 1)
10:00:51 4 UNION
10:00:51 5 SELECT score, null bd_id, pciv.empl_no,pciv.claim_suffix_no, null temp_claim_no, abn_no, prov_nme,
null prov_inv_no, null bd_td_id, null td_desc, null bd_trim_identifier, pciv.empl_sur_nme, pciv.empl_given_nm
e,pciv.empl_former_sur_nme, pciv.birth_dte, pciv.ags_no, pciv.inj_desc, pciv.inj_dte,pciv.pracsys_claim_status, pci
v.pracsys_determ_status, null is_desc, ext_inv_dt,inv_tot_amt, pciv.claim_inv_no, null bd_dc_id, null bd_ic_id,
null bd_doc_title,dl_id, dl_desc, comm, 'Invoice' pracsys_rec_type, pciv.pracsys_inv_status FROM ( SELECT *
FROM ( SELECT /* FIRST_ROWS(100) */ SCORE(1)*10000 score, bis_tbl_rowid claim_invoice_rowid FROM bpu_ind
ex_store bis WHERE CONTAINS(bis_text, parser.progrelax('+21852'), 1)> 0 AND bis_tbl_nme = 'CLAIM_INVOICE' ORDER BY SCORE(
1) DESC ) WHERE ROWNUM < 101 ) mi INNER JOIN pracsys_claim_invoice_v pciv ON (pciv.claim_invoice_rowid = mi.claim_invoice_rowid)
INNER JOIN document_location dl ON (dl_id = 1) ORDER BY 1 desc;
SCORE BD_ID BD_EMPL_NO BD_CLAIM_SUFFIX_NO BD_TEMP_CLAIM_NO
-------------------- -------------------- -------------------- -------------------- --------------------
BD_PROV_ABN BD_PROV_NME BD_PROV_INV_NO BD_TD_ID
-------------------- -------------------------------------------------- -------------------- --------------------
TD_DESC BD_TRIM_IDENTIFIER BD_EMPL_SUR_NME
-------------------------------------------------- -------------------- -------------------------
BD_EMPL_GIVEN_NME BD_EMPL_FORMER_SUR_NME BD_EMPL_BIRTH_DT BD_EMPL_AGS_NO
-------------------------------------------------- ------------------------- -------------------- --------------------
BD_INJ_DESC
------------------------------------------------------------------------------------------------------------------------
BD_INJ_DT PRACSYS_CLAIM_STATUS
-------------------- ------------------------------------------------------------
PRACSYS_DETERM_STATUS IS_DESC
------------------------------------------------------------ --------------------------------------------------
BD_PROV_INV_DT BD_PROV_INV_AMT PRACSYS_CLAIM_INV_NO BD_DC_ID BD_IC_ID
-------------------- -------------------- -------------------- -------------------- --------------------
BD_DOC_TITLE
----------------------------------------------------------------------------------------------------
BD_DL_ID DL_DESC
-------------------- --------------------------------------------------
BD_COMMENT
------------------------------------------------------------------------------------------------------------------------
PRACSYS PRACSYS_INV_STATUS
------- ------------------------------------------------------------
.
.
.
101 rows selected.
Elapsed: 00:02:10.58
10:03:02 OPS$PP2@DPR01>
[mod-edit: code tags added by bb]
[Updated on: Mon, 24 June 2013 22:28] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: know the execution time without running the query [message #588375 is a reply to message #333625] |
Tue, 25 June 2013 06:35 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Hey Barbara, that was an awesome presentation.
I am struggling to understand with plan directives in a multi-level plan scenario. I mean if we have multiple max_est_exec_time from multiple plans respectively, which directives would be used.
Could you please use the same example and help me to understand multi-level plan scenario. A BIG thanks in advance.
|
|
|
|
|
|
| Re: know the execution time without running the query [message #588424 is a reply to message #588375] |
Tue, 25 June 2013 14:51 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Lalit Kumar B,
Please see if the following modification of the original example is what you are looking for.
Barbara
-- one-time setup:
SYS@orcl_11gR2> BEGIN
2 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE
3 ('scott', 'ADMINISTER_RESOURCE_MANAGER', FALSE);
4 END;
5 /
PL/SQL procedure successfully completed.
SYS@orcl_11gR2> CONNECT scott/tiger
Connected.
SCOTT@orcl_11gR2> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
3
4 DBMS_RESOURCE_MANAGER.CREATE_PLAN
5 ('estimated_execution_time', 'estimated_execution_time plan');
6 DBMS_RESOURCE_MANAGER.CREATE_PLAN
7 ('estimated_execution_time2a', 'estimated_execution_time2a plan');
8 DBMS_RESOURCE_MANAGER.CREATE_PLAN
9 ('estimated_execution_time2b', 'estimated_execution_time2b plan');
10
11 DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
12 ('time_estimate', 'time_estimate group');
13
14 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
15 (PLAN => 'estimated_execution_time2a',
16 GROUP_OR_SUBPLAN => 'time_estimate',
17 COMMENT => 'estimated_execution_time2a time_estimate directive',
18 MAX_EST_EXEC_TIME => 2 );
19 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
20 (PLAN => 'estimated_execution_time2b',
21 GROUP_OR_SUBPLAN => 'time_estimate',
22 COMMENT => 'estimated_execution_time2b time_estimate directive',
23 MAX_EST_EXEC_TIME => 3 );
24 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
25 (PLAN => 'estimated_execution_time',
26 GROUP_OR_SUBPLAN => 'estimated_execution_time2a',
27 COMMENT => 'estimated_execution_time estimated_exuciton_time2a directive');
28 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
29 (PLAN => 'estimated_execution_time',
30 GROUP_OR_SUBPLAN => 'estimated_execution_time2b',
31 COMMENT => 'estimated_execution_time estimated_exuciton_time2b directive');
32 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
33 (PLAN => 'estimated_execution_time',
34 GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
35 COMMENT => 'estimated_execution_time other_groups directive' );
36 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
37 (PLAN => 'estimated_execution_time2a',
38 GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
39 COMMENT => 'estimated_execution_time2a other_groups directive' );
40 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
41 (PLAN => 'estimated_execution_time2b',
42 GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
43 COMMENT => 'estimated_execution_time2b other_groups directive' );
44
45 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
46
47 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP
48 ('scott', 'time_estimate', TRUE );
49 DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP
50 ('scott', 'time_estimate');
51 END;
52 /
PL/SQL procedure successfully completed.
-- estimated_execution_time2a (max_est_exec_time 2 seconds):
SCOTT@orcl_11gR2> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = estimated_execution_time2a scope = memory
2 /
System altered.
SCOTT@orcl_11gR2> SELECT COUNT(*) FROM all_objects
2 /
SELECT COUNT(*) FROM all_objects
*
ERROR at line 1:
ORA-07455: estimated execution time (4 secs), exceeds limit (2 secs)
-- estimated_execution_time2b (max_est_exec_time 3 seconds):
SCOTT@orcl_11gR2> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = estimated_execution_time2b scope = memory
2 /
System altered.
SCOTT@orcl_11gR2> SELECT COUNT(*) FROM all_objects
2 /
SELECT COUNT(*) FROM all_objects
*
ERROR at line 1:
ORA-07455: estimated execution time (4 secs), exceeds limit (3 secs)
-- estimated_execution_time (max_est_exec_time 2 seconds)
-- (inherits lesser of max_est_exec_time of 2 seconds
-- from estimated_execution_time2a (2 seconds)
-- and estimated_execution_time2b (3 seconds)):
SCOTT@orcl_11gR2> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = estimated_execution_time scope = memory
2 /
System altered.
SCOTT@orcl_11gR2> SELECT COUNT(*) FROM all_objects
2 /
SELECT COUNT(*) FROM all_objects
*
ERROR at line 1:
ORA-07455: estimated execution time (4 secs), exceeds limit (2 secs)
-- cleanup:
SCOTT@orcl_11gR2> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ''
2 /
System altered.
SCOTT@orcl_11gR2> BEGIN
2 DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
3 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
4 DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE ('estimated_execution_time');
5 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
6 END;
7 /
PL/SQL procedure successfully completed.
[Updated on: Tue, 25 June 2013 14:52] Report message to a moderator |
|
|
|
|
|
|
|
| Re: know the execution time without running the query [message #589266 is a reply to message #588442] |
Wed, 03 July 2013 20:47 |
|
klb1953
Messages: 5
Registered: January 2013
|
Junior Member |
|
|
Hi Barbara
I did some more investigation and came accross this post.
http://www.pythian.com/blog/oracle-limiting-query-runtime-without-killing-the-session/
We set the up resource manager as follows
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
DBMS_RESOURCE_MANAGER.CREATE_PLAN ('X_TEST_PLAN', 'X_TEST_PLAN plan');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP ('time_limit', 'Group with limited CPU time');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'X_TEST_PLAN',
GROUP_OR_SUBPLAN => 'time_limit',
COMMENT => 'X_TEST_PLAN time_limit group use only 30 secs CPU directive',
SWITCH_GROUP => 'CANCEL_SQL',
SWITCH_TIME => 30,
SWITCH_ESTIMATE => false);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ('X_TEST_PLAN','OTHER_GROUPS', 'X_TEST_PLAN other_groups directive' );
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('XSLINDEN', 'time_limit', TRUE );
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('OPS$PP2', 'time_limit', TRUE );
END;
/
-- Enable new plan
alter system set RESOURCE_MANAGER_PLAN=X_TEST_PLAN scope=memory;
alter system set RESOURCE_LIMIT=true scope=memory;
This correctly cancels the sql after 30 CPU secs of execution but what I really need is to cancel the sql after 30 secs elapsed time. As there does not seem to be a setting for elapsed time, we are now trying to come up with a correlation between CPU exec time and elapsed time in 'normal load'. This post
http://stackoverflow.com/questions/3559189/oracle-query-execution-time
suggested 'looking at consistent gets/logical reads as a better proxy for 'work' than run time'.
Regards
Ken
|
|
|
|
|
|
| Re: know the execution time without running the query [message #590413 is a reply to message #589288] |
Wed, 17 July 2013 20:49 |
|
klb1953
Messages: 5
Registered: January 2013
|
Junior Member |
|
|
Hi Barbara
After a lot of experimentation I found the following to work. I have posted the details for the record. I would value any comments on it.
Regards
Ken
-- Take 3 using the technique form here http://www.pythian.com/blog/oracle-limiting-query-runtime-without-killing-the-session/
-- but using dbms_session.switch_current_consumer_group and dbms_resource_manager.switch_consumer_group_for_sess
-- instead of alter system ...
-- First
-- Using a background monitor job to cancel long running SQL using elapsed time
-- Ideally using exec dbms_resource_manager.switch_consumer_group_for_sess(sid,serial#,'CANCEL_SQL');
-- However, there is a bug in Oracle 11.2.03 and a patch that may fix it. The following is an example
-- of the error and the URL that mentions the bug and patch
/*
I get this error
14:53:06 OPS$PP2@DPR01>exec dbms_resource_manager.switch_consumer_group_for_sess(516,9803,'CANCEL_SQL');
BEGIN dbms_resource_manager.switch_consumer_group_for_sess(516,9803,'CANCEL_SQL'); END;
*
ERROR at line 1:
ORA-29366: invalid CONSUMER_GROUP argument specified
ORA-06512: at "SYS.DBMS_RMIN", line 505
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 916
ORA-06512: at line 1
14:53:11 OPS$PP2@DPR01>
This post mentions a bug and a patch in 11.2.03 that could be related
http://garrulus1.rssing.com/browser.php?indx=1763441&item=19
*/
-- To work around this until and if we are able to apply the patch set up a consumer group that limits CPU
-- execution time to 1 sec
-- Second (Only needed until such time as the patch is applied and assuming the patch fixes the error)
-- Set up resource config which limits cpu execution time (active time) to 1 second before cancelling SQL
-- Run as SYS --
alter system set RESOURCE_LIMIT=false scope=memory;
alter system set RESOURCE_MANAGER_PLAN='' scope=memory;
-- Create new resource config
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
DBMS_RESOURCE_MANAGER.CREATE_PLAN ('X_TEST_PLAN', 'X_TEST_PLAN plan');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP ('time_limit', 'Group with limited CPU time');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'X_TEST_PLAN',
GROUP_OR_SUBPLAN => 'time_limit',
COMMENT => 'X_TEST_PLAN time_limit group use only 1 sec CPU directive',
SWITCH_GROUP => 'CANCEL_SQL',
SWITCH_TIME => 1,
SWITCH_ESTIMATE => false);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ('X_TEST_PLAN','OTHER_GROUPS', 'X_TEST_PLAN other_groups directive' );
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('OPS$PP2', 'time_limit', TRUE );
END;
/
-- Enable new plan
alter system set RESOURCE_MANAGER_PLAN=X_TEST_PLAN scope=memory;
alter system set RESOURCE_LIMIT=true scope=memory;
-- Third
-- Create a query_control table to hold the details of queries to be monitored
DROP TABLE QUERY_CONTROL;
CREATE TABLE QUERY_CONTROL
(
QC_ID NUMBER(10,0) NOT NULL
,QC_SID NUMBER(10,0) NOT NULL
,QC_SERIAL# NUMBER(10,0) NOT NULL
,QC_OSUSER VARCHAR2(30) NOT NULL
,QC_MACHINE VARCHAR2(64) NOT NULL
,QC_START_DATE DATE NOT NULL
,QC_END_DATE DATE NOT NULL
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE USERS
;
ALTER TABLE QUERY_CONTROL ADD
(
CONSTRAINT PK_QUERY_CONTROL
PRIMARY KEY (QC_ID)
USING INDEX
TABLESPACE USERS
)
/
DROP SEQUENCE SQ_QUERY_CONTROL;
CREATE SEQUENCE SQ_QUERY_CONTROL INCREMENT BY 1 START WITH 1;
-- Fourth
-- Block to execute a long running query in one session with a 30 sec elapsed timeout
declare
PRAGMA AUTONOMOUS_TRANSACTION;
l_old_group varchar2(40);
l_qc_id query_control.qc_id%type := null;
l_sid number(10,0) := null;
l_serial# number(10,0) := null;
l_osuser varchar2(30) := null;
l_machine varchar2(64) := null;
begin
-- Insert row into query_control
select sid, serial#, osuser, machine
into l_sid, l_serial#, l_osuser, l_machine
from v$session
where audsid = userenv('sessionid');
l_qc_id := SQ_QUERY_CONTROL.nextval;
insert into query_control (
QC_ID
,QC_SID
,QC_SERIAL#
,QC_OSUSER
,QC_MACHINE
,QC_START_DATE
,QC_END_DATE
) values (
l_qc_id
,l_sid
,l_serial#
,nvl(l_osuser,'UNKNOWN')
,nvl(l_machine,'UNKNOWN')
,sysdate
,sysdate+30/(24*60*60)
);
commit;
-- Execute long running query
dbms_output.put_line('query started ' || sysdate);
for r in (
select * from (
-- Long running query test should be cancelled
SELECT score, bd.bd_id, bd_empl_no, bd_claim_suffix_no, bd_temp_claim_no,bd_prov_abn, bd_prov_nme, bd_prov_inv_no, bd_td_id, td_desc, bd_trim_identifier,bd_empl_sur_nme, bd_empl_given_nme, bd_empl_former_sur_nme, bd_empl_birth_dt,bd_empl_ags_no, bd_inj_desc, bd_inj_dt, null pracsys_claim_status, null pracsys_determ_status, is_desc, bd_prov_inv_dt, bd_prov_inv_amt, null pracsys_claim_inv_no,bd_dc_id, bd_ic_id, bd_doc_title, bd_dl_id, dl_desc, bd_comment, null pracsys_rec_type, null pracsys_inv_status FROM ( SELECT * FROM ( SELECT /* FIRST_ROWS(100) */ SCORE(1)*10000 score, bis_tbl_rowid bd_rowid FROM bpu_index_store bis WHERE CONTAINS(bis_text, parser.progrelax('an* chu*'), 1) > 0 AND bis_tbl_nme = 'BPU_DOCUMENT' ORDER BY SCORE(1) DESC ) WHERE ROWNUM < 101 ) mbd INNER JOIN bpu_document bd ON (bd.ROWID = mbd.bd_rowid)INNER JOIN trim_destination ON (td_id = bd_td_id) INNER JOIN document_location dl ON (dl_id = bd_dl_id)LEFT OUTER JOIN invoice_status ON (is_id = bd_is_id)
UNION
SELECT score, null bd_id, pcv.empl_no,pcv.claim_suffix_no, null temp_claim_no, null prov_abn, null prov_nme, null prov_inv_no,null bd_td_id, null td_desc, null bd_trim_identifier, pcv.empl_sur_nme, pcv.empl_given_nme,pcv.empl_former_sur_nme, pcv.birth_dte, pcv.ags_no, pcv.inj_desc, pcv.inj_dte,pcv.pracsys_claim_status, pcv.pracsys_determ_status, null is_desc, null ext_inv_dt, null inv_tot_amt, null pracsys_claim_inv_no, null bd_dc_id, null bd_ic_id, null bd_doc_title,dl_id, dl_desc, null comm, 'Claim' pracsys_rec_type, null pracsys_inv_status FROM (SELECT * FROM ( SELECT /* FIRST_ROWS(100) */ SCORE(1)*10000 score,bis_tbl_rowid claims_rowid FROM bpu_index_store bis WHERE CONTAINS(bis_text,parser.progrelax('an* chu*'), 1) > 0 AND bis_tbl_nme = 'CLAIMS' ORDER BY SCORE(1) DESC )WHERE ROWNUM < 101 ) mc INNER JOIN pracsys_claims_v pcv ON (pcv.claims_rowid = mc.claims_rowid) INNER JOIN document_location dl ON (dl_id = 1)
UNION
SELECT score, null bd_id, pciv.empl_no,pciv.claim_suffix_no, null temp_claim_no, abn_no, prov_nme, null prov_inv_no, null bd_td_id, null td_desc, null bd_trim_identifier, pciv.empl_sur_nme, pciv.empl_given_nme,pciv.empl_former_sur_nme, pciv.birth_dte, pciv.ags_no, pciv.inj_desc, pciv.inj_dte,pciv.pracsys_claim_status, pciv.pracsys_determ_status, null is_desc, ext_inv_dt,inv_tot_amt, pciv.claim_inv_no, null bd_dc_id, null bd_ic_id, null bd_doc_title,dl_id, dl_desc, comm, 'Invoice' pracsys_rec_type, pciv.pracsys_inv_status FROM ( SELECT * FROM ( SELECT /* FIRST_ROWS(100) */ SCORE(1)*10000 score, bis_tbl_rowid claim_invoice_rowid FROM bpu_index_store bis WHERE CONTAINS(bis_text, parser.progrelax('an* chu*'), 1)> 0 AND bis_tbl_nme = 'CLAIM_INVOICE' ORDER BY SCORE(1) DESC ) WHERE ROWNUM < 101 ) mi INNER JOIN pracsys_claim_invoice_v pciv ON (pciv.claim_invoice_rowid = mi.claim_invoice_rowid) INNER JOIN document_location dl ON (dl_id = 1) ORDER BY 1 desc
--Short query test should complete
--SELECT score, bd.bd_id, bd_empl_no, bd_claim_suffix_no, bd_temp_claim_no,bd_prov_abn, bd_prov_nme, bd_prov_inv_no, bd_td_id, td_desc, bd_trim_identifier,bd_empl_sur_nme, bd_empl_given_nme, bd_empl_former_sur_nme, bd_empl_birth_dt,bd_empl_ags_no, bd_inj_desc, bd_inj_dt, null pracsys_claim_status, null pracsys_determ_status, is_desc, bd_prov_inv_dt, bd_prov_inv_amt, null pracsys_claim_inv_no,bd_dc_id, bd_ic_id, bd_doc_title, bd_dl_id, dl_desc, bd_comment, null pracsys_rec_type, null pracsys_inv_status FROM ( SELECT * FROM ( SELECT /* FIRST_ROWS(100) */ SCORE(1)*10000 score, bis_tbl_rowid bd_rowid FROM bpu_index_store bis WHERE CONTAINS(bis_text, parser.progrelax('+angela +chun'), 1) > 0 AND bis_tbl_nme = 'BPU_DOCUMENT' ORDER BY SCORE(1) DESC ) WHERE ROWNUM < 101 ) mbd INNER JOIN bpu_document bd ON (bd.ROWID = mbd.bd_rowid)INNER JOIN trim_destination ON (td_id = bd_td_id) INNER JOIN document_location dl ON (dl_id = bd_dl_id)LEFT OUTER JOIN invoice_status ON (is_id = bd_is_id)
--UNION
--SELECT score, null bd_id, pcv.empl_no,pcv.claim_suffix_no, null temp_claim_no, null prov_abn, null prov_nme, null prov_inv_no,null bd_td_id, null td_desc, null bd_trim_identifier, pcv.empl_sur_nme, pcv.empl_given_nme,pcv.empl_former_sur_nme, pcv.birth_dte, pcv.ags_no, pcv.inj_desc, pcv.inj_dte,pcv.pracsys_claim_status, pcv.pracsys_determ_status, null is_desc, null ext_inv_dt, null inv_tot_amt, null pracsys_claim_inv_no, null bd_dc_id, null bd_ic_id, null bd_doc_title,dl_id, dl_desc, null comm, 'Claim' pracsys_rec_type, null pracsys_inv_status FROM (SELECT * FROM ( SELECT /* FIRST_ROWS(100) */ SCORE(1)*10000 score,bis_tbl_rowid claims_rowid FROM bpu_index_store bis WHERE CONTAINS(bis_text,parser.progrelax('+angela +chun'), 1) > 0 AND bis_tbl_nme = 'CLAIMS' ORDER BY SCORE(1) DESC )WHERE ROWNUM < 101 ) mc INNER JOIN pracsys_claims_v pcv ON (pcv.claims_rowid = mc.claims_rowid) INNER JOIN document_location dl ON (dl_id = 1)
--UNION
--SELECT score, null bd_id, pciv.empl_no,pciv.claim_suffix_no, null temp_claim_no, abn_no, prov_nme, null prov_inv_no, null bd_td_id, null td_desc, null bd_trim_identifier, pciv.empl_sur_nme, pciv.empl_given_nme,pciv.empl_former_sur_nme, pciv.birth_dte, pciv.ags_no, pciv.inj_desc, pciv.inj_dte,pciv.pracsys_claim_status, pciv.pracsys_determ_status, null is_desc, ext_inv_dt,inv_tot_amt, pciv.claim_inv_no, null bd_dc_id, null bd_ic_id, null bd_doc_title,dl_id, dl_desc, comm, 'Invoice' pracsys_rec_type, pciv.pracsys_inv_status FROM ( SELECT * FROM ( SELECT /* FIRST_ROWS(100) */ SCORE(1)*10000 score, bis_tbl_rowid claim_invoice_rowid FROM bpu_index_store bis WHERE CONTAINS(bis_text, parser.progrelax('+angela +chun'), 1)> 0 AND bis_tbl_nme = 'CLAIM_INVOICE' ORDER BY SCORE(1) DESC ) WHERE ROWNUM < 101 ) mi INNER JOIN pracsys_claim_invoice_v pciv ON (pciv.claim_invoice_rowid = mi.claim_invoice_rowid) INNER JOIN document_location dl ON (dl_id = 1) ORDER BY 1 desc
)
) loop
exit;
end loop;
dbms_output.put_line('query complete ' || sysdate);
-- Delete row inserted into query_control
delete from query_control
where qc_id = l_qc_id;
commit;
exception
when others then
if sqlcode= -40 or (sqlcode = -29903 and instr(sqlerrm, 'ORA-00040:') > 0) then
-- SQL cancelled so reset consumer group
-- Do this by first switching to the time_limit group and then to the blank group
-- as just switching to the blank group does not work unless dbms_session is used
-- to switch to the time_limit group first
dbms_session.switch_current_consumer_group('time_limit', l_old_group, true);
dbms_session.switch_current_consumer_group('', l_old_group, true);
end if;
dbms_output.put_line('In exception block ' || sysdate);
dbms_output.put_line('sqlcode=' || sqlcode);
dbms_output.put_line('sqlerrm=<<' || sqlerrm || '>>');
-- Delete the query control row in case it has not already been deleted
delete from query_control
where qc_id = l_qc_id;
commit;
end;
/
-- Monitor query in another session (or from a database scheduled job)
declare
l_qc_id query_control.qc_id%type := null;
begin
for r in (
select * from query_control order by qc_id
) loop
if r.qc_end_date < sysdate then
l_qc_id := r.qc_id;
dbms_output.put_line('Cancelling query ' || sysdate);
dbms_resource_manager.switch_consumer_group_for_sess(r.qc_sid, r.qc_serial#, 'time_limit'); -- Until issue with group CANCEL_SQL is sorted
delete from query_control
where qc_id = l_qc_id;
commit;
end if;
end loop;
exception
when others then
if sqlcode = -29394 then -- Session no longer exists so just delete the query_control row
delete from query_control
where qc_id = l_qc_id;
commit;
else
dbms_output.put_line('In exception block ' || sysdate);
dbms_output.put_line('sqlcode=' || sqlcode);
dbms_output.put_line('sqlerrm=<<' || sqlerrm || '>>');
end if;
end;
/
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Jun 01 22:56:52 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
