This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Category: Performance Tuning

Oracle 11g Methods of Performance Tuning SQL

>90% of upgrade related problems are performance issues after an upgrade.

Source: Oracle Corp

Oracle tools for helping you tune the database:

  • Statspack – FREE – (See note 394937.1)

  • AWR – Diagnostics Pack & Tuning Pack license required.
  • Real Application Testing (Features: SQL Performance Analyser & Database Replay) – Tuning Pack license required.

Since 11g, Oracle recommend, instead of: storing outlines, fixing stats, using SQL hints, using the Rule Based Optimiser (desupported); you should use the SQL Plan Management tool along with SQL Profiling.

See spm_white_paper_ow07.pdf for more information.

HowTo: Use AWR snapshot data to provide a history of sequential read values

Scenario: You have changed your back-end storage hardware (SAN array or SATA disk storage) and you want to see a historical overview of sequential read times of your database.

It’s possible to query the Oracle AWR data (provided you have paid for the license), to provide a historical list of sequential read times acccording to the snapshots taken by AWR.
You are obviously limited to the amount of data retained by AWR and the frequency of the AWR snapshots.

set linesize 400 pagesize 400
SELECT event_start.snap_id,
       to_char(snap.begin_interval_time,'DD-MM-YY HH24:MI') as begin_time,
       to_char(snap.end_interval_time,'HH24:MI') as end_time,
       round(decode(
                 (event_end.total_waits - nvl(event_start.total_waits, 0)),0, to_number(NULL),
       ((event_end.time_waited_micro -    nvl(event_start.time_waited_micro,0))/1000) / (event_end.total_waits - nvl(event_start.total_waits,0))
),0) avgwait,
       event_end.event_name event_name,
      (event_end.time_waited_micro - nvl(event_start.time_waited_micro,0)/1000000) total_ms,
      event_end.total_waits
FROM dba_hist_system_event event_start,
     dba_hist_system_event event_end,
     dba_hist_snapshot snap
WHERE event_end.snap_id = event_start.snap_id + 1
  AND event_end.event_name = 'db file sequential read'
  AND event_start.event_name = event_end.event_name
  AND event_start.snap_id = snap.snap_id
  AND event_start.dbid = snap.dbid
  AND event_start.instance_number = snap.instance_number
  AND snap.begin_interval_time > SYSDATE - 14            -- max 14 days history.
-- AND to_char(snap.begin_interval_time,'HH24') IN ('09','10','11','12','13','14','15','16','17')
-- AND to_char(snap.begin_interval_time,'MI') = '50'
ORDER BY event_start.snap_id;

NOTE: You can restrict the snapshot intervals used to provide “hourly” values by uncommenting the additional two lines.

SAP Performance FBL3n – Oracle Execution Plan Detail

Here’s a good reason to ensure that you always output (and read) the predicate information of the execution plan at the Oracle level and not just at the SAP level.

Inside the ST01 SQL Trace screen (in R/3 4.7 anyway), it doesn’t show how the predicates are accessed/filtered.

But in the Oracle view of the execution plan (I used autotrace, or DBMS_XPLAN) you can clearly see the “Predicate Information” section:

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 281 | 10 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| BSAS | 1 | 281 | 10 (0)|
|* 2 | INDEX RANGE SCAN | BSAS~Z2 | 1 | | 9 (0)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MANDT"=:A0 AND "BUKRS"=:A1 AND "HKONT"=:A2 AND
"XBLNR"=:A4 AND "BUDAT"<=:A3)
filter("XBLNR"=:A4)

This allowed me to see that the index BSAS~Z2 was not being accessed correctly due to a “feature” in the Oracle 10g CBO (see note: 176754, question #5) which SAP has documented as:

“If you specify indexed columns with LIKE, >, <, >=, <=, or BETWEEN, the system can no longer use the columns that follow to restrict the index range scan.”

After creating a new index BSAS~Z3 which contains exactly the same columns, but changing the order of the last two (BUDAT and XBLNR), I was able to get the execution plan to look like this:

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 281 | 1 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| BSAS | 1 | 281 | 1 (0)|
|* 2 | INDEX RANGE SCAN | BSAS~Z3 | 1 | | 1 (0)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MANDT"=:A0 AND "BUKRS"=:A1 AND "HKONT"=:A2 AND
"XBLNR"=:A4 AND "BUDAT"<=:A3)

What’s the difference I hear you ask. Just look at the COST column.
In a table with 100 million records, the difference is about 20 seconds.
Filter predicates are not as efficient as access predicates (just ask Tom).

This example was fairly specific to transaction FBL3n performance, since I noted that whenever this transaction was used to query open items, it always adds a predicate of “BUDAT < 99991231” before any of the dynamic selection predicates. Bad SAP!

Proving new Oracle Index in SAP Without a Transport

Let’s suppose you have created a new Oracle level index on a SAP table using SE11.
You’ve created it in DEV and proven that it changes the EXPLAIN PLAN using SQL Trace or Oracle level tools.

Unfortunately you don’t have a significant amount of data in your DEV instance, but you do have data in your UAT or integration test system.
You could release your workbench request that you will have created to create the new index in SE11, but this means a flurry of potential additional transports to remove it again if it doesn’t work.
You just want a proof of concept that can be removed again.

Since it’s entirely possible for you to create an index at the Oracle level below SAP, you just need to ensure that you create the index the same as SAP would.
You can choose a couple of methods to ensure this, but we will discuss the SAP biased method.

Using SE11, find the table on which the new index exists in DEV.

NOTE: We’re showing R/3 Enterprise, but it should be similar for Netweaver based systems.

Click the “Indexes…” button to list the indexes:

Double click the new index you created earlier.
Now on the menu, select “Utilities -> Activation Log”:

Click the magnifying glass icon next to the “Technical log for mass activation” option on the main screen.

Expand all the possible expansion icons:

Look for the text “CREATE” to find the create statement:

Copy the text and paste into SQL*Plus in any other SAP systems Oracle database to create the index.
You’re done.

Just remember that this index does not exist in the SAP data dictionary, so you must remove it once you have proven the EXPLAIN PLAN is working for the larger amount of data.

Use Oracle 10g Segment Advisor Usage

Generally, the Oracle 10g Segment Advisor collection job runs automatically out-of-the-box in an Oracle 10g install.
It’s useful to run the advisor sometimes on large indexes as it should be able to report whether the index is efficiently storing index records, or if it could be re-built.
Oracle Enterprise Manager is already capable of pulling Space Advisor information from Oracle database.

Here are some links to the Oracle docs:

Oracle Doc: 10g Advisors
Oracle Doc: 10g Segment Advisor

Oracle Doc: Manually Running the Segment Advisor to Reclaim Wasted Space

An excellent whitepaper on pro-actively managing space in the Oracle 10g database.

The collector job (Automatic Segment Advisor) analyses segments in the database and compares to AWR reports to produce recommendations which can be reported on.
You can check the last run of the job using the SQL below:

— Check the DBA SEGMENT ADVISOR job is collecting data.
COL actual_start_date FORMAT A20
COL run_duration FORMAT A15
COL job_name FORMAT A30
SELECT TO_CHAR(actual_start_date,’DD-MM-YY HH24:MI:SS’) start_date,
run_duration,
job_name
FROM dba_scheduler_job_run_details
WHERE owner=’SYS’
AND job_name = ‘AUTO_SPACE_ADVISOR_JOB’
ORDER BY actual_start_date;

Once the collector is running, you can query the database using the DBMS_ADVISOR package to create analysis tasks that will query the recommendations and produce a recommendation report.

NOTE: The collector job does not analyse every object.

Below is the process I used to create a simple task to analyse a specific table and an index:

— Create some variables to hold our task details.
VARIABLE TASK_ID NUMBER;
VARIABLE TASK_NAME VARCHAR2(100);
VARIABLE OBJECT_ID NUMBER;

— Create a new empty task, which will populate the variables just defined.
EXEC DBMS_ADVISOR.CREATE_TASK(‘Segment Advisor’, :TASK_ID, :TASK_NAME);

— Assign a table and index check to the new task (replace <TABLE NAME> and <INDEX NAME>).
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, ‘TABLE’, ‘<SCHEMA>’, ‘<TABLE NAME>’, NULL, NULL, :OBJECT_ID);
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, ‘INDEX’, ‘<SCHEMA>’, ‘<INDEX NAME>’, NULL, NULL, :OBJECT_ID);

— Execute the task.
EXEC DBMS_ADVISOR.EXECUTE_TASK(:TASK_NAME);

NOTE: On a 40GB table this took approximately 10 minutes.

— Check the results in two ways:
— Query the tables DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_ACTIONS.

— or use DBMS_SPACE package (recommended).

SELECT
  RECOMMENDATIONS RECOMMENDATION,
  C1 ACTION1,
  C2 ACTION2,
  C3 ACTION3
FROM
  TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS)
WHERE
  TASK_ID = :TASK_ID;

— Delete the task at the end.
EXEC DBMS_ADVISOR.DELETE_TASK(:TASK_NAME);