Enabling and Reading event 10046 / SQL Trace

July 20, 2011 by 1 Comment

As I’m done with the book and back from a quick vacation (to Prague, which is an awesome place – well, at least during the summer) I promised (in Twitter) that now I’d start regularly writing blog articles again. In a separate tweet I asked what to write about. Among other requests (which I’ll write about later), one of the requests was to write something about enabling and reading SQL trace files… 

I am probably not going to write (much) about SQL trace for a single reason – Cary Millsap has already written a paper so good about this topic, that there’s no point for me to try to repeat it (and my paper wouldn’t probably be as clear as Cary’s).

So, if you want to get the most out of SQL Trace, read Cary’s Mastering Performance with Extended SQL Trace paper:

 

The above link directs you to Method-R’s article index, as there’s a lot of other useful stuff to read there.

Wow, now I’m done with my first request – to write something about SQL Trace :-)

 

Filed Under: Oracle Tagged With: , ,

MOATS: The Mother of All Tuning Scripts!

March 29, 2011 by 56 Comments

People talk about the Oracle SQL Developer 3 being out, which is cool, but I have something even cooler for you today ;-)

I finally figured out how to convert my screen-recordings to uploadable videos, so that the text wouldn’t get unreadable and blurry.

So, here’s the first video, about a tool called MOATS, which we have built together with fellow OakTable Network member and a PL/SQL wizard Adrian Billington (of oracle-developer.net).

Here’s the video, it’s under 3 minutes long. Play the video in full screen for best results (and if it’s too slow loading, change it to lower resolution from HD mode):

Check it out and if you like MOATS, you can download it from Adrian’s website site (current version 1.05) and make sure you read the README.txt file in the zip!

Also thanks to Randolf Geist for finding and fixing some bugs in our alpha code… Note that MOATS is still kind of beta right now…

P.S. I will post my ORA-4031 and shared pool hacking video real soon now, too! :-)

P.P.S. Have you already figured out how it works?! ;-)

Update: Now you can suggest new features and improvement requests here:

Filed Under: Cool stuff, Oracle, Productivity Tagged With: , , , ,

An index of my TPT scripts

March 24, 2011 by 8 Comments

A lot of people have asked me whether there’s some sort of index or “table of contents” of my TPT scripts (there’s over 500 scripts in the tpt_public.zip file – http://tech.e2sn.com/oracle-scripts-and-tools )

I have planned to create such index for years, but never got to it. I probably never will :) So a good way to extract the descriptions of some scripts is this (run the command in the directory where you extracted my scripts to):

Note: the single and double-quotes may get messed up when the browser tries to be smart and replace them with nicer looking characters (which Unix doesn’t recognize then). When copying & pasting this command, make sure that the single & double-quotes are the regular ones Unix shell can accept):

$ grep -i Purpose: *.sql | awk -F: ‘{ printf(“%20s %-50s\n”, $1, $3) }’
            bhla.sql      Report which blocks are in buffer cache, protected by a cache
         bufprof.sql      Display buffer gets done by a session and their reason
            calc.sql      Basic calculator and dec/hex converter       
        channels.sql      Report KSR channel message counts by channel endpoints
        curheaps.sql      Show main cursor data block heap sizes and their contents
             dba.sql      Convert Data Block Address (a 6 byte hex number) to file#, block#
             ddl.sql      Extracts DDL statements for specified objects
              df.sql  Show Oracle tablespace free space in Unix df style
        diag_sid.sql      Display current Session Wait info            
        diag_sid.sql      An easy to use Oracle session-level performance snapshot utility
           disco.sql      Generates commands for disconnecting selected sessions
     getplusparm.sql      get sqlplus parameter value (such linesize, pagesize, sqlcode,
            grpn.sql      Quick group by query for aggregating Numeric columns
            hash.sql      Show the hash value, SQL_ID and child number of previously
             i2h.sql      Advanced Oracle Troubleshooting Seminar demo script
              im.sql      Display In-Memory Undo (IMU) buffer usage    
            init.sql Initializes sqlplus variables for 156 character terminal
       kglbroken.sql                                                   
       kglbroken.sql      Report broken kgl locks for an object this can be used for 
            kill.sql      Generates commands for killing selected sessions
              la.sql      Show which latch occupies a given memory address and its stats
     lastchanged.sql      Detect when a datablock in table was last changed
       latchprof.sql      Perform high-frequency sampling on V$LATCHHOLDER
   latchprof_old.sql      Perform high-frequency sampling on V$LATCHHOLDER
      latchprofx.sql      Perform high-frequency sampling on V$LATCHHOLDER
              lh.sql      Show latch holding SIDs and latch details from V$LATCHHOLDER
             lhp.sql      Perform high-frequency sampling on V$LATCHHOLDER
            lhpx.sql      Perform high-frequency sampling on V$LATCHHOLDER
     lotshparses.sql      Generate Lots of hard parses and shared pool activity 
    lotshparses2.sql      Generate Lots of hard parses and shared pool activity 
        lotslios.sql      Generate Lots of Logical IOs for testing purposes
        lotspios.sql      Generate Lots of Physical IOs for testing purposes
     lotssparses.sql      Generate Lots of soft parses and library cache/mutex activity 
    lotssparses2.sql      Generate Lots of soft parses and library cache/mutex activity 
       mutexprof.sql      Display KGX mutex sleep history from v$mutex_sleep_history
       nonshared.sql      Print reasons for non-shared child cursors from v$sql_shared_cursor
      nonshared2.sql      Show the reasons why more child cursors were created instead of
      ostackprof.sql      Take target process stack samples and show an execution profile
            pmem.sql      Show process memory usage breakdown – lookup by process SPID
     pmem_detail.sql      Show process memory usage breakdown details – lookup by process SPID
        prefetch.sql      Show KCB layer prefetch                      
          pvalid.sql      Show valid parameter values from V$PARAMETER_VALID_VALUES
        rowcache.sql      Show parent rowcache entries mathcing an object name
              rs.sql      Display available Redo Strands               
               s.sql      Display current Session Wait and SQL_ID info (10g+)
          sample.sql      Sample any V$ view or X$ table and display aggregated results
      sampleaddr.sql      High-frequency sampling of contents of a SGA memory address
             ses.sql      Display Session statistics for given sessions, filter by
            ses2.sql      Display Session statistics for given sessions, filter by
        sgastatx.sql      Show shared pool stats by sub-pool from X$KSMSS
            smem.sql      Show process memory usage breakdown – lookup by session ID
     smem_detail.sql      Show process memory usage breakdown details – lookup by session ID
         snapper.sql      An easy to use Oracle session-level performance snapshot utility
     snapper3.15.sql      An easy to use Oracle session-level performance snapshot utility
      snapper_v1.sql      An easy to use Oracle session-level performance snapshot utility
      snapper_v2.sql      An easy to use Oracle session-level performance snapshot utility
            stat.sql      Execute SQL statement in script argument and report basic
              sw.sql      Display current Session Wait info            
             sw2.sql      Display current Session Wait info            
             swg.sql      Display given Session Wait info grouped by state and event
             swo.sql      Display current Session Wait info            
          topsql.sql      Show TOP SQL ordered by user-provided criteria
            usql.sql      Show another session’s SQL directly from library cache
           usqlx.sql      Show another session’s SQL directly from library cache
        waitprof.sql      Sample V$SESSION_WAIT at high frequency and show resulting 
              xb.sql      Explain a SQL statements execution plan with execution 
            xde2.sql      Describe X$ tables, column offsets and report indexed fixed table
              xm.sql      Explain a SQL statements execution plan directly from library cache
             xma.sql      Explain a SQL statements execution plan directly from library cache
            xmai.sql      Explain a SQL statements execution plan with execution 
             xms.sql      Explain your last SQL statements execution plan with execution 
            xmsh.sql      Explain a SQL statements execution plan with execution 
            xmsi.sql      Explain a SQL statements execution plan with execution

 

Filed Under: Oracle, Productivity Tagged With: , , ,

ORA-4031 errors, contention, cursor management issues and shared pool fragmentation – free secret seminar!

March 16, 2011 by 18 Comments

Free stuff! Free stuff! Free stuff! :-)

The awesome dudes at E2SN have done it again! (and yes, Tom, this time the “we at E2SN Ltd” doesn’t mean only me alone ;-)

On Tuesday 22nd March I’ll hold two (yes two) Secret Oracle Hacking Sessions – about ORA-04031: unable to allocate x bytes of shared memory errors, cursor management issues and other shared pool related problems (like fragmentation). This event is free for all! You’ll just need to be fast enough to register, both events have 100 attendee limit (due to my GotoWebinar accont limitations).

I am going to run this online event twice, so total 200 people can attend (don’t register for both events, please). One event is in the morning (my time) to cater for APAC/EMEA region and the other session is for EMEA/US/Americas audience.

The content will be the same in both sessions. There will be no slides (you cant fix your shared pool problems with slides!) but there will be demos, scripts, live examples and fun (for the geeks among us anyway – others go and read some slides instead ;-)!

Filed Under: Cool stuff, Oracle Tagged With: , , , , , , ,

Finding Oracle Homes which Oracle instances are using on Linux

February 28, 2011 by 15 Comments

I had a question about how to quickly identify which Oracle process runs out of which ORACLE_HOME on Linux.

I have uploaded a little script for that – it’s basically looking up all PMON process IDs and then using /proc/PID/exe link to find out where is the oracle binary of a running process located.

You may have to run this as root (as on some Linux versions I get “ls: cannot read symbolic link: Permission denied” error even when running this command as the owner of all Oracle homes (it seems to happen when your users UID and primary GID are different than thet setuid/setgid bits on the oracle binary):

oracle@linux03:~$ sudo ./findhomes.sh
   PID NAME                 ORACLE_HOME
  4421 asm_pmon_+ASM        /u01/app/oracle/product/11.2.0/db_1/
  4545 ora_pmon_demo112     /u01/app/oracle/product/11.2.0/dbhome_1/
  4547 ora_pmon_test112     /u01/app/oracle/product/11.2.0/dbhome_1/

You can use a similar approach on other Unixes too where the executable location or current working directory (CWD) is externalized in the /proc filesystem – or just use pmap to get this info instead.

Filed Under: Oracle Tagged With: ,

Performance Stories from Exadata Migrations

January 11, 2011 by 19 Comments
Here are my UKOUG 2010 slides about Exadata migration performance, this is real life stuff, not repeating the marketing material:
View more presentations from tanelp.
Filed Under: Oracle Tagged With: , ,

The full power of Oracle’s diagnostic events, part 2: ORADEBUG DOC and 11g improvements

June 23, 2010 by 16 Comments

I haven’t written any blog entries for a while, so here’s a very sweet treat for low-level Oracle troubleshooters and internals geeks out there :)

Over a year ago I wrote that Oracle 11g has a completely new low-level kernel diagnostics & tracing infrastructure built in to it. I wanted to write a longer article about it with comprehensive examples and use cases, but by now I realize I won’t ever have time for this, so I’ll just point you to the right direction :)

Basically, since 11g, you can use SQL_Trace, kernel undocumented traces, various dumps and other actions at much better granularity than before.

For example, you can enable SQL_Trace for a specific SQL_ID only:

SQL> alter session set events 'sql_trace[SQL: 32cqz71gd8wy3] 
{pgadep: exactdepth 0} {callstack: fname opiexe}
plan_stat=all_executions,wait=true,bind=true';


Session altered.

Actually I have done more in above example, I have also said that trace only when the PGA depth (the dep= in tracefile) is zero. This means that trace only top-level calls, issued directly by the client application and not recursively by some PL/SQL or by dictionary cache layer. Additionally I have added a check whether we are currently servicing opiexe function (whether the current call stack contains opiexe as a (grand)parent function) – this allows to trace & dump only in specific cases of interest!

The syntax is actually more powerful than that, in this example I’m running kernel tracing for a kernel component plus instructing Oracle to dump various other things at level 1 (callstack,process state and query block debug info) whenever a tracepoint (event) in the SQL Transformation component family is hit:

SQL> alter session set events 'trace[RDBMS.SQL_Transform] [SQL: 32cqz71gd8wy3]
disk=high RDBMS.query_block_dump(1) processstate(1) callstack(1)';


Session altered.

And by now you are probably asking that where is this syntax formally documented? Google and MOS searches don’t return anything useful. Well, as with many other things, a good reference is stored within Oracle kernel itself!

Just log on as sysdba and type ORADEBUG DOC:

ORADEBUG DOC

SQL> oradebug doc
Internal Documentation
**********************
EVENT                           Help on events (syntax, event list, ...)
COMPONENT       [<comp_name>]   List all components or describe <comp_name>

This gives you the index page, now you can navigate on by running ORADEBUG DOC EVENT and take it from there. There’s lots of documentation there!

I have put the output with some comments and examples into my website too:

http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc

Note that this feature is quite fresh, almost not used at all in the real (production) world, so I consider this quite experimental. I have managed to crash my session with some tests, so take the usual advice about any undocumented stuff (and oradebug) – don’t use it in production without thinking first and if you do use it, then use it at your own risk!

Filed Under: Cool stuff, Oracle Tagged With: , , ,

Flexible Sqlplus command line history with RLWRAP

May 7, 2010 by 11 Comments

At Hotsos Symposium Training Day I used rlwrap with sqlplus – which gives nice command line editing and history capabilities for tools like sqlplus. Additionally I pre-generated commonly used Oracle keywords, data dictionary view and package names into rlwrap wordfile, so I got nice tab-completion too. Sqlplus sucks much less with rlwrap ;-)

It’s relatively easy to install rlwrap on Unix (there are rlwrap RPMs out there, Solaris freeware packages and I installed it on Mac via macports.org). Just google around…

You can have rlwrap on Windows too – As rlwrap has been coded for Unix flavors, then on Windows you need to run it on a Unix library environment emulator – like Cygwin.

Dave Herring and Michael Paddock have both written an article about how to get rlwrap & sqlplus running on Windows, check out the articles here. It’s worth reading both as they have different additions…

So, if you want command line history, search and tab completion for sqlplus on Unix flavors or Windows, check these articles out!

http://daveherringsdbablog.blogspot.com/2010/03/sqlplus-command-history-with-cygwin.html

http://blog.enkitec.com/2010/04/29/using-sqlplus-with-rlwrap-on-ms-windows/

Filed Under: Cool stuff, Oracle, Productivity Tagged With: ,

Oracle Latch Contention Troubleshooting

March 27, 2010 by 12 Comments

I wrote a latch contention troubleshooting article for IOUG Select journal last year (it was published earlier this year). I have uploaded this to tech.E2SN too, I recommend you to read it if you want to become systematic about latch contention troubleshooting:

http://tech.e2sn.com/oracle/troubleshooting

I’m working on getting the commenting & feedback work at tech.E2SN site too, but for now you can comment here at this blog entry…

Filed Under: Cool stuff, Oracle Tagged With: , , , , , , , , , ,

Oracle Session Snapper v3.10

March 22, 2010 by 11 Comments

Hi all, long time no see!  =8-)

Now as I’m done with the awesome Hotsos Symposium (and the training day which I delivered) and have got some rest, I’ll start publishing some of the cool things I’ve been working on over the past half a year or so.

The first is Oracle Session Snapper version 3!

There are some major improvements in Snapper 3, like ASH style session activity sampling!

When you troubleshoot a session’s performance (or instance performance) then the main things you want to know first are very very simple:

  1. Which SQL statements are being executed
  2. What are they doing, are they working on CPU or waiting.
  3. If waiting, then for what

Often this is enough for troubleshooting what’s wrong. For example, if a session is waiting for a lock, then wait interface will show you that. If a single SQL statement is taking 99% of total response time, the V$SESSION (ASH style) samples will point out the problem SQL and so on. Simple stuff.

However there are cases where you need to go beyond wait interface and use V$SESSTAT (and other) counters and even take a “screwdriver” and open Oracle up from outside by stack tracing :-)

When I wrote the first version of Snapper for my own use some 4-5 years ago I wrote it mainly having the “beyond wait interface” part in mind. So I focused on V$SESSTAT and various other counters and left the basic troubleshooting to other tools. I used to manually sample V$SESSION/V$SESSION_WAIT a few times in a row to get a rough overview of what a session was doing or some other special-purpose scripts.

However after Snapper got more popular and I started getting some feedback about it I saw the need for covering more with Snapper, not just the “beyond wait interface” part, but also the “wait interface” and “which SQL” part too.

So, now I’m presenting Snapper v3 which does all the 3 points above using ASH style V$SESSION sampling and it still has the first step to “beyond wait interface” part in it, which is very useful for advanced performance troubleshooting and diagnosis – I’m talking about the V$SESSTAT counters above.

I’ve made some syntax changes in Snapper too and right now the v3 doesn’t work on Oracle 9.2 (it will work some day :)

To give you an idea of the new ASH style sampling capabilities, heres some example output:

SQL> @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all

Sampling...


-- Session Snapper v3.10 by Tanel Poder @ E2SN ( http://tech.e2sn.com )

--------------------------------------------------------------


Active% |    SID | EVENT                     | WAIT_CLASS


--------------------------------------------------------------
   100% |    133 | db file scattered read    | User I/O
     5% |    165 | control file parallel wri | System I/O
     2% |    162 | ON CPU                    | ON CPU
     2% |    167 | db file parallel write    | System I/O
     2% |    166 | log file parallel write   | System I/O



---------------------------------------------------
Active% | PLSQL_OBJE | PLSQL_SUBP | SQL_ID
---------------------------------------------------
    77% |            |            | a5xyjp9gt796s
    23% |            |            | 4g4u44bk830ms
    12% |            |            |


-------------------------------------------------------------------------------------------
Active% | PROGRAM                   | MODULE                    | ACTION
-------------------------------------------------------------------------------------------
100% | sqlplus@mac01 (TNS V1-V3) | sqlplus@mac01 (TNS V1-V3) |
  5% | oracle@solaris02 (CKPT)   |                           |
  2% | oracle@solaris02 (DBW0)   |                           |
  2% | oracle@solaris02 (CJQ0)   |                           |
  2% | oracle@solaris02 (LGWR)   |                           |

--  End of ASH snap 1, end=2010-03-22 17:35:06, seconds=5, samples_taken=43

You can read some usage examples and download it here:

P.S. People who attended Hotsos Symposium Training Day where I demoed the initial version of Snapper v3 – download the new version from above link (v3.10), it’s much more flexible than the one I demoed couple of weeks ago!


Filed Under: Oracle Tagged With: , , ,
«Older Posts