Category: Blogroll

Note:
Performance Tuning Guide
Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.

This said, let’s take a small example. If you have a query which is running fast most of the time and sometimes is running very slow due an unexpected plan change, you may want to considering enforcing plan stability with a Stored Outline.

To fake this example, let’s try to enforce a full table scan for select empno from emp where ename=’SCOTT’.

SQL> set autot on exp
SQL> select empno from emp where ename='SCOTT';
EMPNO
----------
7788

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 By


For the purpose of this exercice, I consider this to be the right plan and I want to enforce Oracle to use this plan for this query.


SQL> create or replace outline o for category emp_scott on
select empno from emp where ename='SCOTT';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2
2 1 INDEX (UNIQUE SCAN) OF 'I' (UNIQUE) (Cost=1


Oracle uses an index scan, but the index is located on a tape (which is not possible on 11gR1 and older 😉 ) and it takes ages to complete the query. Let’s try to use the good plan that was used at the time we created the stored outline


SQL> alter session set use_stored_outlines=emp_scott;

SQL> SELECT LAST_LOAD_TIME,OUTLINE_CATEGORY,sql_text
from v$sql
where sql_text like 'select%SCOTT%';
LAST_LOAD_TIME OUTLINE_C SQL_TEXT
------------------- --------- ----------------------------
2008-10-16/09:42:30 select empno from emp where
2008-10-16/09:46:50 EMP_SCOTT select empno from emp where


The plan using the outline is now used


$ sqlplus scott/tiger@DEVL
SQL> select * from all_db_links;
no rows selected
SQL> select * from dual@PROD;
D
-
X


Hey, why does this work???

Ok, after some research I found out that this seems to be an implicit loopback database link. The fact that the DEVL database has the global name set to PROD is just to annoy more 😉

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
PROD.LAURENTSCHNEIDER.COM


Today I missed Scott in my emp table. When selecting from EMP, Scott is not there. Gone…

Ok, let’s recreate the scott schema.

C:> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SYS@lsc02> spool scott.txt
SYS@lsc02> drop user scott cascade;
User dropped.
SYS@lsc02> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 10g Enterprise Edition

12 rows selected.


Hey Scott, I miss you!

Let’s see if I can dig out something in the log file I created with spool scott.txt

(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87',
'dd-mm-rr')-85,3000,NULL,20)
ERROR at line 2:
ORA-01858: a non-numeric character was found
where a numeric was expected


Hey! the UTLSAMPL script is not supposed to work where JUL is not a month ?
reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE /s /f NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
NLS_LANG REG_SZ FRENCH_SWITZERLAND.WE8MSWIN1252

Since FRENCH has no month called JUL the UTLSAMPL failed to insert SCOTT. Let’s fix the issue :
C:>reg add HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
/v NLS_LANG /t reg_sz /d AMERICAN_AMERICA.WE8MSWIN1252
Value NLS_LANG exists, overwrite(Yes/No)? yes
The operation completed successfully.
C:>reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1 /v NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
NLS_LANG REG_SZ AMERICAN_AMERICA.WE8MSWIN1252

and retry

C:>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SYS@lsc02> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 10g Enterprise Edition

14 rows selected.


Welcome back Scott!

warning: the script below is destructive and not 100% safe

update: it is unsafe to drop SYS_ objects, check for instance note 579399.1

A question was posted on the french forums of developez.net about how to drop all objects of an user. The drop user toto cascade; followed by create user toto identified by tott; was suggested as an easy answer, but I commented that create user must re-grant quotas, roles, system privileges, table privileges. Create user must also set the correct security status of the account (password, case-sensitive password, lock status, expiration status, profile), and the password history and failed login history is lost. Also it must set the default and temporary tablespaces.

It is also important to note that “drop user” requires dba privileges, when dropping the own object does not require privileges

To drop all objects I tried with plsql and a dictionary loop.

select object_type, count(*)
from user_objects
group by object_type;

Purge successfully completed.

begin
for f in (
select object_type, object_name from user_objects
where object_type in (
‘MATERIALIZED VIEW’)) loop
execute immediate
‘drop materialized view “‘||f.object_name||'” preserve table’;
end loop;
for f in (
select table_name from user_tables) loop
execute immediate
‘drop table “‘||f.table_name||'” cascade constraints’;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘DIMENSION’,’CLUSTER’,’SEQUENCE’,
‘VIEW’,’FUNCTION’,’PROCEDURE’,
‘PACKAGE’,’SYNONYM’,’DATABASE LINK’,
‘INDEXTYPE’)
and object_name like ‘SYS_%$’) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||'”‘;
end loop
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA SOURCE’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||'”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA RESOURCE’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||'”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA CLASS’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||'”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘TYPE’,’OPERATOR’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||
f.object_name||'” force’;
end loop;
end;
/

PL/SQL procedure successfully completed.
select * from user_objects

no rows selected.


All objects of the current schema disappeared, do not try this as sys, this script is destructive with no confirmation