Striving for Optimal Performance

The 31st of January and 1st of February 2012 I will present a 2-day seminar about the Oracle query optimizer in Ballerup (DK). The event is organized by Miracle A/S. The content, which is based on the chapters 2, 4, 5, 6, 9 and 10 of my book, is the following:

• Chapter 1 describes the life cycle of SQL statements and when the database engine can share cursors.
• Chapter 2 describes the aim and architecture of the query optimizer.
• Chapter 3 and 4 discuss the statistics used by the query optimizer to carry out its work.
• Chapter 5 describes the initialization parameters influencing the behavior of the query optimizer and how to set them.
• Chapter 6 outlines different methods of obtaining execution plans, as well as how to read them and recognize inefficient ones.
• Chapter 7 describes how to take advantage of available access structures in order to access data stored in a single table efficiently.
• Chapter 8 goes beyond accessing a single table, by describing how to join data from several tables together.

The flyer and this page provide detailed information about the seminar.

Challenges and Chances of the 11g Query Optimizer is the name of a presentation I gave at several events (e.g. Trivadis Performance Days, Oracle OpenWorld, DOAG Konferenz, UKOUG Conference) throughout 2011. Its abstract is the following:

With every new release, the query optimizer is enhanced. Oracle Database 11g Release 1 and Release 2 are no exception to the rule. Specifically, they introduce key improvements in the following areas: indexing, optimization techniques, object statistics and plan stability. The aim of this presentation is to review the new features from a practical point of view as well as to point out challenges related to them. In other words, to let you know what you can expect from the query optimizer when you upgrade to Oracle Database 11g.

The aim of this short post is to point out that I made available the current version of the slides and all the scripts that go with them here.

The structure of the presentation (incl. a reference to the available scripts) is the following:

• Observations
  • Number of Query Optimizer Parameters by Release
  • Number of Query Optimizer Bugs Fixed by Patchset
• Indexing
  • Invisible Indexes (ex_invisible_index.sql)
  • Index Support for Linguistic LIKE (ex_linguistic_like.sql)
  • INDEX REBUILD and Statistics History (ex_index_rebuild.sql)
• Optimization Techniques
  • Full Outer Join (ex_full_outer_join.sql)
  • Join-Filter Pruning (ex_join_filter_pruning.sql)
  • Table Expansion (ex_table_expansion.sql)
  • Join Factorization (ex_join_factorization.sql)
  • OR Expansion (ex_or_expansion.sql)
  • Join Elimination (ex_join_elimination.sql)
  • Subquery Unnesting (ex_subquery_unnesting.sql)
• System and Object Statistics (DBMS_STATS)
  • Workload System Statistics
  • Object Statistics – Default Preferences
  • Object Statistics – Auto Sample Size
  • Object Statistics – Pending Statistics (ex_pending_object_statistics.sql)
  • Object Statistics – Incremental Statistics (ex_incremental_stats.sql)
  • Object Statistics – Extended Statistics on Expressions (ex_extended_statistics1.sql)
  • Object Statistics – Extended Statistics on Column Groups (ex_extended_statistics2.sql)
  • Object Statistics – Seeding Column Groups
  • Object Statistics – Comparing Statistics (ex_comparing_statistics.sql)
  • Object Statistics – Locks not Exported
  • JOB_QUEUE_PROCESSES
• Plan Stability
  • CURSOR_SHARING
  • SQL Plan Baselines (ex_execution_plan_stability.sql, ex_execution_plan_stability_10g.sql, ex_execution_plan_stability_11g.sql)
  • Stored Outlines
  • Adaptive Cursor Sharing (ex_bind_peeking.sql, ex_bind_peeking_bind_aware.sql)
  • Cardinality Feedback (ex_cardinality_feedback.sql)

Recently I was involved in a project where I had to trace the database calls of an application based on Oracle Portal 10.1.4. The basic requirements were the following:

• Tracing takes place in the production environment
• Tracing has to be enable for a single user only
• Instrumentation code cannot be added to the application

Given that Oracle Portal uses a pool of connections and that for each HTTP call it can use several database sessions, statically enable SQL trace for specific sessions was not an option.

Knowing nothing about Oracle Portal I started RTFM and, gladly, I discovered that there is a simple way to inject a piece of code before and after a requested procedure is called. This is done by setting, via the administration GUI, the parameters PlsqlBeforeProcedure and PlsqlAfterProcedure.

Since Oracle Portal provides a function (WWCTX_API.GET_USER) to get the current user, I decided to create the following procedures to set/clear the client identifier before/after every call. Note that I added the call to SUBSTR and the exception handler to make sure that the procedures do not raise exceptions (hey, it’s a production system and I do not want to impact everyone!).

CREATE PROCEDURE tvd_set_client_identifier AS
BEGIN
  dbms_session.set_identifier(substr(portal.wwctx_api.get_user,1,64));
EXCEPTION
  WHEN others THEN NULL;
END;

CREATE PROCEDURE tvd_clear_client_identifier AS
BEGIN
  dbms_session.clear_identifier;
EXCEPTION
  WHEN others THEN NULL;
END;

To “enable” these procedures we did the following:

• Set PlsqlBeforeProcedure=portal.tvd_set_client_identifier
• Set PlsqlAfterProcedure=portal.tvd_clear_client_identifier
• Restarted the application server

With this configuration in place enabling SQL trace for a single user was easily done by calling the DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE procedure by specifying the user to be traced as value for the CLIENT_ID parameter.

In 2003 I published a paper entitled Debugging PL/SQL and Java Stored Procedures with JPDA. Its aim was to describe how to debug PL/SQL and Java code deployed into the database with JDeveloper 9i. Two weeks ago a reader of my blog, Pradip Kumar Pathy, contacted me because he tried, without success, to do something similar with JDeveloper 11g, WebLogic 11g and Oracle Database 11g. Unfortunately I was not able to help him. The reason is quite simple, since 2004 I’m an Eclipse user…

Few days later Pradip contacted me again to let me know that, at last, he succeeded. Here you find his notes…

1. Grant the required privileges

GRANT DEBUG CONNECT SESSION to &&schema_name;
GRANT DEBUG ANY PROCEDURE TO &&schema_name;

2. Create a database connection under “Application Resources” and compile the stored procedure in debug modus (Figure 2a and Figure 2b)
3. Write some Java code to let the database engine connect JDeveloper (this piece of code must be executed before calling the PL/SQL stored procedure)

private static void remotePLSQLDebug(Connection con) throws SQLException
{    
  SystemProperties systemProperties = null;
  systemProperties = SystemProperties.getInstance(); 
  CallableStatement callState = null;
  String port = systemProperties.get("plsqldebug.port").trim();
  String storedCall = "call dbms_debug_jdwp.connect_tcp('localhost'," + port + ")";
  callState = con.prepareCall("{" + storedCall + "}");
  callState.execute();
  callState.close();
}

4. Configure the plsqldebug.port parameter in the application properties
5. Edit the project properties to enable JPDA and defining the port used by the JPDA listener (Figure 5a and Figure 5b)
6. Deploy the EAR in WebLogic and start the JPDA listener configured in the previous step
7. Execute the Java code of step 3; as a result the database engine connects the JPDA listener and a connection process is created (Figure 7)
8. When the PL/SQL stored procedure is executed, JDeveloper is able to debug it (open the PL/SQL stored procedure in the “Database Navigator” for that purpose)
9. Once the debugging is over detach the connection process

Thank you Pradip to share your findings!

The book Der Oracle DBA (Hanser, 2011), which was written in German, is at last available!

I say “at last” because the authors worked on this project for not less than two years.

Who are the authors? Several colleagues of mine at Trivadis (Mirko Hotzy, Konrad Häfeli, Daniel Steiger, Sven Vetter, Peter Welker), Andrea Held, Lutz Fröhlich, Marek Adar and myself.

For my part, I wrote two chapters: Speicherplatzverwaltung and Optimierung. The first one, Speicherplatzverwaltung, is available here. Also available online are the table of content, the preface and the index.