Friday, August 18, 2017

UPDATE! Getting the SQL_ID in the cloud or not



A few years back I post a block of code that would take as input a sql file with one sql statement in it and return the SQL_ID and HASH_VALUE using DBMS_SQL_TRANSLATOR.  The post is here.  It turns out there was a pretty big flaw in that code.  It was assuming there would only be one slash (/) at the end of the statement.  Ack!  Of course if you use the /*+ for hints or /* for a comment, then it would trim off the file at the first slash it found which is clearly wrong.

So here is the new code:
set termout on heading off feedback off verify off
-- File name hgetsqlid.sql
-- Get the SQLID/HASH for a SQL statement in a file
-- The file must contain only ONE SQL statement
-- The Directory "SQL_FILE_LOCATION" must be defined
-- This is the location where this will read the file from
-- Example
-- CREATE OR REPLACE DIRECTORY SQL_FILE_LOCATION AS '/home/oracle/OP';
-- The file MUST end with a "/" on last line
-- Example:
-- Select * from emp
-- /
--
-- May 2015 RVD initial coding
-- Aug 2017 RVD fixed issue with finding the / in commment or hint would trim the file too short
--              some formating and other minor changes

set tab off
set serveroutput on
column directory_path format a100 wrap
prompt *********************************************
prompt Get SQL_ID and HASH VALUE for a SQL statement
prompt One statement in the file and must end with /
prompt Current setting of SQL_FILE_LOCATION:
prompt *********************************************
select directory_path from dba_directories where directory_name = 'SQL_FILE_LOCATION';
prompt *********************************************
accept hgetsqlid_file prompt 'Enter the full file name (with extension): '

DECLARE
    v_bfile BFILE;
    v_clob  CLOB;
    v_sqlid VARCHAR2(13);
    v_sqlhash number;
    v_slash integer := 0;
    e_noslash exception;
BEGIN
    v_bfile := BFILENAME ('SQL_FILE_LOCATION', '&hgetsqlid_file');
    IF DBMS_LOB.FILEEXISTS (v_bfile) = 1 THEN
        DBMS_LOB.OPEN (v_bfile);
        DBMS_LOB.CREATETEMPORARY (v_clob, TRUE, DBMS_LOB.SESSION);
        DBMS_LOB.LOADFROMFILE (v_clob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
        DBMS_LOB.CLOSE (v_bfile);
        -- remove all carrage returns (ASCII 13) from the clob
        -- each line must end with only a line feed (ASCII 10)
        v_clob := replace(v_clob, CHR(13) , '');
        -- trims off training spaces at the end of the file
        v_clob := rtrim(v_clob);
        -- trim off anything else at the end back to the /
        while (dbms_lob.substr(v_clob,1,(DBMS_LOB.GETLENGTH (v_clob)))) <> '/'
          loop
           DBMS_LOB.TRIM (v_clob,(DBMS_LOB.GETLENGTH(v_clob))-1);
          end loop;
        -- remove any trailing spaces or tabs (ASCII 9)
        while DBMS_LOB.INSTR (v_clob, ' '||CHR(10)) > 0 or
              DBMS_LOB.INSTR (v_clob, CHR(9)||CHR(10)) > 0
             loop
             v_clob := replace(v_clob, ' '||CHR(10), CHR(10));
             v_clob := replace(v_clob, CHR(9)||CHR(10), CHR(10));
             end loop;
        -- Find the / at the end of the file
        v_slash := DBMS_LOB.INSTR (v_clob,'/',DBMS_LOB.GETLENGTH(v_clob));
        IF v_slash = 0 THEN RAISE e_noslash; END IF;
        -- remove the line with the slash and everything after it
        DBMS_LOB.TRIM (v_clob,v_slash-2);
        v_sqlid   :=  DBMS_SQL_TRANSLATOR.SQL_ID (v_clob);
        v_sqlhash :=  DBMS_SQL_TRANSLATOR.SQL_HASH (v_clob);
        dbms_output.put_line ('*************************');
        dbms_output.put_line ('The SQL ID is '||v_sqlid);
        dbms_output.put_line ('Hash value is '||v_sqlhash);
        dbms_output.put_line ('*************************');
    ELSE
        dbms_output.put_line ('** File not found **');
    END IF;
    EXCEPTION
        when e_noslash then
        dbms_output.put_line ('-+-+-+-+-+-+-+-+-');
        dbms_output.put_line ('Slash not found!');
        dbms_output.put_line ('-+-+-+-+-+-+-+-+-');
END;
/

set serveroutput off
set heading on

And an example of using the code:

SQL> @hgetsqlid
*********************************************
Get SQL_ID and HASH VALUE for a SQL statement
One statement in the file and must end with /
Current setting of SQL_FILE_LOCATION:
*********************************************

C:\OP
*********************************************
Enter the full file name (with extension): with.sql
*************************
The SQL ID is 2n63z3ab978kn
Hash value is 2526257748
*************************
SQL> @with

       COUNT(*)
---------------
          75875
          81280
SQL> select PREV_SQL_ID,PREV_CHILD_NUMBER  from v$session WHERE audsid = userenv('sessionid');

PREV_SQL_ID   PREV_CHILD_NUMBER
------------- -----------------
2n63z3ab978kn                 0
SQL>


SQL> get with
  1  select /*+ qb_name(allobjs) */ count(*)
  2    from withlab_allobjects a,
  3  (select /*+ qb_name(owners1) */distinct owner username
  4     from withlab_allobjects ) owners
  5   where a.owner = owners.username
  6  union all
  7  select /*+ qb_name(dbaobjs) */ count(*)
  8    from withlab_dbaobjects d,
  9  (select /*+ qb_name(owners2) */distinct owner username
 10     from withlab_allobjects ) owners
 11*  where d.owner = owners.username
SQL>
Enjoy!!

Monday, July 31, 2017

A two minute tech tip for the cloud or not!

In case you missed this, here I am in video giving a 2 Minute tech tip on QB_NAME.  There is a rumor I mention my favorite water hole in this, you'll have to watch it to see if I do or not.

https://www.youtube.com/watch?v=3t5NHtPKji0






Tuesday, July 25, 2017

SQLPLUS, login.sql and 12.2 in the cloud or not



Another little hiccup in 12.2 this time with SQLPlus.  It appears that it will not run the login.sql in your current directory as it always has in the past.    

There seems to be a problem with SQLPlus and SQLCL using the SQLPATH setting.  SQLPlus as far as I can tell, still uses SQLPATH as it always has for running a SQL file like:

SQL> @emp

    EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
--------------- -------------------- ------------------------- -------------------------
            105 David                Austin                    DAUSTIN
            151 David                Bernstein                 DBERNSTE
            165 David                Lee                       DLEE
SQL>

In SQLPlus, the GET command as far as I know has never used SQLPATH to find files, it only looks in the current directory, and that seems to be working like normal.  

But SQLCL the GET command doesn’t appear to be working correctly.  You can get it to look in what SQLPATH is set to as long as SQLPATH only has ONE directory in it.  If SQLPATH has multiple entries (which mine does) then it doesn’t use it at all. 

This is a known issue in SQLPlus and you can look it up under this document number in your myoraclesupport account.  Doc ID 2274608.1   By the way, the doc talks about the glogin.sql file not being run, that doesn’t appear to be the case for me.  My fix was to add a call to my login.sql file in the glogin.sql file (in ORACLE_HOME/sqlplus/admin) and it worked just fine. The doc indicates that if the glogin.sql file is in some other directory pointed to by SQLPATH it doesn’t work, that very well may be true.  

I’ve logged an SR about the issue with SQLCL. I presume there is some common code between the two hence they seem to be related.

Thursday, July 13, 2017

Going to 12.2 in the cloud or not

UPDATE: 25JULY2017 - It appears that an UPGRADE to 12.2 from 12.1 does not generate the issues described below.  Only in a fresh install will these problems appear.  The upgrade on windows used the oracle account to install the software which I believe to be the source of the issue with the ORA-27027 error. - Ric


We all have to upgrade at some point to the latest and greatest version of oracle.  And this is really a good thing as the database does get better with each release.   And every time we do there are always little (or big) gotchas along the way.

In 12.2 I’ve found two things.  First on Windows (yes I do install Oracle on my Windows laptop), I’d recommend NOT using the anonymous user for the owner of the software.  This is recommended by oracle but my experience is it generates a heap of errors in the alert.log and made my laptop un-bootable.  Yea that was fun.

The errors were like this, over and over again.  There were associated with the monitoring back ground processes (MMON and MMNL in particular).  The database did work and log switches worked fine as did DML activity:

ORA-27037: unable to obtain file status,
OSD-04011: GetFileInformationByHandle() failure, unable to obtain file info

To solve this I reinstalled 12.1 and then upgraded to 12.2, and used the oracle user that owned the 12.1 software to install the 12.2 software.  The upgrade took quite a big longer to do then just installing 12.2 alone but I ended up with a database that didn’t cause a ton of errors and I can reboot my laptop too!!  Win!

But wait there’s more! 

Another issue I’ve found is that with a fresh install of 12.1 I can’t get STATISTICS_LEVEL to work properly.  With a new 12.2 install once I set STATISTICS_LEVEL to ALL it seems to always stay that way.  On a Linux cloud instance I have 12.2 installed as a fresh install (which doesn’t have the errors I saw in my Windows install) once I set STATISTICS_LEVEL to ALL it never seems to change, even when it “looks” like it’s changed.  This same thing did happen on my Windows laptop when I did the fresh install, but doesn’t happen after the upgrade, very odd to be sure.

As a quick example:

SQL>
SQL>select sql_trace, count(*) from v$session group by sql_trace;

SQL_TRAC COUNT(*)
-------- ---------------
DISABLED 42
SQL>
SQL>select count(*) from DBA_ENABLED_TRACES;

COUNT(*)
---------------
0
SQL>
SQL>show parameter statistics_level

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
SQL>
SQL>select * from dual;

D
-
X
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

---------------------------------------------------------------------------------
| Id | Operation       | Name | Starts | E-Rows | A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |      |      1 |        |      1 |00:00:00.01 |       3 |
| 1 | TABLE ACCESS FULL| DUAL |      1 |      1 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------

The statistics/tracing level sure appears to be typical and no trace appears to be turned on, yet it still shows all the stats you’d see if it was on. 

What you should get for this plan is something like this, note the warning at the bottom:

SQL> select * from dual;

D
-
X

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

----------------------------------------
| Id | Operation       | Name | E-Rows |
----------------------------------------
| 0 | SELECT STATEMENT |      |        |
| 1 | TABLE ACCESS FULL| DUAL |      1 |
----------------------------------------

Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

I have created an SR with Oracle Support on both these issues and they have their top people working on them. Top people. 

If anyone else can confirm or deny my findings I’d sure like to hear from you. Have a great day!