Skip to main content
Delphix

Troubleshooting Masking Performance In-Place Oracle (KBA5985)

 

KBA

KBA# 5985

 

At a Glance

Summary: Masking Performance troubleshooting steps for Oracle.
DIY Investigation: These are DIY steps: 
  1. Measure latency using ping.
  2. Measure and collect Execution Plans (or Live Query Stats) for:
    • SELECT COUNT
    • SELECT
    • UPDATE

See below for explanation and queries.

Support Investigation: These are steps to send a bundle to Delphix support:
  1. Upload the details collected above.
  2. Set Feedback Size to 10,000 and run one job with Streams = 1.
  3. Upload a Support Bundle. 
  4. Detail the Masking Job ID, Execution ID, and the Table.
Bottlenecks and actions: When the investigation of the bottleneck has been completed, these are possible next actions: 

SELECT:
  • Investigate the execution plan for SELECT and network latency.
  • If a WHERE clause is used, consider indexing these column(s).

Masking:

  • Check the number of masked columns.
  • Check the algorithms used (are they optimal for performance).
  • Check the capacity of the masking engine.

UPDATE:

  • Investigate the details around UPDATE and other operations on the database. 
  • Check if there are Indexes on Masked Columns and, if there are, these should be dropped. 
  • Check if there are any Triggers on masked tables. These need to be disabled.

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0

5.3

5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

4.2

4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1

4.1

4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

Oracle Masking Performance

This Knowledge Article will look at how to troubleshoot Oracle Masking Performance. With masking performance, we will only look at the rpm (rows per minute) of the actual masking operation (In-Place only). It will not include the duration it takes to execute SQL for Pre/Post-Scripts, Constraints, and Indexes. 

 

The steps in this guide can be performed with available tools and will help to determine and answer the following questions:

  • Is the bottleneck reading the data (SELECT)?
  • Is the bottleneck the masking operation (algorithms and number of columns)?
  • Is the bottleneck the output (UPDATE)?

Performance factors 

The groups and the key factors to performance are: 

  1. Masking Engine - the number of masked columns, data types, and algorithms.
  2. Network - latency and throughput.
  3. Database - read and write performance, the number of records per block, and fragmentation.  

 

The diagram below shows where the delays (in milliseconds) are accumulating to become slow performance. 

Generic_-_Timing_Events_v4.png

tother = Java, JDBC, etc. 

  • COUNT
    • Typical: Full Table Scan (FTS), reads all blocks.
    • tc = tlc + tdbc + tother 
  • SELECT
    • Typical: Full Table Scan (FTS), reads all blocks.
    • ts = tls + tdbs + tother
  • MASKING
    • tm
  • UPDATE
    • Uses System Global Area (SGA).
    • Typical: Reads all blocks. AWR Gets is equal to updated rows. 
    • tu = tlu + tdbu + tother

 

The diagram excludes the throughput and the size of the data as this is normally not a performance-limiting factor. time tc (time to count) is only included for reference.

Masking performance will be dictated by compounding and capping factors based on the timing events above.

To improve masking performance, the current bottleneck needs to be found and resolved (and the process repeated if needed).

Masking Performance at different stages

The performance will differ at different stages of the masking job. These stages can be measured at the start of the masking job. Measuring these stages can help determine where the bottleneck is located.

The performance can be measured when the masking job finishes as well but it is usually not practical to measure these as one has to wait until the job finishes successfully. 

 

A normal In-Place masking job execution has the following characteristics:

  1. Initial SELECT
    • The first 10,000 to 20,000 records are normally read fast as the records are read into the buffers of the masking job.
    • This is usually visible at the start of the job and will show the performance of the SELECT before masking any records.
  2. Masking Transformation  
    • There are also buffers to be filled at the end of the job. 
    • The masking performance usually drops when records are masked. How much depends on the masking factors (see above).
  3. Update
    • The last step that can dictate the performance is the UPDATE step.
    • If this step is the slowest (normal case) all buffers will be full and the job is capped by the UPDATE performance. 

 


Oracle Moving v7 (fast input w gauge).gif

SELECT and UPDATE Performance Graph

By temporarily setting the Feedback Size to 10,000, the execution can be illustrated in a graph. This graph shows the performance as the buffers fill up and this will indicate the bottleneck.

An illustration of this can be seen below, which shows the job above. The graph shows the relative performance compared with the rpm of the overall masking job.

 

The characteristics are: 

  • The SELECT performance is initially the max performance that can be fetched from the database. 
  • As soon as the records start to appear in the masking engine they will be sent to the UPDATE step.
  • Hence, the UPDATE performance can also be seen at the beginning of the job. 
  • The SELECT will be at max speed until the initial buffers are full (around 20,000 - 30,0000 rows).
  • The performance will then be (in this case) capped to the performance of the masking operation.
  • When all buffers (relevant) are full the SELECT step will be forced to the same performance as the UPDATE step.

Masking Relative Performance - SELECT Mask UPDATE.png

Example of Bottlenecks 

No masking job is exactly the same and the characteristics will differ. With that said, there are three key bottleneck cases based on where the main bottleneck is located. 

These cases are represented below (with example graphs): 

SELECT 

In this case, the performance will not be faster than the SELECT step and hence capped on SELECT.

Actions:

  • Investigate the execution plan for SELECT and network latency.
  • If a WHERE clause is used consider indexing these column(s). 
Masking operation 

In this case, the SELECT will reach max performance until the initial buffers are full and then be capped by the masking operation.

Actions:

  • Check the number of masked columns.
  • Check the algorithms used (are they optimal for performance).
  • Check the capacity of the masking engine.
UPDATE 

This is the performance case detailed above. 

Actions:

  • Investigate the details around UPDATE and other operations on the database. 
  • Check if there are Indexes on Masked Columns and if there are these should be dropped. 
  • Check if there are any Triggers on masked tables. These need to be disabled.

 

Masking Relative Performance - SELECT Cap.png Masking Relative Performance - Masking Cap.png Masking Relative Performance - UPDATE Cap.png

 

Collecting data

To find the bottleneck, we need to look at a few areas. Many of the reports will only show a limited amount of information. To see the complete picture, we need all data.

Some points to remember:  

  • Masking jobs:
    • Run one job with Stream = 1.
    • If multiple tables are executed, indicate which table should be focused on. 
  • AWR reports:
    • Collect data including the start of the job.
    • For small jobs - till the end.
    • For longer jobs - for around 7 minutes (and the job can then be canceled). 
  • Testing performance on the database:
    • Make sure to flush buffers and the cache before testing. 
  • (Optional) Testing masking algorithm performance:
    • Make sure only one column is masked and set the algorithm to a fast algorithm (for example, TK) to see if that makes a difference. 

What to collect

The following information needs to be collected: 

  • Masking Rule Set and Inventory.
    • For example: how many masked columns, algorithms, size of the data.
  • Database performance.
    • The number of rows in the table.
    • The durations from the database tests. 
    • Any execution plan.
  • Testing the masking performance.
    • Temporarily set Feedback Size to 10,000.
    • Test run the masking job (and the table of interest) for around 5 min.
    • Remember to reset the Feedback Size to the original value (10,000 is too small for large jobs and will cause the logs to grow).
  • Collect an AWR from the masking job test.

Commands to test performance

Network performance to Masking Engine

Ping the Masking Engine and collect latency data. Run this a few time to get a good average value for the latency.

ping Your_Masking_Enging

Collect trace file and timing data

To know how fast Oracle can perform the statements on the database, we need to enable tracing and collect some data. 

To collect the data, run the statement in the commands below:

  • If possible, flush the cache and the shared pool before testing. 
  • Use SQLPLUS or another tool and log in as sysdba.
  • Set up tracing and make note of the trace file name.
  • Run the statements below for the Read and the Update.
  • Exit SQLPLUS.
  • Run tkprof on the collected trace file. 

 

Flush cache and pool (requires dba)

alter system flush buffer_cache;
alter system flush shared_pool;
connect / as sysdba
set autotrace on statistics

Collecting the data

oradebug setmypid
oradebug tracefile_name
alter session set sql_trace=true;
set timing on
[Your Performance Statements]
exit
tkprof Your_Trace_File_From_Above.trc

SELECT Performance

The SELECT is here encapsulated in a SELECT COUNT to reduce the output size. 

Actions

  • Change YOUR_TABLE to the table to test.
  • Take note of the number of records.
  • Take note of the duration to complete this statement (in milliseconds).

Performance Statement

select count(*) from (select rowid from USER.YOUR_TABLE);

Database performance estimates 

With the metadata above, the select performance can be calculated (note that this is the performance on the database and excludes network latency and java processing times): 

  • SELECT rpmRowCount / Duration * 1000 * 60
  • SELECT bpmUsedBlocks / Duration * 1000 * 60

UPDATE Performance 

The UPDATE performance is usually slower and the likely bottleneck. It is also harder to measure this as we can either do the test with UPDATE only (will be faster) or use a cursor with SELECT which will measure UPDATE and SELECT.

Actions

  • Modify and use the SQL below.
  • Change YOUR_TABLE to the table to test.
  • Change MASKED_COL to the masked column (add multiple if multiple columns are masked).
  • Take note of the number of records.
  • Take note of the duration to complete this statement (in milliseconds). 

 

Warning

Warning:

Ensure there is enough UNDO to complete this DML. 

 

Performance Statement (simple)

update USER.YOUR_TABLE set MASKED_COL = MASKED_COL WHERE ROWNUM < 500000;

 

Performance Statement (alternative using cursor)

  • Modify the datatype for 'my_col1' to match one of the columns.
  • For multiple columns add one per column.
DECLARE
   CURSOR c1 is
      select rowid, MASKED_COL from USER.YOUR_TABLE;
   my_col1 VARCHAR2(20);
   my_rowid VARCHAR2(20);
BEGIN
   OPEN c1;
   FOR i IN 1..50 LOOP
   FOR i IN 1..10000 LOOP
      FETCH c1 INTO my_rowid,my_col1;
      EXIT WHEN c1%NOTFOUND;
      update USER.YOUR_TABLE set MASKED_COL=MASKED_COL where rowid=my_rowid;
      COMMIT;
   END LOOP;
   END LOOP;
   CLOSE c1;
END;
/

Database performance estimates 

With the metadata above, the UPDATE performance can be calculated. This includes rpm and bpm (rows and blocks per min) and is the performance on the database, excluding network latency and java processing times: 

  • SELECT rpmRowCount / Duration * 1000 * 60
  • SELECT ppmUsedPages / Duration * 1000 * 60

Reading the trace file data

Example of a trace file. 

  • This is from 'SELECT COUNT(*)'
  • 'Elapsed' time can be seen (Total). 
  • The number of blocks can be seen in 'Fetch'. 
    • 'Disk' will show how many blocks were read from the disk (this might be lower due to cache). 
    • 'Query' will show the actual blocks needed. This is the best value to use.
  • The number of rows can be seen in the Masking UI or in the aggregate in the trace file (40,960 in the example below). 
SQL STATEMENT ANALYZED

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.90          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.22        612        618          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       1.12        612        618          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=618 pr=612 pw=0 time=223635 us)
     40960      40960      40960   TABLE ACCESS FULL USERS (cr=618 pr=612 pw=0 time=102571 us cost=173 size=0 card=40960)

 

Collect data for Support Investigation

If needed, the performance can be investigated by support. 

  1. Upload the details collected above:
    • Table description.
    • Details about Indexes and Triggers.
    • SELECT and UPDATE statistics collected from the database. 
       
  2. Set Feedback Size to 10,000 and run one job with Streams = 1.
    • Remember to reset the Feedback Size to the original size (10,000 is too small for large jobs and will cause the logs to grow).
  3. Upload a Support Bundle. 
  4. Detail the Masking Job ID, Execution ID, and the table to investigate. 

Details to collect from the UI:

  • The Job ID, Execution ID, and the table name. 
  • The number of masked rows.
  • The rpm for the table. 

Performance data from Oracle's AWR report 

Capture the report

When capturing the AWR report, it is best to get the report from when the masking job starts. This will ensure that SELECT COUNT is included as well as Elapsed Time will be correctly measured.

  • If the job is smaller (up to 30 min) - run the job to completion and capture the AWR report after the job.
  • If the job is longer - you can cancel the job after 10-15 min and capture the AWR report.
  • If possible, please provide the AWR report over a 1 hr time period. 

Key items 

The key elements to look for in the AWR report are: 

  • Oracle version.
  • The time period of the AWR report.
  • The section 'Complete List of SQL Text' should include the following statement from the masked table. 
    • SELECT COUNT - this gets the number of columns to mask.
    • SELECT - this is the 'Table input' step.
    • UPDATE - this is the 'Update' step. 
  • 'SQL Order by Elapsed Time'.
  • 'SQL ordered by Read'
  • 'SQL ordered by Physical Reads (UnOptimized)'
  • Value 'table fetch continued row'
    • This value is not shown below but is an important value.
    • If this value is very large it could be an indication that the data is fragmented. 

 

Note

Notes:

  • The number of writes is not available as Oracle optimizes this. 
  • The number of Update Threads is not affecting the AWR report (the number of rows is still COMMIT SIZE (default 10,000) per Commit).

 

AWR: Report summary

The key measurements are: 

  • Begin and End Snap Time.
  • Elapsed Time.

Perf Test AWR - Summary_Crop.png

AWR: SQL ordered by Elapsed Time

The key Masking events and measurements: 

These times excludes any time outside the database. For example network latency. 

  • SELECT COUNT
    • The fastest execution as the result set is small  (only one row).
  • SELECT 
    • This is slightly slower on the database as there are more data in the result set. 
  • UPDATE
    • The update will take significantly longer. 
  • Check Executions
    • SELECT COUNT - should be 1 execution.
    • SELECT - should also be 1 execution.
    • UPDATE - number of Commits needed to commit the data.
Example calculations
  • COUNT performance: 0.06 secs >> 40,960 / 0.06 * 60 = 40,960,000 rpm.
  • SELECT performance: 0.07 secs >> 40,960 / 0.07 * 60 = 35,108,571 rpm.
  • UPDATE performance: 1.44 secs >> 40,960 / 1.44 * 60 = 1,706,666 rpm.
  • UPDATE executions: Ceiling(total rows / commit size). 40,960 / 10,000 = 4.1 < 5.

Perf Test AWR - SQL by Elapsed Time_Crop.png

AWR: SQL ordered by Reads

The key Masking events and measurements: 

  • SELECT COUNT
    • Roughly one Read per Block (less if there is a PK or Index or Multi-Block reads).
    • The data is stored in x number of blocks and we required x blocks to read the data.
  • SELECT 
    • Usually the same as SELECT COUNT.
  • UPDATE
    • Optimally should be less than the number of blocks for the table (as blocks might be in SGA (memory)).
Note

Note:

Indexes on masked columns will result in a much larger number of Reads and could be a root cause of performance issues.

Example calculations
  • 40,960 rows / 612 blocks ~ 67 rows/block.

 

Note

Note:

Verify the number of blocks in the trace above between Disk and Query. If Disk is much lower then use Query (more accurate). 

 

Perf Test AWR - SQL by Reads_Crop.png

 

AWR: SQL ordered by Physical Reads (UnOptimized)

This indicates how many times Oracle accessed the disk to read the data (Physical Reads).

  • UPDATE
    • This should be the same as Reads as we need to read the block to update it (could be less than total, as blocks might be in SGA).
  • SELECT COUNT
    • Indicates if Multiblock Read used for Full Table Scan. 
  • SELECT 
    • Indicates if Multiblock Read used for Full Table Scan. 
Example calculations
  • SELECT COUNT: 
    • Number of blocks (from trace file above) / Physical Reads: 612 / 78 = 7.8
    • Typically Oracle will read 8 blocks in each Physical Read (Multiblock Read as part of Full Table Scan). 
    • If this number is not close to 8 then the read is less optimal.
Note

Note:

This usually does not cause an issue as SELECT is normally not the bottleneck. 

Perf Test AWR - SQL by Phyiscal Read_Crop.png

 

Related Articles

The following articles may provide more information or related information to this article: