There are multiple methods for enabling tracing for sessions in oracle. 1. Enabling tracing for all session of a user.              For this we need to create a trigger. CREATE OR REPLACE TRIGGER USER_TRACING_SESSION AFTER LOGON ON DATABASE BEGIN IF USER = ‘SIEBEL’THEN execute immediate […]

 Follow below steps for migrating sql profiles from one database to another. 1. Create a staging table SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>’PBRPROFILE’,schema_name=>’DBA_RAJ’); PL/SQL procedure successfully completed. 1. Pack the profiles to the staging table  SQL> exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>’PBRPROFILE’,STAGING_SCHEMA_OWNER => ‘DBA_RAJ’,profile_name=>’SYS_SQLPROF_015255a4b1f40001′); PL/SQL procedure successfully completed. SQL>  exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>’PBRPROFILE’,STAGING_SCHEMA_OWNER =>’DBA_RAJ’,profile_name=>’SYS_SQLPROF_015253634eb20000′); PL/SQL procedure successfully completed. 3. Export the […]

When we run SQL tuning advisor against a SQL statement or sql_id, it provides tuning recommendations that can be done that query to improve performance. It might give suggestion to create few indexes or accepting a SQL profile. Diagnostic and Tuning license is required to use this feature In this below tutorial we will explain how to run […]

If you wish to save your statistics of schema or table, which you can use later during any query issue Or if you wish copy the statistics from production database to development , then this method will be helpful. Here i will take export of statistics of a table RAJ.TEST from PROD and import into […]

In major production database, sometimes gathering stats can degrade the performance of the queries. So what we can do it, first gather the stats of the table or schema,without publishing it. After testing, once we confirm that stats is not degrading the performance,Then we can go ahead and publish the stats. DEMO: First change the […]

By analyzing the awr report ,you found that there was some issue with a particular sql_id, and you want to get the explain plans associated with that sql_id. We can get the explains using dbms_xplan.display_awr Suppose the sql_id is : 00tr6c6tngp4x   SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(’00tr6c6tngp4x’)); PLAN_TABLE_OUTPUT ———————————————————————————————- SQL_ID 00tr6c6tngp4x ——————– insert into SIEBEL.S_DOCK_INITM_01 […]

            V$SQL_PLAN_MONITOR displays real time plan level monitoring statistics for the currently running sql queries.. Each row in V$SQL_PLAN_MONITOR corresponds to an operation of the execution plan being monitored.   — sql_is from v$sql_monitor SELECT sql_id FROM v$sql_monitor; SQL_ID ——- 00tr6c6tngp4x   Use below query to get the plan  details:   […]

Sql tuning advisor was introduced by oracle from 10g onwards.  DBMS_SQLTUNE is the package , which helps the DBA’s in generating recommendations for bad sql queries.  Here we will use a simple query for sql tuning advisor and see what suggestion it is providing . We will take below query: select count(*) from bigtab where weight in […]

Result cache was introduced in oracle 11g. Its job is to keep the result set of a query into a subset of the shared pool.It is usually helpful when we have a select query which is frequently used and data is being modified rarely. So when we use result_cache for a query for the first […]

There are few situations where we know the execution plan of a particular query can be improved with use of proper hint, But if we are not allowed to change the application query in any way(we can’t even add a hint also),then how to achive this? Example: For the below query, we want to force […]