PERFORMANCE TUNING

Useful TFACTL commands

You can also refer – How to install TFACTL    1. Check tfactl status with version: tfactl status 2. Check tfactl tool status: tfactl toolstatus 3. Get config details: tfactl print config 4. List of user having access to tfactl: tfactl access lsusers 6. changing property of a user: tfactl access promote -user oracle 5.Adding […]

How to install trace file analyzer( TFACTL)

Recently oracle has bundled both  Trace file analyzer(TFA /TFACTL) and orachk into Autonomous health framework popularly known as AHF. So just installing ahf setup will take care of both tfa and orachk including few other monitor tools. STEPS TO INSTALL/UPGRADE AHF Download the AHF tool from oracle support. – DOWNLOAD AHF ENVIRONMENT – SOLARIS 2 NODE […]

How to generate AWR report in RAC

AWR report can be generating in RAC database using 2 scripts awrrpt.sql or awrrpti.sql awrrpt.sql – > This will generate the one report for the database across all the nodes(i.e for all instances) for a partiular snapshot range. awrrpti.sql – > This will genereate report for a particular instance, i.e for a 2 node RAC […]

Useful ADRCI commands in oracle

ADRCI is the command line interface for diagnostic utility used for viewing diagnostics data like listener log , alert log ,incident and cor dump etc and creating incident packages. Below are the the list of useful commands. 1. Get current  base location:( Also known as ADR_BASE) adrci> show base ADR base is “/u01/app/oracle/” 2. Set new […]

How to drop SQL baselines in oracle

Below is the steps for dropping a sql baseline from the database.   1.Get the sql_handle and sql_baseline name of the sql_id: SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE sql_id=’&SQL_ID’) SQL_HANDLE PLAN_NAME ——————————————— —————————————————- SQL_164b2be280f1ffba SQL_PLAN_1cktbwa0g3zxu06dab5d5 2. Drop the baseline: SQL> select sql_handle,plan_name from dba_sql_plan_baselines where plan_name=’SQL_PLAN_1cktbwa0g3zxu06dab5d5′; SQL_HANDLE […]

How to generate ADDM report

Automatic Database Diagnostic Monitor(ADDM) can analyze performance issues during a particular period and provide suggestion. An ADDM analysis is performed on a set of awr snapshots. addmrpt.sql script is used to generate addm report. Generate ADDM report: cd $ORACLE_HOME/rdbms/admin SQL> @addmrpt.sql It will ask for begin snap_id and end snap_id SQL> @addmrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DB […]

SGA TARGET ADVISORY IN ORACLE

The V$SGA_TARGET_ADVICE view provides information that helps us in deciding optimal value for  SGA_TARGET. MMON background process gather statistics about sga_target usage and update the V$SGA_TARGET_ADVICE view. Check sga target advisory is enable? — DB_CACHE_ADVICE should be ON SQL> show parameter db_cache_advice NAME TYPE VALUE ———————————— ——————————– —————— db_cache_advice string ON — STATISTICS_LEVEL should be […]

Shared Pool Advisory in oracle

Shared pool stores and caches the SQL and PL/SQL queries hitting the database , which avoids hard parsing the repetitive SQLs and It leads to increasing performance and memory usage. shared_pool_size is the parameter which controls the size of shared_pool.But Most of the time DBAs wonder how much value should be the shared_pool_size. In that […]

How to get the execution plan for a SQL between two AWR snapshots

We can get the all the execution plan details of an sql_id for a particular awr snapshot. EXAMPLE: sql_id – > 5vqy6cj4jr89k Run the below script: start $ORACLE_HOME/rdbms/admin/awrsqrpt.sql We need to pass below inputs while running the script. REPORT_TYPE – NUM_DAYS – BEGIN_SNAP – END_SNAP – SQL_ID – SQL> start $ORACLE_HOME/rdbms/admin/awrsqrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id […]

What is colored sql_id in dba_hist_colored_sql

By default, only top sql_ids are captured in database snapshots. If an SQL ID is colored, it will be captured in every snapshot, independent of its level of activities  (so that it does not have to be a TOP SQL). Mark an sql_id as colored execute DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(sql_id=>’b62q7nc33gzwx’); PL/SQL procedure successfully completed. colored sql_ids will be […]