 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / February 2006Many applications in Compiling status
Environment: DB2 V7 + FP 14 running Linux. One of my systems is performing poorly. CPU is 100% busy. I activated an Event Monitor and the prepared statements sent by applications are taking very long to execute. I can see a bottleneck during the PREPARE. When the application issues an OPEN, response is quick. When I run a "list applications show detail", I can see dozens of applications in Compiling status. Diaglevel 3, DFT_QUERYOPT = 5, DFT_DEGREE= ANY Can anyone suggest anything that can be looked at in order to fix this issue? Thanks, > Environment: DB2 V7 + FP 14 running Linux. > [quoted text clipped - 14 lines] > > Thanks, (I don't think you have the same problem but just in case...) I've seen this kind of problem when some tables were changed and automatic rebinding occured. Rebinding the packages solved the problem. I just executed a db2rbind DB all and my system is now stable again. CPU consumption is back to reasonable levels. Thank you for your fast response and great hint. -Michel Unbelievable. After running smooth for approx 20 minutes, my system is again very slow. Hundreds of applications in Compiling status. And CPU again at 100%. Any ideas ? Thanks > Unbelievable. After running smooth for approx 20 minutes, my system is > again very slow. [quoted text clipped - 4 lines] > > Thanks Are you swamping your package cache? Check your snapshots! Cheers Serge  SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab >> Unbelievable. After running smooth for approx 20 minutes, my system is >> again very slow. [quoted text clipped - 6 lines] >> > Are you swamping your package cache? Check your snapshots! select PKG_CACHE_NUM_OVERFLOWS, PKG_CACHE_SIZE_TOP, PKG_CACHE_LOOKUPS, PKG_CACHE_INSERTS from table(snapshot_database(cast(NULL as varchar(250)),-1)) as t; SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab Serge, thanks. Unfortunately I am still running DB2 V7. Here is what I can see from database snapshots: Package cache lookups = 11860267 Package cache inserts = 1267129 Package cache overflows = 3 Package cache high water mark (Bytes) = 24602066 Application section lookups = 23711267 Application section inserts = 11558385 It seems like this problem started to happen after we have tried to bind (@db2ubind.lst @db2cli.lst) from a V8 client to this V7 servers. During this process, we got many warning. After that, the server was never the same. I am not sure if this has something to do with the server behaving so sluggish. Any other snapshots or parameters I can take a look at? Thanks > Serge, thanks. Unfortunately I am still running DB2 V7. > [quoted text clipped - 16 lines] > > Thanks How big is your package cache? Seems like you need to get it up past 8000 (*4k). Do you really have 1.2M Distinct SQL Statements? Or are you feeding in constants? Take a look at the dynamic SQL snapshot Cheers Serge SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab My package cache is currently at 6000. MAXAPPLS=1200. I was reading the documentation and it recommends MAXAPPLS*8. I will change that and see what happens. No, the applications send prepared statements and feeds constants. The number of distinct statements executed in this DB is certainly small. Thanks, > My package cache is currently at 6000. MAXAPPLS=1200. > [quoted text clipped - 3 lines] > No, the applications send prepared statements and feeds constants. The > number of distinct statements executed in this DB is certainly small. You said this is V7? If it were V8.2 I'd wonder if you bound CLI with REOPT(ALWAYS) Take a look at the dynamic SQL snapshot and check number of executions, etc... Also verify that you really get the parameter markers in the text. Cheers Serge SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab I changed the packagesize. The problem is still happening. I just took a dynamic SQL snapshot. There are 5k distinct "statement text" entries. Another interesting detail: there are only 94 statements that use "?". Seems like my app is using a lot of static statements. I also looked at other counters: # Very few statements had a 'worst preparation' time (above 1000ms). In average, <10 ms. # Only two of these statements had more than 1 number of compilations. I am still confused. Why have this problem begin after a simple V8 client bind ... Thanks again. > I changed the packagesize. The problem is still happening. > [quoted text clipped - 10 lines] > I am still confused. Why have this problem begin after a simple V8 > client bind ... Hmph.. V7 server you said... Is this AIX? kill -36 <busyprocessid> should tell you where the beast is hanging out... But this turns quickly into open a PMR IMHO Cheers Serge SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab This is a Linux server. Is there any similar kill signal in Linux ? 'kill -l' shows SIGRTMIN+4. All db2sysc processes are consuming a small chunk of CPU. But as a group, cpu usage is very high. The problem really comes and goes. System is stable for 20 minutes, then busy for some time. I heard IBM discontinued support for V7 servers. Is it still possible to open a PMR in this situation ? We do have a support contract. Thanks, > This is a Linux server. Is there any similar kill signal in Linux ? > 'kill -l' shows SIGRTMIN+4. [quoted text clipped - 7 lines] > I heard IBM discontinued support for V7 servers. Is it still possible > to open a PMR in this situation ? We do have a support contract. You mean you have an extended support contract? Sure, of course you can open a PMR. Not to pur salt into the wound, but: DB2 V8 has a whole slew of cool problem determination features which would help here. Especially the db2pd tool (aka Informix onstat) woudl be useful. Cheers Serge SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab Yes, DB2 migration is being taken seriously now. I could open a PMR today, but it will take some time until IBM provides support (I totally understand this). I need to study a workaround for the case ASAP. I have ran another event monitor on the DB and here is another interesting detail: OPERATION EXECUTIONTIME ------------------------------ -------------------- Close 16.318283 Prepare 2.536525 Close 2.053398 Prepare 2.775977 Close 2.737252 (...) Open and Execute statement operations are fast. Close and Prepare are slow. Why would DB2 take so long to run such simple operations ... seems like a nightmare. > Yes, DB2 migration is being taken seriously now. I could open a PMR > today, but it will take some time until IBM provides support (I totally [quoted text clipped - 17 lines] > Why would DB2 take so long to run such simple operations ... seems like > a nightmare. Careful, I think you are falling victim to a red herring here. If I'm not entirely mistaken the time you see is the entire time to process the cursor, including time spent in the application. With cursors it's best to look at USER and SYSTEM CPU time only. Again CLOSE would include OPEN and all the FETCH time. Cheers Serge SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab >> Yes, DB2 migration is being taken seriously now. I could open a PMR >> today, but it will take some time until IBM provides support (I totally [quoted text clipped - 23 lines] > With cursors it's best to look at USER and SYSTEM CPU time only. > Again CLOSE would include OPEN and all the FETCH time. BTW, could it be your system is thrashing? Is USER or SYSTEM CPU high? What about swapping? Cheers Serge SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab Memory is not constrained, but CPU usage is heavy. I will try to reorg and runstats my most often used tables and see if it gets any better. Thanks for the help Serge. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.