AskDba.org Weblog » Archive for April 2008
Application Design and ORA – 4031
Much has been written on designing good application (which uses bind variables) so as to have better performance and avoid shared pool memory issues. Tom Kyte explains this on one of his post consequences of not using bind variables If you do not use bind variables and you flood the server with hundreds/thousands of unique queries you will -run dog slow -consume a ton of RAM (and maybe run out) -not scale beyond a handful of users, if that among other really bad side effects. This is very true and have observed it myself many times. But today we will see one more kind of bad application design which I came across working on a ORA – 4031 issue. Environment was already using CURSOR_SHARING=SIMILAR (application developers were not ready to change code, so it was Kind … Read entire article »
Physical Corruption: ORA-1578 -Part 1
ORA-1578 is indication of physical block corruption. Oracle computes an internal checksum before fetching the block from disk, when it is about to fetch the block it reads the block header and compare the checksum stored in it. If the checksum mismatches we get ora-1578 error.It indicates that a block is corrupted at hardware level i.e at OS. This error will always come whenever the corrupted block is read by Oracle. Reasons for ora-1578: 1. Bad sectors in the Storage Disk Drive.2. Formatting of blocks At OS level.( Zeroed out disk blocks).3. some corruption in underlying hardware which is making block unreadable. How to collect information for ora-1578: Error Text: ORA-01578: ORACLE data block corrupted (file # 14, block # 38976) The block # 38976 shows the corrupted block number and the file # 14 indicatesthe reletive … Read entire article »
Deciding “PROCESSES” parameter value
Many times we observe following error which corresponds to low setting of processes parameter. $ oerr ora 20 00020, 00000, “maximum number of processes (%s) exceeded” // *Cause: All process state objects are in use. // *Action: Increase the value of the PROCESSES initialization parameter. This is mostly seen in new database installation as the processes parameter is not taken into account. According to Oracle® Database Reference 10g Release 2 (10.2) Part Number B14237-03 PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes. The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. Therefore, if you change the value of PROCESSES, you should evaluate whether to adjust the … Read entire article »
ERRORSTACK- Oracle Debugging Event
One more post arising out of my frequent visits to Oracle forum Basically there was a question on “How to set events for ORA – 1652 errors” I had replied to the post but Aman asked me if I could provide any notes regarding Errorstack.I didn’t had one so I informed him that I will be posting on my blog ( Cheap Publicity of my blog ) Ok, this was the background to it, now coming to the main thing. Errorstack is used as debugging event for following reasons To extract Failing SQL statement To get Oracle Function call (to be used by Oracle) for interpreting cause for the error (e.g ORA 4030, ORA- 4031). It is also used in case of database hang scenarios. Refer to my earlier article … Read entire article »
Changing Database Characterset With Export/Import Utilities
Recently there was a thread on Oracle Forums regarding change of Database characterset from WE8ISO8859P1 to AL32UTF8. User wanted to upgrade his database from 9i to 10g and also change the database characterset. He was looking forward to use either Export/Import utilities or Database Upgrade Assistant for the same. There were few responses to this question with responses from few people (NLS being a dry topic for most of DBA’s ) To make this issue simpler, we will keep this problem to only change of Database characterset. Export/Import is considered to be a Straightforward approach but we should understand that this should not be the first step. As per Globalization Support Guide, there are two steps to change the characterset 1)Data Scanning 2)Data Conversion Export/Import comes under second step, i.e Data … Read entire article »
Tweaking _Shared_pool_reserved_min_alloc and ORA – 4031
Here comes one more article dedicated towards diagnosing ORA- 4031 and related parameters. While googling on ORA-4031 error or working on TAR with Oracle Support you will most likely hit this parameter “_Shared_pool_reserved_min_alloc “ and find a advice for altering this parameter to 4000 or so. And many times you go and implement it as this advice has come from Oracle support or you find it on one of your Favourite Oracle DBA’s site. But how many times have you exactly gone ahead and tried to understand the validity of this suggestion. Oracle Shared pool is managed by Heap Memory manager and is divided into memory chunks (Can visualize as blocks in tablespace) and the free memory chunks are managed by memory freelists. When a SQL statement is parsed in shared … Read entire article »
Subscribe To Feed
License
AskDba.org Forum
- Mahesh.G on "DR rebuild- Data restore issue" March 11, 2010
- Saurabh Sood on "DR rebuild- Data restore issue" March 11, 2010
- sunkkuma on "DR rebuild- Data restore issue" March 11, 2010
- Amit Bansal on "DB and Listener availability monitoring script" March 11, 2010
- animesh on "DR rebuild- Data restore issue" March 11, 2010
Recent Comments