105 lines
4.1 KiB
MySQL
105 lines
4.1 KiB
MySQL
-------------------------------------------------------------------------------------
|
|
--
|
|
-- File name: oStackProfU.sql ( Oradebug short_Stack Profiler )
|
|
-- Purpose: Take target process stack samples and show an execution profile
|
|
--
|
|
-- Author: Tanel Poder
|
|
-- Copyright: 2008 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
|
|
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
|
|
--
|
|
-- Usage: @ostackprofu <sid> <interval> <#samples>
|
|
--
|
|
-- @ostackprofu 148 0 100
|
|
--
|
|
-- - takes 100 stack samples of server process tied to SID 148
|
|
-- with not waiting between samples
|
|
--
|
|
-- @ostackprofu 148 0.1 60
|
|
|
|
-- - takes 60 stack samples of process tied to sid 148 with 0.1
|
|
-- second interval
|
|
--
|
|
-- Updates: Version 1.2 - support also Unix/Mac clients via Python based stack_helper
|
|
--
|
|
-- Other: WARNING!!! This tool is experimental and not meant for use in
|
|
-- production environments. This is due oradebug short_stack
|
|
-- being somewhat unstable on some platforms and it has
|
|
-- event crashed target processes on Windows platforms (9.2)
|
|
--
|
|
-- Use an OS stack sampler instead when need to diagnose
|
|
-- production issues (and test even those well in dev)
|
|
--
|
|
--
|
|
-- Note that this script uses a stack_helper.py Python script for post
|
|
-- processing oradebug short_stack output. Therefore you need to run
|
|
-- it on a Unix/Linux/Mac SQLPLUS client with python installed. The
|
|
-- server can be on any platforms as long as it supports oradebug
|
|
-- short_stack - 10.2 on Solaris x64 doesn't seem to support it though,
|
|
-- but you have DTrace there anyway ;)
|
|
--
|
|
-------------------------------------------------------------------------------------
|
|
|
|
PROMPT
|
|
PROMPT -- oStackProf v1.2 - EXPERIMENTAL script by Tanel Poder ( https://tanelpoder.com )
|
|
PROMPT
|
|
PROMPT WARNING! This script can crash the target process on Oracle 9.2 on Windows
|
|
PROMPT and maybe other versions/platforms as well. Test this script out thorouhgly
|
|
PROMPT in your dev environment first!
|
|
PAUSE Hit CTRL+C to cancel, ENTER to continue...
|
|
|
|
SET TERMOUT OFF FEEDBACK OFF VERIFY OFF
|
|
|
|
DEF ostackprof_sid=&1
|
|
DEF ostackprof_interval=&2
|
|
DEF ostackprof_samples=&3
|
|
|
|
COL spid NEW_VALUE ostackprof_spid
|
|
SELECT spid FROM v$process WHERE addr = (SELECT /*+ NO_UNNEST */ paddr FROM v$session WHERE sid = &1);
|
|
COL spid CLEAR
|
|
|
|
ORADEBUG SETOSPID &ostackprof_spid
|
|
|
|
SELECT
|
|
'oradebug short_stack'||
|
|
DECODE(TO_NUMBER(&ostackprof_interval),
|
|
0, '',
|
|
chr(13)||chr(10)||'exec dbms_lock.sleep('||TO_CHAR(&ostackprof_interval)||')'
|
|
) cmd
|
|
FROM
|
|
(select 1 from dual CONNECT BY level <= &ostackprof_samples)
|
|
.
|
|
|
|
SPOOL ostackprof_&ostackprof_spid..tmp
|
|
SET HEADING OFF
|
|
PROMPT spool ostackprof_&ostackprof_spid..txt
|
|
/
|
|
PROMPT spool off
|
|
SET HEADING ON
|
|
SPOOL OFF
|
|
|
|
SET TERMOUT ON
|
|
PROMPT Sampling...
|
|
SET TERMOUT OFF
|
|
@ostackprof_&ostackprof_spid..tmp
|
|
|
|
SET TERMOUT ON FEEDBACK ON
|
|
|
|
DEF _nothing="" -- a hack
|
|
PROMPT
|
|
PROMPT Below is the stack prefix common to all samples:
|
|
PROMPT ------------------------------------------------------------------------&_nothing
|
|
PROMPT Frame->function()
|
|
PROMPT ------------------------------------------------------------------------&_nothing
|
|
|
|
-- Windows client
|
|
--HOST "cscript //nologo %SQLPATH%\stack_helper.vbs -strip < ostackprof_&ostackprof_spid..txt | sort | cscript //nologo %SQLPATH%\stack_helper.vbs -report | sort /r"
|
|
|
|
-- Unix/Mac client
|
|
HOST $SQLPATH/stack_helper.py prefix < ostackprof_&ostackprof_spid..txt
|
|
PROMPT ----------------------------------------------------------------------&_nothing
|
|
PROMPT -- # Num additional function samples in call stack
|
|
PROMPT ----------------------------------------------------------------------&_nothing
|
|
HOST $SQLPATH/stack_helper.py details < ostackprof_&ostackprof_spid..txt | sort | uniq -c | sort -nbr
|
|
|
|
HOST &_delete ostackprof_&ostackprof_spid..tmp ostackprof_&ostackprof_spid..txt
|