Some time ago I wrote that since Oracle 10.2, some of the buffer cache can physically reside within shared pool granules. I just noticed this in an 11.2 instance: SQL> select * from v$sgastat where name like 'KGH%'; POOL         NAME                            BYTES ------------ -------------------------- ---------- streams pool KGH: NO ACCESS                4186144 So, it looks that also streams pool can surrender parts of its memory granules to buffer cache, if it's unable to flush everything out from the granule for complete granule handover. Let's see ...

Tanel Poder
Cool stuff, Internals, Oracle, Oracle 11gR2, Troubleshooting

Gwen Shapira has written an article about a good example of a non-trivial performance problem. I'm not talking about anything advanced here (such as bugs or problems arising at OS/Oracle touchpoint) but that sometimes the root cause of a problem (or at least the reason why you notice this problem now) is not something deeply technical or related to some specific SQL optimizer feature or a configuration issue. Instead of focusing on the first symptom you see immediately, it pays off to take a step back and see how the problem task/application/SQL is actually used by the users or client applications. In ...

Tanel Poder
Administration, Oracle, Performance, Troubleshooting, Tuning
Oracle, Performance, SQL, Troubleshooting, Tuning

Some time ago I wrote an article about the 10g+ SQL_ID being just a hash value of the SQL statement text. It's just like the "old" SQL_HASH_VALUE, only twice longer (8 last bytes instead of 4 last bytes of the MD5 hash value of SQL text). Slavik Markovich has written a nice python script for calculating SQL_IDs and SQL hash values from SQL text using that approach. Slavik's article is available here: http://www.slaviks-blog.com/2010/03/30/oracle-sql_id-and-hash-value/

Tanel Poder
Cool stuff, Internals, Oracle, Security
Internals, Oracle

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...

Tanel Poder
Cool stuff, Internals, Oracle, Performance, Tools, Troubleshooting, Tuning
contention, latch, method, Oracle, Performance, scripts, systematic, Tools, Troubleshooting, Tuning

This is an updated version of Snapper, which works ok on Oracle 10.1 now as well (9i support is coming some time in the future :) Thanks to Jamey Johnston for sending me the fix info (and saving me some time that way :) So if you have some problems with Snapper on Oracle 10.1, please make sure you have the latest version v3.11, which you can get from here: http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper The output below is from Snapper 3.11 on Oracle 10.1.0.5, the ASH columns in the bottom part of the output are displayed correctly now: SQL> @snapper ash,ash1,ash2,ash3,stats,gather=t 15 1 all Sampling with interval 15 seconds, ...

Tanel Poder
Oracle, Tools, Troubleshooting, Tuning
Oracle, Performance, scripts, snapper, Tools, Troubleshooting, Tuning

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: Which SQL statements are being executed What are they doing, are they working on ...

Tanel Poder
Oracle, Performance, Tools, Troubleshooting, Tuning

Here's a treat for Oracle geeks, hackers and maniacs out there... Update: As the beginning says, this article was meant as something interesting about Oracle's internals and CTRL+C / OCICancel() handling. There's a more practical way for canceling session calls if you are running Oracle Enterprise Edition and are currently using resource manager: You can set the consumer group for a session to CANCEL_SQL to cancel its current call: DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ( session_id IN NUMBER, session_serial IN NUMBER, consumer_group IN VARCHAR2); Thanks to commenter "null" for this info. Note that I haven't tested how/whether this feature works correctly so there's homework for you ;-) I recently received a question ...

Tanel Poder
Uncategorized

If you are in Singapore and have 24th Feb afternoon available then you can register and join a free Oracle performance troubleshooting seminar I'm doing in Singapore Management University's (SMU) campus. The seminar will be about: Systematic Oracle Performance Troubleshooting Identifying performance troublemakers Understanding execution plans The date is Wednesday, 24th Feb The seminar time is from 15:30-19:00 (don't be late) Registration and more details are here: http://tech.e2sn.com/events/singapore-oracle-meetup/

Tanel Poder
Cool stuff, Oracle, Performance, Troubleshooting, Tuning

The LatchProf and LatchProfX scripts allow you to be more systematic with latch contention troubleshooting and tuning. No more guesswork is needed as these scripts give you exact session IDs and in this version also SQLIDs of the troublemaking applications. You can download the new versions here: LatchProf (reads V$ views) LatchProfX (reads X$ tables, but gives better info, run as SYS) Example output (with SQLID info) is below: [code]SQL> @latchprof name,sid,sqlid % % 100000 -- LatchProf 1.21 by Tanel Poder ( http://www.tanelpoder.com ) NAME                                       SID SQLID               Held       Gets  Held %     Held ms Avg hold ms ----------------------------------- ---------- ------------- ---------- ---------- ------- ----------- ----------- cache buffers chains                       133 3jbwa65aqmkvm ...

Tanel Poder
Administration, Cool stuff, Oracle, Performance, Troubleshooting

I have written the first article to the troubleshooting section of my new website tech.E2SN.com: It's about a very valuable Oracle troubleshooting tool -> ERRORSTACK trace. I cover 4 frequently asked questions there: Reading the current executing SQL statement text from errorstack trace Reading the current executing PL/SQL package and PL/SQL source code line number from errorstack trace Reading the current bind variable values from errostack trace Identifying how much private (UGA) memory a cursor is using You can read it here: http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output By the way, if you like my new website, feel free to link to it !!! ;-) http://tech.e2sn.com

Tanel Poder
Administration, Cool stuff, Internals, Oracle, Troubleshooting, Tuning