Here’s a simple little PL/SQL block where we call an inner procedure PARAMETER_TESTER from its parent block. Pay particular attention to the parameter we pass to the procedure, and it’s value throughout the execution of that procedure.

I had a request from a client a while back regarding masking of data. They had an application with sensitive data in the Production environment (where access and audit were very tightly controlled) but the issue was how to respect that sensitivity in non-Production environments whilst still preserving full size data sizes for application testing.

After some conversations about requirements, it turned out that since (even in non-Production environments) all access to application components was logged and audited, the issue was simply protecting against “inadvertent” access to sensitive data. For example, in the application, if I searched for “males with black hair with an interest in technology” I should never see the name “Connor McDonald” on screen in the results, because simply viewing that data could be treated as a breach of privacy.

I posted about my APEX upgrade to 19 yesterday, and someone was quick to point out to me that they believed I hadn’t covered all of the steps.

“What if your APEX instance needs to call web services?” they said. “You need to update your Access Control Lists.”

I hadn’t thought of that, so I logged onto one of my other APEX instances that was still at version 18, and checked the current ACLs

UPDATE: All slots for this study have been filled.

On the other and would love your feedback. Please send any ideas about what you’d like to see in RDS performance monitoring to me at kylelf at amazon.com

Thanks

Kyle

AskTOM moved to Application Express 19.1 without any major issues last weekend. That in itself is a nice endorsement for APEX, given that the AskTOM application dates back nearly 20 years to 2001, and predates even the existence of APEX.

The only fix that we had to make was that AskTOM uses the static CDN files that Joel Kallman blogged about to make it nice and snappy wherever in the world it is used. The reference to those files have a hard-coded version number so that needed to updated. For AskTOM, we have a plugin that uses some jQuery elements that went pear-shaped when referencing the old version 18 files, but after a quick fix to that reference all was well.

I’m sure most of us have read or heard at a conference the benefits of array fetching and array binding when it comes to passing data back and forth to the database. And we’ve all seen the numerous demo scripts in PL/SQL along the lines of:

FORALL i in 1 .. n 
   INSERT ...

As such, there is a misconception out there that you are only going to be able to use bulk binding for basic DML operations. So I thought I’d share this example that came in via AskTOM recently. We got asked if there was any means of improving the performance of this row-by-row operation where the DML was a complex Text index search, with the additional complication that on a row by row basis, the DML may fail but that this was an anticipated outcome that needed to be handled and moved past. The scenario presented was as follows:

Honeycomb.io turns out to be a nice solution for collecting, retrieving and displaying multi-dimensional time series data, i.e. the kind of data you get from sampling.

For example, in the database world we have Active Session History (ASH) which at  it’s core tracks

1. when – timestamp
2. who – user
3. command – what SQL are they running
4. state – are they runnable on CPU or are they waiting and if waiting what are they waiting for like I/O, Lock, latch, buffer space, etc

Collecting this information is pretty easy to store in a relational database as I did when creating S-ASH (Simulated ASH) and Marcin Przepiorowski built upon over the years since, or even store in flatfiles like I did with W-ASH (web enabled ASH).

Amazon RDS Performance Insights (PI) doesn’t have a single pane of glass dashboard for clusters, yet. Currently PI has a dashboard that has to be looked at for each instance in a cluster.

On the other hand one can create a simple cluster dashboard using Cloudwatch.

PI, when enabled, automatically sends three metrics to Cloudwatch every minute.

These metrics are

1. DBLoad
2. DBLoadCPU
3. DBLoadNonCPU

DBLoad = DBLoadCPU + DBLoadNonCPU

I’m feeling very determined on this one. Yes I have a lot of determination to inform blog readers about determinism, and yes I have run out of words that sound like DETERMINISTIC. But one of the most common misconceptions I see for PL/SQL functions is that developers treat them as if they were “extending” the existing database kernel. By this I mean that developers often assume that wherever an existing in-built function (for example TO_NUMBER or SUBSTR etc) could be used, then a PL/SQL function of their own creation will work in the exactly the same way.

Often that will be the case, but the most common scenario I see tripping up people is using PL/SQL functions within SQL statements. Consider the following simple example, where a PL/SQL function is utilizing the in-built SYSTIMESTAMP and TO_CHAR functions.

One of the nice things about the job scheduler in the Oracle database is the easily interpreted interval settings you can apply for job frequency. The days of cryptic strings like “sysdate+0.000694444” when all you really wanted to say was “Just run this job every minute” are a thing of the past. I covered how to get the database to convert interval strings into real execution dates here 

But it raises the question: What if I have a job that is scheduled to run every minute, but it takes more than 1 minute to run? Will the scheduler just crank out more and more concurrent executions of that job? Will I swamp my system with ever more background jobs? So I thought I’d find out with a simple test.