Oracle Troubleshooting TV Show: Season 1, Episode 01 ;-)

Ok, it’s official – the first and only Oracle Troubleshooting TV show is live now!

The first show is almost 2 hours about the ORA-4031 errors and shared pool hacking. It’s a recording of the US/EMEA timezone online hacking session I did some days ago.

There are a couple of things to note:

  1. The text still isn’t as sharp as in the original recording, but it’s much better than in my previous upload attempts and is decently readable. I’ll try some more variations with my next shows so I hope the text quality will get better! Or maybe I should just switch to GUI tools or powerpoint slides? ;-)
  2. You probably should view this video in full screen (otherwise the text will be tiny and unreadable)
  3. There’s advertising in the beginning (and maybe end) of this show! I’ll see how much money I’ll make out of this – maybe these shows start contributing towards the awesome beer selection I’ll have in my fridge some day (right now I have none). Viewing a 30-sec advert is small price to pay for 2 hours of kick-ass shared pool hacking content !!!
  4. You can download the scripts and tools used in the demos from
  5. Make sure you check out my online Oracle troubleshooting seminars too (this April and May already)

View the embedded video below or go to my official Oracle Troubleshooting TV show channel:


NB! I am running one more Advanced Oracle Troubleshooting training in 2018! You can attend the live online training and can download personal video recordings too. The Part 1 starts on 29th January 2018 - sign up here!

This entry was posted in Cool stuff, Oracle and tagged , , , , . Bookmark the permalink.

31 Responses to Oracle Troubleshooting TV Show: Season 1, Episode 01 ;-)

  1. Tanel Poder says:

    It seems that this show won’t play on iPad .. at least not on my one … I’ll see if I can fix this some day in the future, no time right now unfortunately…

  2. Tanel Poder says:

    I got it working on Ipad as well. Just go to to go to regular page and pick MOV as the playback format (not SD nor HTML5). Then you get the full unconverted 1024×768 resolution which is very clear and sharp!

  3. mdinh says:

    You are not a movie star!!!

  4. Tanel Poder says:

    Oh, this video is from the 2nd session, so for example the demos which failed in the 1st session (for APAC/EMEA region) did work in the 2nd one, so watch it again! :-)

  5. Tanel Poder says:

    … I just upgraded my account and am uploading this video again (under a high-res setting). I hope this give you sharper text… Anyway, if you’ve got an ipad then read my above instructions and stream the MOV it will be very sharp

  6. amrit says:


  7. Claudio Pereira says:

    Hi Tanel,

    I really appreciated your Webniar, hacking 4031, this was the better class I have had.

    We have been in trouble with a customer here in Brazil and I wish use your scripts (like snapper) to find out where is the problem. I hope write you soon to telling you how was.

    For now, I just want to thanks your good work!

  8. wonderfull,i did not watch yet but congrats for new service :)

  9. Hi Tanel,

    First of all your session was Awesome..!!
    Your session provided further understanding to my basics, which I was trying to understand from long
    time – specially on columns of x$XXXXX tables.

    After watching video of ORA-4031, I observed that things are still missing.
    I probably might be wrong.

    SQL> select * from v$version;

    Oracle Database 11g Enterprise Edition Release – Production
    PL/SQL Release – Production
    CORE Production
    TNS for 32-bit Windows: Version – Production
    NLSRTL Version – Production

    Environment – Windows 7

    SGA Settings

    sga_target 0 Target size of SGA
    __sga_target 272629760 Actual size of SGA
    memory_target 419430400 Target size of Oracle SGA and
    PGA memory

    memory_max_target 419430400 Max size for Memory Target
    pga_aggregate_target 0 Target size for the aggregate
    PGA memory consumed by the

    __pga_aggregate_target 146800640 Current target size for the
    aggregate PGA memory consumed
    _io_shared_pool_size 4194304 Size of I/O buffer pool from SGA

    __shared_pool_size 155189248 Actual size in bytes of shared

    shared_pool_size 0 size in bytes of shared pool
    shared_pool_reserved_size 6501171 size in bytes of reserved area
    of shared pool

    _shared_pool_reserved_pct 5 percentage memory of the
    shared pool allocated for the
    reserved area

    _shared_pool_reserved_min_alloc 4400 minimum allocation size in
    bytes for reserved area of
    shared pool

    _io_shared_pool_size 4194304 Size of I/O buffer pool from

    _shared_pool_max_size 0 shared pool maximum size when
    auto SGA enabled

    SQL> select ksmchcls, count(*) ,sum(ksmchsiz),min(ksmchsiz),max(ksmchsiz)
    2 from x$ksmsp group by ksmchcls
    3 order by 1 ;

    ——– ———- ————- ————- ————-
    R-free 37 7443136 4540 217000
    R-freea 76 38460 24 24360
    R-perm 3 12344908 3980212 4189660
    R-recr 1 3977200 3977200 3977200
    free 1359 8614736 20 3517404
    freeabl 14782 41589608 16 334348
    perm 32 47765824 44 3981312
    recr 23414 33413304 32 43692

    As per my understanding, number of pools are equally divided and mapped acrossed in Reserved area
    in parallel to Shared pool. In Coming future is oracle is going to extend the sub heap pools
    of shared pool – then defintely they are going to extend same functinality to Reserved Pool
    (your comments would be appreciated on the above)

    Current have max chunk size 200 bytes 217000 – R-free and 37 granules

    SQL> select component,current_size,granule_size,current_size/granule_size
    2 from v$sga_dynamic_components
    3 where component like ‘%pool%’
    4 ;

    —————————————————————- ———— ———— ————————-
    shared pool 155189248 4194304 37
    large pool 4194304 4194304 1
    java pool 4194304 4194304 1
    streams pool 4194304 4194304 1

    _kghdsidx_count 1 max kghdsidx count

    Note :- I am not carrying out any work on Database.

    SQL> select 6501171/37 from dual;


    37 granuals – 175 hundered bytes max from each granuals

    R-free 37 7443136 4540 217000

    we find a difference = 41293 bytes

    As per concept, max size of chunksize from granual would be 175 hundered bytes but it went upto 217 hundered bytes
    Is my understanding is correct ? if yes then from where oracle allocated extract 41293 bytes ?


    x$ksmspr – we can fetch the details of Reserved space instead of x$ksmsp

    SQL> select sum(ksmchsiz) ,min(ksmchsiz),max(ksmchsiz),ksmchcls ,count(*)
    2 from x$ksmspr
    3 group by ksmchcls ;

    ————- ————- ————- ——– ———-
    95920 24 35152 R-freea 70
    6534108 4540 217000 R-free 33
    12344908 3980212 4189660 R-perm 3
    3977200 3977200 3977200 R-recr 1

    SQL> select ksmchcls, count(*) ,sum(ksmchsiz),min(ksmchsiz),max(ksmchsiz)
    2 from x$ksmsp group by ksmchcls
    3 order by 1 ;

    ——– ———- ————- ————- ————-
    R-free 37 7385484 4540 217000
    R-freea 78 96112 24 35152
    R-perm 3 12344908 3980212 4189660
    R-recr 1 3977200 3977200 3977200
    free 1454 7585916 20 3341184
    freeabl 14995 41681600 16 334348
    perm 32 47765824 44 3981312
    recr 24046 34350132 32 43692

    From the above two outputs, if you check/compare R-free (Reserved areas we find difference)

    37 chunks reported from x$ksmsp and
    33 chunks reported from x$ksmspr – reserved space

    4 chunks of memory extra reported (Total 851376 bytes ) ? try to check below out put

    SQL> select *
    2 from(
    4 from x$ksmsp
    5 where ksmchcls = ‘R-free’
    6 minus
    8 from x$ksmspr
    9 where ksmchcls = ‘R-free’
    10 )
    11 ;

    ———- —————- ——– ———- ——– ———- ——–
    1 free memory 2400007C 212844 R-free 0 00
    1 free memory 2440007C 212844 R-free 0 00
    1 free memory 2840007C 212844 R-free 0 00
    1 free memory 2880007C 212844 R-free 0 00

    Note – Good thing is that we did not find the differece reported in max and min chunk available in
    Sub heap pool of shared pool

    What could oracle would be doing. ?

    SQL> select * from v$sgastat where name = ‘KGH: NO ACCESS’;

    results in null.

    When i checked for the chunk pointer (2400007C) from previous output that resulted in one record

    2 from x$ksmsp
    3 where ksmchptr=’2400007C’;

    ——– ———- ———- ———- ———- —————- ——– ———- ——– ———- ——–
    2CFEB3D8 1147 1 1 4 free memory 2400007C 212844 R-free 0 00

    KSMCHDUR – 4 (4*212844) 851376 bytes = not included or not reported in x$ksmspr ?

    what could be the reason is this bug or some thing is missing in my understanding with concept.

    I justed utilized you script which fetches across the kernel tables of v$sgastat view
    for information which get utilized to understand.

    SQL> @C:\Users\Pavan\Desktop\tpt_public\tpt_public\sgastatx.sql free

    — All allocations:

    —————————— ———- ———-
    shared pool (1): 155194072 148
    shared pool (Total): 155194072 148
    — Allocations matching “free”:

    —————————— —————————————- ———- ———-
    shared pool (1): free memory 18072448 17.24
    ksunfy : SSO free list 2753792 2.63
    message pool freequeue 1154736 1.1
    kghx free lists 28728 .03
    kxfpSO qref freelists 240 0
    kxfpSO q freelists 240 0
    kglsim free heap list 204 0
    kglsim free obj list 204 0
    KTI freelists 44 0
    KTCTSNL freelists 44 0
    ksuloi: long op free list 16 0
    sim kghx free lists 4 0

    It would be helpful if you guide me on that I carry further R&D on things.
    Thanking you in Advance..

  10. Tanel Poder says:

    @Pavan Kumar N

    Yeah as I said during the session, I could have gone on for another four hours, but I think that’s enough detail. Yes shared pool reserved area is not one large area, but the 5% is taken for each shared pool extent, that’s why my 500kB allocation failed as the reserved area consisted of ~200kB chunks in separate extents. If you do a shared pool heapdump, you’ll see the placement nicely.

    Anyway, I won’t put much more time under this topic, that’s why there’s video. If you want more detail, you’ve got do research yourself ;-)

  11. Tanel Poder says:

    One thing what I’ll follow up some day is the discrepancy of free memory in X$KSMSP vs V$SGASTAT … whenever I’ll have the time …

  12. @Tanel Poder

    Thanks for providing the guidance and clue for proceeding further. I will carry forward from here my R&D, I hope your clues will follow up me to reach my goal.
    I you permit me, I would like to post across the process of “snapper” script(how it works) on my blog. So, that it would be helpful for new commers (developer’s) and DBA’s in order to build their own scripts based on their requirement.. It’s just a tutor to them

    Thanks for sparing your time in your busy schedule.. !!

  13. Tanel Poder says:

    @Pavan Kumar N
    Please do not upload my videos to your site. You’re welcome to post a link to my site though…

    I will create a Snapper TV show episode in the future too

  14. Ofir Manor says:

    thanks for the sharing this awesome session! it was quite fascinating, I learned a lot

  15. Haris says:

    Hi Tanel

    I am from Malaysia and I really appreciate the effort you have taken to produce such a very good, very informative Oracle Troubleshooting TV Show. This is really Awesome!!!!

    I hope and wish you continue sharing your research, knowledge, experience in Oracle Database. I learned a lot from your blog and it’s really benefit to oracle community as a whole.

    One request if you could simulate the steps to solve a performance issue such as oracle database hang or any other performance issues, the use of snapper and other scripts to troubleshoot and resolve it.

    Hope to see more of Oracle Troubleshooting TV Show. :)


  16. Tanel Poder says:


    I’ll probably not talk about hangs, crashes etc much in public videos, I’ve got to leave some stuff for my AOT seminars too!

  17. mdinh says:

    @Tanel Poder

    You are not a movie star!!! – Bad Speller.

    not is now

    I just saw this revisiting the site.

  18. sachin says:

    hello Tanel,
    what is US/EMEA ? so next time you do any hacking session, I wont miss

  19. Tanel Poder says:


    I arranged two events on the same day, one was aimed for US and EMEA time zone, another was for EMEA / Asia time zone

  20. Tanel,

    Congrats and wonderful job on your first Oracle TV show- sure to become a quick hit! Now maybe we can partner together to come up with an Oracle DBA and fitness tv show? Ya know, mix workouts for DBAs with performance tuning? Sounds odd but would be unique and fun! I figure we can demonstrate how to tune Oracle with different workouts and analogies?


  21. Kumar says:

    This is Excellent Troubleshooting Guide and Thank you very much for your guidence on this trouble shooting. This is the way of Material i am looking for such long time. Thank you very much once again for your such wonderful sessions.

  22. Pavel Slepushkin says:

    I was reviewing video(because I’m troubleshooting ora-4031 now) and there was a small bug in script ksmsp.sql. Script returns wrong size of buckets in column SIZE. To fix, you need to replace round(ksmchsiz/1024) to trunc(ksmchsiz/1024).
    Once again, thanks for the great video.

  23. Tanel Poder says:

    @Pavel Slepushkin
    Hi Pavel,

    Yep – I remember noticing it myself, but I probably forgot to fix it … thanks for reminding me!


  24. Kostas Hairopoulos says:

    Hi Tanel. it seems that the first episode cannot be downloaded as podcast from iTunes. Second episode is very good, congratulations

    Best Regards

  25. Tanel Poder says:

    @Kostas Hairopoulos
    Yeah I think I enabled it just before uploading the 2nd episode…

  26. blues says:

    hi,tanel poder,where is the Episode 2?thanks

  27. blues says:

    hi,dear tanle poder ,
    i want learn this video,but i can not find a valid link,including on vimeo,itunes and enkitec,please upload a new one?thank you very much.

Leave a Reply

Your email address will not be published. Required fields are marked *