Tracing ...

TRACING SESSIONS ...

Things you will want to know about tracing

* The timed_statistics init.ora parameter must be set to true in
order to get any meaningful timing data out of tkprof.

* I prefer to set the max_dump_file_size init.ora parameter to either
unlimited or a really large value (2G).
If you're going through the extra work of tracing a process you don't want to
potentially miss the most important part.

-- set identifier in output file
SQL> alter session set tracefile_identifier='mytest';

-- Turn on session level tracing
SQL> alter session set sql_trace=true;

-- Turn on TRACING IN YOUR CURRENT SESSION.
EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);

-- Turn on some trace level.
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

-- SOME DBA STUFF
-- trace another session
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);

-- trace another session with some trace level
EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');

XPLAN PLAN

Use the following to setup an explain plan table for a database.

I tested this using 10.2.0.2 so the plan table may differ, but theory should be the same from 9i onwards.


drop table plan_table;
create global temporary table plan_table(
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_alias varchar2(65),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(30),
other_xml clob
) on commit preserve rows;
drop public synonym plan_table;
create public synonym plan_table for plan_table;
grant all on plan_table to public;

create or replace view xplan_basic as
select * from table(dbms_xplan.display('plan_table',null,'BASIC'));
drop public synonym xplan_basic;
create public synonym xplan_basic for xplan_basic;
grant all on xplan_basic to public;

create or replace view xplan_typical as
select * from table(dbms_xplan.display('plan_table',null,'TYPICAL'));
drop public synonym xplan_typical;
create public synonym xplan_typical for xplan_typical;
grant all on xplan_typical to public;

create or replace view xplan_parallel as
select * from table(dbms_xplan.display('plan_table',null,'ALL'));
drop public synonym xplan_parallel;
create public synonym xplan_parallel for xplan_parallel;
grant all on xplan_parallel to public;

create or replace view xplan_serial as
select * from table(dbms_xplan.display('plan_table',null,'SERIAL'));
drop public synonym xplan_serial;
create public synonym xplan_serial for xplan_serial;
grant all on xplan_serial to public;

dynamic sql - dml

The following is some code that will generate some dml via pl/sql. May be useful, although probably not the best way of implementing from a performance issue.

declare
cursor c_users is
select table_name from user_tables where table_name in ('T1','T2','T3');

begin
for a in c_users loop
execute immediate (
'insert into t4 select username from ' ||
a.table_name ||
' where username not in (select username from dba_users)');
commit;

end loop;
end;
/

blocking sessions

select
d.session_id sid,
d.lock_type,
d.mode_requested,
d.mode_held,
d.blocking_others
from
dba_locks d,
v$session v
where
d.session_id = v.sid;

savepoints

Savepoints work something like this ...

dml a
savepoint a;
dml b
rollback to savepoint a;
commit;
dml a will be commited, but not dml b.

Unsure when a good time to use it, but it does work a treat.

truncate partitions

Use the following to truncate specific partitions

alter table ... truncate partition ...

add new partitions

Use the following to add new date partitions ...

alter table ... add partition ... values less than (to_date(...))
...

outer joins

An outer join is essentially a join that grabs all intersecting records from two tables and all the records from one of joining tables.

select ... from t1 a, t2 b
where a.col1 = b.col1(+);

This will grab all intersecting records as well as all records from t1.

execute immediate exceptions

Use the following to generate some exceptions for execute immediate

SQL> BEGIN
EXECUTE IMMEDIATE 'garbage';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK );
RAISE;
END;


where the dbms_output statement can be used instead of some logging procedure.

The output would be something like ...

BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 6

adding quotes in dynamic sql

Use the following to add a quote when writing dynamic sql. Useful when you can't remember ascii codes.

SELECT 'select ... '|| ' where username like ''A%'' ;'

The output looks something like the following ...
select ... where username like '%A' ;

hiding CODE

Use the following to hide pl/sql code in the database.

wrap iname="sql in file" oname="sql out file"
compile in the database.

Works an absolute trick !!!

database upgrade

Below are some steps when upgrading / patching a database:

1. Install SOFTWARE in new OH
2. BACKUP database
3. SHUTDOWN DB
4. Update ORATAB for database so that it is pointing to new OH.
5. RUN oraenv for DB and check that env parameters are pointing to the new OH.
6. COPY network and database parameter files are copied to new OH.
7. RUN PREUpgrade Steps
8. RUN UPGRADE STEPS
9. RUN POST UPGRADE STEPS (backup configuration, monitoring, etc)
10. BACKUP Database.