Oracle Hacks

oracle.md

Not-so-known Oracle commands

Simulate ORA-600

Execute as SYSDBA.

execute dbms_system.ksdwrt(2,'ORA-600: test');

Output in alert.log:

Thu Feb 28 17:27:23 2013
ORA-600: test

Create multiple AWR SQL Reports

Copy & Paste the pattern below.

define dbid = 1234567890;
define inst_num = 1;
define num_days = 1;
define report_type = 'html';
define begin_snap = 1111;
define end_snap   = 1112;
define report_name = 'awrrpt_1.html'
define sql_id = 'abcdefgabcdefg'
@?/rdbms/admin/awrsqrpi.sql

Windows default NLS_LANG

Check with regedit. Default is AMERICAN_AMERICA.WE8MSWIN1252

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XX

What's my server process pid?

select p.spid from v$session s, v$process p, v$mystat m
 where m.sid = s.sid and s.paddr = p.addr and rownum <= 1;

Manually create SQL Monitor Report

Make sure your SQL is long running, or add /*+ MONITOR */ hint

set trim on
set trimspool on
set pages 0
set lines 1000
set long 1000000
set longchunksize 1000000
spool sqlreport.html
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
    sql_id => 'abcdeabcdeabc',
    type => 'ACTIVE'
  ) FROM dual;
spool off

Create rows from a list

select *
  from table(sql_objects(1,2,3,4,5));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5

Skip fetch phase

You might want to warm-up the dictionary cache, when you have sooooo many partitions. But you don't want to fetch the data. This is how you do it.

begin
  execute immediate 'select * from large_partition_table';
end;
/

Display explain plan

Show everything about the explain plan result.

explain plan for select * from dual;
select * from table(dbms_xplan.display(null, null, 'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - SEL$1 / DUAL@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "DUAL"."DUMMY"[VARCHAR2,1]

Show the HINTS to get this exact plan

explain plan for select * from dual;
select * from table(dbms_xplan.display(null, null, 'outline'));

:
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "DUAL"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Change SQL*Plus prompt

Change "SQL>" into something useful.

set sqlprompt 'SQL(&_USER@&_CONNECT_IDENTIFIER)> '

SQL(SCOTT@instance1)>

You will want to put the set statements in the file below.

$SQLPATH/login.sql

Modify AWR settings

Check the current settings.

col snap_interval for a20
col retention for a20
select snap_interval, retention from dba_hist_wr_control;

Below means "every 1 hour" and "retain for 8 days".

SNAP_INTERVAL        RETENTION
-------------------- --------------------
+00000 01:00:00.0    +00008 00:00:00.0

Change the snap interval (in minutes). 1000000 (almost two years) is large enough, if you want to manually take the snapshots during the performance testing.

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 1000000);

Type below to manually take the snapshot.

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();