Oracle DBA Place

Using Autotrace SQL> set autotrace traceonly explain SQL> select ename from emp where sal > 500; Execution Plan ---------------------------------------------------------- Plan hash value: 2872589290 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 25 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------- Using DBMS_XPLAN Package SQL> explain plan for select ename from emp where sal > 500; Explained. SQL> select * from TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------ Plan hash value: 2872589290 ------------------------------------------------------------------------- |...

Using Enterprise Manager or dbconsole is the convenient way for using SQL Advisor, the problem is that these options not always exists, and there are some scenarios that you are not authorized to activate dbconsole. So we are left with the PL/SQL option which appears to be not so complicated. In order to access the SQL tuning advisor API, a user must be granted the ADVISOR privilege: CONN sys/password AS SYSDBA GRANT ADVISOR TO my_user; CONN my_user/my_password Creating tuning task DECLARE l_sql VARCHAR2(3200); l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql := 'SELECT COUNT (*) FROM MY_TABLE'; l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_text => l_sql, scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 360, task_name => 'Yossi_Nixon_tuning_task1', description => 'Tun...