Re: _query_execution_time_limit

Underscore parameters are undocumented and unsupported, so ideally you will be able to proceed without setting this parameter.

If you want to limit the execution time of a query, you should use Resource Manager. You can configure maximum execution times, specify what to do when the limit is reached, monitor it with SQL Monitor, etc. See here
<http://docs.oracle.com/database/121/CNCPT/cncptdba.htm#CNCPT1396> and here <http://docs.oracle.com/database/121/ADMIN/dbrm.htm#ADMIN027> for more details.

-Kevin J

-- 
Kevin Jernigan
Senior Director Product Management
Advanced Compression, Hybrid Columnar
Compression (HCC), Database File System
(DBFS), SecureFiles, Database Smart Flash
Cache, Total Recall, Database Resource
Manager (DBRM), Direct NFS Client (dNFS),
Continuous Query Notification (CQN),
Index Organized Tables (IOT), Information
Lifecycle Management (ILM)
+1-650-607-0392 (o)
+1-415-710-8828 (m)

On 6/15/15 7:30 AM, Leroy Kemnitz wrote:


>

> Daniel,

>

> Thanks for pointing that out!  I missed that.  A few other people also 

> mentioned it.  Thank you.

>

> I think I might be passed this error now – I bounced the database.  I 

> applied patches over the weekend and missed the final bounce.

>

> Weird that this parameter isn’t mentioned anywhere in OTN.  It is not 

> even listed in the view of hidden parameters. Obviously, I am not 

> perfect – so maybe it is listed and I didn’t see it.  Will be 

> double-checking.

>

> Thanks for all the help.

>

> LeRoy

>

> *From:*Daniel Westermann [mailto:daniel.westermann_at_dbi-services.com]

> *Sent:* Monday, June 15, 2015 9:25 AM

> *To:* Leroy Kemnitz

> *Cc:* Oracle-l Digest Users; oracle-l-bounce_at_freelists.org; Sayan 

> Sergeevich Malakshinov

> *Subject:* Re: _query_execution_time_limit

>

> did you try to set it less? e.g. 188641956*1* ?

>

> These numbers are somehow flipped, the left number is higher than the 

> right number

>

> Cheers,

>

> Daniel

>

>

>

> ------------------------------------------------------------------------

>

> *From: *"Leroy Kemnitz" <lkemnitz_at_uwsa.edu <mailto:lkemnitz_at_uwsa.edu>>

> *To: *"Sayan Sergeevich Malakshinov" <malakshinovss_at_psbank.ru 

> <mailto:malakshinovss_at_psbank.ru>>

> *Cc: *"Oracle-l Digest Users" <oracle-l_at_freelists.org 

> <mailto:oracle-l_at_freelists.org>>, oracle-l-bounce_at_freelists.org 

> <mailto:oracle-l-bounce_at_freelists.org>

> *Sent: *Monday, June 15, 2015 3:48:12 PM

> *Subject: *RE: _query_execution_time_limit

>

> Yes, very strange.

>

> My error tells me that the value needs to be between 1886419563 and 

> 1433628233 values. I attempt to set it to 18864195*_65_*, and get the 

> same error.

>

> --------------------

>

> SQL> alter system set "_query_execution_time_limit"=1886419565 scope=both;

>

> alter system set "_query_execution_time_limit"=1886419565 scope=both

>

> *

>

> ERROR at line 1:

>

> ORA-00068: invalid value 1886419565 for parameter 

> _query_execution_time_limit,

>

> must be between 1886419563 and 1433628233

>

> ---------------------

>

> LeRoy

>

> *From:*Sayan Sergeevich Malakshinov [mailto:malakshinovss_at_psbank.ru]

> *Sent:* Monday, June 15, 2015 8:45 AM

> *To:* Leroy Kemnitz

> *Cc:* Oracle-l Digest Users; oracle-l-bounce_at_freelists.org 

> <mailto:oracle-l-bounce_at_freelists.org>

> *Subject:* Re: _query_execution_time_limit

>

> Very interesting parameter. I see it on 12.1.0.2 only:

>

> SQL> _at_param_ _query_execution_time_limit

>

> NAME                                     VALUE        DEFLT       

>  TYPE         DESCRIPTION

> ---------------------------------------- ------------ ------------ 

> ------------ ------------------------------------------------------------

> _query_execution_time_limit              0            TRUE         

> number Query execution time limit in seconds

>

> SQL> alter session set "_query_execution_time_limit" = 10;

>

> Session altered.

>

> Elapsed: 00:00:00.01

> SQL> select count(*) from xmltable('1 to 10000000');

>

> COUNT(*)

> ----------

>  2145142

>

> Elapsed: 00:00:05.21

> SQL> alter session set "_query_execution_time_limit" = 0;

>

> Session altered.

>

> Elapsed: 00:00:00.00

> SQL> select count(*) from xmltable('1 to 10000000');

>

> COUNT(*)

> ----------

> 10000000

>

> Elapsed: 00:00:06.35

>

> --

> Best regards,

> Sayan Malakshinov

> http://orasql.org <http://orasql.org/>

>



--
http://www.freelists.org/webpage/oracle-l