Feed aggregator

 I've just uploaded a new video on monitoring DataGuard where the Standby is a Single Instance environment for a 2-node RAC Cluster.

The script used in the demo are in this ZIP  (script files with extension TXT)

One of the challenges that PeopleSoft gives to an Oracle database is that many processes dynamically generate many SQL statements.  They usually have different literal values each time, some may also reference different non-shared instances of temporary records.  Each statement must be fully parsed by the Oracle statements.  That consumes CPU and takes time.  Oracle has already recommended using bind variables instead of literal values for that reason.  

It would generally be better if the SQL used bind variables rather than literal values.  In Application Engine, one option is to set the ReUseStatement attribute on the steps in question.  Then bind variables in Application Engine remain bind variables in the SQL and are not converted to literals.  This can reduce parse time (see Minimising Parse Time in Application Engine with ReUseStatement). However, this attribute is not set by default.  This is partly for legacy PeopleTools reasons, and partly due to the pitfalls discussed below.  Over the years, Oracle has got much better at setting this attribute where possible in delivered PeopleSoft application code.  There are still many places where it could still be added.  However, there are some considerations before we add it ourselves.

• When a customer sets the ReUseStatement attribute in the delivered code, it is a customisation that has to be migrated using Application Designer.  It has to be maintained to ensure that subsequent releases and patches do not revert it.
• ReUseStatement cannot be introduced across the board, but only on steps that meet certain criteria.  It doesn't work when dynamic code is generated with %BIND(…,NOQUOTES), or if a %BIND() is used in a SELECT clause.  Worse, setting this attribute when it should not be can cause the application to function incorrectly.  So each change has to be tested carefully.

If you can't remove the literal values in the SQL code, then another option is to introduce cursor sharing in Oracle.  Essentially, all literals are converted to bind variables before the SQL is parsed, and thus statements that only differ in the literal values can be treated as the same statement.  If the statement is still in the shared pool, then it is not fully reparsed and uses the same execution plan.

Oracle cautions against using cursor sharing as a long-term fix: "The best practice is to write sharable SQL and use the default of EXACT for CURSOR_SHARING… FORCE is not meant to be a permanent development solution."

I realise that I am now about to suggest doing exactly that, but only for specific processes, and never for the whole database.  I have tested enabling cursor sharing at database level a few times and have never had a good experience.

It is easy to set a session setting for a specific process run on the PeopleSoft process scheduler.   The first thing a process does is to set the status of its own request record to 7, indicating that it is processing.  

A trigger can be created on this transition that will then be executed in the session of the process.  I initially developed this technique to set other session settings for nVision reports.  I introduced a database table to hold a list of the settings, and the trigger matches this metadata to the processes being run by up for 4 attributes: process type, process name, operation and run control.

• see Setting Oracle Session Parameters for Specific Process Scheduler Processes 
• The scripts are available on GitHub
• Trigger: set_prcs_sess_parm_trg.sql.  The trigger expects that psftapi.sql has also been installed.
• Example metadata set_prcs_sess_parm.sql
  

CREATE OR REPLACE TRIGGER sysadm.set_prcs_sess_parm
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
FOLLOWS sysadm.psftapi_store_prcsinstance 
WHEN (new.runstatus = 7 AND old.runstatus != 7 AND new.prcstype != 'PSJob')
DECLARE
  l_cmd VARCHAR2(100 CHAR);
…
BEGIN
  FOR i IN (
    WITH x as (
      SELECT p.*
      ,      row_number() over (partition by param_name 
             order by NULLIF(prcstype, ' ') nulls last, NULLIF(prcsname, ' ') nulls last, 
                      NULLIF(oprid   , ' ') nulls last, NULLIF(runcntlid,' ') nulls last) priority
      FROM   sysadm.PS_PRCS_SESS_PARM p
      WHERE  (p.prcstype  = :new.prcstype  OR p.prcstype  = ' ')
      AND    (p.prcsname  = :new.prcsname  OR p.prcsname  = ' ')
      AND    (p.oprid     = :new.oprid     OR p.oprid     = ' ')
      AND    (p.runcntlid = :new.runcntlid OR p.runcntlid = ' ')) 
    SELECT * FROM x WHERE priority = 1 
  ) LOOP
…
    IF NULLIF(i.parmvalue,' ') IS NOT NULL THEN
      l_cmd := 'ALTER SESSION '||i.keyword||' '||l_delim||i.param_name||l_delim||l_op||i.parmvalue;
      EXECUTE IMMEDIATE l_cmd;
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN …
END;
/

INSERT INTO sysadm.ps_prcs_sess_parm (prcstype, prcsname, oprid, runcntlid, keyword, param_name, parmvalue)
with x as (
          select 'inmemory_query' param_name, 'SET' keyword, 'DISABLE' parmvalue from dual --Disable inmemory 
union all select 'cursor_sharing'           , 'SET' keyword, 'FORCE'             from dual --to mitigate excessive parse
), y as (
  select  prcstype, prcsname, ' ' oprid, ' ' runcntlid
  from	  ps_prcsdefn
  where   prcsname IN('GLPOCONS')
)
select  y.prcstype, y.prcsname, y.oprid, y.runcntlid, x.keyword, x.param_name, x.parmvalue
from    x,y
/

UPDATE PS_JRNL_LN SET JRNL_LINE_STATUS='0', PROCESS_INSTANCE=:1 
WHERE BUSINESS_UNIT=:2 AND JOURNAL_ID=:3 AND JOURNAL_DATE=TO_DATE(:4,'YYYY-MM-DD') AND UNPOST_SEQ=0

CREATE OR REPLACE TRIGGER gfc_jrnl_ln_gl_jedit2
FOR UPDATE OF process_instance ON ps_jrnl_ln
WHEN (new.process_instance != 0 and old.process_instance = 0)
COMPOUND TRIGGER
  l_process_instance INTEGER;
  l_runcntlid VARCHAR2(30);
  l_module VARCHAR2(64);
  l_action VARCHAR2(64);
  l_prcsname VARCHAR2(12);
  l_cursor_sharing CONSTANT VARCHAR2(64) := 'ALTER SESSION SET cursor_sharing=FORCE';

  AFTER EACH ROW IS 
  BEGIN
    l_process_instance := :new.process_instance;
    --dbms_output.put_line('process_instance='||l_process_instance);
  END AFTER EACH ROW;
  
  AFTER STATEMENT IS 
  BEGIN
    IF l_process_instance != 0 THEN
      dbms_application_info.read_module(l_module,l_action);
      --dbms_output.put_line('module='||l_module||',action='||l_action);
      IF l_module like 'PSAE.GL_JEDIT2.%' THEN --check this session is instrumented as being GL_JEDIT
        --check process instance being set is a running FSPCCURR process
        SELECT prcsname, runcntlid
        INTO l_prcsname, l_runcntlid
        FROM   psprcsrqst
        WHERE  prcsinstance = l_process_instance AND runstatus = '7';
        
        l_module := regexp_substr(l_module,'PSAE\.GL_JEDIT2\.[0-9]+',1,1)||':'||l_prcsname||':PI='||l_process_instance||':'||l_runcntlid;
        dbms_application_info.set_module(l_module,l_action);
        --dbms_output.put_line('set module='||l_module||',action='||l_action);
        EXECUTE IMMEDIATE l_cursor_sharing;
        --dbms_output.put_line('set cursor_sharing');
      END IF;
    END IF;
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
      --dbms_output.put_line('Cannot find running '||l_prcsname||' process instance '||l_process_instance);
      NULL; --cannot find running process instance number
    WHEN OTHERS THEN
      --dbms_output.put_line('Other Error:'||sqlerrm);
      NULL;
  END AFTER STATEMENT;

END gfc_jrnl_ln_gl_jedit2;
/

Here’s a silly little puzzle that baffled me for a few moments until I spotted my typing error. It starts with a small table I’d created to hold a few rows, and then deletes most of them. Here’s a statement to create and populate the table:

create table t1 (id number , c1 clob)
lob(c1) store as basicfile text_lob (
        retention disable storage in row
);

insert into t1 
select  rownum, rpad(rownum,200,'0') 
from    all_objects 
where   rownum <= 1000
;

commit;

Here’s what I meant to type to delete most of the data – followed by the response from SQL*Plus:

SQL> delete from t1 where mod(id,20) != 0;

950 rows deleted.

Here’s what I actually typed, with the response, that gave me a “What?!” moment:

SQL> delete from t1 where mod(rownum,20) != 0;

19 rows deleted.

I don’t think it will take long for you to work out why the result is so different; but I think it’s a nice warning about what can happen if you get a bit casual about using rownum.

Received email from Technical Lead | Senior Manager for the following errors.

Error Description: 0: Invalid pool name ‘oraclePool’ while getting a database connection.
Please check for consistency of the properties files or BPML
Time of Event: 20240419141429
Workflow Id: 88867

First inclination is to check Oracle database parameters (sessions and processes) which wasted time on a wild goose chase.

I am by no mean an expert but Google is your friend.

SI fails to startup with error: “Invalid pool name ‘NewPool_oraclePool’ while getting a database connection. Please check for consistency of the properties files or BPML” (SCI91968)

It puzzle me how a Technical Lead | Senior Manager does not know how to Google.

LMGTFY – Let Me Google That For You for all those people who find it more convenient to bother you with their question rather than to Google it for themselves.

In the last post, we’ve looked at the very basics when it comes to bcachefs, a new file system which was added to the Linux kernel starting from version 6.7. While we’ve already seen how easy it is to create a new file system using a single device, encrypt and/or compress it and that check summing of meta data and user data is enabled by default, there is much more you can do with bcachefs. In this post we’ll look at how you can work with a file system that spans multiple devices, which is quite common in today’s infrastructures.

When we looked at the devices available to the system in the last post, it looked like this:

tumbleweed:~ $ lsblk | grep -w "4G"
└─vda3 254:3    0  1.4G  0 part [SWAP]
vdb    254:16   0    4G  0 disk 
vdc    254:32   0    4G  0 disk 
vdd    254:48   0    4G  0 disk 
vde    254:64   0    4G  0 disk 
vdf    254:80   0    4G  0 disk 
vdg    254:96   0    4G  0 disk 

This means we have six unused block devices to play with. Lets start again with the most simple case, one device, one file system:

tumbleweed:~ $ bcachefs format --force /dev/vdb
tumbleweed:~ $ mount /dev/vdb /mnt/dummy/
tumbleweed:~ $ df -h | grep dummy
/dev/vdb        3.7G  2.0M  3.6G   1% /mnt/dummy

Assuming we’re running out of space on that file system and we want to add another device, how does work?

tumbleweed:~ $ bcachefs device add /mnt/dummy/ /dev/vdc
tumbleweed:~ $ df -h | grep dummy
/dev/vdb:/dev/vdc  7.3G  2.0M  7.2G   1% /mnt/dummy

Quite easy, and no separate step required to extend the file system, this was done automatically which is quite nice. You can even go a step further and specify how large the file system should be on the new device (which doesn’t make much sense in this case):

tumbleweed:~ $ bcachefs device add --fs_size=4G /mnt/dummy/ /dev/vdd 
tumbleweed:~ $ df -h | grep mnt
/dev/vdb:/dev/vdc:/dev/vdd   11G  2.0M   11G   1% /mnt/dummy

Let’s remove this configuration and then create a file system with multiple devices right from the beginning:

tumbleweed:~ $ bcachefs format --force /dev/vdb /dev/vdc

Now we formatted two devices at once, which is great, but how can we mount that? This will obviously not work:

tumbleweed:~ $ mount /dev/vdb /dev/vdc /mnt/dummy/
mount: bad usage
Try 'mount --help' for more information.

The syntax is a bit different, so either do it it with “mount”:

tumbleweed:~ $ mount -t bcachefs /dev/vdb:/dev/vdc /mnt/dummy/
tumbleweed:~ $ df -h | grep dummy
/dev/vdb:/dev/vdc  7.3G  2.0M  7.2G   1% /mnt/dummy

… or use the “bcachefs” utility using the same syntax for the list of devices:

tumbleweed:~ $ umount /mnt/dummy 
tumbleweed:~ $ bcachefs mount /dev/vdb:/dev/vdc /mnt/dummy/
tumbleweed:~ $ df -h | grep dummy
/dev/vdb:/dev/vdc  7.3G  2.0M  7.2G   1% /mnt/dummy

What is a bit annoying is, that you need to know which devices you can still add, as you won’t see this in the “lsblk” output”:

tumbleweed:~ $ lsblk | grep -w "4G"
└─vda3 254:3    0  1.4G  0 part [SWAP]
vdb    254:16   0    4G  0 disk /mnt/dummy
vdc    254:32   0    4G  0 disk 
vdd    254:48   0    4G  0 disk 
vde    254:64   0    4G  0 disk 
vdf    254:80   0    4G  0 disk 
vdg    254:96   0    4G  0 disk 

You do see it, however in the “df -h” output:

tumbleweed:~ $ df -h | grep dummy
/dev/vdb:/dev/vdc  7.3G  2.0M  7.2G   1% /mnt/dummy

Another way to get those details is once more to use the “bcachefs” utility:

tumbleweed:~ $ bcachefs fs usage /mnt/dummy/
Filesystem: d6f85f8f-dc12-4e83-8547-6fa8312c8eca
Size:                     7902739968
Used:                       76021760
Online reserved:                   0

Data type       Required/total  Durability    Devices
btree:          1/1             1             [vdb]                1048576
btree:          1/1             1             [vdc]                1048576

(no label) (device 0):           vdb              rw
                                data         buckets    fragmented
  free:                   4256956416           16239
  sb:                        3149824              13        258048
  journal:                  33554432             128
  btree:                     1048576               4
  user:                            0               0
  cached:                          0               0
  parity:                          0               0
  stripe:                          0               0
  need_gc_gens:                    0               0
  need_discard:                    0               0
  capacity:               4294967296           16384

(no label) (device 1):           vdc              rw
                                data         buckets    fragmented
  free:                   4256956416           16239
  sb:                        3149824              13        258048
  journal:                  33554432             128
  btree:                     1048576               4
  user:                            0               0
  cached:                          0               0
  parity:                          0               0
  stripe:                          0               0
  need_gc_gens:                    0               0
  need_discard:                    0               0
  capacity:               4294967296           16384

Note that shrinking a file system on a device is currently not supported, only growing.

In the next post we’ll look at how you can mirror your data across multiple devices.

L’article Another file system for Linux: bcachefs (2) – multi device file systems est apparu en premier sur dbi Blog.