Finding bind variable values

In 10g, you can use data dictionary view v$sql_bind_capture or AWR view dba_hist_sqlbind (historical version of v$sql_bind_capture) to find bind variables’ values. However it has some significant limitations:

• Captured periodically (_cursor_bind_capture_interval=900 seconds by default), not at real time.
• Captured under maximum size(_cursor_bind_capture_area_size=400)
• Only bind variables in WHERE clause are captured (e.g bind variables passed to function are not captured !)

select name, position, datatype_string, value_string from v$sql_bind_capture where sql_id = '<sql_id>';

But there is a bug related to v$sql_bind_capture as reported in note 444551.1

V$SQL_BIND_CAPTURE Does Not Show The Value For Binds Of Type TIMESTAMP [ID 444551.1]

as a workaround you can get value of TIMESTAMP bind variable by

select name, position, datatype_string, was_captured, value_string,
anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id = '<sql_id>';

it’s fixed in 11.2.

Oracle Diagnostic Tools

Diagnostic Tools Catalog [ID 559339.1]
		Modified 27-APR-2011     Type REFERENCE     Status PUBLISHED

	Diagnostic Tools – Database And Fusion Middleware
	Getting Started With RDA 4	Getting Started with RDA for Rdb	Guardian Resource Center
	Health Check/Validation Engine User Guide	ORA-600/ORA-7445 Error Look-up Tool	Training – RDA 4
	Diagnostic Tools – Database Performance and Tuning Scripts
	Core / Stack Trace Extraction Tool (Stackx) User Guide	HANGFG User Guide	Implementing and Using the PL/SQL Profiler
	LTOM User Guide	ORA-600/ORA-7445 Troubleshooter	Oracle Performance Diagnostic Guide (OPDG)
	OSWatcher User Guide	Performance Tools Quick Reference Guide	SQLTXPLAIN.SQL – Enhanced Explain Plan and related diagnostic info for one SQL statement
	Trace Analyzer TRCANLZR – Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046

Monitoring Index Usage

guang@db11r2> create table test1 (id number);

Table created.

guang@db11r2> insert into test1 values (1);

1 row created.

guang@db11r2> insert into test1 values (2);

1 row created.

guang@db11r2> insert into test1 values (3);

1 row created.

guang@db11r2> commit;

Commit complete.
guang@db11r2> create index idx_test1_id on test1(id);

Index created.

guang@db11r2> select table_name, index_name, monitoring, used from v$object_usage;

no rows selected

guang@db11r2> alter index idx_test1_id monitoring usage;

Index altered.

guang@db11r2> select table_name, index_name, monitoring, used from v$object_usage;

TABLE_NAME                     INDEX_NAME                     MON USE
------------------------------ ------------------------------ --- ---
TEST1                          IDX_TEST1_ID                   YES NO

guang@db11r2> select count(*) from test1 where id=1;

COUNT(*)
———-
1

guang@db11r2> select table_name, index_name, monitoring, used from v$object_usage;

TABLE_NAME                     INDEX_NAME                     MON USE
------------------------------ ------------------------------ --- ---
TEST1                          IDX_TEST1_ID                   YES YES

guang@db11r2> alter index idx_test1_id nomonitoring usage;

Index altered.

guang@db11r2> select table_name, index_name, monitoring, used from v$object_usage;

TABLE_NAME                     INDEX_NAME                     MON USE
------------------------------ ------------------------------ --- ---
TEST1                          IDX_TEST1_ID                   NO  YES

Comparing floating number in shell script using AWK

a=98.4
b=98.39
awk 'BEGIN{if('$a'>'$b') print '$a'" is bigger"; else print '$b'" is bigger"}'

Oracle Performance Tuning on AIX

1) Depending on your version, you may need to set filesystemio_options=setall (in 10g and 9i)

2) Make sure mount file system of data file, control file and redo logs with option "-o cio"

3) Create the filesystems for redo logs with -o cio and agblksize=512

4) Plan on increasing your db_cache_size to compensate for the loss of file
system buffering at the OS level

5) set lock_sga=true

6) use AIX large memory pages (e.g. 16M with vmo -p -o lgpg_regions=2048 lgpg_size=16777216)

Creating Operating System Authenticated User

Creating Operating System Authenticated User
Changes to make for external authentication	1. Set the initSID.ora parameters:remote_os_authent=TRUE os_authent_prefix = “OPS$” 2. Generate a new spfile CREATE spfile FROM pfile=’initorabase.ora’; 3. Add the following to the sqlnet.ora  (ONLY ON WINDOWS) sqlnet.authentication_services = (NTS)
The syntax for CREATE USER where authentication is performed by the  operating system on the server	CREATE USER <user_name> IDENTIFIED EXTERNALLY;
	Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:CREATE USER ops$oracle IDENTIFIED EXTERNALLY; SELECT username, password, external_name FROM dba_users ORDER BY 1; GRANT create session TO ops$oracle; Step 2: Create a user in the operating system named oracle if one does not already exist.Step 3: Go to command line (terminal window in UNIX, cmd in Windows. Type ‘sqlplus /’ You should be connected to the database without having to enter username/password.

Fixing Execution Plan by SQL Profile

After reading Kerry Osborne’s presentation Controlling Execution Plans, I decide to sum up what I learned about SQL Profile and to make some experiments of my own.

To tune a sql in case that we CAN NOT change the code, some tuning methods are available, but keep in mind they’re not specific solution comparing with sql profile:

• gathering or updating objects’ statistics
• adding or removing accessing paths (e.g. indexes, mv, etc.)
• adjust init parameters (e.g. in session level)

SQL Profile will take advantage of other tuning methods in such conditions:

• to fix a bad execution plan of one particular SQL (it is not a general systematic problem affecting a lot of others)
• we can’t change the code ( we don’t own the code; we’re in emergency and have no time to change the code; we need to follow a company rule to make changes in production, etc.)
• the same sql executes well in test environment but has very bad performance in production
• the same sql executes well in the past but has very bad performance today

Description about SQL Profile by Tom Kyte:

So, a SQL profile is sort of like gathering statistics on A QUERY which involves many tables, columns and the like….

In fact – it is just like gathering statistics for a query, it stores additional information in the dictionary which the optimizer uses at optimization time to determine the correct plan. The SQL Profile is not “locking a plan in place”, but rather giving the optimizer yet more bits of information it can use to get the right plan.

There are several ways to create SQL Profiles

• Using SQL Tuning Adviser from EM or via DBMS_SQLTUNE package
• Attaching a SQL Profile to a different SQL (e.g. update existing execution plan and attach it to a different SQL using KO’s scripts, see demo)
• Moving a SQL Profile from one database to another database ( using script )

Create SQL Profile by SQL Tuning Adviser

1. find sql with