Text box

Please note that the problems that i face may not be the same that exist on your environment so please test before applying the same steps i followed to solve the problem .

Thursday 19 May 2011

Find bind variable value

The usages of bind variable increase SQL query performance. Suppose you have a SQL query that execute several times and only change are in literal values, IN such case professionals are suggested to use bind variable rather than hard cote value. The impact is significant on query performance if you use bind variable.

But the problem is rice when you try to collect full query text from database, the value of the bind variable is not present in the query text. But you may need those values when you are working on that SQL query specially when you try to tune that query.

Here are two query that help you to find the valu of bind variable. The only thing you needed is the SQL ID

1. If your query was run within 30 minutes:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM v$sql_bind_capture WHERE sql_id='';


2. If your query was run more than 30 minutes ago. For this you also need a snapshot Id. By default oracle store snapshots of last 7 days:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM DBA_HIST_SQLBIND WHERE SQL_ID='' and SNAP_ID='';


Note: v$sql_bind_capture and DBA_HIST_SQLBIND stors only those bind values
which are given in HWERE clause and they do not store timstamp,lob,clob type value

Find bind variable value

The usages of bind variable increase SQL query performance. Suppose you have a SQL query that execute several times and only change are in literal values, IN such case professionals are suggested to use bind variable rather than hard cote value. The impact is significant on query performance if you use bind variable.

But the problem is rice when you try to collect full query text from database, the value of the bind variable is not present in the query text. But you may need those values when you are working on that SQL query specially when you try to tune that query.

Here are two query that help you to find the valu of bind variable. The only thing you needed is the SQL ID

1. If your query was run within 30 minutes:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM v$sql_bind_capture WHERE sql_id='';


2. If your query was run more than 30 minutes ago. For this you also need a snapshot Id. By default oracle store snapshots of last 7 days:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM DBA_HIST_SQLBIND WHERE SQL_ID='' and SNAP_ID='';


Note: v$sql_bind_capture and DBA_HIST_SQLBIND stors only those bind values
which are given in HWERE clause and they do not store timstamp,lob,clob type value

Monday 16 May 2011

SYSTEM and SYSAUX Tablespaces


Each Oracle Database must contain a SYSTEM tablespace and a SYSAUX tablespace, which are
automatically created when the database is created. The system default is to create a smallfile
tablespace. You can also create bigfile tablespaces, which enable the Oracle database to manage ultra
large files (up to 8 exabytes in size).
A tablespace can be online (accessible) or offline (not accessible). The SYSTEM tablespace is always
online when the database is open. It stores tables that support the core functionality of the database,
such as the data dictionary tables.
The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. The SYSAUX
tablespace stores many database components, and it must be online for the correct functioning of all
database components.
Note: The SYSAUX tablespace may be taken offline for performing tablespace recovery, whereas
this is not possible in the case of the SYSTEM tablespace. Neither of them may be made read-only.