Re: _query_execution_time_limit
From: Kevin Jernigan <kevin.jernigan_at_oracle.com>
Date: Thu, 18 Jun 2015 16:33:06 -0700
Message-ID: <55835532.5050701_at_oracle.com>
LeRoy,
Date: Thu, 18 Jun 2015 16:33:06 -0700
Message-ID: <55835532.5050701_at_oracle.com>
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:Received on Fri Jun 19 2015 - 01:33:06 CEST
>
> 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