Creating Tuning Task with SQL_ID

No Access to OEM, hence SQL Tuning Advisor In command line:

Grab sql_id from v$sql...
Then:

SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(100);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '49kur02wcrkkk', time_limit => 60);
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/

task_id: TASK_828086

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_828086');

while using DBMS_SQLTUNE.EXECUTE_TUNING_TASK ... can check status in DBA_ADVISOR_LOG view,

SELECT * FROM DBA_ADVISOR_LOG where task_name = 'TASK_828086'; --'TASK_828076';

SELECT task_name, status FROM DBA_ADVISOR_LOG;

when status=COMPLETED ... and then got recommendations!!!

SET LINESIZE 200
SELECT DBMS_SQLTUNE. REPORT_TUNING_TASK('TASK_828072') AS recommendations FROM dual;

Findings reports example:

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : TASK_828086

Tuning Task Owner  : XXX_SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

Started at         : 06/27/2013 15:06:35

Completed at       : 06/27/2013 15:08:41

-------------------------------------------------------------------------------

Error: ORA-13639: The current operation was interrupted because it timed out.

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

Schema Name: NOETIX_SYS

SQL ID     : 49kur02wcrkkk

SQL Text   : SELECT GLX0_ALL_BALANCES.PERIOD_NAME,

                      GLX0_ALL_BALANCES.SET_OF_BOOKS_ID,

Oracle Database 12c is here !

The long waited Oracle Database 12c has finally come. 

You can download the software from the Oracle Software Delivery Cloud for the three OS versions:

1.Oracle Database 12c Release 1 (12.1.0.1.0) Media Pack for Linux x86-64 :

https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=16496132

2. Oracle Database 12c Release 1 (12.1.0.1.0) Media Pack for Oracle Solaris on x86-64 (64-bit)

https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=16496136

3. Oracle Database 12c Release 1 (12.1.0.1.0) Media Pack for Oracle Solaris on SPARC (64-bit)

https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=16496134

Library Cache Pin Waits during PL/SQL compilation

Did you ever tried to compile a PL/SQL procedure in your production database and it took long time? 

Did you face slowdown or other production issues because of this? 

Did you hit an error similar to the following when you or a developer tried to compile PL/SQL procedure which is referenced by another constantly executing high load PL/SQL procedure?

ERROR at line 1:

ORA-04021: timeout occurred while waiting to lock object APPS.XXXX9999_PKG

I ran into this issue and used the following:

How to: Determine if a Package that is About to be Compiled is Being Used Currently [ID 1054939.6]

SQL> @/tmp/who_is_using.plb

Procedure created.

SQL> set serveroutput on

SQL> exec who_is_using('XXXX9999_PKG');

(1-866) - APPS

PL/SQL procedure successfully completed.