Oakies Blog Aggregator

oraclebase's picture

Website Outage Warning : 26 Oct 2014 20:00-00:00 GMT

DiagnosticsJust a quick note to say the website will be out of action this evening for 3-4 hours.

There have been a couple of random failures recently. With nothing in the logs to work with, I figured I’d try testing the hardware. Yesterday I tested the disks and they came back OK. Tonight it’s the turn of the memory. The plan is for the site to go down about 20:00 UK Time (GMT) and be up by midnight.

Sorry if this annoys anyone, but I’ve been looking through the site statistics trying to find the best time to do this and Sunday night seems to be the quietest time.

I’ll let you know how it goes. :)

Cheers

Tim…

PS. You can read the stuff from Google’s cache in the short term. Search for the item on Google. When you get it, click the down-arrow next to the URL and select “Cached”. No need to miss anything… :)

GoogleCache


Website Outage Warning : 26 Oct 2014 20:00-00:00 GMT was first posted on October 26, 2014 at 9:38 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.
mwf's picture

Short and sweet: What do you think of when you hear “Republicans?”

Oddly this was a discussion topic on Amazon. Most of it was vitriol from folks who have been schooled to hate Republicans. As a Republican, I know we get a bad rap and often our predominant touted feature is a confusion that we are against stated goals rather than being against the stated means of attempting to achieve those goals.

So here is my answer: What do you think of when you hear “Republicans?”

Folks who want to efficiently create a safe and prosperous society who end up spending all their time trying to stop the Democrats from pursuing stupid, hateful, and wasteful policies that would never work to achieve the laudable goals they claim the policies pursue. Unfortunately the Republicans are then cast as being against those goals, most of which they also support.

davidkurtz's picture

Minimising Parse Time in Application Engine with ReUseStatement

This article explains how to determine the overhead of using literal values rather than bind variables in SQL submitted by PeopleSoft Application Engine programs. Using a combination of PeopleSoft Application Engine Batch Timings and Oracle Active Session History (ASH), it is possible to determine where it is most effective to change to alter this behaviour by setting the ReUse attribute.

ReUse Statement Flag

I originally wrote about the Performance Benefits of ReUseStatement in Application Engine over 5 years ago, and the problem is no less significant today than it was then.  There are still many places in the delivered PeopleSoft application that would benefit from it.  However, it is not possible to simply set the attribute across all Application Engine programs because it will cause errors in steps where SQL is dynamically generated, so each case must be considered.  Where the ReUse attributed is changed, it must be tested and migrated like any other customisation.

Recently, I have been encountering problems in a number of places on a Financials system which were resolved by enabling ReUseStatement.  So I started by calculating how much time was lost by not setting it.

Application Engine Batch Timings

If an Application Engine step is not reused, then it is compiled prior to every execution during which the Application Engine bind variables are resolved to literals. The number and duration of compilations and executions are reported in the Batch Timings, the production of which are controlled with the TraceAE flag in the Process Scheduler configuration file (psprcs.cfg).  

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
;-------------------------------------------------------------------------
; AE Tracing Bitfield
;
; Bit Type of tracing
; --- ---------------
...
; 128 - Timings Report to AET file
...
; 1024 - Timings Report to tables
...
TraceAE=1152
;------------------------------------------------------------------------

Whatever other TraceAE flags you set, I would always recommend that you set 128 and 1024 so that batch timings are always emitted to both file and database.  The overhead of enabling them is negligible because they are managed in memory at run time and physically written once when the Application Engine program ends.

NB: The settings in the configuration file can be overridden by command line options.  If you specify -TRACE parameter in the Process Scheduler definitions remember to also set these flags.

Batch timings are written to the AE Trace file at end of an Application Engine program, and to PS_BAT_TIMINGS PeopleTools tables at the successful end of an Application Engine program. 
It can be useful to have batch timings in the trace file of an Application Engine that failed because they are not written to the database.  As your system runs, you will build up batch timings for all of your successful Application Engine processes (which will be most of them.  This is a useful source of performance metrics.

Compilations, Execution and ReUse

In this example, the number of compilations is equal to the number of executions because ReUseStatement is not set.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
                          PeopleSoft Application Engine Timings
(All timings in seconds)

C o m p i l e E x e c u t e F e t c h Total
SQL Statement Count Time Count Time Count Time Time
------------------------------ ------- -------- ------- -------- ------- -------- --------
99XxxXxx.Step02.S 8453 2.8 8453 685.6 0 0.0 688.4
...

With ReUse Statement enabled, there is now only a single compilation, and most of the time is saved in execution not compilation.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
                               C o m p i l e    E x e c u t e    F e t c h        Total
SQL Statement Count Time Count Time Count Time Time
------------------------------ ------- -------- ------- -------- ------- -------- --------
99XxxXxx.Step02.S 1 0.0 8453 342.3 0 0.0 342.3
...

So we can use the batch timings to identify steps where ReUseStatement is not set because they have as many compilations as executions, and then we can profile the top statements.

 Profile Compilations

This query produces a simple profile of batch timings for statements. 

  • In sub-query x it extract batch timings for statements with more than one compilation in processes that ended in the last 7 days.
  • There is a long-standing bug in batch timings where negative timings can be returned when the clock that returns milliseconds recycles back to zero every 216 milliseconds.  Sub-query y calculates an adjustment that is applied in sub-query z if the timing is negative.
  • Arbitrarily, I am only looking at statements with more than a total of 10000 compilations.


#eeeeee; border: 0px solid #000000; height: 400px; overflow: auto; padding-left: 4px; padding-right: 4px; width: 98%;">
REM ReUseCand.sql
REM (c)Go-Faster COnsultancy Ltd. 2014
COLUMN detail_id FORMAT a32
COLUMN step_time FORMAT 999990 HEADING 'AE|Step|Secs'
COLUMN compile_count HEADING 'AE|Compile|Count'
COLUMN execute_count HEADING 'AE|Execute|Count'
COLUMN processes HEADING 'Num|Process|Instances'
COLUMN process_name HEADING 'Process|Name'
SPOOL ReUseCand
WITH x AS (
SELECT l.process_instance, l.process_name
, l.time_elapsed/1000 time_elapsed
, l.enddttm-l.begindttm diffdttm
, d.bat_program_name||'.'||d.detail_id detail_id
, d.compile_count, d.compile_time/1000 compile_time
, d.execute_time/1000 execute_time
FROM ps_bat_Timings_dtl d
, ps_bat_timings_log l
WHERE d.process_instance = l.process_instance
AND d.compile_count = d.execute_count
AND d.compile_count > 1
AND l.enddttm > SYSDATE-7
), y as (
SELECT x.*
, GREATEST(0,60*(60*(24*EXTRACT(day FROM diffdttm)
+EXTRACT(hour FROM diffdttm))
+EXTRACT(minute FROM diffdttm))
+EXTRACT(second FROM diffdttm)-x.time_elapsed) delta
FROM x)
, z as (
SELECT process_instance, process_name, detail_id
, CASE WHEN time_elapsed < 0 THEN time_elapsed+delta
ELSE time_elapsed END time_elapsed
, compile_count
, CASE WHEN compile_time < 0 THEN compile_time+delta
ELSE compile_time END AS compile_time
, CASE WHEN execute_time < 0 THEN execute_time+delta
ELSE execute_time END AS execute_time
FROM y
), a as (
SELECT process_name, detail_id
, SUM(compile_time+execute_time) step_time
, SUM(compile_count) compile_count
, COUNT(DISTINCT process_instance) processes
FROM z
GROUP BY process_name, detail_id)
SELECT * FROM a
WHERE compile_count >= 10000
ORDER BY step_time DESC
/
SPOOL OFF

So now I have a list of steps with lots of compilations.  I know how long they took, but I don't know how much time I might save by enabling ReUseStatement. That will save some time in Application Engine, but it will also cut down database parse time.  So now I need determine the parse time from ASH data.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
WITH x AS (
Process Step Compile Process
Name DETAIL_ID SEcs Count Instances
------------ -------------------------------- ------ ---------- ----------
AP_PSTVCHR AP_PSTCOMMON.ACCT_UPD.Step02.S 12001 40704 10
AP_VCHRBLD APVEDTMOVE.UPDQTY03.Step03.S 4313 49536 28
FS_VATUPDFS FS_VATUPDFS.Seq0-b.DJ300-2.S 4057 203704 3
AP_VCHRBLD APVEDTMOVE.UPDQTY03.Step02.S 2799 49536 28
PC_BI_TO_PC PC_BI_TO_PC.UPD_PRBI.UPDATE.S 1974 23132 10
FS_VATUPDFS FS_VATUPDFS.Seq0-a.X0001.D 1960 37368 3
GL_JEDIT_0 FS_CEDT_ECFS.iTSELog.iTSELog.S 1628 13104 519
AP_APY2015 AP_APY2015.V_CREATE.Step14.H 1192 11318 19

This query is based on the previous one, but includes scalar queries on the ASH data for each step.

  • WARNING: This query can run for a long period because it has to scan ASH data for each entry in BAT_TIMINGS_DTL.
  • This time in sub-query x I am looking for a rolling 7-day period up to the last hour, because ASH data will have been flushed to the ASH repository.
  • In sub-query y there are two scalar queries that retrieve the DB time spent on hard parse, and all DB time for each batch timing entry.  These queries count 10 seconds for each distinct sample ID to estimate elapsed time rather than total DB time.
  • The query does not group by process name because one step can be called from many Application Engine programs and I want to aggregate the total time across all of them.


#eeeeee; border: 0px solid #000000; height: 400px; overflow: auto; padding-left: 4px; padding-right: 4px; width: 98%;">
REM ReUseCandASH.sql
REM ReUseCandASH.sql
REM (c)Go-Faster Consultancy Ltd. 2014
COLUMN processes HEADING 'Num|Process|Instances'
COLUMN process_name HEADING 'Process|Name'
COLUMN detail_id FORMAT a32
COLUMN step_time HEADING 'AE|Step|SEcs' FORMAT 999990
COLUMN compile_count HEADING 'AE|Compile|Count'
COLUMN execute_count HEADING 'AE|Execute|Count'
COLUMN hard_parse_secs HEADING 'Hard|Parse|Secs' FORMAT 99990
COLUMN ash_secs HEADING 'DB|Time' FORMAT 99990
SPOOL ReUseCandASH
WITH x AS (
SELECT l.process_instance, l.process_name
, l.time_elapsed/1000 time_elapsed
, l.begindttm, l.enddttm
, l.enddttm-l.begindttm diffdttm
, d.bat_program_name||'.'||d.detail_id detail_id
, d.compile_count, d.compile_time/1000 compile_time
, d.execute_time/1000 execute_time
FROM ps_bat_timings_dtl d
, ps_bat_timings_log l
WHERE d.process_instance = l.process_instance
AND d.compile_count = d.execute_count
AND d.compile_count > 1
AND l.enddttm >= TRUNC(SYSDATE-7,'HH24')
AND l.enddttm < TRUNC(SYSDATE,'HH24')
), y as (
SELECT x.*
, GREATEST(0,60*(60*(24*EXTRACT(day FROM diffdttm)
+EXTRACT(hour FROM diffdttm))
+EXTRACT(minute FROM diffdttm))
+EXTRACT(second FROM diffdttm)-x.time_Elapsed) delta
FROM x)
, z as (
SELECT process_instance, process_name, detail_id
, CASE WHEN time_elapsed < 0 THEN time_elapsed+delta
ELSE time_elapsed END AS time_elapsed
, compile_count
, CASE WHEN compile_time < 0 THEN compile_time+delta
ELSE compile_time END AS compile_time
, CASE WHEN execute_time < 0 THEN execute_time+delta
ELSE execute_time END AS execute_time
, (
SELECT 10*COUNT(DISTINCT h.sample_id)
FROM psprcsque q
, dba_hist_snapshot x
, dba_hist_active_sess_history h
WHERE q.prcsinstance = y.process_instance
AND x.begin_interval_time <= y.enddttm
AND X.END_INTERVAL_TIME >= y.begindttm
AND h.sample_time between y.begindttm and y.enddttm
AND h.SNAP_id = x.SNAP_id
AND h.dbid = x.dbid
AND h.instance_number = x.instance_number
AND h.module = 'PSAE.'|| y.process_name||'.'||q.sessionidnum
AND h.action = y.detail_id
AND h.in_hard_parse = 'Y'
) hard_parse_secs
, (
SELECT 10*COUNT(DISTINCT h.sample_id)
FROM psprcsque q
, dba_hist_snapshot x
, dba_hist_active_sess_history h
WHERE q.prcsinstance = y.process_instance
AND x.begin_interval_time <= y.enddttm
AND X.END_INTERVAL_TIME >= y.begindttm
AND h.sample_time between y.begindttm and y.enddttm
AND h.SNAP_id = X.SNAP_id
AND h.dbid = x.dbid
AND h.instance_number = x.instance_number
AND h.module = 'PSAE.'|| y.process_name||'.'||q.sessionidnum
AND h.action = y.detail_id
) ash_secs
FROM y
), a AS (
SELECT /*process_name ,*/ detail_id
, SUM(compile_time+execute_time) step_time
, SUM(compile_count) compile_count
, COUNT(DISTINCT process_instance) processes
, SUM(hard_parse_secs) hard_parse_secs
, SUM(ash_secs) ash_secs
FROM z
GROUP BY /*process_name,*/ detail_id)
SELECT a.*
FROM a
WHERE compile_count >= 10000
ORDER BY step_time DESC
/
spool off

Now we can also see how much time the database is spending on hard parse on each step, and it is this time that is likely to be saved by enabling ReUseStatement.
However, before we can enable the ReUseStatement attribute we must first manually inspect each step in Application Designer and determine whether doing so would break anything.  The Comment column in this profile was added manually as I did that.  Some statements I can change, some I have to accept the overhead.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
                                   Step    Compile    Process      Parse         DB
DETAIL_ID Secs Count Instances Secs Time Comment
-------------------------------- ------ ---------- ---------- ---------- ---------- …………………………………………………………………………………………………………………………………
AP_PSTCOMMON.ACCT_UPD.Step02.S 12001 40704 10 11820 11920 Set ReUseStatement
FS_CEDT_ECMB.4EditCDT.uValCDT.S 5531 10289 679 620 5870 Dynamic SQL, can't set ReUseStatement
APVEDTMOVE.UPDQTY03.Step03.S 4306 49471 27 4020 4100 Set ReUseStatement
FS_VATUPDFS.Seq0-b.DJ300-2.S 4057 203704 3 3150 3860 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECFS.iTSELog.iTSELog.S 3332 19073 716 2130 3520 Dynamic SQL, can't set ReUseStatement
APVEDTMOVE.UPDQTY03.Step02.S 2796 49471 27 2730 2820 Set ReUseStatement
PC_BI_TO_PC.UPD_PRBI.UPDATE.S 1974 23132 10 230 1920 Set ReUseStatement
FS_VATUPDFS.Seq0-a.X0001.D 1960 37368 3 0 0 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECMB.4uAnchCT.uAnchCDT.S 1319 10289 679 510 1290 Dynamic SQL, can't set ReUseStatement
AP_APY2015.V_CREATE.Step14.H 1169 11094 19 0 0 Set ReUseStatement
GL_JETSE.GA100.CHKEDT.S 1121 15776 569 860 930 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECMB.iTSELog.iTSELog.S 988 10289 679 450 990 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECMB.uMarkVal.uMarkVal.S 711 10289 679 50 670 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECMB.uMarkInv.uMarkInv.S 668 10289 679 40 790 Dynamic SQL, can't set ReUseStatement
  • Due to a bug in the instrumentation of Application Engine, the session's action attribute is not set for Do Select (type D) and Do When (type H) steps.  ASH data cannot therefore be matched for them.
  • More DB Time is reported for FS_CEDT_ECMB.uMarkInv.uMarkInv.S than is reported by batch timings.  This is a consequence of ASH sampling, where we count 10 seconds for each sample.

Conclusion

Setting ReUseStatement is very simple because it doesn't involve changing SQL, but there are lots of places where it could be set.  This technique picks out the relatively few places where doing so could potentially have a significant effect.

    davidkurtz's picture

    Minimising Parse Time in Application Engine with ReUseStatement

    This article explains how to determine the overhead of using literal values rather than bind variables in SQL submitted by PeopleSoft Application Engine programs. Using a combination of PeopleSoft Application Engine Batch Timings and Oracle Active Session History (ASH), it is possible to determine where it is most effective to change to alter this behaviour by setting the ReUse attribute.

    ReUse Statement Flag

    I originally wrote about the Performance Benefits of ReUseStatement in Application Engine over 5 years ago, and the problem is no less significant today than it was then.  There are still many places in the delivered PeopleSoft application that would benefit from it.  However, it is not possible to simply set the attribute across all Application Engine programs because it will cause errors in steps where SQL is dynamically generated, so each case must be considered.  Where the ReUse attributed is changed, it must be tested and migrated like any other customisation.

    Recently, I have been encountering problems in a number of places on a Financials system which were resolved by enabling ReUseStatement.  So I started by calculating how much time was lost by not setting it.

    Application Engine Batch Timings

    If an Application Engine step is not reused, then it is compiled prior to every execution during which the Application Engine bind variables are resolved to literals. The number and duration of compilations and executions are reported in the Batch Timings, the production of which are controlled with the TraceAE flag in the Process Scheduler configuration file (psprcs.cfg).  

    #eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
    ;-------------------------------------------------------------------------
    ; AE Tracing Bitfield
    ;
    ; Bit Type of tracing
    ; --- ---------------
    ...
    ; 128 - Timings Report to AET file
    ...
    ; 1024 - Timings Report to tables
    ...
    TraceAE=1152
    ;------------------------------------------------------------------------

    Whatever other TraceAE flags you set, I would always recommend that you set 128 and 1024 so that batch timings are always emitted to both file and database.  The overhead of enabling them is negligible because they are managed in memory at run time and physically written once when the Application Engine program ends.

    NB: The settings in the configuration file can be overridden by command line options.  If you specify -TRACE parameter in the Process Scheduler definitions remember to also set these flags.

    Batch timings are written to the AE Trace file at end of an Application Engine program, and to PS_BAT_TIMINGS PeopleTools tables at the successful end of an Application Engine program. 
    It can be useful to have batch timings in the trace file of an Application Engine that failed because they are not written to the database.  As your system runs, you will build up batch timings for all of your successful Application Engine processes (which will be most of them.  This is a useful source of performance metrics.

    Compilations, Execution and ReUse

    In this example, the number of compilations is equal to the number of executions because ReUseStatement is not set.

    #eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
                              PeopleSoft Application Engine Timings
    (All timings in seconds)

    C o m p i l e E x e c u t e F e t c h Total
    SQL Statement Count Time Count Time Count Time Time
    ------------------------------ ------- -------- ------- -------- ------- -------- --------
    99XxxXxx.Step02.S 8453 2.8 8453 685.6 0 0.0 688.4
    ...

    With ReUse Statement enabled, there is now only a single compilation, and most of the time is saved in execution not compilation.

    #eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
                                   C o m p i l e    E x e c u t e    F e t c h        Total
    SQL Statement Count Time Count Time Count Time Time
    ------------------------------ ------- -------- ------- -------- ------- -------- --------
    99XxxXxx.Step02.S 1 0.0 8453 342.3 0 0.0 342.3
    ...

    So we can use the batch timings to identify steps where ReUseStatement is not set because they have as many compilations as executions, and then we can profile the top statements.

     Profile Compilations

    This query produces a simple profile of batch timings for statements. 

    • In sub-query x it extract batch timings for statements with more than one compilation in processes that ended in the last 7 days.
    • There is a long-standing bug in batch timings where negative timings can be returned when the clock that returns milliseconds recycles back to zero every 216 milliseconds.  Sub-query y calculates an adjustment that is applied in sub-query z if the timing is negative.
    • Arbitrarily, I am only looking at statements with more than a total of 10000 compilations.


    #eeeeee; border: 0px solid #000000; height: 400px; overflow: auto; padding-left: 4px; padding-right: 4px; width: 98%;">
    REM ReUseCand.sql
    REM (c)Go-Faster COnsultancy Ltd. 2014
    COLUMN detail_id FORMAT a32
    COLUMN step_time FORMAT 999990 HEADING 'AE|Step|Secs'
    COLUMN compile_count HEADING 'AE|Compile|Count'
    COLUMN execute_count HEADING 'AE|Execute|Count'
    COLUMN processes HEADING 'Num|Process|Instances'
    COLUMN process_name HEADING 'Process|Name'
    SPOOL ReUseCand
    WITH x AS (
    SELECT l.process_instance, l.process_name
    , l.time_elapsed/1000 time_elapsed
    , l.enddttm-l.begindttm diffdttm
    , d.bat_program_name||'.'||d.detail_id detail_id
    , d.compile_count, d.compile_time/1000 compile_time
    , d.execute_time/1000 execute_time
    FROM ps_bat_Timings_dtl d
    , ps_bat_timings_log l
    WHERE d.process_instance = l.process_instance
    AND d.compile_count = d.execute_count
    AND d.compile_count > 1
    AND l.enddttm > SYSDATE-7
    ), y as (
    SELECT x.*
    , GREATEST(0,60*(60*(24*EXTRACT(day FROM diffdttm)
    +EXTRACT(hour FROM diffdttm))
    +EXTRACT(minute FROM diffdttm))
    +EXTRACT(second FROM diffdttm)-x.time_elapsed) delta
    FROM x)
    , z as (
    SELECT process_instance, process_name, detail_id
    , CASE WHEN time_elapsed < 0 THEN time_elapsed+delta
    ELSE time_elapsed END time_elapsed
    , compile_count
    , CASE WHEN compile_time < 0 THEN compile_time+delta
    ELSE compile_time END AS compile_time
    , CASE WHEN execute_time < 0 THEN execute_time+delta
    ELSE execute_time END AS execute_time
    FROM y
    ), a as (
    SELECT process_name, detail_id
    , SUM(compile_time+execute_time) step_time
    , SUM(compile_count) compile_count
    , COUNT(DISTINCT process_instance) processes
    FROM z
    GROUP BY process_name, detail_id)
    SELECT * FROM a
    WHERE compile_count >= 10000
    ORDER BY step_time DESC
    /
    SPOOL OFF

    So now I have a list of steps with lots of compilations.  I know how long they took, but I don't know how much time I might save by enabling ReUseStatement. That will save some time in Application Engine, but it will also cut down database parse time.  So now I need determine the parse time from ASH data.

    #eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
    WITH x AS (
    Process Step Compile Process
    Name DETAIL_ID SEcs Count Instances
    ------------ -------------------------------- ------ ---------- ----------
    AP_PSTVCHR AP_PSTCOMMON.ACCT_UPD.Step02.S 12001 40704 10
    AP_VCHRBLD APVEDTMOVE.UPDQTY03.Step03.S 4313 49536 28
    FS_VATUPDFS FS_VATUPDFS.Seq0-b.DJ300-2.S 4057 203704 3
    AP_VCHRBLD APVEDTMOVE.UPDQTY03.Step02.S 2799 49536 28
    PC_BI_TO_PC PC_BI_TO_PC.UPD_PRBI.UPDATE.S 1974 23132 10
    FS_VATUPDFS FS_VATUPDFS.Seq0-a.X0001.D 1960 37368 3
    GL_JEDIT_0 FS_CEDT_ECFS.iTSELog.iTSELog.S 1628 13104 519
    AP_APY2015 AP_APY2015.V_CREATE.Step14.H 1192 11318 19

    This query is based on the previous one, but includes scalar queries on the ASH data for each step.

    • WARNING: This query can run for a long period because it has to scan ASH data for each entry in BAT_TIMINGS_DTL.
    • This time in sub-query x I am looking for a rolling 7-day period up to the last hour, because ASH data will have been flushed to the ASH repository.
    • In sub-query y there are two scalar queries that retrieve the DB time spent on hard parse, and all DB time for each batch timing entry.  These queries count 10 seconds for each distinct sample ID to estimate elapsed time rather than total DB time.
    • The query does not group by process name because one step can be called from many Application Engine programs and I want to aggregate the total time across all of them.


    #eeeeee; border: 0px solid #000000; height: 400px; overflow: auto; padding-left: 4px; padding-right: 4px; width: 98%;">
    REM ReUseCandASH.sql
    REM ReUseCandASH.sql
    REM (c)Go-Faster Consultancy Ltd. 2014
    COLUMN processes HEADING 'Num|Process|Instances'
    COLUMN process_name HEADING 'Process|Name'
    COLUMN detail_id FORMAT a32
    COLUMN step_time HEADING 'AE|Step|SEcs' FORMAT 999990
    COLUMN compile_count HEADING 'AE|Compile|Count'
    COLUMN execute_count HEADING 'AE|Execute|Count'
    COLUMN hard_parse_secs HEADING 'Hard|Parse|Secs' FORMAT 99990
    COLUMN ash_secs HEADING 'DB|Time' FORMAT 99990
    SPOOL ReUseCandASH
    WITH x AS (
    SELECT l.process_instance, l.process_name
    , l.time_elapsed/1000 time_elapsed
    , l.begindttm, l.enddttm
    , l.enddttm-l.begindttm diffdttm
    , d.bat_program_name||'.'||d.detail_id detail_id
    , d.compile_count, d.compile_time/1000 compile_time
    , d.execute_time/1000 execute_time
    FROM ps_bat_timings_dtl d
    , ps_bat_timings_log l
    WHERE d.process_instance = l.process_instance
    AND d.compile_count = d.execute_count
    AND d.compile_count > 1
    AND l.enddttm >= TRUNC(SYSDATE-7,'HH24')
    AND l.enddttm < TRUNC(SYSDATE,'HH24')
    ), y as (
    SELECT x.*
    , GREATEST(0,60*(60*(24*EXTRACT(day FROM diffdttm)
    +EXTRACT(hour FROM diffdttm))
    +EXTRACT(minute FROM diffdttm))
    +EXTRACT(second FROM diffdttm)-x.time_Elapsed) delta
    FROM x)
    , z as (
    SELECT process_instance, process_name, detail_id
    , CASE WHEN time_elapsed < 0 THEN time_elapsed+delta
    ELSE time_elapsed END AS time_elapsed
    , compile_count
    , CASE WHEN compile_time < 0 THEN compile_time+delta
    ELSE compile_time END AS compile_time
    , CASE WHEN execute_time < 0 THEN execute_time+delta
    ELSE execute_time END AS execute_time
    , (
    SELECT 10*COUNT(DISTINCT h.sample_id)
    FROM psprcsque q
    , dba_hist_snapshot x
    , dba_hist_active_sess_history h
    WHERE q.prcsinstance = y.process_instance
    AND x.begin_interval_time <= y.enddttm
    AND X.END_INTERVAL_TIME >= y.begindttm
    AND h.sample_time between y.begindttm and y.enddttm
    AND h.SNAP_id = x.SNAP_id
    AND h.dbid = x.dbid
    AND h.instance_number = x.instance_number
    AND h.module = 'PSAE.'|| y.process_name||'.'||q.sessionidnum
    AND h.action = y.detail_id
    AND h.in_hard_parse = 'Y'
    ) hard_parse_secs
    , (
    SELECT 10*COUNT(DISTINCT h.sample_id)
    FROM psprcsque q
    , dba_hist_snapshot x
    , dba_hist_active_sess_history h
    WHERE q.prcsinstance = y.process_instance
    AND x.begin_interval_time <= y.enddttm
    AND X.END_INTERVAL_TIME >= y.begindttm
    AND h.sample_time between y.begindttm and y.enddttm
    AND h.SNAP_id = X.SNAP_id
    AND h.dbid = x.dbid
    AND h.instance_number = x.instance_number
    AND h.module = 'PSAE.'|| y.process_name||'.'||q.sessionidnum
    AND h.action = y.detail_id
    ) ash_secs
    FROM y
    ), a AS (
    SELECT /*process_name ,*/ detail_id
    , SUM(compile_time+execute_time) step_time
    , SUM(compile_count) compile_count
    , COUNT(DISTINCT process_instance) processes
    , SUM(hard_parse_secs) hard_parse_secs
    , SUM(ash_secs) ash_secs
    FROM z
    GROUP BY /*process_name,*/ detail_id)
    SELECT a.*
    FROM a
    WHERE compile_count >= 10000
    ORDER BY step_time DESC
    /
    spool off

    Now we can also see how much time the database is spending on hard parse on each step, and it is this time that is likely to be saved by enabling ReUseStatement.
    However, before we can enable the ReUseStatement attribute we must first manually inspect each step in Application Designer and determine whether doing so would break anything.  The Comment column in this profile was added manually as I did that.  Some statements I can change, some I have to accept the overhead.

    #eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
                                       Step    Compile    Process      Parse         DB
    DETAIL_ID Secs Count Instances Secs Time Comment
    -------------------------------- ------ ---------- ---------- ---------- ---------- …………………………………………………………………………………………………………………………………
    AP_PSTCOMMON.ACCT_UPD.Step02.S 12001 40704 10 11820 11920 Set ReUseStatement
    FS_CEDT_ECMB.4EditCDT.uValCDT.S 5531 10289 679 620 5870 Dynamic SQL, can't set ReUseStatement
    APVEDTMOVE.UPDQTY03.Step03.S 4306 49471 27 4020 4100 Set ReUseStatement
    FS_VATUPDFS.Seq0-b.DJ300-2.S 4057 203704 3 3150 3860 Dynamic SQL, can't set ReUseStatement
    FS_CEDT_ECFS.iTSELog.iTSELog.S 3332 19073 716 2130 3520 Dynamic SQL, can't set ReUseStatement
    APVEDTMOVE.UPDQTY03.Step02.S 2796 49471 27 2730 2820 Set ReUseStatement
    PC_BI_TO_PC.UPD_PRBI.UPDATE.S 1974 23132 10 230 1920 Set ReUseStatement
    FS_VATUPDFS.Seq0-a.X0001.D 1960 37368 3 0 0 Dynamic SQL, can't set ReUseStatement
    FS_CEDT_ECMB.4uAnchCT.uAnchCDT.S 1319 10289 679 510 1290 Dynamic SQL, can't set ReUseStatement
    AP_APY2015.V_CREATE.Step14.H 1169 11094 19 0 0 Set ReUseStatement
    GL_JETSE.GA100.CHKEDT.S 1121 15776 569 860 930 Dynamic SQL, can't set ReUseStatement
    FS_CEDT_ECMB.iTSELog.iTSELog.S 988 10289 679 450 990 Dynamic SQL, can't set ReUseStatement
    FS_CEDT_ECMB.uMarkVal.uMarkVal.S 711 10289 679 50 670 Dynamic SQL, can't set ReUseStatement
    FS_CEDT_ECMB.uMarkInv.uMarkInv.S 668 10289 679 40 790 Dynamic SQL, can't set ReUseStatement
    • Due to a bug in the instrumentation of Application Engine, the session's action attribute is not set for Do Select (type D) and Do When (type H) steps.  ASH data cannot therefore be matched for them.
    • More DB Time is reported for FS_CEDT_ECMB.uMarkInv.uMarkInv.S than is reported by batch timings.  This is a consequence of ASH sampling, where we count 10 seconds for each sample.

    Conclusion

    Setting ReUseStatement is very simple because it doesn't involve changing SQL, but there are lots of places where it could be set.  This technique picks out the relatively few places where doing so could potentially have a significant effect.

      marco's picture

      Download Oracle VirtualBox VM with Database version 12.1.0.2

      Somehow missed the announcement that the Oracle Development team refreshed (9/26/2014) the VirtualBox Oracle Database…

      JonathanGennick's picture

      Page-Specific Footers in the Squarespace Bedford

      Would you like page-specific footers in your site design? Templates in Squarespace typically have one footer that is applied across all pages. Sometimes there is a pre-footer and a footer, and then both are applied across all pages. Page-specific footers are possible though. All it takes is a combination of creating thinking and CSS rules. 

      Alternative Text Choices

      Your work is sweet and easy if you're wanting to display alternative text passages in footers on different pages of your site. Place your text alternatives into a Code block, and choose between them using CSS rules that effectively function as switches. 

      Figures 1 through 3 show an example of the technique. (Click any figure to embiggen it). Figure 1 presents the default footer on my site as I write this post. Figures 2 and 3 show blog-specific footers referring readers of one blog to my other. Readers of my Database and Tech blog see a link to my Outside the Box blog, and vice-versa. 

      Figure 1. My default footer

      Figure 1. My default footer

      Figure 2. Database and Tech blog

      Figure 2. Database and Tech blog

      Figure 3. Outside the Box blog

      Figure 3. Outside the Box blog

      (Please understand that I've created the example footers just to write this post, and that I will revert to a single footer design soon after going live. You likely won't see the page-specific footers from Figures 2 and 3 on my site when you are reading today, but they did exist at the time of writing, and the code that you are about to see is how I implemented them). 

      The following Code block is the basis for the example in the figures. I implement the copyright line and the two RSS feed links in the normal way by dragging a text block and two RSS blocks into the footer. The magic is that everything below those items is implemented as a Code block having the following HTML markup. Skim the markup now, and the explanation after will help you make sense of it. 

      <div id="my-footers">
      <div id="footer-box-blog">
         <p>Visit my
           <a href="http://gennick.com/database/">Database and Techa>
           blogp>
      div>
      <div id="footer-tech-blog">
         <p>Visit my
           <a href="http://gennick.com/the-box/">Outside the Boxa>
           blogp>
      div>
      <div id="footer-always-show">
        <p>Congratulations! You've made it to the bottom.
           Well done.p>
      div>
      div>

      There's an outer div named my-footers. That's there just to keep things tidy and make it easier to write formatting rules. Nested inside are three more divs named footer-box-blog, footer-tech-blog, and footer-always-show. These div names are switches that I can throw via CSS.

      For example, the following three rules in my Custom CSS Editor set the initial condition:

      #footer-always-show {display: block;}
      #footer-box-blog {display: none;}
      #footer-tech-blog {display: none;}

      The initial condition is that my footer-always-show line is enabled. This is the line that begins with "Congratulations!" The other two options are disabled by their respective, display: none rules. 

      Tip: If you're new to working with CSS and Squarespace, my post on CSS and Squarespace: Getting Started may be helpful. There is also my book.

      Next in my Custom CSS Editor come the following two rules to enable the blog-specific text:

      #collection-524b8324e4b08c5ba070d6f2 #footer-tech-blog {display: block;}
      #collection-525c7eb2e4b0e89bfe803d2d #footer-box-blog {display: block;}

      The #collection values are identifier (ID) values from the respective blog page body tags. You can get the ID for any page by

      1. Opening the page in your browser.
      2. Viewing the source (by pressing CTRL-U in most PC browsers, and Command-Option-U in Mac browsers)
      3. Finding the body tag.
      4. And looking for the ID attribute, which is typically listed first inside the tag.

      Targeting the specific pages via their collection identifiers allows me to switch on the blog-specific footer text when the respective blogs are viewed, and on any non-blog page that text remains switched off as specified by my initial condition rules. 

      Some formatting touch-ups may be needed when using the technique. Following are some additional rules I needed to write. One specifies my link color to be the same hot-pink that I give in the Style Editor for other links on my pages. Another represents some fiddling with the bottom margin of the paragraph to get the vertical spacing just right. I looked at the page in my browser's inspector and worked through the math, but you can also just use trial and error until you're happy with the result. 

      #my-footers {display: block;}
      #my-footers a {color: #ff065a;}
      #my-footers p {
        text-align: center; font-weight: 
        bold; margin-bottom: 17px;
      }

      One last comment about the technique is that it's hugely helpful to put all your alternatives into one code block, and to be sure that at least one alternative is always chosen. That's because the Code block will have padding and possibly margin associated with it, and that padding and margin will take up vertical space on the page even when all the footer alternatives are switched off. The result will not look good, and you can avoid the problem entirely by ensuring that at least one option is set to display at all times. Hence my footer-always-on text. 

      Tip: If you're a Flatiron user, then be sure to read Styling the Squarespace Flatiron. There I show a technique for inhibiting the footer from the home view of a Flatiron index page.

      Alternative Block Choices

      It's also possible to choose between alternate Squarespace blocks, and even to disable them completely. This approach is more difficult, and more brittle too in the sense that you can easily break your design when making subsequent footer changes if you forget to check for and rewrite any related CSS rules. 

      Warning! This section describes an advanced modification. Do not attempt it without a working knowledge of HTML and CSS. You must be able to on your own identify and target the needed div elements in the HTML that is generated by the Squarespace layout engine.

      Figure 4 shows a footer specific to my About page. This footer replaces my RSS feed links with a Social Links block, for just the one page. It was a somewhat tricky modification to make.

      Figure 4. About-page footer, with social icons

      Figure 4. About-page footer, with social icons

      First, I had to recognize the need to work in terms of alternative rows. Figure 5 shows the design view of my footer after implementing the About-page alternative in Figure 4. There are two rows having redundant copyright lines on the left, but differing content on the right. The need for two rows goes to how the underlying HTML is mapped onto the 12-column grid on which Squarespace pages are built.

      Figure 5. Design view of my footer showing the default and the About-page alternatives

      Figure 5. Design view of my footer showing the default and the About-page alternatives

      I set the initial condition by writing the following CSS rule and placing it into my Custom CSS Editor. I viewed the source for my page, found the div enclosing the second footer row, and wrote a rule to inhibit the display of that row. This rule will vary by template, and I cannot even promise that what appears to be the second row in a footer will, in fact, be addressable by a selector such as nth-of-type(2)

      #footerBlocks .row .col .row:nth-of-type(2) {
        display: none;
      }

      With the initial condition set, it's a pretty simple matter to write page-specific rules to flip the display status of the two footer rows. For example, the collection identifier in the following rules targets my About page, hiding the first footer row and showing the second, just on that one page.

      #collection-52892383e4b0ff28fb88147b #footerBlocks
      .row .col .row:nth-of-type(1) {display: none;} 
      #collection-52892383e4b0ff28fb88147b #footerBlocks
      .row .col .row:nth-of-type(2) {display: block;} 

      Formatting tweaks are often needed when you monkey around like this. I found that I needed to remove some padding when I displayed the second row, leading to the following rule, which is also specific to the About page:

      #collection-52892383e4b0ff28fb88147b #footerBlocks
      .row .col .row:nth-of-type(2)
      .sqs-block {padding-top: 0; padding-bottom: 0;}

      This rule removes some padding that Squarespace adds to what the layout engine interprets as the second row in the footer, but really it is the first row. Removing the padding results in the social icons row displaying in the same vertical position within the footer as the alternative row with the RSS feed links. 

      Keeping it Clean and Simple

      Simpler is better; go with one footer design if you possibly can. I'll revert my own footer to a single design shortly after making this post for that very reason. 

      Of the two solutions, I like the first one best. It's simple and robust, easy to understand and unlikely to be accidentally subverted by future edits.

      There doesn't appear to me be one, universal "scaffolding" that works in all possible cases. The fundamental technique of setting an initial state followed by switching different elements on and off as needed seems sound. How you implement that technique, and how you design your footer in support of it, is going to vary depending upon how complex your footer is and what your goals are. And for anything but the first method, you're going to need good knowledge of HTML and CSS, or the patience and tenacity to learn fast and work things out. 


      Learn CSS for Squarespace

      9.99

      Learn CSS for Squarespace is a seven-chapter introduction to working with CSS on the Squarespace platform. The book is delivered as a mobi file that is readable from the Kindle Fire HDX or similar devices. A complimentary sample chapter is available. Try before you buy.

      View the complete description for more details.

      Add To Cart

      null

      JonathanGennick's picture

      Page-Specific Footers in the Squarespace Bedford

      Do you want page-specific footers in your template-driven Squarespace site?
      One footer design normally applies across all pages, but you can combine
      creative thinking with CSS rules to enable...



      Read the full post at www.gennick.com/database.

      oraclebase's picture

      Oracle ACE Program: Follow Up

      I just wanted to write something as a follow up to my recent and provocatively titled Oracle ACE = Oracle’s Bitch? post. Obviously, that subject has been preying on my mind a little… I said before, it is hard to be objective about yourself, so maybe some aspects of the “being an Oracle ACE changes you” debate may be true. It would be wrong of me to deny that outright, but I think there are some indirect consequences of being an ACE that might alter my opinions about things, so I thought I would share my thoughts…

      Access to Information

      I don’t want this to sound patronising, but there are a lot of people out there spouting rubbish about things that happen in the tech industry with no knowledge of the history or actual decision processes that lead up to the final result. If you don’t know what is actually going on, it is easy to come to the wrong conclusion. Let’s use Oracle Linux as an example.

      When Oracle Linux was announced it seemed like the whole internet was full of people saying, “Poor Red Hat. Oracle is trying to kill them!”. If you had spoken to people in the Oracle Linux team you would know that Oracle was incredibly frustrated with how long it was taking Red Hat to get performance improvements through to the kernel because of the way they manage their long term support of versions. Apart from critical updates, many major performance features will not be added into the kernel until the next major release. This was exactly what was happening with RHEL5. There were a whole host of performance improvements in the mainline Linux kernel, that had not been added to the RHEL5 kernel, but would be in the RHEL6 kernel. The problem was RHEL6 was *massively* delayed. That delay meant the performance of Oracle on Linux was essentially being crippled by Red Hat’s release cycles. Add to that other features like InfiniBand support that were being held back and you start to see the problem.

      One option was for Oracle to make a binary clone of RHEL (like CentOS, Scientific Linux etc.) and give the option of their own kernel (UEK) that was closer to the mainline Linux kernel and therefore included all the latest performance stuff, without having to wait for Red Hat to get their ass in gear. That is what Oracle did with Oracle Linux. We had the performance of RHEL6, but we were still effectively using RHEL5. What’s more, by breaking this dependency between the distro version and the kernel version, the incentive to upgrade from RHEL5 to RHEL6 to RHEL7 was broken. For the most part, Oracle servers use a minimal amount of the features in the full distro. Who cares what version of Firefox is on the database server? For some people, running OL5 + UEK is pretty much like running OL7, so why bother upgrading as long as you still have erata produced?

      Most people out there had not spoken to the Oracle Linux team, so they were quite happily spouting the “Oracle are killing Red Hat” crap, without knowing the real motivation. When someone like me comes along and sings the praises of Oracle Linux and actively defends Oracle’s position, I sound like a kiss ass, but really I’m just standing up for what I believe to be right.

      Caveats: The arguments I was told could have been fiction used to influence me, but I was there through much of the process and have a lot of respect for the people involved, so a choose to believe them!

      Why that long explanation? If I had not been in the ACE program, I personally would never have had that contact with the people in the Oracle Linux team and I would have been one of those people saying Oracle were a bunch of bastards! Because of my involvement in the ACE program, I got to see “behind the curtain” and chose a different path. So yes, being an Oracle ACE did change me!

      I’ve used Oracle Linux as an example, but I could have used a whole bunch more!

      Access to Support

      We have all lived through Oracle Support nightmares. I’ve written several things in the past ranting about support. Since being part of the ACE program I’ve got to know a number of product managers (and in some cases developers) at Oracle, so when I have problems I can contact them directly and ask questions. In many cases, that significantly reduces the amount I actually have to interact with Oracle Support. If I know I will be working with product X, I actively seek out people in that field (ACEs and Oracle employees) and use that networking to my advantage. A case in point is the 12c JSON support. At Oracle OpenWorld I made a point of going to the demo grounds and speaking to Mark Drake about it. When I met up with David Peake I asked him some questions about what I was planning to do with APEX in 12c to get a second opinion. As long as you don’t bug these folks with stupid questions, they are usually willing to help.

      If I had not been part of the ACE program, I would probably never have met these people and this sort of thing would not be possible for me *. That must have changed me, but I don’t think of it as incidious. I guess in this case I could say being an Oak Table member has changed me because of my access to people and information also…

      Maybe you see change where there has been no change?

      You hear those stories about people winning the lottery then losing all their friends, because their friends don’t want to be seen as “hangers on” so they avoid them. In some cases it is possible that the people who become ACEs haven’t changed, but your perception of them has. Before I became an ACE, if I said something supportive of Oracle you probably wouldn’t notice. If I say the same thing now I am a sell-out. :) I can think of a couple of cases.

      Grid Control/Cloud Control : I’ve used OEM in its various forms since 9i, where it was the Java-based console on top of the Management Server. Back then you couldn’t admit to using it in public or you would be ridiculed. You had to quickly close it down and open SQL*Plus if someone came in the room. Over the course of 10g and 11g Grid Control, then Cloud Control, became cool and everyone talks about it now. When I am presenting and I say things like, “I believe every DBA should use Cloud Control”, I mean it because I think it is true. The problem is I sound like a suck-up now. I’m just telling people what Oracle want me to say! Back in the 9i days when I was afraid to admit I used the 9i OEM Management Server I had credibility. :)

      Certification : I’ve been doing certifications since Oracle 7. I started doing them to confirm to myself I really did know something about being a DBA. :) Now it is all about my personal development. From time to time I have contact with Oracle Education about my views on certification. For a few years they interviewed me at OOW and so far have used about 5 seconds on the footage. Why? Because my views don’t line up with theirs. Just before OOW14 I was asked if I would write a post for the Oracle Certification Blog. I was willing to do one with a focus on personal development, but said I could not fall in line with the Oracle Education message. I don’t think that post will happen now, which is a pity. I think the people involved are a great group of people and I’ve met many of them for years at OOW, but we do have a difference of opinion where the value of certification is concerned. So now when I say I like certification (for my reasons) and I agree with Oracle’s new re-certification policy I am a drone that constantly spouts the Oracle message!

      Conclusion

      If you are looking for conspiracy you will find it, but it doesn’t mean it’s real!

      I’m sorry this post has been so long, but I do care about what I do and I care about the ACE program. It’s been a big part of my life for the last 8 years. As you can tell, I’m a little butt-hurt about this subject, but I know that trying to defend yourself makes you look all the more guilty… :)

      Sod it. It’s nearly the weekend, which means I get more time to play with Oracle…

      Cheers

      Tim…

      * For clarification, I wasn’t suggesting I can only speak to these people because I’m an ACE. I meant that I (me personally) only came into contact with them in the first place because I’m an ACE.


      Oracle ACE Program: Follow Up was first posted on October 24, 2014 at 1:13 pm.
      ©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.
      marco's picture

      Ordina Open Wereld 2014

      Werelds grootste IT conferentie, Oracle OpenWorld in San Francisco is voorbij. Meerdere Ordina mensen zijn…

      oraclebase's picture

      Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c

      I’ve spent the last couple of evenings playing with the new SQL pattern matching feature in Oracle 12c.

      I’m doing some sessions on analytic functions in some upcoming conferences and I thought I should look at this stuff. I’m not really going to include much, if anything, about it as my sessions are focussed on beginners and I don’t really want to scare people off. The idea is to ease people in gently, then let them scare themselves once they are hooked on analytics. :) I’m thinking about Hooked on Monkey Fonics now…

      At first glance the pattern matching seems pretty scary. There are a lot of options and as soon as you throw regular expressions into the mix it does make your head swim a little. After a couple of half-baked attempts, where I found convenient excuses to give in when the going got tough, I finally sat down and plugged through the docs. If you actually RTFM it is a lot easier than hoping to wing it. Who’da thunk it? :)

      I’ve tried to keep the article really light. The docs are pretty good for this stuff (if you read them) and they have a lot of examples. I started adding more and more detail to the article, then chopped most of it out. There is no point regurgitating all the options when it is in the docs. Most of the examples I’ve seen before just talk about basic patterns, like V and W shapes, but it’s quite simple to do complicated stuff once you start playing. In fact it takes more time to set up the example data than it does to figure out the queries to bring it back.

      In the near future I will be copy/pasting examples and adjusting them or just sitting with my article and the docs when trying to use this stuff. I think it’s going to take a long time before I can type this stuff from memory. Partly that’s because I can’t see myself having lots of cause to use it. I can’t think of any scenarios I’ve experienced where this would have been a natural fit. Having said that, I’ve never worked in things like stock markets, betting and stuff like that where I can imagine this sort of pattern matching is needed all the time. I seem to remember one person at a conference, who shall remain nameless, saying this feature was one of their drivers for upgrading to 12c. I wonder if that was for real or an exaggeration?

      Anyway, if you need this sort of analysis, I think it’s worth checking out, but try to remember it’s not as scary as it first looks. :)

      Cheers

      Tim…


      Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c was first posted on October 23, 2014 at 6:45 pm.
      ©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.