DanBrother的部落格

Symptom: SYS_OP_C2C Causing FULL Table Scan Instead of Index Range Scan

SYS_OP_C2C is the implicit function that is used to convert the column between NCHAR and CHAR;

PL/SQL Interpreted & Native Compilation Comparison Testing
conn testuser
Enter password:
Connected.

Oracle 11g: PL/SQL native compilation know-how

Start with Oracle 11gR1, PL/SQL native compiler can generate native code (machine code) directly, instead of translating it to C programs and have the C compiler generate native code as of Oracle 9i and 10g do.
Thus, the performance can be enhanced significantly.
The natively compiled program units are stored within the database in the SYSTEM tablespace.

Query sessions that are being locked:
select s.username as username,
s.program as program,
s.osuser as osuser,
s.sid as sid,
s.serial# as serial#,
s.logon_time as logon_time
from v$locked_object o, v$session s
where o.session_id = s.sid
order by s.logon_time;
Query SQL Text for locked session:
select t.sql_text as sql_text
from v$session s, v$sqltext_with_newlines t
where decode(a.sql_hash_value,0,prev_hash_value,sql_hash_value) = b.hash_value
and a.sid = '{get_from_above}'
order by piece;
Kill locked session by using:
alter system kill session 'sid ,serial# ';

For AWR (Automatic Workload Repository) Report (generated by running $ORACLE_HOME/rdbms/admin/awrrpt.sql), it's convenient to have SQL IDs listed for tuning any particular sql statement.
DISPLAY_AWR Function of DBMS_XPLAN Package
Its syntax is as follows:
DBMS_XPLAN.DISPLAY_AWR(
   sql_id                             IN VARCHAR2,
   plan_hash_value    IN  NUMBER       DEFAULT NULL,
   db_id                              IN  NUMBER      DEFAULT NULL,
   format                           IN  VARCHAR2 DEFAULT TYPICAL);
To display the execution plans associated with the SQL ID '3pw0jftkr7u42';
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('3pw0jftkr7u42'));