LeRoy,
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@xxxxxxxxxxxxxxxx]
*Sent:* Monday, June 15, 2015 9:25 AM
*To:* Leroy Kemnitz
*Cc:* Oracle-l Digest Users; oracle-l-bounce@xxxxxxxxxxxxx; 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@xxxxxxxx <mailto:lkemnitz@xxxxxxxx>>
*To: *"Sayan Sergeevich Malakshinov" <malakshinovss@xxxxxxxxx <mailto:malakshinovss@xxxxxxxxx>>
*Cc: *"Oracle-l Digest Users" <oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>>, oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
*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@xxxxxxxxx]
*Sent:* Monday, June 15, 2015 8:45 AM
*To:* Leroy Kemnitz
*Cc:* Oracle-l Digest Users; oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
*Subject:* Re: _query_execution_time_limit
Very interesting parameter. I see it on 12.1.0.2 only:
SQL> @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/>
