Abdul Hafeez Kalsekar -- Oracle database tuning case studies

    I already had another blog for sql profile  however at times we have scenario where sql profile is not used  and  we still see sql plan flip .  In such case we have to  prefer creating sql plan baseline .  There are nany articles online already on  sql baselines however i wanted to create my own  The use of SQL plan baselines is controlled by the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter, which is set to TRUE by default. Access to the DBMS_SPM package requires the ADMINISTER SQL MANAGEMENT OBJECT privilege. Creating Sql plan   baseline using  Sql   tuning sets : =>  Drop   existing baseline for same sqlid if any  BEGIN    DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'STS_f9948h4y2aa22'); END; / ==>  Create new sql plan  baseline :  BEGIN   DBMS_SQLTUNE.CREATE_SQLSET(     sqlset_name => 'STS_f9948h4y2aa22',     description => 'SQ...

  1)  JDBC TCPS Connection Fails with java.sql.SQLRecoverableException: IO Error: Connection reset by peer, Authentication lapse 0 ms. (Doc ID 2961999.1) -->  (SERVER=DEDICATED)  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<HOST>)(PORT=<PORT>))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<SERVICE NAME>))) 2) BTM JDBC Connections Are Being Closed With the Following Message in the Logs: java.sql.SQLRecoverableException: Closed Connection (Doc ID 1462656.1) -- > (ENABLE=broken) jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=broken)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOSTNAME1>.<DOMAIN_NAME>)(PORT=1521))(ADDRESS= (PROTOCOL=TCP)(HOST=<HOSTNAME2>.<DOMAIN_NAME>)(PORT=1521))(LOAD_BALANCE=on)(FAILOVER=on))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=<SERVICE_NAME>.<DOMAIN_NAME>)(FAILOVER_MODE=(TYPE=session)(METHOD=basic)(RETRIES=10)(DELAY=3)))) 3)   Tracing  How to Trace the JDBC Driver (Doc ID 338433...

  Applies to: Oracle Net Services - Version 11.2.0.3 and later JDBC - Version 11.2.0.3.0 and later Oracle Database - Enterprise Edition - Version 11.2.0.3 and later Information in this document applies to any platform. Goal This note discusses how to trace what is sent and received between the Oracle JDBC Driver release 11.2 and up and the Oracle database. Solution In this 6 minute video, you will learn about JDBC logging and tracing A Quick Video on How to Enable Oracle JDBC Logging & Packet Tracing(Doc ID 2975841.1)  Direct Link   From release 11.2, the JDBC Thin Driver includes a network trace capability. Hence this note is applicable to JDBC 11.2 and above versions. This capability allows you to trace the network packets that the driver exchanges with the server. To generate network level trace, one need the following - Oracle debugging JDBC driver (e-g ojdbc6_g.jar, ojdbc7_g.jar, ojdbc8_g.jar) - Specify the logging properties and point the location at t...

  How to Trace the JDBC Driver (Doc ID 338433.1) To Bottom In this Document Goal Solution   Managing log files References Applies to: JDBC - Version 11.2.0.1.0 to 23.5 [Release 11.2 to 23] Information in this document applies to any platform. Goal The purpose of this document is to illustrate the JDBC tracing feature.  This is also referred as JDBC logging. JDBC logging is now documented in the JDBC Developer's guide: Oracle Database JDBC Developer's Guide, 11g Release 2 (11.2) Part Number E16548-02 31 Diagnosability in JDBC Logging Oracle® Database JDBC Developer's Guide 12c Release 1 (12.1) E49300-05 32 Diagnosability in JDBC Logging JDBC Developer's Guide Release 19 34 Diagnosability in JDBC 34.1 About Logging Feature of Oracle JDBC Drivers JDBC Developer's Guide and Reference Release 21 36 Diagnosability in JDBC 36.1 About Logging Feature of Oracle JDBC Drivers   Solution In this 6 minute video, you will learn about JDBC logging and tracing A Quick Video on How ...