Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Dynamic Debugging and Instrumentation of Production PL/SQL

Explains how to easily instrument PL/SQL source code to great benefit.
by

Bill Coulam

on 26 April 2012

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Dynamic Debugging and Instrumentation of Production PL/SQL

“Write your code as if the person who maintains it is a
homicidal maniac

who knows where you live.” – origin unverifiable
Dynamic Debugging &
Instrumentation of Production PL/SQL
Bill Who?
PL/SQL enthusiast. 16 yrs
Andersen Consulting – SF, Denver
New Global Telecom – Golden
Structure Consulting Group – Houston
Church of Jesus Christ of Latter Day Saints
RMOUG, IOUG and UTOUG. 10 yrs
How did the users/mgmt appreciate your handling of the issue?
Production
Problem
Lifecycle

What is
Instrumentation?

Oracle-provided
Instrumentation

Requirements
of
Good Instrumentation

Existing
Instrumentation
Libraries

Demos
Discussion
Become aware of a problem
Find the source of the problem
Fix the source of the problem
Repair issues the problem caused
Rebuild trust (costliest, longest step)
Do things better so problem doesn’t happen again
There's always another bug.
Test with dirty data and plenty of it.
Get another pair of eyes on it.
Document the code well.
Simplify.
Involve users early and often.
Reduce and Reuse. Don't Repeat.
Options without instrumentation:
Hunt
Poke
Prod
Peek
Query
Hope
Trace
Explain
OEM
TOAD
AskTom
Google
Pray
Options with instrumentation:
Review what happened
Replicate and monitor in real-time
Proactively analyze and notify
Instrumentation is a big word...
But more familiar than it seems.
Instrumenting/Instrumentation:
The process of fitting production applications with code that directs
runtime context
to some
destination
where it can be
useful
.
Runtime Context:
Who, when, what was passed in, what changed, time taken, errors and warnings encountered, etc.
Debugging
(disabled by default)
Logging
(enabled by default)
Column-level
Auditing
Errors
Warnings
Informational
Metrics
Destination:
Direct runtime context to...
stdout (screen)
logging table
V$ views
log file on the database host
queue for asynchronous re-direction
DBMS pipe or alert
Other slower, more complex alternatives like HTTP, FTP and UDP callouts.
A mouthful , that's not fun to type repeatedly...
Column-level Auditing
Nothing until 11g Flashback Data Archive (Total Recall)
Basic AUDIT and FGA seem promising.
But they are not.
But it i$ an extra co$t option...
...and can be too slow to retrieve changes.
Metrics
DBMS_UTILITY.get_time()
returns elapsed time from an arbitrary point in the past known internally to Oracle
result is in hundredths of seconds
divide result by 100 for seconds
get time, do work, get time again, subtract the difference to get response time
DMBS_PROFILER
requires existence of three PLSQL_PROFILER* tables
quickly identify bottlenecks
better for development and tuning
less awesome for current Production problems
Most build custom triggers to capture change, and tables to hold the history.
Logging & Debugging
DBMS_OUTPUT
(Dev only)
"poor man's" debugger
lazy (and awful) debugger
output goes to buffer
buffer must be retrieved by client tool
transaction DEpendent
DBMS_DEBUG & DBMS_DEBUG_JDWP
(Yes)
since Oracle 7 (DBMS_PROBE then)
plumbing for traditional IDE interactive debugging
need DEBUG CONNECT SESSION
JDWP flavor supports "remote" debugging
unfortunately, client has to be coded for this
objects must be compiled with debug info
don't leave this in Production
love it, but not for Production
IDE implementations not stable enough
ORADEBUG
(Rarely, if ever)
"undocumented" but well-known
real-time peeking into active sessions!
Oracle Support: don't use unless instructed
requires SYSDBA
DBMS_ERRLOG
(No)
used to create a special DML error logging table
DBMS_ALERT
(No)
transaction DEpendent
waiting client can’t see the desired event message until after the alerting session commits
resource intensive (client blocks, server needs lock and pipe) PER SESSION!
takes too long to re-register and return to waiting (miss messages)
messages limited to 1800 chars
session metadata, like client_id, can't be shared from alerting session to waiting session
DBMS_PIPE
(Possibly)
transaction INdependent!
basic security
packing messages cumbersome
does not guarantee delivery like AQ
message can never be read more than once
DBMS_SYSTEM.ksdwrt()
(Nope)
transaction INdependent
write to trace or alert log
need access to DBMS_SYSTEM
each new line interpreted as entirely separate message
can't control message formatting
special characters escaped for HTML
UTL_FILE
(Yes)
UTL_HTTP
(Rarely)
UTL_TCP
(Rarely)
DBMS_APPLICATION_INFO

(Yes)
set_module() //48 bytes
can take module and action as inputs
set_action() //32 bytes
set_client_info() //64 bytes
set_session_longops()
V$SESSION_LONGOPS
track own scripts, DDL operations or DML statements
tags can be too durable: stale, wrong
nested routines with instrumentation overwrite tags of calling routine
Session & DB Metadata

(Yes)
V$SESSION attributes
DBMS_UTILITY.current_instance
DBMS_DB_VERSION.version
USERENV namespace
SYS_CONTEXT('userenv','attributename')
Show
Debugger
Simple API to clock and record metrics
Should handle nested timers

Simple API to tag sessions and long operations
Should handle nested tagging

Simple API to write files

Simple API for static & dynamic log messages
transaction independent

Standard method of handling exceptions

Auto-gather caller, client & session metadata

Column-level auditing structures and triggers
Dynamic Logging
Off by default, and low overhead, so insightful debug lines can remain in Prod code
Can be turned on and off without Prod interruption
Toggles kept in a table or application context
Turn on for a PL/SQL unit or list of units, session, end user or named process, IP address, domain
Simple
dbg(‘Calling X with ‘||i_parm);
info(‘BEGIN: Nightly Reconcile’);
warn(‘X took ‘||l_diff||’ s too long’);
err();
tag();
startT(); <stuff> stopT(); elapsed();
Choice of Output
Minimally: to table and screen
Optionally: to file
Nice to have: ftp, pipe, AQ, http, etc.
Output must be transaction-independent
DBMS_SESSION

(Yes)
set_identifier()
puts end user ID into DB (AUDIT, V$SESSION, alert, trace, DBMS_MONITOR, etc.
Simple
Framework
•who called the routine, their IP address, client machine name, and client OS user name

•when they called the routine, how long the subroutines took, and how long the entire call took

•what the call accomplished, the number of iterations, the current iteration, the value(s) passed back

•parameter values passed into the routine, as well as changing and derived variables determined within

•old and new column values if data is changing

•path the call took through the control statements

•anomalies, warnings and errors detected, etc.
"Starter" Framework Too Much?
Thousands of downloads, but not much feedback or developer contributions.
Too many moving parts.
Too complex for shops with one app per schema or one app per DB.
60 page doc and days to week learning curve
Security often done in directory server now
Common messages almost never used
Email-from-DB tables rarely used
Locking always needs customization
"Simple" Starter Framework
7 services and 17 objects
Single app schema
Download and Install
Go to http://sourceforge.net
Search for PL/SQL framework.
Select first project (sourdough loaf icon)
Select Browse All Files.
Drill to plsqlfmwksimple/2.1.
Download and unzip Simple.zip
Start SQL*Plus as SYS
If installing to existing scheme, remove DROP and CREATE USER statements.
Run __InstallSimpleFmwk.sql
Done.
Major Services
LOGS
err(), warn(), info(), dbg()
TIMER
startme(), stopme(), elapsed()
ENV
init_client_ctx(), reset_client_ctx()
tag(), untag()
tag_longop()
gen_audit_triggers.sql
Auditing
Run gen_audit_triggers.sql.
Generates trigger for every table in your schema.
Remove triggers not needed.
Remove auditing on columns not needed.
Done.
Audited changes are recorded to APP_CHG_LOG and APP_CHG_LOG_DTL
May need view or materialized view to simplify access to audit data.
Metrics
TIMER package
startme(timer name)
stopme(timer name)
elapsed(timer name)
log elapsed times using logs.info()
Create separate automated processes to monitor metrics, learn from them over time, and notify when anomalies are detected.
Logging
LOGS package
info(msg), warn(msg), err(msg)
record important data, expected and unexpected error conditions
dbg(msg)
to document code and leave hooks for dynamic, real-time logging
set_dbg (boolean and directed)
Debugging
Parameter values table-driven
Parameters in APP_PARM
Debug (on/off, session, unit, user)
Debug Toggle Check Interval (in minutes)
Default Log Targets (Screen=N,Table=Y,File=N)
Parameters can be temporarily overridden through logs.set* routines
Monitor & Trace
ENV offers:
tag/untag to modify module, action and client_info
tag_longop to track long operations
init/reset_client_ctx to set/unset client_identifier and other client metadata if desired
Performance of Log Destinations
Screen (10K msgs = 1 sec)
Quick-and-dirty testing and debugging.
Log Table (10K msgs = 4 sec)
A default job keeps the table trimmed to a couple weeks of data.
File (10K msgs = 15 sec)
Pipe (10K msgs = 8 sec + 4 sec to log them)
Conclusion
Putting it All Together
Write and document public interface.
Write tests that all fail.
Write body in pseudo-code.
Fill in the algorithm, making sure routine does one thing and one thing well. Ensure it uses assertions to check assumptions. Clean. To standard. Formatted.
Wrap pseudo-code with log and debug calls, adding a little runtime context. Voila! 3-birds with one stone.
Then I run the tests until they all work, using the instrumentation and metrics if there is trouble.
Instrumentation should be in place before production problems occur.
But it can be added easily after as well.
Adopt or build a standard library.
It must be simple and easy to use.
Encourage or enforce its use.
Do it today! It’s easy and rewarding.
Questions?

Contact:
bcoulam@yahoo.com
Framework:
http://sourceforge.net/projects/plsqlframestart
Papers & Presentations:
http://www.dbartisans.com
vs.
Could by kept and immediately changed in a global application context, but this is not supported in a RAC environment until 11gR2.

Strictly DBA? Strictly developers? Hybrids?

Written PL/SQL that was release to Prod?
Who has never had anything go wrong in that production PL/SQL?
When things do go wrong, how long does it take to diagnose the problem?
Intro
Monitoring
&
Tracing
Becoming Aware there is a Problem
Silent Fester
Side Effect
New Guy
Phone Call
Email
Pink slip
Options without Instrumentation
Options with Instrumentation
Proactively monitor and analyze:
sessions
logs
metrics
audit data
Finding the Problem Source
Monitor & Trace
Full transcript