Posts in category Oracle Database

Debugging PL/SQL and Java Stored Procedures with JPDA

Oct18
2011 Leave a Comment Written by Christian Antognini

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!

Posted in 11gR1, 11gR2, Java

Ad: Der Oracle DBA – Handbuch für die Administration der Oracle Database 11gR2

Oct14
2011 6 Comments Written by Christian Antognini

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.

Posted in 11gR1, 11gR2

Impact of STATISTICS_LEVEL on Cardinality Feedback and Adaptive Cursor Sharing

Sep19
2011 7 Comments Written by Christian Antognini

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?

Posted in 11gR1, 11gR2, Query Optimizer