CODEWITHSUNDEEP
sqlperformanceoracle-databaseoracle11gprofiling
user3225011
user3225011

Reputation: 161

Why SQL_PROFILE not showing recommendations?

I am trying run sql tuning advisor from sqlplus. I am following below steps to create

To create tuning task using SQL_ID:

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_tuning_task',
                          description => 'Tuning task for statement 19v5guvsgcd1v.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

To execute tuning:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '19v5guvsgcd1v_tuning_task');

To check the report:

SET PAGESIZE 10000
SET LINESIZE 20000
SELECT DBMS_SQLTUNE.report_tuning_task('19v5guvsgcd1v_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24

When I run create tuning task block and execute tuning task they are successful completed, but when I try to see recommendations I see blank report. But When I run sql tuning advisor (sql tuning advisor Icon) from SQL Developer, it is coming up with 2 recommendations. But why I am not able to see that through when I run in Sqlplus.

Upvotes: 3

Views: 1734

Answers (1)

Jon Heller
Jon Heller

Reputation: 36902

Add SET LONG:

   SQL> set long 2000000000

Upvotes: 3

Related Questions