Home » SQL & PL/SQL » SQL & PL/SQL » know the execution time without running the query (10.0 win xp)
know the execution time without running the query [message #333590] Sun, 13 July 2008 05:39 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

Is there a way to know how much time will a query take to execute without running it, just like using the autotrace (traceonly) and explain plan utility.
Re: know the execution time without running the query [message #333596 is a reply to message #333590] Sun, 13 July 2008 07:51 Go to previous messageGo to next message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, no.
Re: know the execution time without running the query [message #333597 is a reply to message #333590] Sun, 13 July 2008 07:54 Go to previous messageGo to next message
Kevin Meade
Messages: 1970
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
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 #333629 is a reply to message #333625] Sun, 13 July 2008 14:47 Go to previous messageGo to next message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you, Barbara! Wow!
Re: know the execution time without running the query [message #588320 is a reply to message #333590] Mon, 24 June 2013 19:38 Go to previous messageGo to next message
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 #588323 is a reply to message #588320] Mon, 24 June 2013 22:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
Are the estimates way off for all of your queries or just those that use Oracle Text or other domain indexes? It may be that this method doesn't work with Oracle Text for some reason.
Re: know the execution time without running the query [message #588324 is a reply to message #588323] Mon, 24 June 2013 23:17 Go to previous messageGo to next message
klb1953
Messages: 5
Registered: January 2013
Junior Member
Seems to be for oracle text queries mainly, but I have not tested it extensively on plain SQL. What I am trying to do is either cancel or trap is advance long running queries against oracle context index. I can do it from a java client using the jdbc statement.cancel method executed by a separate timeout thread. The same technique (using java stored procedures) doesn't work in the JVM in the database (the thread model seems to work differently???) so I thought about trying to trap in advance when I cam across your suggested solution which looked pretty amazing.
Re: know the execution time without running the query [message #588375 is a reply to message #333625] Tue, 25 June 2013 06:35 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
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 #588421 is a reply to message #588324] Tue, 25 June 2013 14:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
Ken,

Sorry, but I don't have any idea why it doesn't work for Oracle Text stuff or a better way.

Barbara
Re: know the execution time without running the query [message #588424 is a reply to message #588375] Tue, 25 June 2013 14:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
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 #588442 is a reply to message #588421] Tue, 25 June 2013 19:09 Go to previous messageGo to next message
klb1953
Messages: 5
Registered: January 2013
Junior Member
Hi Barbara

Thanks for your help. I'll post if any new info comes up Smile

Ken
Re: know the execution time without running the query [message #588451 is a reply to message #588424] Wed, 26 June 2013 00:29 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Barbara,

Perfect!!! Solves my issue. So, by default Oracle takes the MIN of the MAX_EST_EXEC_TIME(s) depending on the GROUP or SUBPLAN in multiple plans case.

Previously I was so confused due to the option GROUP/SUBPLAN. The example clarified my query.
Thank you so much.

[Updated on: Wed, 26 June 2013 00:39]

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 Go to previous messageGo to next message
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 #589288 is a reply to message #589266] Thu, 04 July 2013 01:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
Ken,

You might be interested in v$session_longops:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_3021.htm

http://www.oracle-base.com/dba/script.php?category=monitoring&file=longops.sql

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7482790480065

Barbara
Re: know the execution time without running the query [message #590413 is a reply to message #589288] Wed, 17 July 2013 20:49 Go to previous messageGo to next message
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;
/
Re: know the execution time without running the query [message #590498 is a reply to message #590413] Thu, 18 July 2013 16:24 Go to previous message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
Thanks for posting your solution. I am interested in seeing whether the bugs are fixed in Oracle 12c, but I am having problems downloading the new 12c.
Previous Topic: Need Help in Advance Subqueries (Exists,With .Correlated Subquery)
Next Topic: insert multiple recode in table.
Goto Forum:
  


Current Time: Sun Dec 28 02:51:15 CST 2014

Total time taken to generate the page: 0.07972 seconds