Feed aggregator

The Transgressive Manifesto is quite short: It's okay to use GOTO. The single underlying principle is that we value willful controversy over mindless conformity.

I do have a serious point here. Even programmers who haven't read the original article (because they can't spell Dijkstra and so can't find it through Google) know that GOTOs are "considered harmful". But as Marshall and Webber point out, "the problem lies ... in the loss of knowledge and experience. If something is forbidden for long enough, it becomes difficult to resurrect the knowledge of how to use it."

How many Oracle developers even realise PL/SQL supports GOTO? It does, of course. Why wouldn't it? PL/SQL is a proper programming language.

The standard objection is that there is no role for GOTO because PL/SQL has loops, procedures, CASE, etc. But sometimes we need to explicitly transfer control. In recent times I have have across these examples:

• a loop which raised a user-defined exception to skip to the END LOOP; point when the data was in certain ststes, thus avoiding large chunk of processing. A GOTO would have have been cleaner, because it is poor practice to represent normal business states as exceptions.
• a huge function with more than a dozen separate RETURN statements. GOTOs directing flow to a single RETURN call would have been really helpful, because I needed to log the returned value.
• a condition which set a counter variable to a large number so as to short-circuit a loop. Here a GOTO would simply have been more honest.

Here is a situation I have come across a few times. The spec is to implement a series of searches of increasing coarseness, depending on which arguments are passed; the users want the most focused set of records available, so once a specific search gets some hits we don't need to run the more general searches.

Nested IF statements provide one way to do this:

    result_set := sieve_1(p1=>var1, p2=>var2, p3=>var4, p4=>var5);

    if result_set.count() = 0
    then
        result_set := sieve_2(p1=>var2, p2=>var3, p3=>var4);

        if result_set.count() = 0
        then
            result_set := sieve_3(p1=>var3, p2=>var5);

            if result_set.count() = 0
            then
                ....
            end if;
        end if;
    end if;      
    return result_set;     

    result_set := sieve_1(p1=>var1, p2=>var2, p3=>var4, p4=>var5);
    if result_set.count() > 0
    then
        goto return_point;
    end if;
    
    result_set := sieve_2(p1=>var2, p2=>var3, p3=>var4);
    if result_set.count() > 0
    then
        goto return_point;
    end if;

    result_set := sieve_3(p1=>var3, p2=>var5);
    if result_set.count() > 0
    then
        goto return_point;
    end if;
    ...
    << return_point >>
    return result_set;     

tl;dr
GOTO: not as evil as triggers.

I often get asked to take a look at an Oracle eBusiness Suite concurrent request to see what it is doing, this can come from a few different angles:

1. What requests are currently running?
2. I have an operating system process that is taking too much CPU - what is it doing?
3. Can you tell me where the request is at? I've clicked on the log but it doesn't show anything!
4. My request is taking too long - can you check for blocking locks?

There are a number of strategies to track and trace where things are at for a running request, these include:

1. Monitor the currently running requests in fnd_concurrent_requests
2. Checking the v$sqlarea to see what SQL statement or PL/SQL is running
3. Tailing the concurrent request log / output file while it is being written to near realtime - prior to request completing
4. Checking for locks blocking the concurrent request

So without further ado, let's take a look at the following sweet query. UPDATE: 23-Aug-2012 fixed multi rows due to missing join on inst_id between gv$session and gv$process. Also note for non-RAC environments change gv$ to v$ and remove joins to sys.v_$active_instances:

set pages 9999 feed on lines 150
col user_concurrent_program_name format a40 head PROGRAM trunc
col elapsed format 9999
col request_id format 9999999 head REQUEST
col user_name format a12
col oracle_process_id format a5 head OSPID
col inst_name format a10
col sql_text format a30
col outfile_tmp format a30
col logfile_tmp format a30

REM *********************
REM **** RAC VERSION ****
REM *********************
select /*+ ordered */ 
       fcp.user_concurrent_program_name
,      fcr.request_id
,      round(24*60*( sysdate - actual_start_date )) elapsed
,      fu.user_name
,      fcr.oracle_process_id
,      sess.sid
,      sess.serial#
,      inst.inst_name
,      sa.sql_text
,      cp.plsql_dir || '/' || cp.plsql_out outfile_tmp
,      cp.plsql_dir || '/' || cp.plsql_log logfile_tmp
from   apps.fnd_concurrent_requests fcr
,      apps.fnd_concurrent_programs_tl fcp
,      apps.fnd_concurrent_processes cp
,      apps.fnd_user fu
,      gv$process pro
,      gv$session sess
,      gv$sqlarea sa
,      sys.v_$active_instances inst
where  fcp.concurrent_program_id = fcr.concurrent_program_id
and    fcp.application_id = fcr.program_application_id
and    fcr.controlling_manager = cp.concurrent_process_id
and    fcr.requested_by = fu.user_id (+)
and    fcr.oracle_process_id = pro.spid (+)
and    pro.addr = sess.paddr (+)
and    pro.inst_id = sess.inst_id (+)
and    sess.sql_address = sa.address (+)
and    sess.sql_hash_value = sa.hash_value (+)
and    sess.inst_id = inst.inst_number (+)
and    fcr.phase_code = 'R' /* only running requests */
;

REM *********************
REM ** NON-RAC VERSION **
REM *********************
select /*+ ordered */ 
       fcp.user_concurrent_program_name
,      fcr.request_id
,      round(24*60*( sysdate - actual_start_date )) elapsed
,      fu.user_name
,      fcr.oracle_process_id
,      sess.sid
,      sess.serial#
,      sa.sql_text
,      cp.plsql_dir || '/' || cp.plsql_out outfile_tmp
,      cp.plsql_dir || '/' || cp.plsql_log logfile_tmp
from   apps.fnd_concurrent_requests fcr
,      apps.fnd_concurrent_programs_tl fcp
,      apps.fnd_concurrent_processes cp
,      apps.fnd_user fu
,      v$process pro
,      v$session sess
,      v$sqlarea sa
where  fcp.concurrent_program_id = fcr.concurrent_program_id
and    fcp.application_id = fcr.program_application_id
and    fcr.controlling_manager = cp.concurrent_process_id
and    fcr.requested_by = fu.user_id (+)
and    fcr.oracle_process_id = pro.spid (+)
and    pro.addr = sess.paddr (+)
and    sess.sql_address = sa.address (+)
and    sess.sql_hash_value = sa.hash_value (+)
and    fcr.phase_code = 'R' /* only running requests */
;

PROGRAM                                   REQUEST ELAPSED USER_NAME    OSPID        SID    SERIAL# INST_NAME  SQL_TEXT                       OUTFILE_TMP                    LOGFILE_TMP
---------------------------------------- -------- ------- ------------ ----- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------------
Workflow Background Process               2960551       1 VIRTUATE     24814        130      29699 APPLPROD1  BEGIN WF_ENGINE.BACKGROUNDCONC /usr/tmp/o0068194.tmp          /usr/tmp/l0068194.tmp
                                                                                                              URRENT(:errbuf,:rc,:A0,:A1,:A2
                                                                                                              ,:A3,:A4,:A5); END;

1 row selected.

From the above we can see key information:

• The running Concurrent Request Program Name and Request_ID
• The database node operating system process id (OSPID) so we can monitor usage via top / iostat / vmstat
• The SID / Serial in case we want to kill the session via alter system kill session '130,29699';
• The instance name the database session is running on in a RAC environment
• The currently running SQL text
• The temporary files where concurrent request log/out is being written to via utl_file while running. These files are copied over to the Concurrent Tier $APPLCSF/$APPLOUT and $APPLLOG after completion of the request.

We can break out the above into a few queries and procedures to drill into specific information information from the core EBS tables and DBA v$ views

col user_concurrent_program_name format a40 head PROGRAM trunc
col elapsed format 9999
col request_id format 9999999 head REQUEST
col user_name format a12
col oracle_process_id format a5 head OSPID
select  fcp.user_concurrent_program_name
,       fcr.request_id
,       round(24*60*( sysdate - actual_start_date )) elapsed
,       fu.user_name
,       fcr.oracle_process_id
from    apps.fnd_concurrent_requests fcr
,       apps.fnd_concurrent_programs_tl fcp
,       apps.fnd_user fu
where   fcp.concurrent_program_id = fcr.concurrent_program_id
and     fcp.application_id = fcr.program_application_id
and     fu.user_id = fcr.requested_by
and     fcr.phase_code = 'R';

PROGRAM                                   REQUEST ELAPSED USER_NAME    OSPID
---------------------------------------- -------- ------- ------------ -----
Virtuate GL OLAP Data Refresh             2960541       5 VIRTUATE     21681

col inst_name format a10
col sql_text format a30
col module format a20

REM *********************
REM **** RAC VERSION ****
REM *********************
select sess.sid
,      sess.serial#
,      sess.module
,      sess.inst_id
,      inst.inst_name
,      sa.fetches
,      sa.runtime_mem
,      sa.sql_text
,      pro.spid
from   gv$sqlarea sa
,      gv$session sess
,      gv$process pro
,      sys.v_$active_instances inst
where  sa.address = sess.sql_address
and    sa.hash_value = sess.sql_hash_value
and    sess.paddr = pro.addr
and    sess.inst_id = pro.inst_id
and    sess.inst_id = inst.inst_number (+)
and    pro.spid = &OSPID_from_running_request;

REM *********************
REM ** NON-RAC VERSION **
REM *********************

select sess.sid
,      sess.serial#
,      sess.module
,      sa.fetches
,      sa.runtime_mem
,      sa.sql_text
,      pro.spid
from   v$sqlarea sa
,      v$session sess
,      v$process pro
where  sa.address = sess.sql_address
and    sa.hash_value = sess.sql_hash_value
and    sess.paddr = pro.addr
and    pro.spid = &OSPID_from_running_request;

If you're running something that has long SQL statements, get the full SQL Statement by selecting from v$sqltext_with_newlines as follows

select t.sql_text
from   v$sqltext_with_newlines t
,      v$session s
where  s.sid = &SID
and    s.sql_address = t.address
order by t.piece

col outfile format a30
col logfile format a30
select cp.plsql_dir || '/' || cp.plsql_out outfile
,      cp.plsql_dir || '/' || cp.plsql_log logfile
from  apps.fnd_concurrent_requests cr
,     apps.fnd_concurrent_processes cp
where cp.concurrent_process_id = cr.controlling_manager
and cr.request_id = &request_id;

OUTFILE                        LOGFILE
------------------------------ ------------------------------
/usr/tmp/PROD/o0068190.tmp     /usr/tmp/PROD/l0068190.tmp

REM Now tail log file on database node to see where it is at, near realtime
REM tail -f /usr/tmp/l0068190.tmp

Then on the Database node you can tail -f the above plsql_out or plsql_log files to see where program is at. Combine this with good logging techniques (date/time stamp on each entry) and you'll be able to know where your program is at.

If locks are the potential problem, then drill into those:

set lines 150
col object_name format a32
col mode_held format a15
select /*+ ordered */
       fcr.request_id
,      object_name
,      object_type
,      decode( l.block
             , 0, 'Not Blocking'
             , 1, 'Blocking'
             , 2, 'Global'
             ) status
,      decode( v.locked_mode
             , 0, 'None'
             , 1, 'Null'
             , 2, 'Row-S (SS)'
             , 3, 'Row-X (SX)'
             , 4, 'Share'
             , 5, 'S/Row-X (SSX)'
             , 6, 'Exclusive'
             , to_char(lmode)
             ) mode_held
from   apps.fnd_concurrent_requests fcr
,      gv$process pro
,      gv$session sess
,      gv$locked_object v
,      gv$lock l
,      dba_objects d
where  fcr.phase_code = 'R'
and    fcr.oracle_process_id = pro.spid (+)
and    pro.addr = sess.paddr (+)
and    sess.sid = v.session_id (+)
and    v.object_id = d.object_id (+)
and    v.object_id = l.id1 (+)
;

REQUEST_ID OBJECT_NAME                      OBJECT_TYPE         STATUS       MODE_HELD
---------- -------------------------------- ------------------- ------------ ---------------
   1070780 VIRTUATE_GL_OLAP_REFRESH         TABLE               Not Blocking Exclusive

So there you have it - enough tools to keep you happy Track n Tracing! Maybe next time we'll look at tracing with bind / waits or PL/SQL Profiling concurrent programs

• Query: Find scheduled or on hold concurrent requests
• Query: Concurrent Programs by User and Responsibility
• Where do I get the XML file or request output file for my BI Publisher Report / Concurrent Request?

Berkeley DB Java Edition 5.x has significant performance improvements. One user noted that they are seeing a 20x improvement in cleaner performance.

Snip from a very Nice Slide Deck from a Presentation by the E-Business Suite Team.

See the entire presentation here

Snip from a very Nice Slide Deck from a Presentation by the E-Business Suite Team.

See the entire presentation here

Going freelance has been a big step, as I was with Logica for over seventeen years. Actually, the company I joined was CMG, which through a chain of mergers, acquisitions and re-brandings became CMG Admiral, LogicaCMG, Logica and is now part of CGI. Contracting is something I have been considering for a while years but the actual decision almost took me by surprise. It's two weeks now, and I'm still enjoying it.

The "new town" part of the title is not quite accurate either, as Croydon is a town with a long and venerable history. But it's not a place I had been to before, although I have driven through it. This is not a crack at Croydon, it's just that sort of place; Wikipedia describes it as "a natural transport corridor between London and England's south coast".

Croydon is a bit odd, comprising zones of office blocks, shopping malls, car parks and entertainment complexes delineated by dual carriage ways and fly-overs. For me its chief virtue is a shortish commute. My last engagement for Logica entailed working away from home. After several months of living in a hotel room it's just nice to come back the family every evening.

1. Recognizers to identify the file types of the source we post
2. Rules to identify items within the files and report on the them

<?xml version="1.0" encoding="UTF-8"?>
<rulesfile version="1.0" name="Shell file recognizer" description="Recognize .sh files">
  <recognizer name="sh" description="sh recognizer" enabled="true">
    <fileExtension extension="sh" />
    <expression><![CDATA[#!/bin/sh]]></expression>
  </recognizer>
</rulesfile>

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="../schema/scanner.xsd"?>
<rulesfile version="1.0" name="Sample sh scanner rules"
    description="Sample rules to show whats possible" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="../../schema/scanner.xsd">
    <ruleset name="isql sample rules" enable="true" type="SYBASE"
        description="" source="sh" codetype="isql">
        <required>
            <regex>
                <expression><![CDATA[go|isql]]></expression>
            </regex>
        </required>
        <rules>
            <regex>
                <expression><![CDATA[go *\n]]></expression>
                <replacement type="text"><![CDATA[]]>
                </replacement>
            </regex>
            <regex>
                <expression><![CDATA[use.*\n]]></expression>
                <replacement type="regex"><![CDATA[]]>
                </replacement>
            </regex>
            <regex>
                <expression><![CDATA[isql.*EOF]]></expression>
                <replacement type="regex"><![CDATA[sqlplus barry/barry <<EOF]]></replacement>
            </regex>
        </rules>
    </ruleset>
    <ruleset name="sql sample rules" enable="true" type="SYBASE"
        description="" source="sh" codetype="sql">
        <required>
            <regex>
                <expression><![CDATA[select]]></expression>
            </regex>
        </required>
        <rules>
            <regex>
                <expression><![CDATA[select.*\n]]></expression>
                <replacement type="translator"/>
            </regex>
        </rules>
    </ruleset>
</rulesfile>

bamcgill-macbook-pro:src bamcgill$ cat test.sh
#!/bin/sh
isql -UMYUSER -PMYPASS -SMYSERVER <<EOF
use pubs2
go
select count(*) from authors
go
select top 5 au_lname,au_fname,postalcode from authors
go
EOF
bamcgill-macbook-pro:src bamcgill$ 

bamcgill-macbook-pro:demo bamcgill$ migration -actions=scan -dir=/Users/bamcgill/code/demo/src -rulesdir=/Users/bamcgill/code/demo/rules -inplace=true

#!/bin/sh
sqlplus barry/barry <<EOF
SELECT COUNT(*) 
  FROM authors ;

SELECT au_lname ,
       au_fname ,
       postalcode 
  FROM authors  WHERE ROWNUM <= 5;

EOF

bamcgill-macbook-pro:demo bamcgill$ migration -actions=scan -dir=/Users/bamcgill/code/demo/src -rulesdir=/Users/bamcgill/code/demo/rules -inplace=true

Oracle SQL Developer
 Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved. 

Finding files....
Default Application Name

                test.sh
                        3:go

                        5:go

                        7:go

                        2:use pubs2

                        1:isql -UMYUSER -PMYPASS -SMYSERVER <<EOF
                        2:select count(*) from authors

                        3:select top 5 au_lname,au_fname,postalcode from authors

------------------------ Application Results -----------------

Call Breakdown Summary by File
------------------------------
/Users/bamcgill/code/demo/src/test.sh
        3:      go

        1:      use pubs2

        1:      isql -UMYUSER -PMYPASS -SMYSERVER <<EOF
        1:      select count(*) from authors

        1:      select top 5 au_lname,au_fname,postalcode from authors


-------------------------------------------------------------

Call Breakdown Summary
----------------------
        3:      go

        1:      use pubs2

        1:      isql -UMYUSER -PMYPASS -SMYSERVER <<EOF
        1:      select count(*) from authors

        1:      select top 5 au_lname,au_fname,postalcode from authors


-------------------------------------------------------------

File Type Summary
----------------------
         sh      1 file

-------------------------------------------------------------
------------------------    Summary   -----------------------
High Level Overview
-------------------
        7 total calls found
        5 distinct calls found
        1 files scanned
        1 language types
        2 total files in source
        9 lines of code in scanned application
-------------------------------------------------------------
-------------------------------------------------------------
scan completed successfully

#onecommand.params generated by GUI tool
# Confuration information
CONFIGVERSION=2.4.0.0.0

# CLUSTERINFO
DBMPREFIX=test1
DOMAIN=test.com
CLUSTERNAME=test1-c
REGION=America
TIMEZONE=America/Chicago

# DATABASE INFO
DbHomeArr=(test1)
OraHomeArr=(/u01/app/oracle/product/11.2.0/dbhome_1)
DbBlkSizArr=(8192)
DBBackupType=External
DataDgArr=(DATA)
RecoDgArr=(RECO)
RedoDgArr=(REDO)
RecoSizeArr=(2048)
DbTypeArr=(RAC)
DbNodeArr=(ALL )
DbCharset=AL32UTF8
DbTeritory=AMERICA
DbLang=AMERICAN
DbClass=VSmall
DbQOSTypArr=(ADMIN)
COMPONENT_LANGUAGES={en}

./oakcli copy -conf /software/test11_12.txt
Successfully copied the config file!!!

[root@oak1 onecmd]# cd /opt/oracle/oak/onecmd
[root@oak1 onecmd]# ./GridInst.pl -l
INFO   : Logging all actions in /dev/null and traces in /dev/null
INFO   : Loading configuration file /opt/oracle/oak/onecmd/onecommand.params...
The steps in order are...
Step  0 = ValidateParamFile
Step  1 = SetupNetwork
Step  2 = WriteNodelists
Step  3 = SetupSSHroot
Step  4 = SetupDNS
Step  5 = UpdateEtcHosts
Step  6 = SetTimezone
Step  7 = SetupNTP
Step  8 = SetupILOM
Step  9 = ValidateEnv
Step 10 = CreateUsers
Step 11 = SetupStorage
Step 12 = SetupSSHusers
Step 13 = InstallGIClone
Step 14 = RunGIClonePl
Step 15 = RunRootScripts
Step 16 = GIConfigAssists
Step 17 = CreateASMDiskgroups
Step 18 = InstallDBClone
Step 19 = RunDBClonePl
Step 20 = DbcaDB
Step 21 = SetupEMDbControl
Step 22 = SetupACFS
Step 23 = SetupTFA
Step 24 = SetupASR
Step 25 = ResecureMachine
[root@oak1 onecmd]#

[root@oak1 onecmd]# ./GridInst.pl -r 1-25

• Would a 15-minute video capture the attention of people who Do people glaze over (TL;DR) on longish printed case studies on which they’d gladly watch about 15 minutes of video?
• Or do people just pass on the prospect of watching a 15-minute case study about a problem they might not even realize they have yet? I ask this, because I find myself not clicking on any video that I know will take longer than a minute or two, unless I believe it’s going to help me solve a difficult problem that I know I have right now.