May 2006

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

May 2006
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Finding the Bind Values in a Query

You’re invited to submit your Oracle-related questions to us at askdbspecialists05@dbspecialists.com. Please include your name and telephone number in your email.

Statistics Gathering and its Impact on Performance

Earlier this year Terry Sutton, Database Specialists’ Senior Staff Consultant, was invited to give presentations at RMOUG Training Days and Hotsos Performance Symposium on how statistics gathering in Oracle using DBMS_STATS impacts performance. We’re pleased to announce that we have recently published Terry’s accompanying white paper.

Terry writes that “the DBMS_STATS package covers a lot of territory, and within its many procedures there are a multitude of options available. The choice of which options to use can dramatically affect your results, both in the accuracy of statistics and the performance of the statistics gathering operation itself. So, what’s a DBA to do?”

Find out more and read the full white paper at: http://dbspecialists.wpengine.com/presentations/dbms_stats.html.

For Open Source Fans

Sean Hull, author of Oracle and Open Source, published by O’Reilly books, has compiled a listing of Oracle-related open source tools covering the following topics:

  • Command Line Tools
  • Web-based Clients
  • GUI-based Clients
  • Libraries
  • Languages
  • Server Software

Explore the site and learn about the many open source Oracle tools and code available at http://www.oreillynet.com/oracle/os_dir. You’ll find everything from SQL*Plus replacement shells to an Oracle external procedure with the Perl interpreter hidden inside so that you can run Perl from PL/SQL.

Cost-Effective Oracle Database Management

Do you work for a small or mid-sized company without enough Oracle support? Database Specialists, Inc. provides DBA support services for small and mid-sized companies, many with either one database administrator on staff or none at all. We provide 24/7 support that’s not possible with just one person, and you can offload your production support duties to us so that you can focus on activities that require business-specific knowledge.

Our remote database administration service can be tailored to your specific needs, from just one comprehensive database review per month to full 24/7 coverage and daily reviews, reports, and recommendations. In addition to production support, we can work in areas such as upgrades and platform changes, performance tuning, database design review, and backup and recovery strategy development. All of our Oracle certified experts have 10+ years of Oracle experience.

We’d be happy to answer any questions you may have about our services. Call us toll-free at 888/648-0500. Or, read more about our remote DBA services at http://dbspecialists.wpengine.com/dbapro.html.

 

This month’s question comes to us from Usha in Northern California: I have an extended SQL trace file with bind values and waits. How do I replace the bind variables in the cursors to understand the complete statements? Also, is there any other way to see bind variable values other than using extended SQL trace?

Ian Jones of the Database Specialists team responds: Extended SQL trace is one of the oldest and most complete methods for capturing the values of bind variables used in SQL statements. You can enable extended SQL trace in your own session with the following statement:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';

You can enable extended SQL trace in another user’s session using one of the following procedure calls:

SYS.dbms_system.set_ev (sid, serial#, 10046, 4, '')

SYS.dbms_support.start_trace_in_session (sid, serial#, waits=>FALSE, binds=>TRUE)

SYS.dbms_monitor.session_trace_enable (sid, serial#, waits=>FALSE, binds=>TRUE)

The dbms_monitor package is new in Oracle 10g. Any of these will cause Oracle to write a trace file on the database server in the directory specified by the user_dump_dest instance parameter. The trace file will contain text like:

PARSING IN CURSOR #1 len=116 dep=0 uid=77 oct=3 lid=77 tim=4528731877418 hv=3407
047714 ad='b8cd9050'
SELECT e.*
FROM emp e,
     dept d
WHERE e.deptno = d.deptno AND
      d.deptno = :l_deptno AND
      e.sal > :l_sal
END OF STMT
PARSE #1:c=0,e=1644,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=4528731877382
BINDS #1:
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=c000000000000000
 size=48 offset=0 bfp=800003fb800697f0 bln=22 avl=02 flg=05  value=10
 bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=c000000000000000
 size=0 offset=24 bfp=800003fb80069808 bln=22 avl=02 flg=01 value=300
EXEC #1:c=0,e=810,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=4528731879874
FETCH #1:c=10000,e=14607,p=3,cr=6,cu=0,mis=0,r=1,dep=0,og=4,tim=4528731894755
FETCH #1:c=0,e=154,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=4,tim=4528731898231

In the above example, “BINDS #1” indicates values bound into the bind variables of the statement in cursor #1. (See the previous “PARSING IN CURSOR #1” entry for the text of the statement in cursor #1.) Below “BINDS #1”, the “bind 0” entry corresponds to the first bind variable in the statement i.e. :l_deptno. Similarly, “bind 1” corresponds to the second bind variable, :l_sal. The last item on the “bind 0” and “bind 1” entries shows the value of the bind variable at the time of binding. If a statement is executed multiple times in a traced session, there will be additional “BINDS” entries as new values are bound.

Beginning in Oracle 10g, it is also possible to monitor bind variable values in your own session or other sessions without performing an extended SQL trace. A new v$ view called v$sql_bind_capture shows bind variable data across all sessions:

SQL> DESCRIBE v$sql_bind_capture
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 ADDRESS                                            RAW(8)
 HASH_VALUE                                         NUMBER
 SQL_ID                                             VARCHAR2(13)
 CHILD_ADDRESS                                      RAW(8)
 CHILD_NUMBER                                       NUMBER
 NAME                                               VARCHAR2(30)
 POSITION                                           NUMBER
 DUP_POSITION                                       NUMBER
 DATATYPE                                           NUMBER
 DATATYPE_STRING                                    VARCHAR2(15)
 CHARACTER_SID                                      NUMBER
 PRECISION                                          NUMBER
 SCALE                                              NUMBER
 MAX_LENGTH                                         NUMBER
 WAS_CAPTURED                                       VARCHAR2(3)
 LAST_CAPTURED                                      DATE
 VALUE_STRING                                       VARCHAR2(4000)
 VALUE_ANYDATA                                      SYS.ANYDATA

There are a few important restrictions on v$sql_bind_capture: Only simple-type bind variables that appear in the WHERE and HAVING clauses are captured. (Thus it is not possible to obtain the values used in INSERT statements or SET clauses of UPDATE statements, or LOBs or LONGs.) Also, to reduce overhead, bind variable information is captured no more frequently than once every 15 minutes for a given cursor. Finally, this view is only populated when the statistics_level parameter is set to TYPICAL or ALL.

For example, we can retrieve bind variable information for all statements parsed by us with a query such as:

SELECT   b.name, b.value_string, sq.sql_text
FROM     v$sql_bind_capture b, v$sql sq, v$session s
WHERE    sq.sql_id = b.sql_id
AND      sq.address = b.address
AND      sq.child_address = b.child_address
AND      sq.parsing_user_id = s.user#
AND      s.username = USER
ORDER BY sq.sql_id, sq.address, sq.child_address, b.position;

This might produce results such as:

NAME        VALUE_STRING      SQL_TEXT
----------- ----------------- ----------------------------------------------------------
:L_DEPTNO   10                SELECT e.* FROM emp e, dept d WHERE e.deptno = d.deptno...
:L_SAL      300               SELECT e.* FROM emp e, dept d WHERE e.deptno = d.deptno...

Even though the data collected in v$sql_bind_capture is only sampled periodically, it can still help DBAs track down performance issues in code they are not familiar with. For example, since v$sql_bind_capture indicates the data type of the bind variables, it is possible to use the view to discover statements that have performance problems due to implicit type conversions. Recently we were asked by a development group why the following statement would not use an index on the image_no column:

SELECT * FROM images WHERE image_no = :B1;

By comparing the data type of the bind variable against the data type of the image_no column, it was straightforward to show a type conversion was taking place that was defeating the index. When the developer changed the bind variable data type from numeric to character, the query began using the index.

Extended SQL trace allows you to see all bind variable values for all executions of SQL statements captured in the trace file. Beginning in Oracle 10g, the v$sql_bind_capture view allows you to see sampled bind variable information for all sessions without having to use tracing. Both tools can be extremely useful to the Oracle DBA.

Leave a Reply

Your email address will not be published. Required fields are marked *