Search

Top 60 Oracle Blogs

Recent comments

August 2009

Shared pool freelists (and durations)

My earlier blog about shared pool duration got an offline response from one of my reader:
” So, you say that durations aka mini-heaps have been introduced from 10g onwards. I have been using Steve Adams’ script shared_pool_free_lists.sql. Is that not accurate anymore?”

Shared pool free lists

I have a great respect for Steve Adams . In many ways, he has been a great virtual mentor and his insights are so remarkable.

Coming back to the question, I have used Steve’s script before and it is applicable prior to Oracle version 9i. In 9i, sub-heaps were introduced. Further, shared pool durations were introduced in Oracle version 10g. So, his script may not be applicable from version 9i onwards. We will probe this further in this blog.

This is the problem with writing anything about internals stuff, they tend to change from version to version and In many cases, our work can become obsolete in future releases(including this blog!).

In version 9i, each sub-heap of the shared_pool has its own free list. In version 10g and 11g, each duration in sub-heap has its own free list. This is visible through x$ksmsp and column x$ksmsp.ksmchdur indicates the duration that chunk belongs to. In 9i, that column always has a value of 1 (at least, that I have experimented so far). In 10g & 11g (up to 11.1.0.7), there are exactly 4 durations in each sub-heap and values range from 1-4 for this column ksmchdur. Each duration has its own free list.

Shared_pool_free_list.sql script

Memories...

Wow, I stumbled on this - and I so remember it.

I remember my first Pascal (turbo of course), that really got me started programming at home, in my spare time. That was the beginning of the end.

And when I got Turbo C, that was it. I was hooked. I cannot count the number of times I had to reboot my computer learning C - as I was constantly overwriting memory in the beginning - but it was worth it. How many times did I have to flip flop the floppies "Insert Library Disk 1", "Insert Library Disk 2" - because I had no hard drive...

A blast from the past.

This morning, as I was crawling under my desk to get to the USB hub to plug in yet another device, I was thinking "remember back in the day when every device you bought came with an 'expansion card' and you had to crack the case to install hardware - this is too easy".

My first hard disk - partition it into 32mb or less partitions (DOS didn't do more than 32mb on a disk back then...), install hardware, reinstall hardware, load drivers (by hand...), have at a really really slow disk..

My first computer CD device - hardware to install first, then lots of device drivers (by hand, edit that config.sys)...

My first scanner, ditto

Modem... the same.

and so on. It is very much easier these days, but you lose a bit of knowledge with that ease of use. Maybe that is why I had to opportunity to write this...

ORA-6502 "Bulk Bind: Truncated Bind" error

ORA-6502 is an error that is apparently not well documented when it occurs in conjunction with the use of PL/SQL tables, and possibly bulk binds.

I ran into this problem recently when some custom code that had worked well for several years suddenly started failing.

As it turns out, and you will see just a little later here, the error is rather easy to fix. What makes it diffucult is if you've never encountered an ORA-6502 under these circumstances. There is precious little about it via MetaLink or Google. Writing about it here may be of help to the next innocent victim of ORA-6502 with bulk binds.

My suspicion was that new data loaded into the system from another database had something to do with the problem, the problem was determining where it was occurring and why.

The problem went unnoticed for some time due to a quirk in error handling built into the package. (Tom Kyte would likely agree)

An unusual cause of ORA-12154

The ORA-12154 (and its cousin ORA-12514) have been the bane of many a novice DBA.

This error is essentially telling you that you have messed up your database naming configuration, whether it be tnsnames, oracle names or OID. The fix is normally quite simple - just correct the naming.

This is usually quite easily done with netmgr, a tool that is fairly good at its job. The syntax for setting up a tnsname is a little convoluted, and I've fallen back on netmgr a number of times when I can't seem to get it just right by typing the entries in the file.

There's at least one other way to cause ORA-12154 to pop up and consume more time than you may like to admit. I won't tell how long I played with this...

The cause is actually due to security policy. While the characters !@#$%^&*()_-=+~` are not normally allowed in Oracle passwords, it is actually quite simple to include them. Simply enclose the password in double quotes.

Querying v$lock

There have been a number of scripts made available for querying v$lock to diagnose locking issues.

One example is one I got long ago from tsawmiller on Oracle-L. The original script showlock.sql, or something close to it is still available at OraFaq.com showlock.sql

showlock.sql has morphed over the years to keep up with changing versions of Oracle.

At one time the showlock.sql resembled the OH/rdbms/admin/utllockt.sql script, in that it created a temporary table to speed up the results, as the join on v$lock, dba_sessions and dba_waiters was so slow.

That was remedied at one point by the use of the ordered hint. That hint may no longer be necessary, but the script is still fast on all versions of Oracle that I need it on, (9i-11g) and I am too lazy to test something that isn't broken.

ORA-4031 and Shared Pool Duration

After reading my earlier post on shared pool A stroll through shared pool heap , one of my client contacted me with an interesting ORA-4031 issue. Client was getting ORA-4031 errors and shared pool size was over 4GB ( in a RAC environment). Client DBA queried v$sgastat to show that there is plenty of free memory in the shared pool. We researched the issue and it is worth blogging. Client DBA was confused as to how there can be ORA-4031 errors when the shared pool free memory is few GBs.

Heapdump Analysis

At this point, it is imperative to take heapdump in level 2 and Level 2 is for the shared pool heap dump. [ Please be warned that it is not advisable to take shared pool heap dumps excessively, as that itself can cause performance issue. During an offline conversation, Tanel Poder said that heapdump can freeze instance as his clients have experienced.]. This will create a trace file in user_dump_dest destination and that trace file is quite useful in analyzing the contents of shared pool heap. Tanel Poder has an excellent script heapdump_analyzer . I modified that script adding code for aggregation at hea, extent and type levels to debug this issue further and it is available as heapdump_dissect.ksh . ( with a special permission from Tanel to publish this script.)

Shared pool review

It is true...

I get to see a lot of "in house" applications - those applications developed internally for and by a company itself.

The screens on these applications many times have more fields on them than the mind can fathom. Fields and buttons galore.

Just like this cartoon demonstrates...

It is so true - I like simple user interfaces (yes, I'm a metalink classic fan too...)

Sometimes...

Every now and then, you read something that makes you go "huh".

I was answering some questions on asktom today and had one about a CSV (comma separated values) file. Thought I would point the person to the 'specification' for that file format and the first searching I did turned this up (from a forum)

Perhaps I misunderstand the question because I'm not sure what
"format" and "escape" characters are.

However, to my knowledge, CSV files are nothing more than ascii text
files, which means the font is courier and 12 pt.

The emphasis is mine - a CSV file is just a file where the text is in 12pt courier! That is so simple.

Sometimes, you read something and it just makes you laugh out loud...