Database News You Can Use
A monthly newsletter for Oracle users
You’re invited to submit your Oracle-related questions to us at firstname.lastname@example.org. Please include your name and telephone number in your email.
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.
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
- 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.
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.