Abdul Hafeez Kalsekar -- Oracle database tuning case studies

   Knowing when and how to gather statistics in a timely manner is critical to maintain acceptable performance on any system. Determining what statistics gathering operations are currently executing in an environment and how changes to the statistics methodology will impact the system can be difficult and time consuming. Reporting subprograms in DBMS_STATS package make it easier to monitor what statistics gathering activities are currently going on and what impact changes to the parameter settings of these operations will have.  The DBMS_STATS subprograms are  REPORT_STATS_OPERATIONS, REPORT_SINGLE_STATS_OPERATION and REPORT_GATHER_*_STATS.  The report shows detailed information about what statistics gathering operations have occurred, during a specified time window. It gives details on when each operation occurred, its status, and the number of objects covered and it can be displayed in either text or HTML format. COLUMN REPORT FORMAT A300 VARIABLE my_report CL...

   As we see 19c comes with lot of performance instability ,  recently  we been  facing issues with sql on partition tables .  It was  observed that After upgrade to 19c, partition pruning  not work for the SQL with predicates of user defined function, while partition pruning works for the same SQL prior to 19c. This is bug 27175987, also not published. Made as fix_control, can be seen in v$system_fix_control, can be disabled if desired. Fix 1)  Enable partition pruning disable the fix of Bug 27175987 by setting "_fix_control" = '27175987:off' alter session set "_fix_control" = '27175987:off'; or Add hint /*+ OPT_PARAM('_fix_control' '27175987:off') */: Fix 2 )  Change user defined function to deterministic PL/SQL functions if it is non-deterministic, and add the DETERMINISTIC clause to the function if the function is truly deterministic SQL> CREATE or REPLACE FUNCTION <USER_FUNCTION_NAME>(ARG VARCHAR2) RETURN VARCHAR2 determ...

  In Oracle Database 19c, a new function SQL_DIAGNOSE_AND_REPAIR is introduced to diagnose a given SQL statement for a given SQL for the given problem type. This function creates an incident, populate incident metadata with required information like, SQL ID, SQL text, compilation environment, and so on. It also creates a diagnostic task, executes it and accepts recommendation for a given SQL. For example: SQL> select max(col3) from tbl1 where col1=10000 and col2=10000; MAX(COL3) ------------------------------ A10000 SQL> column sql_id new_value sql_id SQL> select prev_sql_id as sql_id from v$session where sid = userenv('sid');   SQL> var incident_id number; SQL> exec :incident_id := dbms_sqldiag.sql_diagnose_and_repair(sql_id => '&sql_id',scope=>DBMS_SQLDIAG.SCOPE_COMPREHENSIVE,time_limit=>DBMS_SQLDIAG.TIME_LIMIT_DEFAULT,problem_type=>DBMS_SQLDIAG.PROBLEM_TYPE_PERFORMANCE,auto_apply_patch=>'NO'); PL/SQL procedure successfully co...

   The SQL Tuning Health-Check Script is a tool developed by the Oracle Server Technologies Center of Expertise. The tool, also known as SQLHC, is used to check the environment in which a single SQL Statement runs, checking Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance of the one SQL being analyzed. SQLHC does not require any special licensing, it is free and it is designed to run on all systems. If the Diagnostics and Tuning packs are installed, that data will be used by the script. If you are not licensed for Diagnostics and Tuning, answer “NO” to that question in the script to avoid licensing problems. What all information can SQLHC provide: Some of the key information that this tool can provide you is below: Explain plan of the SQL ID and any changes Validity of various statistics and parameters with a brief explanation Tables and index details Objects Statistics Details SQL_TEX...

 Listing down  handy troubleshooting  steps to check listener connection issues  1)  Check if Listener is Up and Running  ps –ef | grep lsnr 2) Check if  Listener is listening to Service  lsnrctl services LISTENER_NAME | grep -i  service_name  If database is having service configured check  if database service is up  srvctl config service -d  db_+name  srvctl status service -d  db_name  -s service  3) Check if port and host is reachable from client machine  telnet database_host_name 1524 4)  Using netstat on database server to see if post is open and listening   netstat –a  netstat -a | wc -l  netstat -a | wc -l  netstat -anp | pg netstat -anp | more netstat -a | more netstat -a | more netstat -a | grep ESTAB      See check established connection  netstat -a| grep LISTEN netstat -an | grep 6100 | grep LISTE netstat -an | grep 6100   conn...