Re: Shutdown Immediate hangs
DENNIS WILLIAMS wrote: We have been running Oracle 8.1.6 unchanged for several years. Within the past 3 days, our cold backup scripts have had a shutdown immediate hang. In the alert log the message is: Shutting down instance (immediate) Sun Mar 23 00:09:10 2003 SHUTDOWN: waiting for active calls to complete. Normally the message is: Sun Mar 16 00:04:06 2003 Shutting down instance (immediate) Sun Mar 16 00:05:11 2003 ALTER DATABASE CLOSE NORMAL Our immediate suspicion is that someone has implemented an application this last week that connects to the database in a more active manner than we've experienced before. Does anyone have any idea what I should look for (aside from asking each developer: What did you do last week?. Since 3 systems have been affected, we are wondering if a process using a database link could cause a problem like this. Any ideas appreciated. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Dennis, check out if you had any active oracle jobs at the moment of shutdown. Sometimes they may be the cause of hang. -- Kind regards, Edward Shevtsov -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Edward Shevtsov INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Named users! - final clarification
AFAIK minimum of 25 for EE since september Regards, Ed -Original Message- From: [EMAIL PROTECTED] [mailto:root;fatcity.com] On Behalf Of Ron Rogers Sent: Tuesday, October 22, 2002 4:34 PM To: Multiple recipients of list ORACLE-L Subject: Re: Named users! - final clarification The last quote I received had a minimum of 10 named users. Ron ROr mª¿ªm [EMAIL PROTECTED] 10/22/02 07:58AM Thanks folks for your replies on my initial query. Just to clarify by example, how many named user licences would I need for a database with following users. Forgetting minimum purchase requirements I suspect it's 3 is this correct? USERNAME -- SYS SYSTEM OUTLN DBSNMP BLOGSJ LARSONG GATESB - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Aout plan stabilty matching.
Chuan, If I remember right, you're supposed to turn the outlines on and then run your sikvel or procedure to catch the offensive statement(s). Regards, Ed Hi, All, From Oracle Doc: if the SQL text of the incoming statement exactly matches the SQL text in an outline in that category, then Oracle considers both texts identical, and Oracle uses the outline. Oracle considers any differences a mismatch. How could I ensure the incoming SQL text exactly match the SQL text in an outline? If I fish out an offensive SQL from library cache by some scripts in SQL*Plus, is this offensive SQL text identical to the incoming SQL text? Supposed this SQL text is extracted from stored procedure. Appreciated your experience. Chuan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
RE: Row Length of Index ?
Hi Vivek, You should take into account the space for storing rowids...and index can be compressed. validate index your_index; Select lf_rows_len from index_stats; Regards, Ed How can it be Obtained ? Does the principle which Applies to Table also apply to index ? SQL select AVG(nvl(vsize(1st Key Field),1)+ nvl(vsize(2nd Key SQL Field),1)+ ...) FROM Table Containing respective Index; Where (1st Key Field,2nd Key Field) give the Definition of the Index -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
RE: Disabling indexes - temporarily
Title: Marul, have a look at table partition and ALTER TABLE EXCHANGE PARTITION WITH TABLE statement. Using the combination of these approaches you're able to limit index rebuilding with a few partitions if your indexes are local. At least you will be able to separate I/O. Regards, Ed -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Marul MehtaSent: Monday, September 02, 2002 2:38 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Disabling indexes - temporarily Thanks Naveen, Lets forget aboutthe statistics and performance, but I have such type of requirenment than is there any way out ? Marul. - Original Message - From: Naveen Nahata To: Multiple recipients of list ORACLE-L Sent: Saturday, August 31, 2002 11:58 PM Subject: RE: Disabling indexes - temporarily Firstly, you are only inserting 100-400 records daily, which is not a big deal. Even if there was a way to stop the indexesfrom gettingupdated, it won't increase the performance by a noticable amount. Secondly, there isno way(asfar as i know)to make the indexes READ-ONLY with the table in READ-WRITE mode. Thirdly, rebuilding 20 indexes on a table with 1 million record will take a long time, in comparison updation by 100-400 records is nothing. It neither feasible nor advisable. Naveen -Original Message-From: Marul Mehta [mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 11:08 PMTo: Multiple recipients of list ORACLE-LSubject: Disabling indexes - temporarily Hi all, Need to know if the following is possible in Oracle(any version):- I have a table of around (a) 30 Columns (b) 20 out of 30 are indexed (c) around 1 million (1,000,000) records. Most of the time there will be heavy reads (select queries) on this table except for some 100-400 records to be inserted in a day. The newly inserted records will not be selected by the queries for the next 24 hours (this is based on some business logic), thats for sure. Now the problem is when ever a record(s) is inserted the entire bunch of indexes is updated/rebuild by the Oracle which considerably slows down the throughput of the system during that period of time (until all indexes are updated). Can we have a solution whereby indexes should not be updated when a record(s) is inserted, because I know that these records will not be the part of the query for the next 24 hrs. The indexes will be re-built manually/scheduled during the off-peak hours once a day. In this way, the next day, new records inserted a day before will be ready to be fetched by the queries. Note- I can't put my indexes offline not for a single minute during peak hours. Any clues? TIA, Marul.
RE: Resizing of redo log
Hi Pawan, You should create a new redo log group with a bigger size then drop an old redo group with INACTIVE status (v$log) and repeate this procedure once again for each substituted redo log group. You can control status of a substituted redo log by using ALTER SYSTEM SWITCH LOGFILE; Regards, Ed How is it possible to increase the size og online redo log files Regards Pawan Dalmia -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pawan Dalmia 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
RE: Problem in creating DB Link
Hi Aleem, Just try to create a simple link without PUBLIC and SHARED keywords if you don't really need them. Login as user B and issue the following command: CREATE DATABASE LINK mydblink CONNECT TO userA IDENTIFIED BY password_of_userA USING 'abc'; If you get ORA-02019 then check if the correct service name (abc in that case) exists in your tnsnames.ora on server B. You can use TNSPING abc to ensure that your service name points remote db properly. Regards, Ed Hi, I have created a Database Link under the scenario detailed below: but when I try to access tables through the link it gives error ORA-02019: connection description for remote database not found. If I try to drop the link it gives error ORA-02024: database link not found TIA! Aleem This is the scenario: We have two db servers running on our LAN, for simplicity 'A', connect string 'abc' and 'B', connect string 'xyz'. Some of the tables on A in schema UserA1 are required (read only) by schema UserB1 on server B. As suggested by someone I tried to create a dblink. Using SQL*Plus connected as 'System' to Server B (since it requires to access tables from the other db) and applied the following command. CREATE SHARED PUBLIC DATABASE Link my_link CONNECT TO UserA1 IDENTIFIED BY abc AUTHENTICATED BY UserB1 IDENTIFIED BY def USING 'abc' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
RE: How to increase maxextents of LOBSEGMENT?
Title: Hi Jonny, try this: select table_name, column_name from dba_lobswhere segment_name = 'SYS_LOB022841C3$$' alter table your_table_name modify lob (column_name)(storage (maxextents500)); Regards, Ed -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Jonny DelmontSent: Monday, August 26, 2002 3:48 PMTo: Multiple recipients of list ORACLE-LSubject: How to increase maxextents of LOBSEGMENT? Dear list, How to increase maxextents of LOBSEGMENT? SQL alter LOBSEGMENT TRST.SYS_LOB022841C3$$ storage (maxextents 1022);alter LOBSEGMENT TRST.SYS_LOB022841C3$$ storage (maxextents 1022) *ERROR at line 1:ORA-00940: invalid ALTER command TIA, Jonny Get a bigger mailbox -- choose a size that fits your needs.http://uk.docs.yahoo.com/mail_storage.html
RE: Problem in creating DB Link
Aleem, The keyword PUBLIC is not required and that's the difference between creating public and private db links. Have a look at SQL Reference Giude. If my memory serves me right Oracle don't support describe for a remote table. It's OK that Oracle append us.oracle.com to the name of your db link. Have a look at parameter DB_DOMAIN, it should be described in your init.ora and sqlnet.ora files. Default value is 'WORLD'. Regards, Ed Thank you Edward, As suggested, I have tried the following command (since PUBLIC is required) CREATE PUBLIC DATABASE LINK my_link CONNECT TO UserA1 IDENTIFIED BY passwordA USING 'abc' The Server B from where I am issuing this command, connects to the Server A using the same connect string 'abc'. After this connection I am able to view tables, data etc. But the command describe table1@my_link seems to be liking the error ora-02019: connection description for remote database not found. The view all_db_links lists this link but appends us.oracle.com to my_link under db_link column. Any more suggestions For dropping the link, I was missing PUBLIC keyword Aleem -Original Message- Sent: Monday, August 26, 2002 4:03 PM To: Multiple recipients of list ORACLE-L Subject: RE: Problem in creating DB Link Hi Aleem, Just try to create a simple link without PUBLIC and SHARED keywords if you don't really need them. Login as user B and issue the following command: CREATE DATABASE LINK mydblink CONNECT TO userA IDENTIFIED BY password_of_userA USING 'abc'; If you get ORA-02019 then check if the correct service name (abc in that case) exists in your tnsnames.ora on server B. You can use TNSPING abc to ensure that your service name points remote db properly. Regards, Ed Hi, I have created a Database Link under the scenario detailed below: but when I try to access tables through the link it gives error ORA-02019: connection description for remote database not found. If I try to drop the link it gives error ORA-02024: database link not found TIA! Aleem This is the scenario: We have two db servers running on our LAN, for simplicity 'A', connect string 'abc' and 'B', connect string 'xyz'. Some of the tables on A in schema UserA1 are required (read only) by schema UserB1 on server B. As suggested by someone I tried to create a dblink. Using SQL*Plus connected as 'System' to Server B (since it requires to access tables from the other db) and applied the following command. CREATE SHARED PUBLIC DATABASE Link my_link CONNECT TO UserA1 IDENTIFIED BY abc AUTHENTICATED BY UserB1 IDENTIFIED BY def USING 'abc' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet
Re: Index Full Scan -- Strange Issue
Hi Ian, Little correction. *Sometimes* index full scan (IFS) also uses multiblock IOs. In this case number of blocks is controlled with _db_file_noncontig_mblock_read_count. If my memory servers me right the default value is 11 for 8.1.7 on Solaris. You can easily check out this by using 10046 event, level 8. My assumption is that IFS may use multiblock IOs for reading leaf blocks and it always use single block IOs for reading branch blocks. I said may because I believe it somehow depends on index structure and on the other hand IFS *must* preserve index order. Regards, Ed You are right Fast Full Scans use multiblock IO. Other index scans do not. That had slipped by me. So my speculation is moot. If it was an FFS, I could come up with a scenario where such a path would be better than range scan. But that's moot, and I'm pretty sure such scenarios are beyond Oracle's optimizer. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: SOFT PARSE RATIO?
Seema, I believe you can reduce that ratio by using session_cursors Regards, Ed Hi In our database I found SOFT PARSE RATION is 62% which is lower than normal.What could be problem and how to correct this problem?Please suggest. Thx -Seema -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: SOFT PARSE RATIO?
Thanks for the correction and for your paper Regards, Ed The parameter is really called session_cached_cursors and it doesn't actually reduce the soft parses - it just decreases the serialization overhead of repeated soft parses within the same session. It's all in my paper on OTN. Thanks, Bjørn. On Tuesday 12 March 2002 14:58, you wrote: Seema, I believe you can reduce that ratio by using session_cursors Regards, Ed Hi In our database I found SOFT PARSE RATION is 62% which is lower than normal.What could be problem and how to correct this problem?Please suggest. Thx -Seema -- Bjørn Engsig, Miracle A/S http://MiracleAS.dk -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Oracle on OS/390
Hi List, a colleague of mine asked me how to setup a listener on OS/390. I've never seen this platform (very rare in Russia). I supose this is kind of a mainframe system. So I have 2 questions to ask: 1. I'm not sure Oracle support listener on mainframes. Does it exist on OS/390? 2. If so, where can I view its settings? TIA Regards, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: Old Chestnut: Tablespace Fragmentation
Bill, I believe in that case your query will spend most time on data transfer and the percent of seeking time will be tiny provided that number of extents is reasonable. One thing to note: sizes of extents should be multiple of db_file_multiblock_read_count in order to minimize number of I/O operations required for FTS. Regards, Ed I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents randomly allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: Old Chestnut: Tablespace Fragmentation
Bill, I believe in that case your query will spend most time on data transfer and the percent of seeking time will be tiny provided that number of extents is reasonable. One thing to note: sizes of extents should be multiple of db_file_multiblock_read_count in order to minimize number of I/O operations required for FTS. Regards, Ed I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents randomly allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: Urgent: Dictionary - Missing Column
Sundeep, if my memory serves me rigth that bug was fixed with 8.1.7.2 patch. Have a look at the bug list. Regards, Ed We have an issue with compiling PL/SQL code. The compilation gives PLS-00201 identifier must be declared on a certain column which exists in the table (a describe or select from the table works). An export and import of our test db made the problem go away. We had the same bug happen again in QA and again export and import set it right but we can't do this in production. Our environment is 8.1.7 on HP-UX11. Has anyone see this bug or knows a workaround? TIA = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: Performance problem .... HELP :-(
Title: Performance problem HELP :-( Hi Ian, take a careful look at fragmentation of their indexes and possible chained rows in the tables. Probably RATE_SCHEDULE_LINK_PK is a good start point Also the cardinality(estimated numbers of output rows for each step) may confuse you if their statistics is lost or obsolete for some objects Regards,Ed - Original Message - From: Biddell, Ian To: Multiple recipients of list ORACLE-L Sent: Wednesday, December 19, 2001 1:50 PM Subject: Performance problem HELP :-( Hi all, Hoping someone can shed some light on a problem I have. We a particular cursor in a batch program running in production at a client site which has suddenly decided to work really badly. The program hasn't been changed but I think the customer has done some sort of reorg on the database. I traced the program on their server and also on a copy of the database on our server (our copy taken before the reorg) As can be seen from the tkprof output from a trace on the program for about an hour theirs does a lot of buffer IO for few rows returned compared to ours. The execution path in the explain is the same but the row counts down the side are different. Does anyone have any idea why this would be happening or what further investigation I can do. All access is via PK so it should be flying like the second example.
Re: Max data file size on NTFS partition
Hi Ashley, Sorry, don't know exact answer, but the problem doesn't always come from OS side. There are *Oracle* limits on some platforms. It seems you use AUTOEXTEND=ON for your files. If so then you may encountered some bugs when your files had extended through size limits. At least there were some bugs in the past. I prefer disabling this option in order to get more control over db and add files when needed. All files are of equal size. Regards, Ed Hello to all my most favorite DBAs... Yes, believe it or not, I checked the archives, Metalink and Oracle documentation, but all come up with a different answer to my question. What is the max data file size for an NTFS partition on Advanced Server? I'm running Oracle 8i Release 2 8.1.6 Standard...soon to be Enterprise. The Oracle 8i documentation states 80EB on an NTFS partition, but I've seen reference to 4GB limits everywhere else. Quite different, wouldn't you say? The reason I'm asking the question is we keep running into errors. The most common were, write/open error block X invalid parameter passed and unable to extend file X. I would appreciate any help. I'm new, so please be gentle... Ashley -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: optimizer_mode=choose uses first_rows, or all_rows?
Hi Patrice, all_rows Regards, Ed Oracle8,8i Certified DBA and Zivanologist A developer asked me this question. Any idea which mode the CBO defaults to when stats exist on objects? TIA Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: Change PCTINCREAE for SYSTEM Tablespace
Ken, AFAIK 50 is default PCTINCREASE for SYSTEM. I believe there are no reasons to change it for SYSTEM. What's your goal? Do you want to decrease total size of the tablespace by setting PCTINCREASE a bit less? Regards, Ed Oracle8,8i Certified DBA and Zivanologist Is there any way that I can change the PCTINCREASE for the SYSTEM tablespace without recreating the DB? For some reason the person who created the DB I am working on set PCTINCREASE to 50 (or didn't did not include this parameter).I am using this DB for a data conversion so there is no software connected to it and in turn no users on it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: unused blocks BELOW HWM
Hi Gene, have a look at dbms_space.unused_space Regards, Ed Oracle8,8i Certified DBA and Zivanologist Hi all: Is there a way for me to see how many blocks under the HWM are unused? thanks Gene = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
internal error while flushing shared pool
Hi List, Oracle 8.1.7.2, Linux RH, 2.2-19 I'm trying to flush shared pool and getting internal error (see below). Has someone encountered something like this? Any ideas except calling support? What does ksedmp mean? Thanks in advance Regards, Ed *** SESSION ID:(51.52061) 2001-12-15 07:00:04.340 Exception signal: 11 (SIGSEGV) *** 2001-12-15 07:00:04.577 ksedmp: internal or fatal error Current SQL statement for this session: alter system flush shared_pool - Call Stack Trace - calling call entryargument values in hex location type point(? means dubious value) Cannot read string table section header in /lib/libm.so.6. Cannot seek to string table section header in /lib/libm.so.6. Cannot seek to string table section header in /lib/libm.so.6. Cannot seek to string table section header in /lib/libm.so.6. Cannot seek to string table section header in /lib/libm.so.6. Cannot read string table section header in /lib/libm.so.6. ksedmp()+142 CALL ksedst()+0 ssexhd()+198 CALL ksedmp()+0 0 ? sigaction()+409 CALLr killpg()+84 CALLsssexhd()+0 kqrfrc()+45 CALL kqrpfr()+0 kghfrunp()+3089 CALLr 2B1B3D40 ? 2B1B3D40 ? 2B1B3D40 ? kghfsh()+210 CALL kghfrunp()+0 0 ? 970E58C ? 0 ? 4C986360 ? 849A449 ? 96B9E80 ? 7FFFBC78 ? kkyasy()+624 CALL kghfsh()+0 opiexe()+8900CALL kkyasy()+0 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: Database link
Hi Witold, if you use oracle replication you're limited to use global_name=true I don't know any other restrictions. Regards, Ed db_domain in init.ora is commented out in both cases. I also set it to: db_domain = but it didn't make any difference. Setting GLOBAL_NAMES to false helped but someone mentioned that it is not a good practice. Can anybody explain little bit why? Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: DANP
Hi Lee,
if my memory serves me right it's somehow connected with db_block_size (a
bug I suppose).
Your db_block_size is less than 8K isn't it?
Regards,
Ed
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, December 10, 2001 2:55 PM
All,
Oracle 8.0.5.0.0 on Tru64 Unix v4.0f
Anyone seen anything like this before ?? I've never seen a negative number
before. I'm assuming its a bug/feature
Regards
Lee
TSPACE RBS STATUSINITK NEXTK SIZEK OPTK
HWMK
MINX NUMXMAXX SHRINAVACTK
-- --- --- --- - - -
--- - -
RBSRBS05ONLINE81920 81920 1064952
1064952
2 13 505 0 0
RBSRBS06ONLINE81920 81920573432
573432
27 505 0 0
RBSRBS07ONLINE81920
490952-1490952
2 33 505 0 0
RBSRBS08ONLINE81920 81920 1720312
1720312
2 21 505 0 0
SYSTEM SYSTEM ONLINE 56 56 392
392
25 505 0 0
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Edward Shevtsov
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).
Re: DANP
Sorry Lee, I made a mistake. I was looking through russian confs. It seems it appears when size of your rollback segment is more than 2Gb. Regards, Ed Thanks for the reply Ed, but my db_block_size is 8k. Regards Lee Hi Lee, if my memory serves me right it's somehow connected with db_block_size (a bug I suppose). Your db_block_size is less than 8K isn't it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: truncate or drop
Hi, there's an option REUSE STORAGE for TRUNCATE command Regards, Ed Both could be slowly if tables have many extents and you use extent management dictionary. However I think drop would be a little more expensive considering that not only it has to erase info from uet$ and fet$, also from basically c_obj# and others clusters. Regards. --- Ruth Gramolini [EMAIL PROTECTED] wrote: Hi Everyone! I am trying to empty a database with some very large tables so I can import it again. Which is faster, truncating or dropping the tables? Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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). = ENG. Christian Trassens Senior DBA [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +34-699240979 +34-649824704 __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: Database down
Hi Jeff, 1. the previous killed instance that still holds a shared memory segment ? 2. unappropriatedly large parameters of shared_pool and/or db_block_buffers ? Regards, Ed ORA-27123: unable to attach to shared memory segment SVR4 Error: 12: Not enough space Any ideas? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: Database down
Jeff, I had a similar situation last week. I would like to know what patch are talking about and what's your version of Oracle? Regards, Ed I discovered the trace file after restarting Oracle - so that process ID is no longer out there. I've also discovered that there is a specific patch for this problem, which appears not to have been applied. I will move forward with this, but would like to provide management with an explanation as to why we had problems now, after so many days of uptime. Thanks for your help. Jeff [EMAIL PROTECTED] 12/10/01 03:35PM I don't suppose you've run ps -ef | grep 29937 yet, just out of curiousity, have you? -Original Message- Just to follow up. I have the database backup. I am afraid however, that the problem is still floating around. Briefly, here is what occured: I restarted Oracle because our application was unable to connect. I could connect through svrmgrl, but a 'select sysdate from dual' just hung. So I brought the database down, and recieved the memory segment error. So we were having problems while the database was up. Here is a trace file dump that may be linked to the problem: PMON unable to acquire latch 80002060 modify parameter values possible holder pid = 58 ospid=29937 *** 2001.12.09.15.27.43.000 We added a third application server last week. Could that somehow be the source of these problems? This application has been running problem free for over 100 days. Thanks for your input Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Wiegard 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: buffer busy wait latch free ( cache buffer chain )
-- 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ARUN K C 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: Parsing count way to high
Ian, what type of optimizer do you use? Regards, Ed - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 06, 2001 8:50 AM Hi all, Just wondering if any one can help me here, I have an online server that has a number of services within it, just lots of Objects. But the response time is not what it should and after tracing it I got this: OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 878 53.85 53.66 0 0 0 0 Execute 10025 4.54 4.90 3501 14806 9854 Fetch 9694 55.90 57.32108 398540 0 9481 --- -- -- -- -- -- -- total20597114.29 115.88111 399041 14806 19335 So my Parse count is way to high, should be insignificant I would have thought. So I increased the maxopencursors and recompiled everything that makes up the server (.exe) But no joy. Is there something else I can look at or do? Thanks Ian -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Biddell, Ian 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: DISK LAYOUT RAID LEVEL
Hi Harvinder, If I were you I would - put OS and Oracle executables on a RAID1 volume with minimal raid block size - put redo log files in chess order on two dedicated RAID1 volumes (the odd redo groups locate on the first volume and the even redo groups locate on the second RAID1 volume). It seems 9Gb disks are more suited for that goal. I'm still not sure what raid block size should be choosen for these volumes. I assume it should be minimal and cover your average redo write size. - make a RAID1 volume for archive redo logs, let say 2x36 (depends on the volume of your archive logs) The most difficult decision I think is how to locate your index, data, rbs, temp and what stripe size to choose. In general it depends on your application. In your case I would prefer to have several RAID volumes in order to be able to balance disk loading on data and indexes. Levels RAID1+0 or RAID0+1 are more suitable here. There are several articles at Steve Adams' site www.ixora.com.au and good Gaja's article about RAID at www.quest.com I think 25-30% of RAM is a good start point for SGA. Regards, Ed Hi, We have to do benchmarks tests(scalability,performance) for our product and have machine with following configuration..We dont have experience for large size databases layout. It will be greatly appreciated if someone points out from his experience: 1) Which raid level to use for which files and on how many disks. 2) As we have 10 disks what file to place on which disk. 3) How much RAM to use for SGA.(this is database dedicated machine) Server - E3500 with 2 internal boot disks (18GB) 4 400MHZ/8MB ecache and 2 GB ram 2 I/O boards Arrays - HDS 9210 Fiber channel array with 10 36GB disks @ 10K RPM with 5.7 read and 6.5 write seek. Sun A5200 with 22 9GB Seagate drives @ 10KRPM with 5.4 read and 6.2 write seek. HDS is a hardware based array and the A5200 is software with Veritas VM Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
average log write size and stripe size
Hi List, I have two RAID1 volumes with stripe size 8K (according to the Compaq'sRAID Configuration Utility, butstrange term for mirroring though). These volumes are dedicated for storing online redo logs only. We use file system (ext2). The average log write size is about 12K. Will I get any benefits from performance point of viewif I increase stripe size up to 16K ? Thanks for your help. Regards, Ed
Re: Oracle 8i vs. 9i on linux.
Jesse, we're working on Linux. Stability is the first thing you should consider while choosing right version of Oracle for a production system (especially on Linux). I would do hara-kiri myself if my boss told me migrate on 9i now. Regards, Ed I was wondering if anyone had any thoughts about which version of either 8i or 9i would be better to run on Linux? 9i seems to support larger database files, but I'm wondering if I would be giving anything up to move from 8i. Any thoughts? Thanks! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: Configuring for Online Redo Logfiles
Hi Vivek, If I didn't worry about data safety I would choose redo logs on dedicated single disks. The reason is that usually the speed of LGWR's writes is more important for performance compared with speed of ARCH's reads. Regards, Ed Conor,List 1) Mirrored Redo Logs WRITES may have an Overhead of 15-20 % EXCERPT from Gaja's Doc One of the common myths with mirroring, is that it takes twice as long to write.But in many performance measurements and benchmarks, the overhead has been observed to be around 15-20%. 2) Mirrored Redo Logs READS are Twice as Fast than NON-Mirrored Considering BOTH the Above to POINTS , FINALLY Strictly w.r.t. performance of Online Redo Logs , WITHOUT any Consideration for Data Safety , Which would be Better ? Thanks -Original Message- From: Connor McDonald [SMTP:[EMAIL PROTECTED]] Sent: Thursday, November 15, 2001 3:15 PM To: Multiple recipients of list ORACLE-L Subject: Re: Configuring for Online Redo Logfiles An unmirrored disk will run faster (with respect to writes) than a mirrored one (not twice as fast since writes on a mirror are typically done in parallel). Then again - now you've got an unmirrored redo log...not a good idea. To get around this, you'd need to duplex it through Oracle - which pretty much gets you back to a mirrored redo log. hth connor --- VIVEK_SHARMA [EMAIL PROTECTED] wrote: EXCERPT from Implementing Raid On Oracle Systems by Gaja Krishna Vaidyanatha, Quest Software Inc. :- RAID 1 - Ideal for online and archived redo logs, Leaves the write-head at the location of the last write. On most systems, you will need 3 volumes for the online redo logs (for 3 groups) and 1 volume for the archived redo logs. If your database is in NOARCHIVELOG mode, you probably can get away with a single RAID 1 volume for your redo-logs. However, for databases in ARCHIVELOG mode you will need to further separate you Qs. Puttting an Online Redo log on a RAID1 Volume ( Volume Containing 1 Disk Mirrored to Another Disk ) versus putting the Same Online redo file on a Single Disk (NON-Mirrored) , which would give Better performance , Why ? -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: lack of memory
Connor, thanks for the reply. I'd thought about it. Unfortunately, it's quite difficult to catch the difference as number of users is not constant and they do different job (oltp queries, big reports). Thanks, Ed (Of course depending on the app), its seems odd that 4G is insufficient for 400 users. Maybe start having a look at the uga/pga stats for connected sessions and seeing what this adds up to. hth connor -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Bug 1367773
Hi List, does anyone have description (body) of bug # 1367773 Thanks in advance, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: lack of memory
Hi Deepak, I'm on Linux. I'm afraid it's not accurate calculation because 'processes' parameter just get you upper limit on number of connections and a session only keeps its 'sort_area_size' untill a sort operation completes. Does pmap utility exist on Linux? I'll check it tomorrow (i'm at home now). Thanks for your reply. Ed Edward, how about checking your processes parameter and then multiplying that # with your sort area size .. thats would give you a good idea about the size of your PGA . also i guess there is a 250 K overhead per connection so you need to add that as well. for more accurate information, yopu could run a pmap for all local connections (connection coming from app) and then grep for the value besides the stack label to get exact PGA on a per connection basis. This assumes you are on unix..how big is your SGA? Deepak --- Edward Shevtsov [EMAIL PROTECTED] wrote: Connor, thanks for the reply. I'd thought about it. Unfortunately, it's quite difficult to catch the difference as number of users is not constant and they do different job (oltp queries, big reports). Thanks, Ed (Of course depending on the app), its seems odd that 4G is insufficient for 400 users. Maybe start having a look at the uga/pga stats for connected sessions and seeing what this adds up to. hth connor -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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). __ Do You Yahoo!? Find the one for you at Yahoo! Personals http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: Configuring for Online Redo Logfiles
Hi Vivek, From performance point of view a single disk will be a little faster as there is a small overhead associated with the process of mirroring to the second member of the RAID1. Regards, Ed EXCERPT from Implementing Raid On Oracle Systems by Gaja Krishna Vaidyanatha, Quest Software Inc. :- RAID 1 - Ideal for online and archived redo logs, Leaves the write-head at the location of the last write. On most systems, you will need 3 volumes for the online redo logs (for 3 groups) and 1 volume for the archived redo logs. If your database is in NOARCHIVELOG mode, you probably can get away with a single RAID 1 volume for your redo-logs. However, for databases in ARCHIVELOG mode you will need to further separate you Qs. Puttting an Online Redo log on a RAID1 Volume ( Volume Containing 1 Disk Mirrored to Another Disk ) versus putting the Same Online redo file on a Single Disk (NON-Mirrored) , which would give Better performance , Why ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
lack of memory
Hi List, We use a server with 4Gb memory on Linux kernel 2.2.19, Oracle 8.1.7.0 dedicated mode. It's an OLTP system with about 450 users. About 2 months ago I initiated gradual migration of our sql code to use bind variables instead of literals because we had problems with shared pool's fragmentation and strong contention on shared pool and library cache latches. The 90% of sql is accumulated on the client side (BDE+Delphi). I cut 150M from shared pool and planned cut it down further as we get results from the migration. Despite that now we have lack of memory. It seems now user processes consume more memory. We can't increase memory because of limitations on kernel 2.2. In general Is there any significant difference in terms of memory consumtion between a user process that uses bind variables and another one that uses literals? Does anyone use 8.1.7, MTS mode on Linux for a system with similar loading (400-500 users). Is that stable enough?. I have doubts. Please help. Regards, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: Correction : EMC symetrix - 1M stripe width - Raid 0+1 - Performa
Hi Mohammed, Look at Gaja's article about RAIDOracle. It's one of the best articles I've ever seen.. You can find it at www.quest.com Regards, Ed Hi, We recently migrated from sun a5200 storage to EMC symetrix, and we have been seeing occasional performance problems. When contacted, Oracle support among other things pointed out the stripe width we have used 1M, is very large and also said users will not see an advantage above 64K. When we had Sun storage before we had 64K as the stripe width. Has anyone faced this kind of issue or has any comments..or can someone explain the low level impact of the stripe width on the ORACLE I/O operations? Much appreciated.. Regards Mohammed Ahsanuddin Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: OT_salary
Hi, hmm, I've never been to the UK but always thought that the cost of living in the UK is higher comparing to the USA. It seems I was wrong. Not sure you consider Russia as a part of Europe... Just for your information The average salary for a med. DBA in Moscow is about 12K per year. Hey, any volunteer to join our teem ? ;-) Regards, Ed Of course its a per annum salary. We DBAs would have been millionaires by now if that kind of a salary were paid monthly.and people would have flocked to the UK instead of the US :)). But then again, it is quite a comfy salary in the UK since the cost of living is low. Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 12 November 2001 11:01 To: SARKAR, Samir Hi Samir, Is this salary per annum or per month? Please clarify. Regards, Ranganath -Original Message- Sent: Monday, November 12, 2001 4:00 PM To: Multiple recipients of list ORACLE-L In the UK, it is around £30-35K. It used to be around £40K but the salaries have dropped after the IT bubble burst. Hi i'd like to know what average salarys are in european countries for a medium skilled DBA. my first contribution is 40 USD -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: OT_salary
hmm, I've never been to the UK but always thought that the cost of living in the UK is higher comparing to the USA. It seems I was wrong. Not sure you consider Russia as a part of Europe... Just for your information The average salary for a med. DBA in Moscow is about 12K per year. Hey, any volunteer to join our teem ? ;-) are you willing to pay relocation?;-) no, instead the company guarantees free dinners ;-) Regards, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
commit rate
Hi List, I try to estimate commit rate on our system. Currently I just periodically collect values of 'user commits' statistics in v$sysstat in order to estimate frequency of LGWR's writes. But I assume this statistics doesn't reflect group commits. Is there more sophisticated method to find frequency of LGWR's writes? Thanks, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Strip size recomendations for RAID5
Hi List, I'm looking for recomendations, best practice, etc about setting strip size for RAID5 arrays. We have a hybrid system (mainly OLTP) with prevelant reads operations and have limited number of disks. My question is should I merely setup the stip size equal to Oracle block size or should I set it larger considering size of maxphys. I/O operation for certain OS? I've read some articles and haven't found clear recommendations yet. We are on 8.1.7, Linux Thank you for your help. Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Compaq's RAIDs and RAID terminology
Hi List, Does someone have experience with Compaq's RAID controllers? I've inhereted a 22x7 system where RAID arrays are supported by SmartArray 5300 controller. Last weekend I had an opportunity to add a spare disk to the RAID5 array which consisted of 7 disks. I was amazed when the Compaq RAID Configuration Utility showed 32K stripe size for that array. The only my assumption is they use the term stripe size instead of strip size. I was looking through some articles about RAID configurations and It seems there is a little mess in the English-speaking RAID terminology. Because I'm not an English-native speaker I'm a little confused about that. So my question is Am I correct saying that: - strip size means size of data that reside on an each particular disk-member in a stripe set - stripe size means total size of data across all disks in a stripe set - stripe-width means the same as stripe size - ??? Do you know another terms? Thanks, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: os block size versus oracle bock size
Hi Jack and List, we have the same situation. Our DB (mainly OLTP) was built with db_block_size 8k and ext2 filesystem (Linux) has 4k block size. AFAIK 4k is max block size for ext2. Do you think it's worth to rebuild the DB with 4k block in order to adjust it to ext2's block size? I know it's quite a difficult question, so I will appreciate your general advices or thoughts. And does anyone know if direct or async options are available on ext2 ? I have little experience of working on Linux. Thanks, Ed Hi I'd say yes. For every Oracle block read the OS has to read two block which causes overhead. Jack [EMAIL PROTECTED]@fatcity.com on 23-10-2001 16:05:21 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) hi all we have an oracle block size of 8k and i believe our W2K server has a default os block size of 4k. Is this a problem with the performance ? thanks g.g. kor rdw ict groningen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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
Re: Problem - V$BUFFER_POOL_STATISTICS
Hi Saurabh, have a look at $ORACLE_HOME/rdbms/admin/catperf.sql It seems you didn't run it Regards, Ed - Original Message - From: Saurabh Sharma To: Multiple recipients of list ORACLE-L Sent: Saturday, October 20, 2001 3:05 PM Subject: Problem - V$BUFFER_POOL_STATISTICS Hi all, Is anyone using Oracle Statspack for performance monitoring. I want to use it, i ran the required scripts for creating the PERFSTAT schema, tables, synonyms and STATSPACK package. but the package creation is giving problem with Dictionary table V$BUFFER_POOL_STATISTICS( as it is not found in the database dictionary) The schema script created the stat$buffer_pool_statistics table to use data from above dict table but package could not be compiled successfully as dictionary table is not found. can anybody explain me why it happened. I'm using Oracle 8.1.5 on NT. thanks in adv Saurabh Sharma Mail to : [EMAIL PROTECTED] : [EMAIL PROTECTED]Contact : saurabh00pc @ MSN / Yahoo
Re: distribution of the sleeps on the library cache latches
Hi Steve, thanks for your reply. I'm thinking about twice increasing number of library latches ( _kgl_latch_count = 23 ) in order to mitigate loading on them. Also I would like to set _kgl_bucket_count = 8 according to output of your script. Do you think it's a good idea in my case. NAME IMPACT SLEEP_RATEHOLDING LEVEL# - -- -- -- -- library cache 60333579.3 0.32% 1729452385 shared pool19313269.2 1.40% 8265405 7 cache buffers chains1950080.11 0.00% 629411 1 row cache objects 738401.912 0.04%3369329 4 session allocation 70758.0784 0.01% 144008 5 cache buffer handles56104. 0.01% 71913 3 redo allocation33494.1227 0.02% 215582 6 cache buffers lru chain 12784.3859 0.00%198869 3 checkpoint queue latch10980.4325 0.00% 52259 7 latch wait list 9976.33016 0.04% 24412 9 redo writing 4846.5256 0.01% 75484 5 Regards, Ed Hi Ed, My scripts use the rule of thumb you mention, but it is not a black and white issue. I would characterise your contention here as having a few hot spots, but a general library cache wide problem as well. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 18 October 2001 9:25 To: Multiple recipients of list ORACLE-L Hi List, what is the criteria of uneven distribution of sleeps on the library cache latches? Is there a rule of thumb to determine uneven distribution? For example, no of sleeps on a latch is twice bigger than average no of the sleeps on the others latches? Is it correct? Do you estimate the following distribution as uneven? NAME GETS MISSES SLEEPS SLEEP1 SLEEP2 SLEEP3 -- -- -- -- -- -- -- library cache 806881977 103462783105912 3358661020725 217664 library cache 464142903 39375581318015 154644 422509 94864 library cache 283177601 19916481127057 120761 368308 80551 library cache 839438890 79674971478426 195907 479182 95918 library cache 978851575 131045961614737 213383 527238 104408 library cache 279613950 1453222 759127 77395 255984 51334 library cache 834477709 116230003101181 4051021058753 168282 library cache 260953580 1434471 825151 93505 278275 52608 library cache 470252271 52629331484982 162567 489911 103336 library cache 501042073 51344671595443 180043 507939 119648 library cache 1265644171 250131692374937 371608 754426 152126 TIA, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: distribution of the sleeps on the library cache latches
Hi Steve, yes, you're absolutely right. I've inhereted that system. The shared_pool_size = 750M. I believe it's HUGE and oversized. The application code is mostly based on literal SQL. The miss rate on the shared pool is normally about 15%-20% with periodical peaks up to 50%. But the previous DBA insist that we shouldn't decrease the size of shared pool as the miss rate will be much higher. He also setup periodical flushing every 3 hours (I assume he did it in order to prevent ORA-4031). If I undestand the things right, deacresing of shared_pool_size will decrease load on shared pool latch _but_ contention on the library latches will be higher because of higher parse rate. Is it correct and what's your advice in my case? Thanks in advance, Ed Hi Ed, I would agree with the _kgl_latch_count change, but the _kgl_bucket_count change seems unwarranted and extreme. Rather I suspect that the size of your library cache hash table rather reflects an oversized shared pool, probably with some use of literal SQL. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- From: Edward Shevtsov [mailto:[EMAIL PROTECTED]] Sent: Friday, 19 October 2001 18:02 To: [EMAIL PROTECTED] Cc: Steve Adams Subject: Re: distribution of the sleeps on the library cache latches Hi Steve, thanks for your reply. I'm thinking about twice increasing number of library latches ( _kgl_latch_count = 23 ) in order to mitigate loading on them. Also I would like to set _kgl_bucket_count = 8 according to output of your script. Do you think it's a good idea in my case. NAME IMPACT SLEEP_RATEHOLDING LEVEL# - -- -- -- -- library cache 60333579.3 0.32% 1729452385 shared pool19313269.2 1.40% 8265405 7 cache buffers chains1950080.11 0.00% 629411 1 row cache objects 738401.912 0.04%3369329 4 session allocation 70758.0784 0.01% 144008 5 cache buffer handles56104. 0.01% 71913 3 redo allocation33494.1227 0.02% 215582 6 cache buffers lru chain 12784.3859 0.00%198869 3 checkpoint queue latch10980.4325 0.00% 52259 7 latch wait list 9976.33016 0.04% 24412 9 redo writing 4846.5256 0.01% 75484 5 Regards, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: distribution of the sleeps on the library cache latches
Hi Steve, I had initiated the process of gradual migration to bind variables. It seems it will take a long time. Thanks for your detailed answer, I appreciate it Ed Hi Ed, Of course, I'd suggest that the application be enhanced to use bind variables appropriately! ;-) In the interim, I would introduce a script such as 'keeper.sql' from the Ixora web site to keep all the reusable material in the library cache so as to reduce the impact of the flushes. Once that is working as desired, I would increase the flush frequency to an interval of say 1 hour or 30 minutes. The size of the library cache and thus shared pool utilization will still grow over time, but more slowly. I would then reduce the shared pool size to approximately the size that it grew to after 1 day of normal application usage. To then mitigate the risk of ORA-4031 errors I would ensure that 'shared_pool_reserved_size' is allowed to default, but set '_shared_pool_reserved_min_alloc' to its minimum value (which is 4000 or 5000, version dependent). An instance restart once a week would be good too if you can manage that. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- From: Edward Shevtsov [mailto:[EMAIL PROTECTED]] Sent: Friday, 19 October 2001 19:09 To: Steve Adams; [EMAIL PROTECTED] Subject: Re: distribution of the sleeps on the library cache latches Hi Steve, yes, you're absolutely right. I've inhereted that system. The shared_pool_size = 750M. I believe it's HUGE and oversized. The application code is mostly based on literal SQL. The miss rate on the shared pool is normally about 15%-20% with periodical peaks up to 50%. But the previous DBA insist that we shouldn't decrease the size of shared pool as the miss rate will be much higher. He also setup periodical flushing every 3 hours (I assume he did it in order to prevent ORA-4031). If I undestand the things right, deacresing of shared_pool_size will decrease load on shared pool latch _but_ contention on the library latches will be higher because of higher parse rate. Is it correct and what's your advice in my case? Thanks in advance, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: distribution of the sleeps on the library cache latches
Hi Ivo, we are on Linux RH, 8.1.7.0.1. I've tried cursor_sharing=force. Unfortunately, it causes ORA-600. Do 8.1.7.1(2) patches fix this problem? Regards, Ed Hi Ed have you tried cursor_sharing=force ? I dont know what version of oracle you have and there are some known problems but maybe it can help you with literal sql statements and then decreasing size of shared_pool. Ivo -Original Message- Sent: Friday, October 19, 2001 12:11 PM To: Multiple recipients of list ORACLE-L Hi Steve, yes, you're absolutely right. I've inhereted that system. The shared_pool_size = 750M. I believe it's HUGE and oversized. The application code is mostly based on literal SQL. The miss rate on the shared pool is normally about 15%-20% with periodical peaks up to 50%. But the previous DBA insist that we shouldn't decrease the size of shared pool as the miss rate will be much higher. He also setup periodical flushing every 3 hours (I assume he did it in order to prevent ORA-4031). If I undestand the things right, deacresing of shared_pool_size will decrease load on shared pool latch _but_ contention on the library latches will be higher because of higher parse rate. Is it correct and what's your advice in my case? Thanks in advance, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
Re: distribution of the sleeps on the library cache latches
Hi Bing, I meant the case when the load (number of requests) is much higher on a particular latch comparing to over latches Regards, Ed When you say uneven, does it mean fragmented? I am learning this too. Bing -Original Message- Sent: Wednesday, October 17, 2001 4:25 PM To: Multiple recipients of list ORACLE-L Hi List, what is the criteria of uneven distribution of sleeps on the library cache latches? Is there a rule of thumb to determine uneven distribution? For example, no of sleeps on a latch is twice bigger than average no of the sleeps on the others latches? Is it correct? Do you estimate the following distribution as uneven? NAME GETS MISSES SLEEPS SLEEP1 SLEEP2 SLEEP3 -- -- -- -- -- -- -- library cache 806881977 103462783105912 3358661020725 217664 library cache 464142903 39375581318015 154644 422509 94864 library cache 283177601 19916481127057 120761 368308 80551 library cache 839438890 79674971478426 195907 479182 95918 library cache 978851575 131045961614737 213383 527238 104408 library cache 279613950 1453222 759127 77395 255984 51334 library cache 834477709 116230003101181 4051021058753 168282 library cache 260953580 1434471 825151 93505 278275 52608 library cache 470252271 52629331484982 162567 489911 103336 library cache 501042073 51344671595443 180043 507939 119648 library cache 1265644171 250131692374937 371608 754426 152126 TIA, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov 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).
