Sergey, My $0.02 below: (comments below and only for relevant stats, some of which have been re-arranged):
NAME VALUE ---------------------------------------------------------------- --------- opened cursors cumulative 1072 opened cursors current 11 * Significant number of cursors closed in a short time, probably due to a *lot* of recursive SQL. This is borne out by the ratio of 'user calls' to 'recursive calls' below. Keep in mind that index block splits are recursive, so if you are inserting, updating or deleting data that re-arranges indexes, this could be explained away. user calls 341 recursive calls 198492 recursive cpu usage 4089 CPU used by this session 696253 * Recursive CPU usage is insignificant compared to other CPU usage, and that's another indication that recursive SQL is not the problem (if this is against indexes). enqueue requests 11878 enqueue releases 11876 * You don't seem to have enqueue waits, so you didn't get into a lock wait situation. session logical reads 77233609 = db block gets + consistent gets db block gets 2642119 consistent gets 74591490 physical reads 148822 db block changes 3005410 consistent changes 141 redo entries 1506007 redo size 1.184E+09 = approx 1.1 Gb!!! physical writes 2728 physical writes non checkpoint 2728 * You performed 148822 physical reads, but 77233609 logical reads!! This, along with the fact that you are performing a large number of consistent gets rather than db block gets implies that are reading and updating the same blocks repeatedly in a loop. This is most certainly due to inefficient and incorrect use of SQL. Tuning is imperative, and improvement will be measured in quantums rather than deltas. The number of writes is negligible compared to reads, supporting the above observation. The redo is based on the number of db block changes - if the same block is changed multiple times, the amount of redo goes up accordingly. This too supports the observation. However, if you have a large number of indexes that change (as a result of updates), this could also produce the same phenomenon - large number of logical reads and same-block updates (and recursive SQL). physical reads direct 3594 physical writes direct 2728 * Do you have Quick I/O (or raw) on some files? table scans (short tables) 210918 table scans (long tables) 36 table scan rows gotten 798264962 table scan blocks gotten 71788386 table fetch by rowid 1074164 table fetch continued row 12 * You seem to be performing almost all your reads via table scans of small tables (20 blocks or lesser). In this case, you aren't performing Indexed reads, which *may* be good (if these are small tables). However, it looks like you are scanning short tables repeatedly (as supported by observations above). leaf node splits 4018 branch node splits 9 * This definitely points to Index block manipulation as a result of updates or inserts. Having said all that above, I hazard the following guesses: * You are inefficiently scanning small tables, while continually updating a limited set of blocks repeatedly. This points to ineffecient coding. SQL Tuning will go a long way! * You are probably updating columns (or inserting rows) that support indexes. You should look at whether these indexes are really required. If indexes are not used (as in many full table scans), why are they present? Perform a trace and TKPROF it. If you post the output, we could help further. My feeling is that 'Database' or 'Instance' level tuning via init parameters isn't going to get you much mileage. However, SQL tuning certainly will! Have a nice weekend, all! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Fear is the darkroom where Evil develops your negatives. Wanna break free of fear? Click on 'http://www.needhim.org' ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
