PERFORMANCE TUNING

Gather stats and Publish them later in oracle

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 […]

Get explain plans of a sql query from AWR using DBMS_XPLAN.DISPLAY_AWR

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  

  We can observe, for that particular sql_id, 3 explain plans were […]

Realtime monitoring of sql query using v$sql_plan_monitor

            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.  

  Use below query to get the plan  details:  

Output 

 

How to use sql tuning advisor for a sql query

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 […]

How to use result cache for queries in 11g

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 […]

change the execution plan without changing the sql query

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 […]

Page 3 of 3123
Skip to toolbar