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:
- 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? ;-)
- You probably should view this video in full screen (otherwise the text will be tiny and unreadable)
- 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 !!!
- You can download the scripts and tools used in the demos from http://tech.e2sn.com/oracle-scripts-and-tools/
- 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:
http://tanelpoder.blip.tv
Enjoy!
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…
I got it working on Ipad as well. Just go to http://tanelpoder.blip.tv?skin=blipnew 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!
You are not a movie star!!!
@mdinh
…yet! ;-P
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! :-)
… I just upgraded my blip.tv 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
Awesome!!!
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!
wonderfull,i did not watch yet but congrats for new service :)
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;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – 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
instance
__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
pool
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
SGA
_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 ;
KSMCHCLS COUNT(*) SUM(KSMCHSIZ) MIN(KSMCHSIZ) MAX(KSMCHSIZ)
——– ———- ————- ————- ————-
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 ;
COMPONENT CURRENT_SIZE GRANULE_SIZE CURRENT_SIZE/GRANULE_SIZE
—————————————————————- ———— ———— ————————-
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;
6501171/37
———-
175707.324
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 ?
Further,
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 ;
SUM(KSMCHSIZ) MIN(KSMCHSIZ) MAX(KSMCHSIZ) KSMCHCLS COUNT(*)
————- ————- ————- ——– ———-
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 ;
KSMCHCLS COUNT(*) SUM(KSMCHSIZ) MIN(KSMCHSIZ) MAX(KSMCHSIZ)
——– ———- ————- ————- ————-
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(
3 select INST_ID,KSMCHCOM,KSMCHPTR,KSMCHSIZ,KSMCHCLS,KSMCHTYP,KSMCHPAR
4 from x$ksmsp
5 where ksmchcls = ‘R-free’
6 minus
7 select INST_ID,KSMCHCOM,KSMCHPTR,KSMCHSIZ,KSMCHCLS,KSMCHTYP,KSMCHPAR
8 from x$ksmspr
9 where ksmchcls = ‘R-free’
10 )
11 ;
INST_ID KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
———- —————- ——– ———- ——– ———- ——–
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
SQL> SELECT *
2 from x$ksmsp
3 where ksmchptr=’2400007C’;
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
——– ———- ———- ———- ———- —————- ——– ———- ——– ———- ——–
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:
SUBPOOL BYTES MB
—————————— ———- ———-
shared pool (1): 155194072 148
shared pool (Total): 155194072 148
– Allocations matching “free”:
SUBPOOL NAME SUM(BYTES) MB
—————————— —————————————- ———- ———-
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 basics..so that I carry further R&D on things.
Thanking you in Advance..
@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 ;-)
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 …
@Tanel Poder
Tanel,
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.. !!
@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
thanks for the sharing this awesome session! it was quite fascinating, I learned a lot
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. :)
-Haris
@Haris
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!
@Tanel Poder
You are not a movie star!!! – Bad Speller.
not is now
I just saw this revisiting the site.
@mdinh
got it :-)
hello Tanel,
what is US/EMEA ? so next time you do any hacking session, I wont miss
@sachin
I arranged two events on the same day, one was aimed for US and EMEA time zone, another was for EMEA / Asia time zone
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?
Cheers,
Ben
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.
Hi.
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.
@Pavel Slepushkin
Hi Pavel,
Yep – I remember noticing it myself, but I probably forgot to fix it … thanks for reminding me!
Tanel
Hi Tanel. it seems that the first episode cannot be downloaded as podcast from iTunes. Second episode is very good, congratulations
Best Regards
@Kostas Hairopoulos
Yeah I think I enabled it just before uploading the 2nd episode…