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…
- Grant the required privileges
GRANT DEBUG CONNECT SESSION to &&schema_name;
GRANT DEBUG ANY PROCEDURE TO &&schema_name;
- Create a database connection under “Application Resources” and compile the stored procedure in debug modus (Figure 2a and Figure 2b)
- 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();
}
- Configure the
plsqldebug.port parameter in the application properties
- Edit the project properties to enable JPDA and defining the port used by the JPDA listener (Figure 5a and Figure 5b)
- Deploy the EAR in WebLogic and start the JPDA listener configured in the previous step
- 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)
- 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)
- 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.
The STATISTICS_LEVEL parameter controls a bunch of features. In addition to the documentation, also the V$STATISTICS_LEVEL view provides a list of the ones it controls.
SQL> SELECT statistics_name, description, activation_level
2 FROM v$statistics_level
3 ORDER BY 3 DESC, 1;
STATISTICS_NAME DESCRIPTION ACTIVATION_LEVEL
-------------------------------------- ------------------------------------------------------------ ----------------
Active Session History Monitors active session activity using MMNL TYPICAL
Adaptive Thresholds Enabled Controls if Adaptive Thresholds should be enabled TYPICAL
Automated Maintenance Tasks Controls if Automated Maintenance should be enabled TYPICAL
Bind Data Capture Enables capture of bind values used by SQL statements TYPICAL
Buffer Cache Advice Predicts the impact of different cache sizes on number of TYPICAL
physical reads
Global Cache Statistics RAC Buffer Cache statistics TYPICAL
Longops Statistics Enables Longops Statistics TYPICAL
MTTR Advice Predicts the impact of different MTTR settings on number of TYPICAL
physical I/Os
Modification Monitoring Enables modification monitoring TYPICAL
PGA Advice Predicts the impact of different values of pga_aggregate_tar TYPICAL
get on the performance of memory intensive SQL operators
Plan Execution Sampling Enables plan lines sampling TYPICAL
SQL Monitoring Controls if SQL Monitoring should be enabled TYPICAL
Segment Level Statistics Enables gathering of segment access statistics TYPICAL
Shared Pool Advice Predicts the impact of different values of shared_pool_size TYPICAL
on elapsed parse time saved
Streams Pool Advice Predicts impact on Streams perfomance of different Streams TYPICAL
pool sizes
Threshold-based Alerts Controls if Threshold-based Alerts should be enabled TYPICAL
Time Model Events Enables Statics collection for time events TYPICAL
Timed Statistics Enables gathering of timed statistics TYPICAL
Ultrafast Latch Statistics Maintains statistics for ultrafast latches in the fast path TYPICAL
Undo Advisor, Alerts and Fast Ramp up Transaction layer manageability features TYPICAL
V$IOSTAT_* statistics Controls if I/O stats in v$iostat_ should be enabled TYPICAL
Plan Execution Statistics Enables collection of plan execution statistics ALL
Timed OS Statistics Enables gathering of timed operating system statistics ALL
Something that I learned only recently is that STATISTICS_LEVEL also controls cardinality feedback and adaptive cursor sharing. This fact, according to me, is neither (clearly) documented nor pointed out by the information provided by V$STATISTICS_LEVEL. In any case, when STATISTICS_LEVEL is set to BASIC at the system level both features are disabled. Interestingly, an ALTER SESSION SET STATISTICS_LEVEL = TYPICAL it is not enough to enable them… For adaptive cursor sharing it is possible to use the BIND_AWARE hint, though.
Note that I never advise to set STATISTICS_LEVEL at the system level to a value that is different from the default (TYPICAL). Probably for this reason I didn’t notice its impact for such a long time…
In any case I find it a bit disappointing that this information is not clearly stated somewhere. Or I’m the only one that was not aware?
