 |
|
Changes to 10g SQL v$ Views
Oracle Tips by Burleson Consulting |
Changes to 10g SQL Related v$ Views
There are several significant changes made to the SQL related
v$
views. The following text addresses some of the most interesting and
important changes that were introduced in the Oracle10g database.
Oracle10g includes changes to the v$sqlarea
view that contains statistics for SQL statements which are already
in memory, parsed and ready for execution and reuse. The following
columns have been added to this view:
application_wait_time,
concurrency_wait_time,
cluster_wait_time,
user_io_wait_time,
plsql_exec_time, and
java_exec_time.
These columns
report different wait times that occurred during SQL execution. The
following SQL query can be issued to get SQL statements that have
high wait time for I/O:
SELECT sql_text, user_io_wait_time
FROM (select * from v$sqlarea ORDER BY user_io_wait_time DESC)
WHERE rownum <= 10;
The following is a sample output:
SQL_TEXT USER_IO_WAIT_TIME
-------------------------------------------------- -----------
select obj#,type#,ctime,mtime,stime,status,dataobj 1670
#,flags,oid$, spare1, spare2 from obj$ where owner
#=:1 and name=:2 and namespace=:3 and remoteowner
is null and linkname is null and subname is null
select object_name, owner object_owner, status, ob 828
ject_type, created, last_ddl_time from sys.dba_obj
ects where object_type = :object_type and (owner =
user)
DECLARE JOBHNDL NUMBER; BEGIN :JOBHNDL := 765
SYS.DBMS_DATAPUMP.OPEN(
operation =>:OPERATION, j
ob_mode => :JOB_MODE, rem
ote_link => :REMOTE_LINK,
job_name => :JOB_NAME, v
ersion => :VERSION); END;
BEGIN
SYS.DBMS_DATAPUMP.GET_STATUS( 517
handle => :JOBHNDL,
mask => :MASK,
timeout => :TIMEOUT,
job_state => :JOB_STATE,
status => :STATUS); END;
select grantee#,privilege#,nvl(col#,0),max(mod(nvl 326
(option$,0),2))from objauth$ where obj#=:1 group b
y grantee#,privilege#,nvl(col#,0) order by grantee
#
select owner#,name,namespace,remoteowner,linkname, 154
p_timestamp,p_obj#, nvl(property,0),subname,d_attr
s from dependency$ d, obj$ o where d_obj#=:1 and p
_obj#=obj#(+) order by order#
select /*+ rule */ bucket, endpoint, col#, epvalue 142
from histgrm$ where obj#=:1 and intcol#=:2 and ro
w#=:3 order by bucket
A new interesting view, v$sql_bind_capture, has been introduced to report information on
bind variables used by SQL cursors. This view allows the retrieval
of the actual values of bind variables for a given SQL cursor. The
script below can be used to retrieve list of bind variables and the
corresponding actual values used for a particular SQL statement.
This query uses the sql_id address that
should be specified for each unique SQL statement:
SELECT a.sql_text, b.name, b.position, b.datatype_string,
b.value_string FROM
v$sql_bind_capture b,
v$sqlarea a
WHERE
b.sql_id = 'dpf3w96us2797'
AND b.sql_id = a.sql_id
The following is a sample output:
SQL_TEXT NAME
POSITION DATATYPE_STRING VALUE_STRING
-------------------------------------------------- ----------
------- ------------
select owner, object_type, count (*) from all_obje :PAR
1 VARCHAR2(4000) SYS%
cts where owner not like :par and object_type = :o
bjtype group by owner,object_type order by 1,2,3
select owner, object_type, count (*) from all_obje :OBJTYPE
2 VARCHAR2(4000) TABLE
cts where owner not like :par and object_type = :o
bjtype group by owner,object_type order by 1,2,3
The changes to the SQL related v$ views enhance the
DBA’s ability to evaluate the impact of SQL statements on the
database. Overall, Oracle10g enhancements to the
v$
views provide new and exciting tools for the DBA to use in the
evaluation of database performance.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
|
and include the URL for the page.
