Oracle Advisor – DBMS_ADVISOR

Some ADDM dumps. …

Remove all advisory reports (ADM).
[REMI@DB01.REMIDIAN.COM]
SQL> select 'exec dbms_advisor.delete_task(''' || TASK_NAME || ''' )' from dba_advisor_tasks;

'EXECDBMS_ADVISOR.DELETE_TASK('''||TASK_NAME||''')'
----------------------------------------------------------------
exec dbms_advisor.delete_task('SYS_AUTO_SPCADV_1300592007' )
exec dbms_advisor.delete_task('SYS_AUTO_SPCADV_2700592007' )
exec dbms_advisor.delete_task('SYS_AUTO_SPCADV_2220592007' )
exec dbms_advisor.delete_task('ADDM:1488676071_1_33108' )
exec dbms_advisor.delete_task('ADDM:1488676071_1_33113' )

[REMI@DB01.REMIDIAN.COM]
SQL>
Read more →

new explain plan options (dbms_xplan)

I still need to upgrade to 10g … …

I still tend to use this old fashioned ‘set autot trace exp’
[REMI@DB01.REMIDIAN.COM]
SQL> set autotrace trace exp
[REMI@DB01.REMIDIAN.COM]
SQL> select * from dual;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=2)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1 Bytes
          =2)



While in 10g the explain plan is pretty awseome – dbms_xplan package
[REMI@DB01.REMIDIAN.COM]
SQL> explain plan for select * from dual;

Explained.

[REMI@DB01.REMIDIAN.COM]
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------

8 rows selected.

[REMI@DB01.REMIDIAN.COM]
SQL> 
Read more →

Invalidated synonyms

In Oracle 10G synonyms are marked invalid when you perform DDL on the referenced object. Luckily the synonym is marked valid again when you simply touch the invalidated synonym. The status of the synonym becomes valid again and the DBA_OBJECTS.LAST_DDL_TIME and DBA_OBJECTS.TIMESTAMP columns reflect the time the synonym was validated again. Listing below shows an example for a create or replace of a view with an associated synonym. …

[REMI@DB01.REMIDIAN.COM]
SQL> create view v_demo as select * from dual;

View created.

[REMI@DB01.REMIDIAN.COM]
SQL> create synonym s_demo for remi.v_demo;

Synonym created.

[REMI@DB01.REMIDIAN.COM]
SQL> select object_name, object_type, status
  2    from all_objects
  3   where object_name in ('V_DEMO', 'S_DEMO');
OBJECT_NAME                      OBJECT_TYPE    STATUS
-------------------------------- -------------- ---------------
S_DEMO                           SYNONYM        VALID
V_DEMO                           VIEW           VALID

[REMI@DB01.REMIDIAN.COM]
SQL> create or replace view v_demo as select * from dual;

View created.

[REMI@DB01.REMIDIAN.COM]
SQL> select object_name,
  2         object_type,
  3         status,
  4       
Read more →

overcome SP2-0734 when using purge command

Issue new Oracle 10G ‘purge‘ command results in ‘SP2-0734: unknown command beginning “purge user…” – rest of line ignored.’ …

Purge Command in pre 10G SQL*Plus release

Whenever you want to issue the ‘purge’ command in any pre 10G SQL*Plus client you will notice that SQL*Plus denies to send your command to the server and it will raise an ‘SP2-0734’ error. You can easiliy overcome this by making use of dynamic sql (or just upgrade your client of course). …

[REMI@DB01.REMIDIAN.COM]
SQL> create table demo(k int);

Table created.

[REMI@DB01.REMIDIAN.COM]
SQL> drop table demo;

Table dropped.

[REMI@DB01.REMIDIAN.COM]
SQL> select * from cat;
TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
BIN$HE/mKBNyRajgRAADuhSWrA==$0 TABLE

[REMI@DB01.REMIDIAN.COM]
SQL> 
[REMI@DB01.REMIDIAN.COM]
SQL> drop table "BIN$HE/mKBNyRajgRAADuhSWrA==$0";
drop table "BIN$HE/mKBNyRajgRAADuhSWrA==$0"
           *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin


[REMI@DB01.REMIDIAN.COM]
SQL> [strong]purge user_recyclebin;
SP2-0734: unknown command beginning "purge user..." - rest of line ignored.[/strong]
[REMI@DB01.REMIDIAN.COM]
SQL> prompt &_SQLPLUS_RELEASE
902000700

Solution is beautiful in it’s simplicity, use dynamic sql and off you go. …

Purge recycle bin with a pre 10G SQL*Plus client
[REMI@DB01.REMIDIAN.COM]
SQL> begin 
  2    execute immediate 'purge user_recyclebin';
  3  end;
  4  /

PL/SQL procedure successfully completed.

[REMI@DB01.REMIDIAN.COM]
SQL> select * from cat;

no rows selected
Read more →

Automatic Workload Repository

A very short howto for the ‘Automatic Workload Repository’, the successor of perfstat. …

Interval

Some queries; …

Listing below shows one snapshot is taken every 20 minutes, the snapshot is stored for a week (7 days). …

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select snap_interval, retention
  2  from   dba_hist_wr_control
  3  /

SNAP_INTERVAL                    RETENTION
-------------------------------- ------------------------------
+00000 00:20:00.0                +00007 00:00:00.0

Alter the interval to once every hour and store it for a month (31 days); …

[SYSTEM@DB01.REMIDIAN.COM]
SQL> begin
  2     dbms_workload_repository.modify_snapshot_settings
  3        ( interval   => 60
  4        , retention  => 31 * 24 * 60);
  5  end;
  6  /

PL/SQL procedure successfully completed.

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select snap_interval, retention
  2  from   dba_hist_wr_control
  3  /

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

History

Five latest snapshots …

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select *
  2  from   (
  3         select snap_id, startup_time, begin_interval_time, end_interval_time
  4         from   dba_hist_snapshot
Read more →