steps to run segment advisor for table using OEM?

1.GO TO OEM  home page -->administration--.storage-->segment advisor
2.provide the table name or schema name to search and submit the job
3.once the job completed ,view the result and recommendations

steps to run awr report for a specific sql_id?

we can generate awr report for a specific  sql_ID

use the below script.

1.@?/rdbms/admin/awrsqrpt.sql;

scrip to check the most expensive sql on the cursor cache?

SQL> @?/rdbms/admin/sqltrpt.sql;

15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID           ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------

steps to manually run the sql_tunning advisor for sql_id in oracle database?

1. Create SQL Tuning Advisor task

DECLARE
  my_task_name VARCHAR2(30);
begin
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'd6v2m5q1t3hpp',scope => 'COMPREHENSIVE',time_limit => 60,task_name => 'my_tune',description => '7a6b4442j5pcz');
end;
/

2. Run Task
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tune');

3. View results
SET LONG 10000
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_tune') from dual;


4.drop the sql_tunning task

exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'my_tune');

5.to list the tunning task job details
dba_advisor_tasks,DBA_ADVISOR_FINDINGS

steps to compare the database performance between two time stamps in oracle database?

1.generate the AWR manually and check the performance of the SQL's and TOP sql's
2.use OEM tool ,go to AWR  & compare awr for two time frames by snapshot  .

steps to check sql is hard/soft parse ,#execution,rows_processed in oracle database?

1.check from oem HISTORY FOR CURSOR AND HISOTRIC DATA
2.USE v$SQL,v$SQLAREA,DBA_HIST_SQLSTAT VIEWS TO GET THE INFORMATION

steps to check when the SQL executed on the oracle database?

1.go to OEM page ->performance->sql-->search sql from AWR if its history
2.select sql_id from dba_hist_sqlstat  where sql_text  like 'select * from emp%'; if its history
3. select sql_id from V$sql where sql_text like 'select ename%'; if its in cursor

monitoring tools for postgreSql database

	1.OPM 2.PGWATCH

steps to generate explain pla for sql in oracle DB?

EXPLAIN PLAN command

select plan_table_output   from table(dbms_xplan.display('plan_table',null,'basic'));

V$SQL_PLAN --last slq staement on cursor

select plan_table_output  from table(dbms_xplan.display_cursor(null,null,'basic'));

Automatic Workload Repository (AWR) 

1.note down the specific time
2.generate the awr for the specific  time
2.genarete the awr and pick the sql_id and genrate the explain plan
or
use dba_hist_sqlstat and get the sql_id and genrate the explain plan

SQL Tuning Set (STS)

SQL Plan Baseline (SPM)

--get the sql_handle from the query and generate the plan

 select SQL_HANDLE, PLAN_NAME, ACCEPTED   from dba_sql_plan_baselines
  where sql_text like 'select * from empty%';


select t.* from
table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed0999',format => 'basic')) ;

steps to flush a particular sql statements from the database

get the SQL_ID for the SQl statement

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '81d%';

ADDRESS HASH_VALUE
---------------- ----------
<address> <hash value>

SQL> exec DBMS_SHARED_POOL.PURGE ('address, hash value', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '81d%';
no rows selected.