Index Rebuild for Table

Select distinct 'ALTER INDEX '|| index_name ||' REBUILD' || ';' FROM dba_ind_columns Where Table_owner='AOLDTA' AND table_name='IFSDMAGLFPF';

SQL Plan Baseline

Display Current SQL Plan 11g

=====================

SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

Display using SQL Handle

==================

SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle=>'SQL_c0dccb1dc5650094'));

Query SQL Plan Baseline

==================

select sql_handle, plan_name, enabled, accepted, CREATED from dba_sql_plan_baselines

order by created;

select sql_handle, sql_text, plan_name, enabled, accepted, CREATED from dba_sql_plan_baselines order by created;

Drop SQL Plan Baseline

=================

SET SERVEROUTPUT ON

DECLARE

l_plans_dropped PLS_INTEGER;

BEGIN

l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (

sql_handle => 'SQL_6903a7554f462647',

plan_name => NULL);

DBMS_OUTPUT.put_line(l_plans_dropped);

END;

/

Enable / Disable SQL Plan Baseline

=========================

SET SERVEROUTPUT ON

DECLARE

l_plans_altered PLS_INTEGER;

BEGIN

l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(

sql_handle => 'SQL_01674a9b1336e780',

plan_name => 'SQL_PLAN_02tuamc9mdtw0f87642b6',

attribute_name => 'enabled',

attribute_value => 'NO');

DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);

END;

/

SQL Tuning

Tuning Tips

=========

1. Using NOT EXISTS instead of NOT IN adds a limiting condition to your queries that can reduce the number of full table scans necessary.

2. If Plan Table does not exist

$ORACLE_HOME/rdbms/admin/utlxplan.sql

X================================================================X

This script help to analyze the SQL ID and find the best optimal plan

set serveroutput on

set long 10000 longchunksize 5000

DECLARE

my_task_name VARCHAR2(30);

BEGIN

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_id => 'f1ph9kjpb88va',

scope => 'COMPREHENSIVE',

time_limit => 3000,

task_name => 'balaji_tune',

description => 'Task to tune a query');

END;

/

exec dbms_sqltune.execute_tuning_task('balaji_tune');

---

set long 20000

set longchunksize 20000

set linesize 400

select dbms_sqltune.report_tuning_task('balaji_tune') from dual;

Reference : http://apunhiran.blogspot.com/2008/09/dbmssqltune-usage-example.html

Enable / Disable CRS (Oracle Clusterware Services)

Disabling CRS Stack

crsctl disable crs

This disables CRS / Clusterware services from being started in subsequent reboots. It will not stop the currently running CRS / Clusterware - it only ensures that the services do not come up after a reboot.

Enabling CRS Stack

crsctl enable crs

Use this to enable CRS / Clusterware to be started in subsequent reboots.

Start / Stop CRS commands

To Start CRS
Go to $CRS_HOME/bin
# ./crsctl start crs

To Stop CRS
Go to $CRS_HOME/bin
# ./crsctl stop crs

CRS enable/disable

Go to $CRS_HOME/bin
crsctl enable crs

crsctl disable crs # ensures that the services do not come up after a reboot.