Feed aggregator
Leadership
Good leadership is when people do things because they want to versus have to.
Recently, I was given a position as team lead and it seems I have fallen in the second position (have vs want).
Wondering if I need to make someone cry? Here’s the story for that.
I was promoted to lead at a young age at Puritan Bennet.
The company had 2 strikes with FDA.
Current team lead has made me aware of my short fuse.
First day as a lead, a manager tells me “One of your team member is crying in the corner.”
CRAP! I went to see her and ask what I did wrong.
I was not scolding nor yelling but rather spoke in a calm voice. She then tells me, “I can see the anger in your eyes.”
Thank God for remote work.
They are the best!
What is Success?
To laugh often and much;
To win the respect of intelligent people and the affection of children;
To earn the appreciation of honest critics and endure the betrayal of false friends;
To appreciate beauty;
To find the best in others;
To leave the world a bit better, whether by a healthy child, a garden patch or a redeemed social condition;
To know even one life has breathed easier because you have lived;
This is to have succeeded.
– Ralph Waldo Emerson
I actually have this in my photo album from decades ago.
Video on monitoring (a single instance) DataGuard Standby for a RAC (2-node) Cluster
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)
CPU Utilization
Receiving Webhook Events from Stripe Payment Processing
Read consistency accross cursors in one procedure
why view's trigger disappear?
Why is json_array_t using 0-based indexing
Converting column number values into array number values in SQL
Enabling Cursor Sharing in PeopleSoft Processes
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.
Reusing AE StatementsIt 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.
Session Settings for Processes Executed on the Process SchedulerIt 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;
/
The first delivered program that was a candidate for cursor sharing was GLPOCONS (GL Consolidations process). All that is necessary is to insert the relevant metadata, and it will apply the next time the process starts. Anything you can set with an ALTER SESSION command can be put in the metadata. At times, other settings have been defined, hence the insert statement is written in this way.
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
/
Cursor Sharing in Stand-Alone Application Engine ProgramsIn PeopleSoft, some Application Engine programs are executed by other programs. For example, the General Ledger Revaluation process (FSPCCURR) and (GLPOCONS), will directly invoke the Journal Edit and Budget Check process (GL_JEDIT2) for each journal that needs to be edited. GL_JEDIT2 inherits the process instance of the FSPCCURR process that invoked it, but there is no process scheduler request record for it to update, so the trigger technique described above does not work.
A different approach, specific to GL_JEDIT2 is required. The first thing GL_JEDIT2 does is write the current process instance number onto the JRNL_LN records it is working on.
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
The update statement may update many rows, but I only want to enable cursor sharing once. Therefore I have created a compound trigger.
- The trigger only fires when a statement updates PS_JRN_LN.PROCESS_INSTANCE from a zero to a non-zero value.
- The after statement section executes once after the update statement completes. This will contain the logic that checks the setting of module to verify that this is a GL_JEDIT2 process and that the current process instance is a process that is currently executing. It also enhances the value of the MODULE setting with the process name and instance; thus making it possible to determine which GL_JEDIT2 process was invoked by which parent process. Finally, it enables cursor sharing for the current session. However, the after statement section cannot read the data values being updated.
- Therefore an after row section is needed to collect the process instance. It fires for each row being updated. It is as minimal as possible to avoid adding overhead to the update statement. It copies the updated value of PROCESS_INSTANCE to a global PL/SQL variable, and nothing else. The variable value can then be read in the after statement section.
- The dbms_output commands are left over from testing and have been commented out in the final trigger.
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;
/
abc LLM JSON Output with Instructor RAG and WizardLM-2
Rownum quiz
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.
Looking for AI? You already have it!
Local RAG Explained with Unstructured and LangChain
Is it a must to run pupbld.sql as system
Mixed version dataguard
How to call rest api which accept x-www-form-urlencoded in PL/SQL procedure in Apex
Error in pl/sql code
Oracle Is Guilty Until Proven Innocent
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.
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.