No worries, we won’t have to lower the Cone of Silence. True, you will see use of an “underbar” init.ora parameter in this post, but its use is not the central theme. No, no Silver Bullets here. This is another post in the Little Things Doth Crabby Make series.
I routinely brag about the sophistication level of my blog readers, so, folks, don’t let me down. Let’s start a thread about why the contents of the following session output would make my Little Things list. OK, come on…
SQL> set timing on
SQL>
SQL> alter session set "_parallel_broadcast_enabled" = FALSE
2
SQL> select count(*) from ap_ae_lines_all where AE_LINE_ID > 1397437860 ;
COUNT(*)
----------
0
Elapsed: 00:01:21.70
SQL>
SQL> alter session set "_parallel_broadcast_enabled" = FALSE;
Session altered.
Elapsed: 00:00:00.00
SQL>
SQL> select count(*) from ap_ae_lines_all where AE_LINE_ID > 1397437860 ;
COUNT(*)
----------
0
Elapsed: 00:01:30.46
The first “alter session” command didn’t execute because there was no semicolon at the end, but the second alter session command did execute. Does that explain the discrepancy in the timings, although they are fairly close?
That’s a 9.64% increase in query response time
on what can be a very large table. I wouldn’d
find that worthy of a remark. [oops, I am
making an observation about it ?]
[I remember that the 11i upgrade scripts had a
few bugslogged for queries on this table
during the upgrade process.]
yep,
you would expect that sqlplus would keep asking for the semicolon. Instead it ignores (silently) the command when you just hit enter again.
Freek,
This was not an interactive SQL*Plus session…it is in a .sql
script…so, I never hit enter at all
In a .sql script, you wouldn’t expect an SQL command to be discarded, so there really should be an error that could be caught (or at least reported on so that you could search for it in a log file).
Actually SET SQLBLANKLINES ON would have had that effect, as the new line wouldn’t have terminated the SQL command. Perhaps I should recommend that as a ‘good practice’ for SQL scripts.
Kevin,
Even so, sqlplus would receive an “enter” if the statement was followed by a blank line.
If your “alter session” would have been directly followed by the select statement, sqlplus would have given an error message:
sys@ORCL> host type c:\little_things.sql
alter session set nls_date_format=’DD/MM/YYYY HH24:MI:SS’
select sysdate from dual;
alter session set nls_date_format=’DD/MM/YYYY HH24:MI:SS’;
select sysdate from dual;
alter session set nls_date_format=’DD/MM/YYYY’
select sysdate from dual;
sys@ORCL> @c:\little_things.sql
sys@ORCL> alter session set nls_date_format=’DD/MM/YYYY HH24:MI:SS’
2
sys@ORCL> select sysdate from dual;
SYSDATE
———-
29/03/2008
sys@ORCL>
sys@ORCL>
sys@ORCL> alter session set nls_date_format=’DD/MM/YYYY HH24:MI:SS’;
Session altered.
sys@ORCL>
sys@ORCL> select sysdate from dual;
SYSDATE
——————-
29/03/2008 18:12:43
sys@ORCL>
sys@ORCL> alter session set nls_date_format=’DD/MM/YYYY’
2 select sysdate from dual;
select sysdate from dual
*
ERROR at line 2:
ORA-00922: missing or invalid option