Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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

 


 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.