Oracle DBA Daily Experiences

Explain plan may not relay the correct execution plan optimizer may run, the following link shows an example
Explain Plan Lies

I use this simple script to find bind variables with or without sql_id.

select
sql_id,
t.sql_text SQL_TEXT,
b.name BIND_NAME,
b.value_string BIND_STRING
from
v$sql t
join v$sql_bind_capture b
using (sql_id)
where
b.value_string is not null
– and sql_id=’974ju5zc5whfn’;

If you need to generate SQL Profiles and then move to another instance below are the procedures. Just keep in mind that in the example below i generated the SQL Profiles in advance (5) then restored and imported to the same instance.

1- Already had the table so I dropped
SYS@ELSIPOC1 SQL> drop table alex.sql_profiles;

Table dropped.

2- Create the staging table
SYS@ELSIPOC1 SQL> SYS@ELSIPOC1 SQL> BEGIN
2 DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
3 table_name => ‘SQL_PROFILES’,
4 schema_name=>’ALEX’);
5 END;
6 /

PL/SQL procedure successfully completed.

3- Packed all the existing SQL profiles in the staging table
SYS@ELSIPOC1 SQL>
SYS@ELSIPOC1 SQL> BEGIN
2 DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
3 profile_category => ‘%’,
4 staging_table_name => ‘SQL_PROFILES’,
5 staging_schema_owner=>’ALEX’);
6 END;
7 /

PL/SQL procedure successfully completed.

4- Count the staging table
SYS@ELSIPOC1 SQL> select count(*) from alex.sql_profiles;

COUNT(*)
———-
5

SYS@ELSIPOC1 SQL>exit

5- Export the staging table
[spocoradb1.aeso.ca]/dbadmin/workspace2/RAT/capture/CDMS>expdp system/abc123 dumpfile=expdp_sql_profiles.dmp TABLES=ALEX.SQL_PROFILES DIRECTORY=CDMS_REPLAY

Export: Release 11.2.0.1.0 – Production on Tue Jun 8 11:14:04 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: system/******** dumpfile=expdp_sql_profiles.dmp TABLES=ALEX.SQL_PROFILES DIRECTORY=CDMS_REPLAY
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “ALEX”.”SQL_PROFILES” 98.50 KB 5 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/dbadmin/workspace2/RAT/capture/CDMS/expdp_sql_profiles.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at 11:14:53
###

6- Restore the database to a backup taken before All 5 SQL profiles were generated###

7- Truncate all the records from staging table
SYS@ELSIPOC1 SQL> truncate table alex.sql_profiles;

Table truncated.

SYS@ELSIPOC1 SQL> exit

8- import the staging table
[spocoradb1.aeso.ca]/dbadmin/workspace2/RAT/capture/CDMS>impdp system/abc123 dumpfile=expdp_sql_profiles.dmp TABLES=ALEX.SQL_PROFILES DIRECTORY=CDMS_REPLAY TABLE_EXISTS_ACTION=REPLACE

Import: Release 11.2.0.1.0 – Production on Tue Jun 8 11:22:49 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_TABLE_01″: system/******** dumpfile=expdp_sql_profiles.dmp TABLES=ALEX.SQL_PROFILES DIRECTORY=CDMS_REPLAY TABLE_EXISTS_ACTION=REPLACE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “ALEX”.”SQL_PROFILES” 98.50 KB 5 rows
Job “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully completed at 11:22:54

[spocoradb1.aeso.ca]/dbadmin/workspace2/RAT/capture/CDMS>

9- Drop the 2 existing SQL profiles that were in the instance when backup was taken.
SYS@ELSIPOC1 SQL> BEGIN
dbms_sqltune.drop_sql_profile(‘SYS_SQLPROF_0228d51a0db60000′, TRUE);
END;
/ 2 3 4

PL/SQL procedure successfully completed.

SYS@ELSIPOC1 SQL> BEGIN
dbms_sqltune.drop_sql_profile(‘SYS_SQLPROF_0128d2628b7e0000′, TRUE);
END;
/ 2 3 4

PL/SQL procedure successfully completed.

10- Unpack the SQL profiles from the staging table
SYS@ELSIPOC1 SQL> BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
staging_table_name => ‘SQL_PROFILES’,
staging_schema_owner=>’ALEX’,
replace=>FALSE);
END;
/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SYS@ELSIPOC1 SQL>

And you should be done…….

Find SQL currently executing in a session:
http://oraclespin.wordpress.com/2010/05/09/how-to-find-sql-running-for-a-usersid/

Session running waiting on
SYS@TESTDB> SELECT event, state, p1, p2, p3, seconds_in_wait FROM v$session_wait where sid = 247;

EVENT STATE
P1 P2 P3 SECONDS_IN_WAIT
—————————————————————- —————
—- ———- ———- ———- ——————-
db file scattered read WAITED KNOWN TIME
122 1188369 128 30

1 row selected.

SYS@TESTDB> SELECT * FROM v$event_name WHERE name = ‘db file scattered read’;

EVENT# EVENT_ID NAME
PARAMETER1 PARAMETE
R2 PARAMETER3
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
———- ———- ———————————————————-
—— —————————————————————- ——–
——————————————————– ———————–
—————————————– ————- ———– ————
—————————————————-
117 506183215 db file scattered read
file# block#
blocks
1740759767 8 User I/O

So Parameter #1: file#=122, Parameter #2: block#=1188369, Parameter #3: blocks=128

IO information consistents gets – reads from db buffers, physical reads – Physical reads, block changes – block changes
SQL> select * from V$sess_io where sid = 247;

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
———- ———- ————— ————– ————- ——————
247 6 1355 25 4 0

Detail user session information:

SQL> select vsn.name, vst.value
from v$sesstat vst, v$statname vsn
where vsn.statistic# = vst.statistic#
and vst.value 0
and vst.sid = 247
order by vst.value;

NAME VALUE
—————————————————————- ————-
logons cumulative 1
sorts (memory) 1
logons current 1
enqueue releases 1
enqueue requests 1
…

Find current locks by session:
– ID1 – is the object id the locks is on
– CTIME – Time elapsed since the object was locked in seconds
SQL> SELECT * FROM V$lock where SID = 247;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
—————- —————- ———- — ———- ———- ———- ———- ———- ———-
000000006934A5B0 000000006934A5D8 247 TM 13828 0 3 0 72989 0
0000000069376700 0000000069376888 24 TX 65539 2262 6 0 72989 0

List session time for various operations
Using V$SESS_TIME_MODEL it displays the session time for various operations. The time reported is the total elapsed or CPU time (in microseconds).
Example:
SQL> select stat_name, value
from V$SESS_TIME_MODEL
where sid = 247
order by value desc;