Little Things Doth Crabby Make Part III. Non-Erroring Errors and Erroneous Experiments.

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

6 Responses to “Little Things Doth Crabby Make Part III. Non-Erroring Errors and Erroneous Experiments.”


  1. 1 Rob Johnson March 28, 2008 at 9:05 pm

    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?

  2. 2 Hemant K Chitale March 29, 2008 at 7:33 am

    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.]

  3. 3 Freek March 29, 2008 at 1:10 pm

    yep,

    you would expect that sqlplus would keep asking for the semicolon. Instead it ignores (silently) the command when you just hit enter again.

  4. 4 kevinclosson March 29, 2008 at 5:06 pm

    Freek,

    This was not an interactive SQL*Plus session…it is in a .sql
    script…so, I never hit enter at all

  5. 5 Gary March 29, 2008 at 9:29 pm

    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.

  6. 6 Freek March 29, 2008 at 10:58 pm

    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


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




DISCLAIMER

I work for Amazon Web Services. The opinions I share in this blog are my own. I'm *not* communicating as a spokesperson for Amazon. In other words, I work at Amazon, but this is my own opinion.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,988 other followers

Oracle ACE Program Status

Click It

website metrics

Fond Memories

Copyright

All content is © Kevin Closson and "Kevin Closson's Blog: Platforms, Databases, and Storage", 2006-2015. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Kevin Closson and Kevin Closson's Blog: Platforms, Databases, and Storage with appropriate and specific direction to the original content.

%d bloggers like this: