RE: Delete vs. truncate to free up spaces.
This type of activity is intended to be done only when there is no DML activity on the table. If that isn't feasible, then I'm afraid you'd have to do conventional deletes. -Original Message- From: Naveen, Nahata (IE10) [mailto:[EMAIL PROTECTED] Sent: Tue 12/23/2003 1:00 AM To: Multiple recipients of list ORACLE-L Cc: Subject:RE: Delete vs. truncate to free up spaces. In such a case what will happen to the transactions that hit the table (since the triggers have been disabled)? Regards Naveen -Original Message- From: Bobak, Mark [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 5:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: Delete vs. truncate to free up spaces. Because there is a lot that could be overlooked, I prefer to do it the other way around: create table tmp_tbl nologging as select * from big_table where (rows you want to keep); truncate table big_table; alter trigger trigger_name disable; (for each trigger on big_table) alter constraint constraint_name disable; (for each constraint) alter index index_name unusable; (for each index) alter table big_table nologging; insert /*+ APPEND */ select * from tmp_tbl; commit; alter table big_table logging; alter index index_name rebuild nologging; alter constraint constraint_name enable; (consider novalidate where appropriate) alter trigger trigger_name enable; @?/rdbmsa/admin/utlrp.sql That way, you're a lot less likely to overlook a grant or synonym. -Mark -Original Message- From: anu [mailto:[EMAIL PROTECTED] Sent: Mon 12/22/2003 6:59 PM To: Multiple recipients of list ORACLE-L Cc: Subject: RE: Delete vs. truncate to free up spaces. And synonyms will have to be re-created. (drop and create). Grants will have to be given. Jared Still [EMAIL PROTECTED] wrote: .. and if your table is not partitioned, consider using 'CREATE TABLE AS' with WHERE clause that eliminates the rows you wish to delete, recreate indexes and constraints on the new table, drop the old table, rename the new to the old. Keep in mind that stored procedures and triggers that reference the table will need to be recompiled. Jared On Mon, 2003-12-22 at 14:44, Jacques Kilchoer wrote: This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition? With a partitioned table you can say alter table ... drop partition ... ; to easily get rid of a large chunk of data and release the space. See Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes http://download-west.oracle.com/docs/cd/B10501_01/server.920/a 96524/c12parti.htm#464767 or http://tinyurl.com/362ba -Original Message- Nguyen, David M I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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.net -- Author: Jared Still 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). _ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen, Nahata (IE10) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
RE: TNSPING VS. REGULAR PING..! WHY SUCH A DIFFERENCE
I came across just this last week with one of our monitoring tools. We set up an execution of a script that was using PING to check whether the status of a list of remote POS devices to make sure they were available. The collection worked fine - until we shutdown the POS device, and physically switched it off - and the PING still replied! The POS devices have UPS built in to them.. The client also thought it may have been the way that DNS/DHCP was set up via the router. It stumped me for a time.. ;) We use NETSVC now to check for the status of a service. Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Jared Still Sent: 23 December 2003 04:34 To: Multiple recipients of list ORACLE-L Paul mentioned a few reasons for this. Another is that a ping does not get past the NIC. The ping is answered by software running on the card. You may have noticed at times that a ping is not a reliable method for determining if a server is still functioning. The OS can crash, but the NIC still responds to a ping. Tnsping on the other hand must get a response from Oracle Net service or daemon running on the server, a much longer path as Paul pointed out. HTH Jared On Mon, 2003-12-22 at 17:29, Murali Vallath wrote: I have recently noticed in this one situation that there is a great difference between a tnsping vs a regular ping to the same server. for example this tnsping took about 270 ms which is strange and its consistent Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADD RESS_LIST = (load_balance = on) (failover = on) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost1.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2.com)( PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = xyzdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 20) (DELAY = 15 OK (270 msec) and a ping to the same host Ping statistics for x.x.x.x: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 61ms, Maximum = 70ms, Average = 67ms Why could there be such a difference? - Do you Yahoo!? Yahoo! Photos - Get your photo on the big screen in Times Square -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.553 / Virus Database: 345 - Release Date: 18/12/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.553 / Virus Database: 345 - Release Date: 18/12/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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: Linux
On Sat, Dec 20, 2003 at 02:34:26PM -0800, Paul Drake wrote: --- Seema Singh [EMAIL PROTECTED] wrote: Hi Does anyone know good Linux administration book? Can you guyes suggest any good linux user group? thx -Seema Matt Welsh's Running Linux is now out in 4th Edition. Just make sure that you find a recent edition, as there are still plenty of RedHat 6.0 books out there on the shelves. Linux System Administration Handbook Prentice Hall PTR; ISBN: 0130084662 cheers, Mihalis. -- 14:00:56 up 6 days, 22:33, 2 users, load average: 0.03, 0.03, 0.00 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mihalis I. Tsoukalos 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: STATSPACK interpretation
Jared, Digging into it more, I found out that it's called from an Informatica client. Apparently, the gist of the client-side algorithim is as follows: For each row in (some view) Call generate_product_keys MERGE (upsert) into product table end loop CREATE OR REPLACE PROCEDURE generate_product_keys (v_marketing_model_id IN VARCHAR2, v_model_country_cd IN VARCHAR2, v_product_seq_id IN OUT NUMBER, v_product_id IN OUT NUMBER) IS BEGIN IF v_product_seq_id IS NOT NULL THEN NULL; ELSE SELECT seq_product_seq_id.nextval INTO v_product_seq_id FROM dual; BEGIN SELECT product_id INTO v_product_id FROM product WHERE marketing_model_id = v_marketing_model_id AND model_country_cd = v_model_country_cd; EXCEPTION WHEN NO_DATA_FOUND THEN SELECT seq_product_id.nextval INTO v_product_id FROM dual; END; END IF; END generate_product_keys; / Thanks. -Original Message- Jared Still Sent: Monday, December 22, 2003 6:15 PM To: Multiple recipients of list ORACLE-L What is taking place inside GENERATE_PRODUCT_KEYS() ? Could be dynamic SQL of the worst kind in there. That is, not using bind variables. A 10046 trace level 4 or 12 will show you what is going on there. Jared On Mon, 2003-12-22 at 12:39, Thomas Jeff wrote: We recently experienced a crash on our prod datewarehouse running 9.2.0.2 on AIX 4.3.3.The cause of the crash was 4031 errors generated by background processes (Oracle support has confirmed there is a bug involved), however, since that crash occurred, a certain nightly batch job has slowed to a crawl. Trying to recreate what has happened, I came across this in the STATSPACK report. The interval for this report is 30 minutes. Is it telling me that I have 746 versions of this call eating up 400+ mb at the time of the snapshot? Why would that be? The procedure in question uses bind variables. SQL ordered by Sharable Memory for DB: DSSP Instance: DSSP Snaps: 3309 -3310 - End Sharable Memory Threshold: 1048576 Sharable Mem (b) Executions % Total Hash Value --- 483,580,268 57 411.8539672786 Module: [EMAIL PROTECTED] (TNS V1-V3) BEGINGENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ; END; - SQL ordered by Version Count for DB: DSSP Instance: DSSP Snaps: 3309 -3310 - End Version Count Threshold:20 Version Count Executions Hash Value 746 57539672786 Module: [EMAIL PROTECTED] (TNS V1-V3) BEGINGENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ; END; Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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.net -- Author: Thomas Jeff 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: TNSPING VS. REGULAR PING..! WHY SUCH A DIFFERENCE
ANd one more thing about Ping vs Tnsping when going thru a firewall. Some firewalls are setup to not allow a Ping to pass thru, but sql connections are allowed. So a Ping will return not found, while a tnsping will return ok. I have that situation here all over the freakin place. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 23, 2003 5:54 AM To: Multiple recipients of list ORACLE-L I came across just this last week with one of our monitoring tools. We set up an execution of a script that was using PING to check whether the status of a list of remote POS devices to make sure they were available. The collection worked fine - until we shutdown the POS device, and physically switched it off - and the PING still replied! The POS devices have UPS built in to them.. The client also thought it may have been the way that DNS/DHCP was set up via the router. It stumped me for a time.. ;) We use NETSVC now to check for the status of a service. Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Jared Still Sent: 23 December 2003 04:34 To: Multiple recipients of list ORACLE-L Paul mentioned a few reasons for this. Another is that a ping does not get past the NIC. The ping is answered by software running on the card. You may have noticed at times that a ping is not a reliable method for determining if a server is still functioning. The OS can crash, but the NIC still responds to a ping. Tnsping on the other hand must get a response from Oracle Net service or daemon running on the server, a much longer path as Paul pointed out. HTH Jared On Mon, 2003-12-22 at 17:29, Murali Vallath wrote: I have recently noticed in this one situation that there is a great difference between a tnsping vs a regular ping to the same server. for example this tnsping took about 270 ms which is strange and its consistent Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADD RESS_LIST = (load_balance = on) (failover = on) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost1.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2.com)( PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = xyzdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 20) (DELAY = 15 OK (270 msec) and a ping to the same host Ping statistics for x.x.x.x: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 61ms, Maximum = 70ms, Average = 67ms Why could there be such a difference? - Do you Yahoo!? Yahoo! Photos - Get your photo on the big screen in Times Square -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.553 / Virus Database: 345 - Release Date: 18/12/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.553 / Virus Database: 345 - Release Date: 18/12/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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.net -- Author: Mercadante, Thomas F 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
Re: Risk of knowing password hash value (Was: OEM permissions)
9.2.0.2 on Sun Solaris... and yes, I got the same encrypted password --- Michael Thomas [EMAIL PROTECTED] wrote: Hi, Okay. I'm almost a believer of this as a problem. How about 9.2.0.4 on RH9.3. 1) What does anyone/everyone get for my this query (my results shown): connect system/[EMAIL PROTECTED]; alter user scott identified by tiger; -- select password from dba_users where username = 'SCOTT'; PASSWORD F894844C34402B67 2) If you all get the same, then I'm concerned. Regards, Mike Thomas --- Yong Huang [EMAIL PROTECTED] wrote: Jared, I see you log out and log back in as SYSTEM to DB2. But how do you know the password for SYSTEM to log back in with after you change it? What if you don't log out? When I tried that (i.e. not logging out), I got ORA-1017. Yong Huang --- Jared Still [EMAIL PROTECTED] wrote: Environment: DB1: RH 8.0 with Oracle EE 9.2.0.4 DB2: Win2k SP3 with Oracle EE 9.2.0.1 SYSTEM user on each database initially have different passwords. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Thomas 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). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Risk of knowing password hash value (Was: OEM permissions)
This is what I got, Oracle 8.1.7.4 on Sun Solaris (I dropped the user): Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production With the Partitioning option JServer Release 8.1.7.4.0 - Production SQL create user scott identified by tiger; User created. SQL select password 2 from dba_users 3 where username = 'SCOTT'; PASSWORD -- F894844C34402B67 SQL Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -Original Message- rahul sharma Sent: Tuesday, December 23, 2003 1:14 AM To: Multiple recipients of list ORACLE-L 8.1.7 on win2000 SQL select password 2 from dba_users 3 where username = 'SCOTT'; PASSWORD -- F894844C34402B67 - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 11:44 AM Hi, Okay. I'm almost a believer of this as a problem. How about 9.2.0.4 on RH9.3. 1) What does anyone/everyone get for my this query (my results shown): connect system/[EMAIL PROTECTED]; alter user scott identified by tiger; -- select password from dba_users where username = 'SCOTT'; PASSWORD F894844C34402B67 2) If you all get the same, then I'm concerned. Regards, Mike Thomas --- Yong Huang [EMAIL PROTECTED] wrote: Jared, I see you log out and log back in as SYSTEM to DB2. But how do you know the password for SYSTEM to log back in with after you change it? What if you don't log out? When I tried that (i.e. not logging out), I got ORA-1017. Yong Huang --- Jared Still [EMAIL PROTECTED] wrote: Environment: DB1: RH 8.0 with Oracle EE 9.2.0.4 DB2: Win2k SP3 with Oracle EE 9.2.0.1 SYSTEM user on each database initially have different passwords. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Thomas 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.net -- Author: rahul sharma 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.net -- Author: Scott Canaan 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: Risk of knowing password hash value (Was: OEM permissions)
[EMAIL PROTECTED] . oraenv ORACLE_SID = [OLDNCS1] ? DEVL [EMAIL PROTECTED] sys SQL*Plus: Release 9.2.0.2.0 - Production on Tue Dec 23 08:30:45 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options JServer Release 9.2.0.2.0 - Production 08:30:51 SQL alter user scott identified by tiger; User altered. 08:31:01 SQL select password from dba_users where username = 'SCOTT'; PASSWORD -- F894844C34402B67 9202 on AIX5L. So much for 40 bit one way encryption ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
ORA-00600 signalled while mounting the database from the second instance of Oracle9i RAC
Title: ORA-00600 signalled while mounting the database from the second instance of Oracle9i RAC Hi there, I have set up two-node Oracle9i RAC on Windows 2000 Advanced Server with OCFS (Oracle Clustered file system). The Oracle version is 9.2.0.1.0 and I have also applied the 9.2.0.3.0 patch on top of it. I am getting the following error while mounting the database or starting up the database: ORA-00600: internal error code, arguments: [kccsbck_first], [1], [3898663947], [], [], [], [], [] The relevant portion from the alertsid.log file is pasted below: Tue Dec 23 17:34:58 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:35:29 2003 ORA-600 signalled during: alter database mount... Tue Dec 23 17:40:13 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:45:22 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:50:38 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:55:53 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:59:15 2003 alter database mount Tue Dec 23 17:59:15 2003 This instance was first to mount Tue Dec 23 17:59:19 2003 Errors in file c:\oracle\admin\clust\udump\clust2_ora_2148.trc: ORA-00600: internal error code, arguments: [kccsbck_first], [1], [3898663947], [], [], [], [], [] I tried changing the aq_tm_processes parameter for both instances from 1 to 0. But the error still recurs. I am also attaching the relevant trace file for your reference. Can anybody let me know the reason for the above errors and how to counter them? Any help in this regard is very much appreciated. Thanks and Regards, Ranganath clust2_ora_2148.zip www.mailfiler.com [RK-KI8G4W] clust2_ora_2148.zip Description: Zip compressed data
RE: TNSPING VS. REGULAR PING..! WHY SUCH A DIFFERENCE
TNSping uses the Port 1521 to communicate .. Not Sure which Port Ping will use.. HTH Regards, Ganesh R HP : (+65)9067-8474 Mail : [EMAIL PROTECTED] == All Opinions expressed are my own and do not in anyway reflect those of my employer == -Original Message- Mercadante, Thomas F Sent: Tuesday, December 23, 2003 20:54 PM To: Multiple recipients of list ORACLE-L ANd one more thing about Ping vs Tnsping when going thru a firewall. Some firewalls are setup to not allow a Ping to pass thru, but sql connections are allowed. So a Ping will return not found, while a tnsping will return ok. I have that situation here all over the freakin place. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 23, 2003 5:54 AM To: Multiple recipients of list ORACLE-L I came across just this last week with one of our monitoring tools. We set up an execution of a script that was using PING to check whether the status of a list of remote POS devices to make sure they were available. The collection worked fine - until we shutdown the POS device, and physically switched it off - and the PING still replied! The POS devices have UPS built in to them.. The client also thought it may have been the way that DNS/DHCP was set up via the router. It stumped me for a time.. ;) We use NETSVC now to check for the status of a service. Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Jared Still Sent: 23 December 2003 04:34 To: Multiple recipients of list ORACLE-L Paul mentioned a few reasons for this. Another is that a ping does not get past the NIC. The ping is answered by software running on the card. You may have noticed at times that a ping is not a reliable method for determining if a server is still functioning. The OS can crash, but the NIC still responds to a ping. Tnsping on the other hand must get a response from Oracle Net service or daemon running on the server, a much longer path as Paul pointed out. HTH Jared On Mon, 2003-12-22 at 17:29, Murali Vallath wrote: I have recently noticed in this one situation that there is a great difference between a tnsping vs a regular ping to the same server. for example this tnsping took about 270 ms which is strange and its consistent Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADD RESS_LIST = (load_balance = on) (failover = on) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost1.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2.com)( PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = xyzdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 20) (DELAY = 15 OK (270 msec) and a ping to the same host Ping statistics for x.x.x.x: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 61ms, Maximum = 70ms, Average = 67ms Why could there be such a difference? - Do you Yahoo!? Yahoo! Photos - Get your photo on the big screen in Times Square -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.553 / Virus Database: 345 - Release Date: 18/12/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.553 / Virus Database: 345 - Release Date: 18/12/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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
RE: TNSPING VS. REGULAR PING..! WHY SUCH A DIFFERENCE
PING doesnt actually use a port number, as it's not TCP/IP based - it's a part of the ICMP protocol.. Mark -Original Message- Ganesh Raja Sent: 23 December 2003 13:40 To: Multiple recipients of list ORACLE-L TNSping uses the Port 1521 to communicate .. Not Sure which Port Ping will use.. HTH Regards, Ganesh R HP : (+65)9067-8474 Mail : [EMAIL PROTECTED] == All Opinions expressed are my own and do not in anyway reflect those of my employer == -Original Message- Mercadante, Thomas F Sent: Tuesday, December 23, 2003 20:54 PM To: Multiple recipients of list ORACLE-L ANd one more thing about Ping vs Tnsping when going thru a firewall. Some firewalls are setup to not allow a Ping to pass thru, but sql connections are allowed. So a Ping will return not found, while a tnsping will return ok. I have that situation here all over the freakin place. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 23, 2003 5:54 AM To: Multiple recipients of list ORACLE-L I came across just this last week with one of our monitoring tools. We set up an execution of a script that was using PING to check whether the status of a list of remote POS devices to make sure they were available. The collection worked fine - until we shutdown the POS device, and physically switched it off - and the PING still replied! The POS devices have UPS built in to them.. The client also thought it may have been the way that DNS/DHCP was set up via the router. It stumped me for a time.. ;) We use NETSVC now to check for the status of a service. Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Jared Still Sent: 23 December 2003 04:34 To: Multiple recipients of list ORACLE-L Paul mentioned a few reasons for this. Another is that a ping does not get past the NIC. The ping is answered by software running on the card. You may have noticed at times that a ping is not a reliable method for determining if a server is still functioning. The OS can crash, but the NIC still responds to a ping. Tnsping on the other hand must get a response from Oracle Net service or daemon running on the server, a much longer path as Paul pointed out. HTH Jared On Mon, 2003-12-22 at 17:29, Murali Vallath wrote: I have recently noticed in this one situation that there is a great difference between a tnsping vs a regular ping to the same server. for example this tnsping took about 270 ms which is strange and its consistent Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADD RESS_LIST = (load_balance = on) (failover = on) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost1.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2.com)( PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = xyzdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 20) (DELAY = 15 OK (270 msec) and a ping to the same host Ping statistics for x.x.x.x: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 61ms, Maximum = 70ms, Average = 67ms Why could there be such a difference? - Do you Yahoo!? Yahoo! Photos - Get your photo on the big screen in Times Square -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.553 / Virus Database: 345 - Release Date: 18/12/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.553 / Virus Database: 345 - Release Date: 18/12/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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
ORA-00600 signalled while mounting the database from the second instance of Oracle9i RAC
Title: ORA-00600 signalled while mounting the database from the second instance of Oracle9i RAC Hi there, I have set up two-node Oracle9i RAC on Windows 2000 Advanced Server with OCFS (Oracle Clustered file system). The Oracle version is 9.2.0.1.0 and I have also applied the 9.2.0.3.0 patch on top of it. I am getting the following error while mounting the database or starting up the database: ORA-00600: internal error code, arguments: [kccsbck_first], [1], [3898663947], [], [], [], [], [] The relevant portion from the alertsid.log file is pasted below: Tue Dec 23 17:34:58 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:35:29 2003 ORA-600 signalled during: alter database mount... Tue Dec 23 17:40:13 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:45:22 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:50:38 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:55:53 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:59:15 2003 alter database mount Tue Dec 23 17:59:15 2003 This instance was first to mount Tue Dec 23 17:59:19 2003 Errors in file c:\oracle\admin\clust\udump\clust2_ora_2148.trc: ORA-00600: internal error code, arguments: [kccsbck_first], [1], [3898663947], [], [], [], [], [] I tried changing the aq_tm_processes parameter for both instances from 1 to 0. But the error still recurs. I am also attaching the relevant trace file for your reference. Can anybody let me know the reason for the above errors and how to counter them? Any help in this regard is very much appreciated. Please send your replies to my mail id also as I am unable to receive Fatcity mails. Thanks and Regards, Ranganath clust2_ora_2148.zip www.mailfiler.com [RK-P7P1D12] clust2_ora_2148.zip Description: Zip compressed data
RE: ORA-00600 signaled while mounting the database from the second instance of Oracle9i RAC
Title: ORA-00600 signalled while mounting the database from the second instance of Oracle9i RAC Have you looked up on Metalink? there is ton of information out there. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Ranganath K [mailto:[EMAIL PROTECTED]Sent: Tuesday, December 23, 2003 9:15 AMTo: Multiple recipients of list ORACLE-LSubject: ORA-00600 signalled while mounting the database from the second instance of Oracle9i RAC Hi there, I have set up two-node Oracle9i RAC on Windows 2000 Advanced Server with OCFS (Oracle Clustered file system). The Oracle version is 9.2.0.1.0 and I have also applied the 9.2.0.3.0 patch on top of it. I am getting the following error while mounting the database or starting up the database: ORA-00600: internal error code, arguments: [kccsbck_first], [1], [3898663947], [], [], [], [], [] The relevant portion from the alertsid.log file is pasted below: Tue Dec 23 17:34:58 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:35:29 2003 ORA-600 signalled during: alter database mount... Tue Dec 23 17:40:13 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:45:22 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:50:38 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:55:53 2003 Restarting dead background process QMN0 QMN0 started with pid=18 Tue Dec 23 17:59:15 2003 alter database mount Tue Dec 23 17:59:15 2003 This instance was first to mount Tue Dec 23 17:59:19 2003 Errors in file c:\oracle\admin\clust\udump\clust2_ora_2148.trc: ORA-00600: internal error code, arguments: [kccsbck_first], [1], [3898663947], [], [], [], [], [] I tried changing the aq_tm_processes parameter for both instances from 1 to 0. But the error still recurs. I am also attaching the relevant trace file for your reference. Can anybody let me know the reason for the above errors and how to counter them? Any help in this regard is very much appreciated. Please send your replies to my mail id also as I am unable to receive Fatcity mails. Thanks and Regards, Ranganath clust2_ora_2148.zip www.mailfiler.com [RK-P7P1D12]
RE: Delete vs. truncate to free up spaces.
EE has enterprise on the CD, but it's an extra cost option. Make sure the license fees have been paid first, otherwise it could be a messy thing to rip out. BTDT. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, December 22, 2003 7:14 PM To: Multiple recipients of list ORACLE-L Then you should have the partitioning option. Partition your table if you can. -Original Message- Nguyen, David M It's Oracle8i Enterprise Edition. -Original Message- Jacques Kilchoer This is one of the cases where a partitioned table can be of great use. What version of Oracle? Standard or Enterprise Edition? With a partitioned table you can say alter table ... drop partition ... ; to easily get rid of a large chunk of data and release the space. See Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01 Chapter 11 Partitioned Tables and Indexes http://download-west.oracle.com/docs/cd/B10501_01/server.920/a 96524/c12p arti.htm#464767 or http://tinyurl.com/362ba -Original Message- Nguyen, David M I am using delete command to delete million records in several tables to free up space in tablespace. I understand delete command does not release unused spaces as truncate command but I could not use truncate to delete ALL records in table as I need to keep one month old of records in table. Please advise a better method I can use to free up spaces. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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.net -- Author: Goulet, Dick 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: Risk of knowing password hash value (Was: OEM permissions)
Same here... SQL select password from dba_users where username = 'SCOTT'; PASSWORD -- F894844C34402B67 -Original Message- Michael Thomas Sent: Monday, December 22, 2003 10:44 PM To: Multiple recipients of list ORACLE-L Hi, Okay. I'm almost a believer of this as a problem. How about 9.2.0.4 on RH9.3. 1) What does anyone/everyone get for my this query (my results shown): connect system/[EMAIL PROTECTED]; alter user scott identified by tiger; -- select password from dba_users where username = 'SCOTT'; PASSWORD F894844C34402B67 2) If you all get the same, then I'm concerned. Regards, Mike Thomas -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Norris, Gregory T [ITS] 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).
ora-4030 pga memory allocation running wild
Hi, I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has 4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen
RE: Hit Ratio
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a humor. Only
if you carefully comtemplate it will you see that there's no relevance of the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event times. If
you need very long db file reads, create a big table and keep scanning it. If
you need long enqueue waits, create a table and insert a row. Create 10 or 100
sessions (depending on your patience) and delete from that table and wait. The
fact that you can get arbitary wait times does not reduce the efficacy of wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very insufficient
used alone, to tune the database. The reason is that they don't contain enough
information to tune the system with. This is the only reason we should not
solely rely on them; in fact, not using them at all doesn't hurt much. The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not condemned.
Although in UNIX performance tuning one looks at absolute numbers such as scan
rate, CPU usage and netstat output more often, hit ratios in some sar output
are still occasionally used. Most ratios could still be distored by a rogue
user repeatedly doing, say, find / for inodes or find / -exec grep SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage patterns
invalidates your numbers even if you're using a well respected tuning method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%. What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement scripts.
Especially when your bonus depends on it, this is a good time to perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
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).
ora-942 message in alert log
Yesterday, wereceived the below message in our alert log. Normally, we do not see such messages in an alert log.It might be a spurious message since in the associated trace file it first says: FATAL ERROR IN TWO-TASK SERVER: error = 12571 which is a networking error. Maybe just had a connection problem and gave a bad error. Mon Dec 22 12:05:43 2003Errors in file f:\oracle\trace\ORA04312.TRC:ORA-00942: table or view does not exist In the trace file: *** SESSION ID:(13.65246) 2003-12-22 12:05:43.380FATAL ERROR IN TWO-TASK SERVER: error = 12571*** 2003-12-22 12:05:43.427ksedmp: internal or fatal errorORA-00942: table or view does not existNo current SQL statement being executed. @m:\sql\grants\triggers\pa_projects_all_rta Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113 ORA04312.TRC Description: Binary data
ora-942 message in alert log
Yesterday, wereceived the below message in our alert log. Normally, we do not see such messages in an alert log.It might be a spurious message since in the associated trace file it first says: FATAL ERROR IN TWO-TASK SERVER: error = 12571 which is a networking error. Maybe just had a connection problem and gave a bad error. Mon Dec 22 12:05:43 2003Errors in file f:\oracle\trace\ORA04312.TRC:ORA-00942: table or view does not exist In the trace file: *** SESSION ID:(13.65246) 2003-12-22 12:05:43.380FATAL ERROR IN TWO-TASK SERVER: error = 12571*** 2003-12-22 12:05:43.427ksedmp: internal or fatal errorORA-00942: table or view does not existNo current SQL statement being executed. @m:\sql\grants\triggers\pa_projects_all_rta Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113
RE: ora-4030 pga memory allocation running wild
This is scary, I'm planning to upgrade 9.2.0.4 from 9.2.0.2. I don't know how removing pga_aggegrate_target will help reducing memory!! Does the program have any memory tables, etc? Did you monitor the PGA size from the Oracle side using v$sesstat? A sql by itself can't consume this memory except there is a major bug some where, which I doubt! Please keep us updated. Thanks Waleed -Original Message-From: Jeroen van Sluisdam [mailto:[EMAIL PROTECTED]Sent: Tuesday, December 23, 2003 10:24 AMTo: Multiple recipients of list ORACLE-LSubject: ora-4030 pga memory allocation running wild Hi, I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has 4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen
RE: TNSPING VS. REGULAR PING..! WHY SUCH A DIFFERENCE
Mark is right, Ping uses ICMP Echo_Request and Echo_Response. Since ICMP sit on the Network Layer along with ARP and the IP protocols, it does not need to use any TCP or UDP port number. TCP and UDP protocols need a number to differentiate from the rest of protocols, therefore the expression port number which translates into an OS network service. Julio Cesar Quijada-Reina Programmer Analyst Computer Services at Alfred State College -Original Message- Mark Leith Sent: Tuesday, December 23, 2003 8:54 AM To: Multiple recipients of list ORACLE-L PING doesnt actually use a port number, as it's not TCP/IP based - it's a part of the ICMP protocol.. Mark -Original Message- Ganesh Raja Sent: 23 December 2003 13:40 To: Multiple recipients of list ORACLE-L TNSping uses the Port 1521 to communicate .. Not Sure which Port Ping will use.. HTH Regards, Ganesh R HP : (+65)9067-8474 Mail : [EMAIL PROTECTED] == All Opinions expressed are my own and do not in anyway reflect those of my employer == -Original Message- Mercadante, Thomas F Sent: Tuesday, December 23, 2003 20:54 PM To: Multiple recipients of list ORACLE-L ANd one more thing about Ping vs Tnsping when going thru a firewall. Some firewalls are setup to not allow a Ping to pass thru, but sql connections are allowed. So a Ping will return not found, while a tnsping will return ok. I have that situation here all over the freakin place. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 23, 2003 5:54 AM To: Multiple recipients of list ORACLE-L I came across just this last week with one of our monitoring tools. We set up an execution of a script that was using PING to check whether the status of a list of remote POS devices to make sure they were available. The collection worked fine - until we shutdown the POS device, and physically switched it off - and the PING still replied! The POS devices have UPS built in to them.. The client also thought it may have been the way that DNS/DHCP was set up via the router. It stumped me for a time.. ;) We use NETSVC now to check for the status of a service. Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Jared Still Sent: 23 December 2003 04:34 To: Multiple recipients of list ORACLE-L Paul mentioned a few reasons for this. Another is that a ping does not get past the NIC. The ping is answered by software running on the card. You may have noticed at times that a ping is not a reliable method for determining if a server is still functioning. The OS can crash, but the NIC still responds to a ping. Tnsping on the other hand must get a response from Oracle Net service or daemon running on the server, a much longer path as Paul pointed out. HTH Jared On Mon, 2003-12-22 at 17:29, Murali Vallath wrote: I have recently noticed in this one situation that there is a great difference between a tnsping vs a regular ping to the same server. for example this tnsping took about 270 ms which is strange and its consistent Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADD RESS_LIST = (load_balance = on) (failover = on) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost1.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2.com)( PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = xyzdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 20) (DELAY = 15 OK (270 msec) and a ping to the same host Ping statistics for x.x.x.x: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 61ms, Maximum = 70ms, Average = 67ms Why could there be such a difference? - Do you Yahoo!? Yahoo! Photos - Get your photo on the big screen in Times Square -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version:
RE: ora-942 message in alert log
Sorry. I read your email twice and still have not seen a question. Are you wondering if this is normal? :) Tom Mercadante Oracle Certified Professional -Original Message-From: Jeffrey Beckstrom [mailto:[EMAIL PROTECTED]Sent: Tuesday, December 23, 2003 10:39 AMTo: Multiple recipients of list ORACLE-LSubject: ora-942 message in alert log Yesterday, wereceived the below message in our alert log. Normally, we do not see such messages in an alert log.It might be a spurious message since in the associated trace file it first says: FATAL ERROR IN TWO-TASK SERVER: error = 12571 which is a networking error. Maybe just had a connection problem and gave a bad error. Mon Dec 22 12:05:43 2003Errors in file f:\oracle\trace\ORA04312.TRC:ORA-00942: table or view does not exist In the trace file: *** SESSION ID:(13.65246) 2003-12-22 12:05:43.380FATAL ERROR IN TWO-TASK SERVER: error = 12571*** 2003-12-22 12:05:43.427ksedmp: internal or fatal errorORA-00942: table or view does not existNo current SQL statement being executed. @m:\sql\grants\triggers\pa_projects_all_rta Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional Transit Authority1240 W. 6th StreetCleveland, Ohio 44113
Standard Versus Enterprise Edition
Hi Qs Does a Pro*C application (Banking) need to be compiled with Standard Edition as Oracle Libraries are being used in the Compilation OR can it be compiled with the Enterprise Edition simply deployed elsewhere at Customer site containing the Standard Edition? NOTE Application is making OCI Calls to the DB. Does Standard Edition support the same? Qs Which are the important differences between Standard Edition of 8i/9i versus Enterprise Edition? Will provide any info needed Thanks Vivek -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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).
Standard Vs. Enterprise Edition for Application Compilation
Hi Qs Does a Pro*C application (Banking) need to be compiled with Standard Edition as Oracle Libraries are being used in the Compilation OR can it be compiled with the Enterprise Edition simply deployed elsewhere at Customer site containing the Standard Edition? NOTE Application is making OCI Calls to the DB. Does Standard Edition support the same? Qs Which are the important differences between Standard Edition of 8i/9i versus Enterprise Edition? Will provide any info needed Thanks Vivek -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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).
OT: Display unix directory hierarchy
Hi, listers. As documentation for a project, I would like to display some unix directories in hierarchical format and add the output to the documentation set. (Solaris 9) Either flowchart-like or explorer-like will do. Sorta like what is shown below. Does anyone know of a freebie tool that will do this? (Or is this some fancy ls command I'm missing?) Thanks for any help. Barb /dna /orasrv /1.4 /scripts /1.7 /scripts /logs __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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: Hit Ratio
Yong,
Connor's script is not a joke, it's a proof by counterexample that the
advice You SQL is tuned if and only if it has a high hit ratio is
rubbish.
The buffer cache hit ratio is a tool. Used properly, nobody's objecting.
It's proper use? To answer the question, What percentage of LIO calls
can be satisfied without an OS read call? The correct point that many
on this list make over and over again, is that this is often the wrong
question to be asking. (And actually, the conventional BCHR=(L-P)/L
formula doesn't answer that question very well anyway; see Steve Adams's
site for more detail.)
It's not the ratio that needs condemning, it's the advice about how to
use the ratio. The ratio just happens to be the emblem on the flag.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Yong Huang
Sent: Tuesday, December 23, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a
humor. Only
if you carefully comtemplate it will you see that there's no relevance
of the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event
times. If
you need very long db file reads, create a big table and keep scanning
it. If
you need long enqueue waits, create a table and insert a row. Create 10
or 100
sessions (depending on your patience) and delete from that table and
wait. The
fact that you can get arbitary wait times does not reduce the efficacy
of wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very
insufficient
used alone, to tune the database. The reason is that they don't contain
enough
information to tune the system with. This is the only reason we should
not
solely rely on them; in fact, not using them at all doesn't hurt much.
The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not condemned.
Although in UNIX performance tuning one looks at absolute numbers such
as scan
rate, CPU usage and netstat output more often, hit ratios in some sar
output
are still occasionally used. Most ratios could still be distored by a
rogue
user repeatedly doing, say, find / for inodes or find / -exec grep
SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage
patterns
invalidates your numbers even if you're using a well respected tuning
method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement scripts.
Especially when your bonus depends on it, this is a good time to perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
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.net
--
Author: Cary Millsap
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).
Standard Vs. Enterprise Edition for Application Compilation
Hi Qs Does a Pro-C application (Banking) need to be compiled with Standard Edition as Oracle Libraries are being used in the Compilation OR can it be compiled with the Enterprise Edition simply deployed elsewhere at Customer site containing the Standard Edition? NOTE Application is making OCI Calls to the DB. Does Standard Edition support the same? Qs Which are the important differences between Standard Edition of 8i/9i versus Enterprise Edition? Will provide any info needed Thanks Vivek -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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: Display unix directory hierarchy
Hi Barb, You can use tree/dtree to do this. Windows also has a tree dos command to do the same. HTH Mark -Original Message- Barbara Baker Sent: 23 December 2003 08:49 To: Multiple recipients of list ORACLE-L Hi, listers. As documentation for a project, I would like to display some unix directories in hierarchical format and add the output to the documentation set. (Solaris 9) Either flowchart-like or explorer-like will do. Sorta like what is shown below. Does anyone know of a freebie tool that will do this? (Or is this some fancy ls command I'm missing?) Thanks for any help. Barb /dna /orasrv /1.4 /scripts /1.7 /scripts /logs __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.537 / Virus Database: 332 - Release Date: 06/11/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.537 / Virus Database: 332 - Release Date: 06/11/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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: Display unix directory hierarchy
Barbara,
You might try tossing the following snippet in a shell script. I call it
dtree and you can pass a parameter of a directory.
=== snip
D=${1:-`pwd`}
(cd $D; pwd)
find $D -type d -print | sort |
sed -e s,^$D,,\
-e /^$/d\
-e s,[^/]*/\([^/]*\)$,\:-\1,\
-e s,[^/]*/,: ,g
== snip
... Output looks like
:-somedir1
: :-somedir2
: :-somedir3
: :-somedir4
:-somedir2
:-somedir3
:-somedir4
: :-somedir1
: : :-somedir2
: : :-somedir3
: : :-somedir4
: :-somedir2
: :-somedir3
-Original Message-
Sent: Tuesday, December 23, 2003 9:49 AM
To: Multiple recipients of list ORACLE-L
Hi, listers.
As documentation for a project, I would like to
display some unix directories in hierarchical format
and add the output to the documentation set.
(Solaris 9)
Either flowchart-like or explorer-like will do. Sorta
like what is shown below. Does anyone know of a
freebie tool that will do this? (Or is this some
fancy ls command I'm missing?)
Thanks for any help.
Barb
/dna
/orasrv
/1.4
/scripts
/1.7
/scripts
/logs
__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Barbara Baker
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.net
--
Author: Seibert, Jason
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: Hit Ratio
Yong:
I have not seen all the threads on this. So there are chances some body
might have
covered this/I may be missing some interesting things..But the issue is,
tuning or measuring the
database performance ONLY with Hit Ratios. By high hit ratios Damagement
will tend to understand , that percentage of data is read from the
cache/memory
and try to add memory till the get closer to 100..
I think what we need to understand is the interpretation of Hit ratio. 90%
HIT
ratio does not mean 90% of the data is read from the disk. It just tells a
block
or buffer which was read in to cache is RE-READ 9 times before it goes to
disk.
I have seen many sites with oversized buffer cache/shared pool targetting
100%
hit ratio and suffering huge latch contention. I have been to a site
recently where a FLUSH
shared pool took nearly 5 minutes and checkpoint took close to a minute,
with 99.99%CHR.
But simulating high wait times by yout tricks for a particular session may
bump the wait times
You may probably generate high times for enqueue or any of the IO
events. But when you use 10046 or V$session_wait for a particular session,
the bumped numbers will not be affecting the diagnosability of your
problem.
But if you want to start questioning, you can question the timing details of
the
wait events. Oracle uses gettimeofday () to get the time of the wait events
and
if you alter the system time couple of times, that may give some odd numbers
to the entire timing data. But the bottomline is , Hit ratios are
beautiful numbers
but, you can not relate the pattern to the performance. May be you can
compare
the hit ratio when the system is good/bad and figure out there is a change
in IO
pattern between those interval.. IMHO and YMMV.
Regards,
K Gopalakrishnan
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, December 23, 2003 8:59 PM
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a humor.
Only
if you carefully comtemplate it will you see that there's no relevance of
the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event
times. If
you need very long db file reads, create a big table and keep scanning it.
If
you need long enqueue waits, create a table and insert a row. Create 10 or
100
sessions (depending on your patience) and delete from that table and wait.
The
fact that you can get arbitary wait times does not reduce the efficacy of
wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very
insufficient
used alone, to tune the database. The reason is that they don't contain
enough
information to tune the system with. This is the only reason we should not
solely rely on them; in fact, not using them at all doesn't hurt much. The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not condemned.
Although in UNIX performance tuning one looks at absolute numbers such as
scan
rate, CPU usage and netstat output more often, hit ratios in some sar
output
are still occasionally used. Most ratios could still be distored by a
rogue
user repeatedly doing, say, find / for inodes or find / -exec grep
SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage
patterns
invalidates your numbers even if you're using a well respected tuning
method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement scripts.
Especially when your bonus depends on it, this is a good time to perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
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
RE: STATSPACK interpretation
Thomas,
The version count is the number of child cursors
present in the cache for this SQL. The cursor is
not being shared for some reason with 456 versions.
The 400m of memory seems a bit excessive.
There is a script at Jonathan's site with some info
about v$sqlarea and a script you can run that looks
at the current memory requirements for a SQL statement.
http://www.jlcomp.demon.co.uk/sqlarea.html
Does the output match what you see in statspack?
Also, the number of executions is much lower than
the version count, which is rather odd. There's a bug
in early 9i versions that would cause this, but was
supposed to be corrected by 9.2.0.2.
In experimenting with this, I managed to get 4 different
sessions to create 2 versions of a cursor. I'm not sure
why as it was pl/sql and variables were used for the calling
parameters.
A 'select * from v$sql_shared_cursor' did not reveal any
reason for it.
After bouncing the database and trying this again, I couldn't
duplicate it.
Maybe a couple of things to pursue here, but perhaps not
an abundance of help. :(
Jared
On Tue, 2003-12-23 at 04:44, Thomas Jeff wrote:
Jared,
Digging into it more, I found out that it's called from an
Informatica client. Apparently, the gist of the client-side
algorithim is as follows:
For each row in (some view)
Call generate_product_keys
MERGE (upsert) into product table
end loop
CREATE OR REPLACE PROCEDURE generate_product_keys
(v_marketing_model_id IN VARCHAR2,
v_model_country_cd IN VARCHAR2,
v_product_seq_id IN OUT NUMBER,
v_product_id IN OUT NUMBER) IS
BEGIN
IF v_product_seq_id IS NOT NULL THEN
NULL;
ELSE
SELECT seq_product_seq_id.nextval
INTO v_product_seq_id
FROM dual;
BEGIN
SELECT product_id
INTO v_product_id
FROM product
WHERE marketing_model_id = v_marketing_model_id
AND model_country_cd = v_model_country_cd;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT seq_product_id.nextval
INTO v_product_id
FROM dual;
END;
END IF;
END generate_product_keys;
/
Thanks.
-Original Message-
Jared Still
Sent: Monday, December 22, 2003 6:15 PM
To: Multiple recipients of list ORACLE-L
What is taking place inside GENERATE_PRODUCT_KEYS() ?
Could be dynamic SQL of the worst kind in there. That
is, not using bind variables.
A 10046 trace level 4 or 12 will show you what is
going on there.
Jared
On Mon, 2003-12-22 at 12:39, Thomas Jeff wrote:
We recently experienced a crash on our prod datewarehouse running
9.2.0.2 on
AIX 4.3.3.The cause of the crash was 4031 errors generated by
background
processes (Oracle support has confirmed there is a bug involved),
however, since that crash occurred, a certain nightly batch job has
slowed to a
crawl.
Trying to recreate what has happened, I came across this in the
STATSPACK report.
The interval for this report is 30 minutes.
Is it telling me that I have 746 versions of this call eating up 400+
mb at
the time of the snapshot? Why would that be? The procedure in
question
uses bind variables.
SQL ordered by Sharable Memory for DB: DSSP Instance: DSSP Snaps: 3309
-3310
- End Sharable Memory Threshold: 1048576
Sharable Mem (b) Executions % Total Hash Value
---
483,580,268 57 411.8539672786
Module: [EMAIL PROTECTED] (TNS V1-V3)
BEGINGENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ; END;
-
SQL ordered by Version Count for DB: DSSP Instance: DSSP Snaps: 3309
-3310
- End Version Count Threshold:20
Version
Count Executions Hash Value
746 57539672786
Module: [EMAIL PROTECTED] (TNS V1-V3)
BEGINGENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ; END;
Jeffery D Thomas
DBA
Thomson Information Services
Thomson, Inc.
Email: [EMAIL PROTECTED]
Indy DBA Master Documentation available at:
http://gkmqp.tce.com/tis_dba
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jared Still
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: ora-4030 pga memory allocation running wild
I'm using auto pga allocation on 9.2.0.3 without any problem. You don't mention which version. You can turn it off with 'alter system set workarea_size_policy=manual; Jared On Tue, 2003-12-23 at 07:24, Jeroen van Sluisdam wrote: Hi, I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has 4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: Risk of knowing password hash value (Was: OEM permissions)
When I brought the issue up, I didn't know if one could in fact maliciously use that info. And, as I originally stated, it was something I had not tried. But paranoia (healthy, I think) dictates there's gotta be a way. When one looks at the Unix password world which brought about the necessity for a shadow file, and the evils of the old NIS where ypcat was available, you have to wonder why allowing access to the encrypted passwords for Unix is considered a dumb thing to do, but somehow in Oracle it would be an OK thing to do. I'm inclined to say that Oracle restricted access to the views and underlying tables for reasons more substantial than just to frustrate non-privileged users. And, if I'm not mistaken, the specs on the views are subject to change without notice. I have enough to do without trying to stay on top of every stinkin' view in Oracle in every stinkin' release and how one might use that view in naughty ways. For what it's worth, after haggling and fussing, we were able to compromise on this. We haven't tried to tear each of these apart to see how it might be abused. If any of you have some warnings to provide, please do! -- Must run this as SYS create role DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PROCESS to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESSION to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCH to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCHNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCHHOLDER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LOCK to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$MYSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SYSSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$STATNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ACCESS to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$FILESTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROLLNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROLLSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SGA to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PARAMETER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROWCACHE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LIBRARYCACHE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$INSTANCE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$DISPATCHER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLAREA to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLTEXT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLTEXT_WITH_NEWLINES to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$OPEN_CURSOR to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PQ_SYSSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SGASTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SHARED_SERVER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$DATAFILE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$TABLESPACE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESS_IO to DBARTISAN_USER_ROLE; grant SELECT on SYS.ALL_OBJECTS to DBARTISAN_USER_ROLE; grant SELECT on SYS.DBA_ROLLBACK_SEGS to DBARTISAN_USER_ROLE; grant SELECT on SYS.PRODUCT_COMPONENT_VERSION to DBARTISAN_USER_ROLE; grant SELECT on SYS.DBA_EXTENTS to DBARTISAN_USER_ROLE; grant DBARTISAN_USER_ROLE to USER_WE_DONT_LIKE; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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).
Interesting analysis of MySQL code quality
http://www.eweek.com/article2/0,4149,1420487,00.asp
RE: Unix question
grep -v try file will give you all the lines which do not contain try cat file|sed s/try//g newfile will strip the characters try from all lines HTH, Bambi. -Original Message- Sent: Monday, December 22, 2003 6:54 AM To: Multiple recipients of list ORACLE-L Hallo all of you, Is there anyone whom could help me with the unix command how to find all rows , that doesnt exists try in a file. I mean how to find all rows which doesnt have the characters try in. Maybe this is too simple, but would appreciate anyone whom could give me some quick help. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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.net -- Author: Bellow, Bambi 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: RE: Hit Ratio
are there really that many people who use hit ratio?
From: Cary Millsap [EMAIL PROTECTED]
Date: 2003/12/23 Tue AM 11:49:33 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Hit Ratio
Yong,
Connor's script is not a joke, it's a proof by counterexample that the
advice You SQL is tuned if and only if it has a high hit ratio is
rubbish.
The buffer cache hit ratio is a tool. Used properly, nobody's objecting.
It's proper use? To answer the question, What percentage of LIO calls
can be satisfied without an OS read call? The correct point that many
on this list make over and over again, is that this is often the wrong
question to be asking. (And actually, the conventional BCHR=(L-P)/L
formula doesn't answer that question very well anyway; see Steve Adams's
site for more detail.)
It's not the ratio that needs condemning, it's the advice about how to
use the ratio. The ratio just happens to be the emblem on the flag.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Yong Huang
Sent: Tuesday, December 23, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a
humor. Only
if you carefully comtemplate it will you see that there's no relevance
of the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event
times. If
you need very long db file reads, create a big table and keep scanning
it. If
you need long enqueue waits, create a table and insert a row. Create 10
or 100
sessions (depending on your patience) and delete from that table and
wait. The
fact that you can get arbitary wait times does not reduce the efficacy
of wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very
insufficient
used alone, to tune the database. The reason is that they don't contain
enough
information to tune the system with. This is the only reason we should
not
solely rely on them; in fact, not using them at all doesn't hurt much.
The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not condemned.
Although in UNIX performance tuning one looks at absolute numbers such
as scan
rate, CPU usage and netstat output more often, hit ratios in some sar
output
are still occasionally used. Most ratios could still be distored by a
rogue
user repeatedly doing, say, find / for inodes or find / -exec grep
SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage
patterns
invalidates your numbers even if you're using a well respected tuning
method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement scripts.
Especially when your bonus depends on it, this is a good time to perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
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.net
--
Author: Cary Millsap
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
RE: Interesting analysis of MySQL code quality
Well, When your declared intentions is to do one thing and one thing only there are a whole lot less places to make mistakes. Conversly when your intentions are to "rule the world" you also open up the world of oops's. Humm, Wonder if there's a lesson in that for Cousin BillyG??? Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, December 23, 2003 1:09 PMTo: Multiple recipients of list ORACLE-LSubject: Interesting analysis of MySQL code qualityhttp://www.eweek.com/article2/0,4149,1420487,00.asp
does CPU usage matter when scaling?
The softwrae engineers are measuring CPU usage. I usually ignore this and dont care about that value. I dont have any docs on it. anyone have any docs on this? or am I wrong? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: STATSPACK interpretation
I recall James Morle saying something about code not being sharable if the declared sizes of the bind variables don't match. If Informatica is using a 3GL to call anonymous pl/sql blocks with different bind variables every time, perhaps it is causing a bind variable mismatch. As for the 400MB - I've often noticed oddities where a new entry is created, but carries forward a report of the memory requirements of earlier variants, so if you have 10 cursors, they don't report 10 units of memory, but 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 units. It is possible that you are seeing some effect like this. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 5:09 PM Thomas, The version count is the number of child cursors present in the cache for this SQL. The cursor is not being shared for some reason with 456 versions. The 400m of memory seems a bit excessive. There is a script at Jonathan's site with some info about v$sqlarea and a script you can run that looks at the current memory requirements for a SQL statement. http://www.jlcomp.demon.co.uk/sqlarea.html Does the output match what you see in statspack? Also, the number of executions is much lower than the version count, which is rather odd. There's a bug in early 9i versions that would cause this, but was supposed to be corrected by 9.2.0.2. In experimenting with this, I managed to get 4 different sessions to create 2 versions of a cursor. I'm not sure why as it was pl/sql and variables were used for the calling parameters. A 'select * from v$sql_shared_cursor' did not reveal any reason for it. After bouncing the database and trying this again, I couldn't duplicate it. Maybe a couple of things to pursue here, but perhaps not an abundance of help. :( Jared On Tue, 2003-12-23 at 04:44, Thomas Jeff wrote: Jared, Digging into it more, I found out that it's called from an Informatica client. Apparently, the gist of the client-side algorithim is as follows: For each row in (some view) Call generate_product_keys MERGE (upsert) into product table end loop -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: RE: Hit Ratio
I hope not, but I think so.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, December 23, 2003 12:29 PM
To: Multiple recipients of list ORACLE-L
are there really that many people who use hit ratio?
From: Cary Millsap [EMAIL PROTECTED]
Date: 2003/12/23 Tue AM 11:49:33 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Hit Ratio
Yong,
Connor's script is not a joke, it's a proof by counterexample that the
advice You SQL is tuned if and only if it has a high hit ratio is
rubbish.
The buffer cache hit ratio is a tool. Used properly, nobody's
objecting.
It's proper use? To answer the question, What percentage of LIO calls
can be satisfied without an OS read call? The correct point that many
on this list make over and over again, is that this is often the wrong
question to be asking. (And actually, the conventional BCHR=(L-P)/L
formula doesn't answer that question very well anyway; see Steve
Adams's
site for more detail.)
It's not the ratio that needs condemning, it's the advice about how to
use the ratio. The ratio just happens to be the emblem on the flag.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Yong Huang
Sent: Tuesday, December 23, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a
humor. Only
if you carefully comtemplate it will you see that there's no relevance
of the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event
times. If
you need very long db file reads, create a big table and keep scanning
it. If
you need long enqueue waits, create a table and insert a row. Create
10
or 100
sessions (depending on your patience) and delete from that table and
wait. The
fact that you can get arbitary wait times does not reduce the efficacy
of wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very
insufficient
used alone, to tune the database. The reason is that they don't
contain
enough
information to tune the system with. This is the only reason we should
not
solely rely on them; in fact, not using them at all doesn't hurt much.
The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not
condemned.
Although in UNIX performance tuning one looks at absolute numbers such
as scan
rate, CPU usage and netstat output more often, hit ratios in some sar
output
are still occasionally used. Most ratios could still be distored by a
rogue
user repeatedly doing, say, find / for inodes or find / -exec grep
SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage
patterns
invalidates your numbers even if you're using a well respected tuning
method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement
scripts.
Especially when your bonus depends on it, this is a good time to
perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
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:
RE: Display unix directory hierarchy
Here's a freebie...
Bambi.
=
#!/bin/ksh
LEVEL=1
INCREMENT=FALSE
ls -lR|grep -v ^total|while read i
do
echo $i|grep ^d /dev/null 2/dev/null
if [ $? -eq 0 ] ;
then
continue
fi
FILE=`echo $i|awk '{print $NF}'`
echo $i|grep \/ /dev/null 2/dev/null
if [ $? -eq 0 ] ;
then
LEVEL=`expr \`echo $i|awk -F/ '{print NF}'\` - 1`
FILE=`echo $i|awk -F/ '{print / $NF}'|sed s/://`
INCREMENT=TRUE
fi
CTR=1
while [ $CTR -lt $LEVEL ] ;
do
echo \c
let CTR=CTR+1
done
echo $FILE
if [ $INCREMENT = TRUE ] ;
then
let LEVEL=LEVEL+1
INCREMENT=FALSE
fi
done|pg
==
-Original Message-
Sent: Tuesday, December 23, 2003 10:49 AM
To: Multiple recipients of list ORACLE-L
Hi, listers.
As documentation for a project, I would like to
display some unix directories in hierarchical format
and add the output to the documentation set.
(Solaris 9)
Either flowchart-like or explorer-like will do. Sorta
like what is shown below. Does anyone know of a
freebie tool that will do this? (Or is this some
fancy ls command I'm missing?)
Thanks for any help.
Barb
/dna
/orasrv
/1.4
/scripts
/1.7
/scripts
/logs
__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Barbara Baker
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.net
--
Author: Bellow, Bambi
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: does CPU usage matter when scaling?
Ryan, Take a look at Why you should focus on LIOs instead of PIOs at www.hotsos.com/e-library. If you have access to the book Optimizing Oracle Performance, check out chapter 9. If an application is written not to crash into some kind of serialization barrier (latch free, enqueue, etc.), then the thing it *should* get stuck spending most of its time doing is consuming CPU service. Applications that consume less CPU service scale better than apps that consume more. I would then say that it's FANTASTIC that your software engineers are looking at CPU consumption as they design, build, and test their code. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 12:39 PM To: Multiple recipients of list ORACLE-L The softwrae engineers are measuring CPU usage. I usually ignore this and dont care about that value. I dont have any docs on it. anyone have any docs on this? or am I wrong? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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.net -- Author: Cary Millsap 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: Risk of knowing password hash value (Was: OEM permissions)
As long Oracle can manage to keep its modified DES algorithm secret, this should make it somewhat difficult to crack passwords in the manner that can be done with unix passwords. It could still be done, but the time required would make it just too time consuming IMO. Jared On Tue, 2003-12-23 at 09:44, [EMAIL PROTECTED] wrote: When I brought the issue up, I didn't know if one could in fact maliciously use that info. And, as I originally stated, it was something I had not tried. But paranoia (healthy, I think) dictates there's gotta be a way. When one looks at the Unix password world which brought about the necessity for a shadow file, and the evils of the old NIS where ypcat was available, you have to wonder why allowing access to the encrypted passwords for Unix is considered a dumb thing to do, but somehow in Oracle it would be an OK thing to do. I'm inclined to say that Oracle restricted access to the views and underlying tables for reasons more substantial than just to frustrate non-privileged users. And, if I'm not mistaken, the specs on the views are subject to change without notice. I have enough to do without trying to stay on top of every stinkin' view in Oracle in every stinkin' release and how one might use that view in naughty ways. For what it's worth, after haggling and fussing, we were able to compromise on this. We haven't tried to tear each of these apart to see how it might be abused. If any of you have some warnings to provide, please do! -- Must run this as SYS create role DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PROCESS to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESSION to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCH to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCHNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCHHOLDER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LOCK to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$MYSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SYSSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$STATNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ACCESS to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$FILESTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROLLNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROLLSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SGA to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PARAMETER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROWCACHE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LIBRARYCACHE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$INSTANCE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$DISPATCHER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLAREA to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLTEXT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLTEXT_WITH_NEWLINES to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$OPEN_CURSOR to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PQ_SYSSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SGASTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SHARED_SERVER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$DATAFILE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$TABLESPACE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESS_IO to DBARTISAN_USER_ROLE; grant SELECT on SYS.ALL_OBJECTS to DBARTISAN_USER_ROLE; grant SELECT on SYS.DBA_ROLLBACK_SEGS to DBARTISAN_USER_ROLE; grant SELECT on SYS.PRODUCT_COMPONENT_VERSION to DBARTISAN_USER_ROLE; grant SELECT on SYS.DBA_EXTENTS to DBARTISAN_USER_ROLE; grant DBARTISAN_USER_ROLE to USER_WE_DONT_LIKE; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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.net -- Author: Jared Still 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: STATSPACK interpretation
Thanks Jonathan. I was wondering about those memory columns possibly acting in that manner. I've never used them to track memory though, and wasn't sure how they might act. Google and MetaLink didn't turn up anything too useful in the regard. Now that you mention it, I recall reading recently somewhere that different size bind variables could cause multiple versions. Probably James, but I can't recall where I saw it. Google, Metalinkm mailing lists and a plethora of books and white papers have made it quite difficult, at least for me, to always remember the source of tidbits such as this. Back to the memory, I was somehow able to cause 2 version of the same SQL, as mentioned earlier, but the reason for it did not appear in v$sql_shared_cursor. Jared On Tue, 2003-12-23 at 10:54, Jonathan Lewis wrote: I recall James Morle saying something about code not being sharable if the declared sizes of the bind variables don't match. If Informatica is using a 3GL to call anonymous pl/sql blocks with different bind variables every time, perhaps it is causing a bind variable mismatch. As for the 400MB - I've often noticed oddities where a new entry is created, but carries forward a report of the memory requirements of earlier variants, so if you have 10 cursors, they don't report 10 units of memory, but 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 units. It is possible that you are seeing some effect like this. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 5:09 PM Thomas, The version count is the number of child cursors present in the cache for this SQL. The cursor is not being shared for some reason with 456 versions. The 400m of memory seems a bit excessive. There is a script at Jonathan's site with some info about v$sqlarea and a script you can run that looks at the current memory requirements for a SQL statement. http://www.jlcomp.demon.co.uk/sqlarea.html Does the output match what you see in statspack? Also, the number of executions is much lower than the version count, which is rather odd. There's a bug in early 9i versions that would cause this, but was supposed to be corrected by 9.2.0.2. In experimenting with this, I managed to get 4 different sessions to create 2 versions of a cursor. I'm not sure why as it was pl/sql and variables were used for the calling parameters. A 'select * from v$sql_shared_cursor' did not reveal any reason for it. After bouncing the database and trying this again, I couldn't duplicate it. Maybe a couple of things to pursue here, but perhaps not an abundance of help. :( Jared On Tue, 2003-12-23 at 04:44, Thomas Jeff wrote: Jared, Digging into it more, I found out that it's called from an Informatica client. Apparently, the gist of the client-side algorithim is as follows: For each row in (some view) Call generate_product_keys MERGE (upsert) into product table end loop -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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.net -- Author: Jared Still 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: RE: Hit Ratio
Cary's being diplomatic, as well as engaging in some wishful thinking.
:)
They appear to be quite prevalent. There are other microcosms of
Oracle users that you will find from time to time that realize
how Oracle works, and how to go about fixing performance problems.
If though you consider the widespread use of texts that propagate
ancient tuning advice, you must come to the conclusion that it is
still in the mainstream.
Jared
On Tue, 2003-12-23 at 10:54, Cary Millsap wrote:
I hope not, but I think so.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, December 23, 2003 12:29 PM
To: Multiple recipients of list ORACLE-L
are there really that many people who use hit ratio?
From: Cary Millsap [EMAIL PROTECTED]
Date: 2003/12/23 Tue AM 11:49:33 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Hit Ratio
Yong,
Connor's script is not a joke, it's a proof by counterexample that the
advice You SQL is tuned if and only if it has a high hit ratio is
rubbish.
The buffer cache hit ratio is a tool. Used properly, nobody's
objecting.
It's proper use? To answer the question, What percentage of LIO calls
can be satisfied without an OS read call? The correct point that many
on this list make over and over again, is that this is often the wrong
question to be asking. (And actually, the conventional BCHR=(L-P)/L
formula doesn't answer that question very well anyway; see Steve
Adams's
site for more detail.)
It's not the ratio that needs condemning, it's the advice about how to
use the ratio. The ratio just happens to be the emblem on the flag.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Yong Huang
Sent: Tuesday, December 23, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a
humor. Only
if you carefully comtemplate it will you see that there's no relevance
of the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event
times. If
you need very long db file reads, create a big table and keep scanning
it. If
you need long enqueue waits, create a table and insert a row. Create
10
or 100
sessions (depending on your patience) and delete from that table and
wait. The
fact that you can get arbitary wait times does not reduce the efficacy
of wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very
insufficient
used alone, to tune the database. The reason is that they don't
contain
enough
information to tune the system with. This is the only reason we should
not
solely rely on them; in fact, not using them at all doesn't hurt much.
The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not
condemned.
Although in UNIX performance tuning one looks at absolute numbers such
as scan
rate, CPU usage and netstat output more often, hit ratios in some sar
output
are still occasionally used. Most ratios could still be distored by a
rogue
user repeatedly doing, say, find / for inodes or find / -exec grep
SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage
patterns
invalidates your numbers even if you're using a well respected tuning
method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement
scripts.
Especially when your bonus depends on it, this is a good time to
perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
RE: RE: Hit Ratio
Jared, I'm going to take some exception to what Cary has said on the subject, but I believe in the end she'll agree with me. LIO's are inherently cheaper than PIO's simply because you have to complete the LIO before asking for a PIO. And no you can't work with data that has not been allocated a space in the buffer pool, but that does not mean that a high hit ratio is a good thing either. The problem with a high hit ratio consequently high LIO's is that the process is simply looking at the same bits of data over and over again in a senseless waste of CPU. The goal of any SQL tuning should be to get the process to complete in the shortest elapsed time as possible irrespective of the CPU, LIO, or PIO necessary to get the job done. That being the case a SQL statement should, ideally, be written to look at any single piece of data once and only once which would result in a lower LIO's higher PIO's. That being said, it's also the case that the ideal SQL statement has not yet been written, including those I author. But at least I try. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, December 23, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Cary's being diplomatic, as well as engaging in some wishful thinking. :) They appear to be quite prevalent. There are other microcosms of Oracle users that you will find from time to time that realize how Oracle works, and how to go about fixing performance problems. If though you consider the widespread use of texts that propagate ancient tuning advice, you must come to the conclusion that it is still in the mainstream. Jared On Tue, 2003-12-23 at 10:54, Cary Millsap wrote: I hope not, but I think so. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 12:29 PM To: Multiple recipients of list ORACLE-L are there really that many people who use hit ratio? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/12/23 Tue AM 11:49:33 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Hit Ratio Yong, Connor's script is not a joke, it's a proof by counterexample that the advice You SQL is tuned if and only if it has a high hit ratio is rubbish. The buffer cache hit ratio is a tool. Used properly, nobody's objecting. It's proper use? To answer the question, What percentage of LIO calls can be satisfied without an OS read call? The correct point that many on this list make over and over again, is that this is often the wrong question to be asking. (And actually, the conventional BCHR=(L-P)/L formula doesn't answer that question very well anyway; see Steve Adams's site for more detail.) It's not the ratio that needs condemning, it's the advice about how to use the ratio. The ratio just happens to be the emblem on the flag. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Yong Huang Sent: Tuesday, December 23, 2003 9:29 AM To: Multiple recipients of list ORACLE-L Hi, Carel-Jan and Rich, Connor's script to bump up buffer cache hit ratios is meant to be a humor. Only if you carefully comtemplate it will you see that there's no relevance of the fact that you can get any hit ratio to the fact that hit ratios are insufficient in performance tuning. It would be equally easy to write scripts to bump up some wait event times. If you need very long db file reads, create a big table and keep scanning it. If you need long enqueue waits, create a table and insert a row. Create 10 or 100 sessions (depending on your patience) and delete from that table and wait. The fact that you can get arbitary wait times does not reduce the efficacy of wait event interface as a performance tuning tool. Buffer cache or library cache hit ratios are not sufficient, very insufficient used alone, to tune the database. The reason is that they don't contain enough information to tune the system with. This is the only reason we should not solely rely on them; in fact, not using them at all doesn't hurt much. The reason is not that we can get any value we want by playing pranks. Hit ratios are still used in other performance tuning and not condemned. Although in UNIX performance tuning one looks at absolute numbers such as scan rate, CPU usage and netstat output more often, hit ratios in some sar output are still
RE: Hit Ratio
One place I can think of (the only place?) where BCHR may be useful,
is as a lower bound for the OPTIMIZER_INDEX_CACHING parameter. This
parameter pretty much asks the question that Cary posed in his email.
I say that the BCHR should be the lower bound for this value because
the question that OPTIMIZER_INDEX_CACHING asks is slightly different
in that it's asking specifically about index blocks. BCHR is across
all block types, and, in some cases, on some databases, the answer to
to those slightky different questions may vary greatly.
What it comes down to is that BCHR is an isolated datapoint. You can't
use it in isolation to determine anything.
This has been done to death. I'll shut up now. If there's any doubt,
read Cary's papers.
-Mark
-Original Message-
From: Cary Millsap [mailto:[EMAIL PROTECTED]
Sent: Tue 12/23/2003 11:49 AM
To: Multiple recipients of list ORACLE-L
Cc:
Subject:RE: Hit Ratio
Yong,
Connor's script is not a joke, it's a proof by counterexample that the
advice You SQL is tuned if and only if it has a high hit ratio is
rubbish.
The buffer cache hit ratio is a tool. Used properly, nobody's objecting.
It's proper use? To answer the question, What percentage of LIO calls
can be satisfied without an OS read call? The correct point that many
on this list make over and over again, is that this is often the wrong
question to be asking. (And actually, the conventional BCHR=(L-P)/L
formula doesn't answer that question very well anyway; see Steve Adams's
site for more detail.)
It's not the ratio that needs condemning, it's the advice about how to
use the ratio. The ratio just happens to be the emblem on the flag.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Yong Huang
Sent: Tuesday, December 23, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a
humor. Only
if you carefully comtemplate it will you see that there's no relevance
of the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event
times. If
you need very long db file reads, create a big table and keep scanning
it. If
you need long enqueue waits, create a table and insert a row. Create 10
or 100
sessions (depending on your patience) and delete from that table and
wait. The
fact that you can get arbitary wait times does not reduce the efficacy
of wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very
insufficient
used alone, to tune the database. The reason is that they don't contain
enough
information to tune the system with. This is the only reason we should
not
solely rely on them; in fact, not using them at all doesn't hurt much.
The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not condemned.
Although in UNIX performance tuning one looks at absolute numbers such
as scan
rate, CPU usage and netstat output more often, hit ratios in some sar
output
are still occasionally used. Most ratios could still be distored by a
rogue
user repeatedly doing, say, find / for inodes or find / -exec grep
SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage
patterns
invalidates your numbers even if you're using a well respected tuning
method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement scripts.
Especially when your bonus depends on it, this is a good time to perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
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
RE: STATSPACK interpretation
Title: STATSPACK interpretation
Using
dbms_application_info package also causes that there are several versions of
same statement - but theyshare same execution plan. You cancheck if
this is the case by queryingv$sqlarea (module and action
columns).
Regards,
Joze
-Original Message-From: Thomas Jeff
[mailto:[EMAIL PROTECTED]Sent: Monday, December 22, 2003
9:39 PMTo: Multiple recipients of list ORACLE-LSubject:
STATSPACK interpretation
We recently experienced a crash on our prod
datewarehouse running 9.2.0.2 on AIX 4.3.3. The cause of the crash was 4031 errors
generated by background processes
(Oracle support has confirmed there is a bug involved), however,
since that crash occurred, a certain
nightly batch job has slowed to a crawl.
Trying to recreate what has happened, I
came across this in the STATSPACK report. The interval for this report is 30
minutes.
Is it telling me that I have 746 versions
of this call eating up 400+ mb at the time of the snapshot? Why would that be?
The procedure in question uses bind
variables.
SQL ordered by Sharable Memory for DB: DSSP
Instance: DSSP Snaps: 3309 -3310 - End Sharable Memory Threshold: 1048576
Sharable Mem (b) Executions %
Total Hash Value ---
483,580,268
57 411.8 539672786 Module: [EMAIL PROTECTED] (TNS V1-V3) BEGIN GENERATE_PRODUCT_KEYS
(:1,:2,:3,:4) ; END;
-
SQL ordered by Version Count for DB: DSSP
Instance: DSSP Snaps: 3309 -3310 - End Version Count
Threshold: 20
Version Count Executions Hash
Value
746
57 539672786 Module: [EMAIL PROTECTED] (TNS V1-V3) BEGIN GENERATE_PRODUCT_KEYS (:1,:2,:3,:4)
; END;
Jeffery D Thomas DBA Thomson Information
Services Thomson, Inc.
Email: [EMAIL PROTECTED]
Indy DBA Master Documentation available
at: http://gkmqp.tce.com/tis_dba
Re: RE: does CPU usage matter when scaling?
the cpu right now is high because they are not-using bind variables. Which is being fixed. So I was ignoring CPU usage. Ive read that article. I dont remember any CPU material in there. I just focused on LIOs and didnt realize I needed to monitor CPU. However, high LIOs will lead to high CPU right? So in order to tune, wouldnt I concentrate on lowering LIOs and as a result that will bring down CPUs. I take CPU as a consequence of something, but not as a means to an ends. or am I wrong here? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/12/23 Tue PM 01:59:32 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: does CPU usage matter when scaling? Ryan, Take a look at Why you should focus on LIOs instead of PIOs at www.hotsos.com/e-library. If you have access to the book Optimizing Oracle Performance, check out chapter 9. If an application is written not to crash into some kind of serialization barrier (latch free, enqueue, etc.), then the thing it *should* get stuck spending most of its time doing is consuming CPU service. Applications that consume less CPU service scale better than apps that consume more. I would then say that it's FANTASTIC that your software engineers are looking at CPU consumption as they design, build, and test their code. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 12:39 PM To: Multiple recipients of list ORACLE-L The softwrae engineers are measuring CPU usage. I usually ignore this and dont care about that value. I dont have any docs on it. anyone have any docs on this? or am I wrong? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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.net -- Author: Cary Millsap 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.net -- Author: [EMAIL PROTECTED] 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: Display unix directory hierarchy
In addition to the fine solution from Bambi, Here's another approach that I
think will work. I did only minimal testing (in TRUE development tradition.
But ... But ... It worked OK in test!). One caveat: This relies on
recursion, so on a big directory tree you might get swatted with OS resource
limitations.
--
#!/bin/ksh
if [ $# -eq 1 ]; then
ARG=$1
else
ARG=0
export MYNAME=`pwd`/`basename $0`
fi
X=0
PAD=
while [ $X -lt $ARG ]; do
PAD=$PAD
X=$(( X + 1 ))
done
## list non-directory files first
for i in `ls -a1 2 /dev/null`; do
if [ $i = . -o $i = .. ]; then
continue
fi
if [ ! -d $i ]; then
echo $PAD$i
fi
done
## then plow into the directories
## NO. They ain't folders. They're DIRECTORIES.
for i in `ls -a1 2 /dev/null`; do
if [ $i = . -o $i = .. ]; then
continue
fi
if [ -d $i ]; then
echo $PAD/$i
{
cd $i
$MYNAME $(( $ARG + 1 ))
cd ..
}
fi
done
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
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: RE: Hit Ratio
At 12:04 23-12-03 -0800, you wrote: Jared, I'm going to take some exception to what Cary has said on the subject, but I believe in the end she'll agree with me. She? Cary, you didn't tell us about this surgery ;-) Dick, last time I saw Cary (October) he was very masculin. I bet this hasn't changed during the last two months. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel 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: STATSPACK interpretation
Jonathan, Wouldn't bind variable issue that prevents cursor from sharing be visible in bind_mismatch? How can one simulate this? var v varchar2(1) begin select count(5) into :v from dual; end; / select address, sql_text from v$sql where sql_text like '%count(5)%'; ADDRESS SQL_TEXT --- 6DE92A74 SELECT count(5) from dual 6DE960D0 begin select count(5) into :v from dual; end; -- Change a bind variable size: var v varchar2(30) begin select count(5) into :v from dual; end; / -- same output, no change, both sql and pl/sql wrapper cursors are still shared -- Change a bind variable type: var v number begin select count(5) into :v from dual; end; / ADDRESS SQL_TEXT 6DE92A74 SELECT count(5) from dual 6DE960D0 begin select count(5) into :v from dual; end; 6DE960D0 begin select count(5) into :v from dual; end; -- ok, here pl/sql parent (dep=0) cursor is no longer shared [EMAIL PROTECTED] select * from v$sql_shared_cursor where kglhdpar = '6DE960D0'; ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 6DE95B54 6DE960D0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 6DE86F94 6DE960D0 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N 2 rows selected. -- yep, bind variables mismatch Thanks, Boris Dali. --- Jonathan Lewis [EMAIL PROTECTED] wrote: I recall James Morle saying something about code not being sharable if the declared sizes of the bind variables don't match. If Informatica is using a 3GL to call anonymous pl/sql blocks with different bind variables every time, perhaps it is causing a bind variable mismatch. As for the 400MB - I've often noticed oddities where a new entry is created, but carries forward a report of the memory requirements of earlier variants, so if you have 10 cursors, they don't report 10 units of memory, but 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 units. It is possible that you are seeing some effect like this. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 5:09 PM Thomas, The version count is the number of child cursors present in the cache for this SQL. The cursor is not being shared for some reason with 456 versions. The 400m of memory seems a bit excessive. There is a script at Jonathan's site with some info about v$sqlarea and a script you can run that looks at the current memory requirements for a SQL statement. http://www.jlcomp.demon.co.uk/sqlarea.html Does the output match what you see in statspack? Also, the number of executions is much lower than the version count, which is rather odd. There's a bug in early 9i versions that would cause this, but was supposed to be corrected by 9.2.0.2. In experimenting with this, I managed to get 4 different sessions to create 2 versions of a cursor. I'm not sure why as it was pl/sql and variables were used for the calling parameters. A 'select * from v$sql_shared_cursor' did not reveal any reason for it. After bouncing the database and trying this again, I couldn't duplicate it. Maybe a couple of things to pursue here, but perhaps not an abundance of help. :( Jared On Tue, 2003-12-23 at 04:44, Thomas Jeff wrote: Jared, Digging into it more, I found out that it's called from an Informatica client. Apparently, the gist of the client-side algorithim is as follows: For each row in (some view) Call generate_product_keys MERGE (upsert) into product table end loop -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Risk of knowing password hash value (Was: OEM permissions)
Not really... you could easily compile a list of passwords and their associated hashes. Once this is done, it's just a simple matter of matching the hashes. -Original Message- Jared Still Sent: Tuesday, December 23, 2003 1:24 PM To: Multiple recipients of list ORACLE-L As long Oracle can manage to keep its modified DES algorithm secret, this should make it somewhat difficult to crack passwords in the manner that can be done with unix passwords. It could still be done, but the time required would make it just too time consuming IMO. Jared On Tue, 2003-12-23 at 09:44, [EMAIL PROTECTED] wrote: When I brought the issue up, I didn't know if one could in fact maliciously use that info. And, as I originally stated, it was something I had not tried. But paranoia (healthy, I think) dictates there's gotta be a way. When one looks at the Unix password world which brought about the necessity for a shadow file, and the evils of the old NIS where ypcat was available, you have to wonder why allowing access to the encrypted passwords for Unix is considered a dumb thing to do, but somehow in Oracle it would be an OK thing to do. I'm inclined to say that Oracle restricted access to the views and underlying tables for reasons more substantial than just to frustrate non-privileged users. And, if I'm not mistaken, the specs on the views are subject to change without notice. I have enough to do without trying to stay on top of every stinkin' view in Oracle in every stinkin' release and how one might use that view in naughty ways. For what it's worth, after haggling and fussing, we were able to compromise on this. We haven't tried to tear each of these apart to see how it might be abused. If any of you have some warnings to provide, please do! -- Must run this as SYS create role DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PROCESS to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESSION to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCH to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCHNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCHHOLDER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LOCK to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$MYSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SYSSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$STATNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ACCESS to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$FILESTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROLLNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROLLSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SGA to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PARAMETER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROWCACHE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LIBRARYCACHE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$INSTANCE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$DISPATCHER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLAREA to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLTEXT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLTEXT_WITH_NEWLINES to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$OPEN_CURSOR to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PQ_SYSSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SGASTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SHARED_SERVER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$DATAFILE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$TABLESPACE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESS_IO to DBARTISAN_USER_ROLE; grant SELECT on SYS.ALL_OBJECTS to DBARTISAN_USER_ROLE; grant SELECT on SYS.DBA_ROLLBACK_SEGS to DBARTISAN_USER_ROLE; grant SELECT on SYS.PRODUCT_COMPONENT_VERSION to DBARTISAN_USER_ROLE; grant SELECT on SYS.DBA_EXTENTS to DBARTISAN_USER_ROLE; grant DBARTISAN_USER_ROLE to USER_WE_DONT_LIKE; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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.net -- Author: Jared Still 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
seq
Hello list , why does : insert into test values (mysequence.nextval, mysequence.currval ) ; have the same effect as insert into test values (mysequence.currval, mysequence.nextval ) ; where 1. mysequence is create sequence mysequence increment by 1 start with 1 maxvalue 1000 nocycle nocache ; and 2. test is Name Null?Type ID NUMBER(7) ID2NUMBER -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Risk of knowing password hash value (Was: OEM permissions)
No. Two different users with the same password would have different hash values. So you would have to loop through a dictionary list for each user within your local database. Once you got a match, then you could logon to the target database with that user/password combo. - Alan Davey Senior Analyst/Project Leader Oracle 9i OCA; 3/4 OCP w) 973.267.5990 x458 w) 212.295.3458 -Original Message- Sent: Tuesday, December 23, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Not really... you could easily compile a list of passwords and their associated hashes. Once this is done, it's just a simple matter of matching the hashes. -Original Message- Jared Still Sent: Tuesday, December 23, 2003 1:24 PM To: Multiple recipients of list ORACLE-L As long Oracle can manage to keep its modified DES algorithm secret, this should make it somewhat difficult to crack passwords in the manner that can be done with unix passwords. It could still be done, but the time required would make it just too time consuming IMO. Jared On Tue, 2003-12-23 at 09:44, [EMAIL PROTECTED] wrote: When I brought the issue up, I didn't know if one could in fact maliciously use that info. And, as I originally stated, it was something I had not tried. But paranoia (healthy, I think) dictates there's gotta be a way. When one looks at the Unix password world which brought about the necessity for a shadow file, and the evils of the old NIS where ypcat was available, you have to wonder why allowing access to the encrypted passwords for Unix is considered a dumb thing to do, but somehow in Oracle it would be an OK thing to do. I'm inclined to say that Oracle restricted access to the views and underlying tables for reasons more substantial than just to frustrate non-privileged users. And, if I'm not mistaken, the specs on the views are subject to change without notice. I have enough to do without trying to stay on top of every stinkin' view in Oracle in every stinkin' release and how one might use that view in naughty ways. For what it's worth, after haggling and fussing, we were able to compromise on this. We haven't tried to tear each of these apart to see how it might be abused. If any of you have some warnings to provide, please do! -- Must run this as SYS create role DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PROCESS to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESSION to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCH to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCHNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCHHOLDER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LOCK to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$MYSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SYSSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$STATNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ACCESS to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$FILESTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROLLNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROLLSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SGA to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PARAMETER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROWCACHE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LIBRARYCACHE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$INSTANCE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$DISPATCHER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLAREA to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLTEXT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLTEXT_WITH_NEWLINES to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$OPEN_CURSOR to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PQ_SYSSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SGASTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SHARED_SERVER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$DATAFILE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$TABLESPACE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESS_IO to DBARTISAN_USER_ROLE; grant SELECT on SYS.ALL_OBJECTS to DBARTISAN_USER_ROLE; grant SELECT on SYS.DBA_ROLLBACK_SEGS to DBARTISAN_USER_ROLE; grant SELECT on SYS.PRODUCT_COMPONENT_VERSION to DBARTISAN_USER_ROLE; grant SELECT on SYS.DBA_EXTENTS to DBARTISAN_USER_ROLE; grant DBARTISAN_USER_ROLE to USER_WE_DONT_LIKE; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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
RE: seq
because the second insert is returning the result of the first insert. try running each one twice in a row and see what happens. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 23, 2003 3:49 PM To: Multiple recipients of list ORACLE-L Hello list , why does : insert into test values (mysequence.nextval, mysequence.currval ) ; have the same effect as insert into test values (mysequence.currval, mysequence.nextval ) ; where 1. mysequence is create sequence mysequence increment by 1 start with 1 maxvalue 1000 nocycle nocache ; and 2. test is Name Null?Type ID NUMBER(7) ID2NUMBER -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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.net -- Author: Mercadante, Thomas F 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: Risk of knowing password hash value (Was: OEM permissions)
Ah, you're right... the username is taken into account when the password is hashed. Quite a few applications have forced usernames, however, so I'm still a bit uneasy. It's clearly not as bad as I thought, however. -Original Message- Davey, Alan Sent: Tuesday, December 23, 2003 2:59 PM To: Multiple recipients of list ORACLE-L No. Two different users with the same password would have different hash values. So you would have to loop through a dictionary list for each user within your local database. Once you got a match, then you could logon to the target database with that user/password combo. - Alan Davey Senior Analyst/Project Leader Oracle 9i OCA; 3/4 OCP w) 973.267.5990 x458 w) 212.295.3458 -Original Message- Sent: Tuesday, December 23, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Not really... you could easily compile a list of passwords and their associated hashes. Once this is done, it's just a simple matter of matching the hashes. -Original Message- Jared Still Sent: Tuesday, December 23, 2003 1:24 PM To: Multiple recipients of list ORACLE-L As long Oracle can manage to keep its modified DES algorithm secret, this should make it somewhat difficult to crack passwords in the manner that can be done with unix passwords. It could still be done, but the time required would make it just too time consuming IMO. Jared On Tue, 2003-12-23 at 09:44, [EMAIL PROTECTED] wrote: When I brought the issue up, I didn't know if one could in fact maliciously use that info. And, as I originally stated, it was something I had not tried. But paranoia (healthy, I think) dictates there's gotta be a way. When one looks at the Unix password world which brought about the necessity for a shadow file, and the evils of the old NIS where ypcat was available, you have to wonder why allowing access to the encrypted passwords for Unix is considered a dumb thing to do, but somehow in Oracle it would be an OK thing to do. I'm inclined to say that Oracle restricted access to the views and underlying tables for reasons more substantial than just to frustrate non-privileged users. And, if I'm not mistaken, the specs on the views are subject to change without notice. I have enough to do without trying to stay on top of every stinkin' view in Oracle in every stinkin' release and how one might use that view in naughty ways. For what it's worth, after haggling and fussing, we were able to compromise on this. We haven't tried to tear each of these apart to see how it might be abused. If any of you have some warnings to provide, please do! -- Must run this as SYS create role DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PROCESS to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESSION to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCH to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCHNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCHHOLDER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LOCK to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$MYSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SYSSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$STATNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ACCESS to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$FILESTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROLLNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROLLSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SGA to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PARAMETER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROWCACHE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LIBRARYCACHE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$INSTANCE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$DISPATCHER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLAREA to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLTEXT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLTEXT_WITH_NEWLINES to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$OPEN_CURSOR to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PQ_SYSSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SGASTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SHARED_SERVER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$DATAFILE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$TABLESPACE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESS_IO to DBARTISAN_USER_ROLE; grant SELECT on SYS.ALL_OBJECTS to DBARTISAN_USER_ROLE; grant SELECT on SYS.DBA_ROLLBACK_SEGS to DBARTISAN_USER_ROLE; grant SELECT on SYS.PRODUCT_COMPONENT_VERSION to DBARTISAN_USER_ROLE; grant SELECT on SYS.DBA_EXTENTS to DBARTISAN_USER_ROLE; grant DBARTISAN_USER_ROLE to USER_WE_DONT_LIKE; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California--
RE: RE: Hit Ratio
SORRY!! Regrettably e-mail does not provide the required info, namely a picture. The only other Cary I know is female, in every sense of the word. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, December 23, 2003 3:45 PM To: Multiple recipients of list ORACLE-L At 12:04 23-12-03 -0800, you wrote: Jared, I'm going to take some exception to what Cary has said on the subject, but I believe in the end she'll agree with me. She? Cary, you didn't tell us about this surgery ;-) Dick, last time I saw Cary (October) he was very masculin. I bet this hasn't changed during the last two months. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel 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.net -- Author: Goulet, Dick 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: RE: Hit Ratio
immature ...and all this time I thought Cary was a man! :) ...now all the talk of showers and Cary's cleanliness from Mogens makes sense!! ...(I'm 99.99% sure Cary IS a man...at least he looked it at IOUG a few years back) :) /immature merry xmas everyone! -Original Message- Sent: Tuesday, December 23, 2003 2:05 PM To: Multiple recipients of list ORACLE-L Jared, I'm going to take some exception to what Cary has said on the subject, but I believe in the end she'll agree with me. LIO's are inherently cheaper than PIO's simply because you have to complete the LIO before asking for a PIO. And no you can't work with data that has not been allocated a space in the buffer pool, but that does not mean that a high hit ratio is a good thing either. The problem with a high hit ratio consequently high LIO's is that the process is simply looking at the same bits of data over and over again in a senseless waste of CPU. The goal of any SQL tuning should be to get the process to complete in the shortest elapsed time as possible irrespective of the CPU, LIO, or PIO necessary to get the job done. That being the case a SQL statement should, ideally, be written to look at any single piece of data once and only once which would result in a lower LIO's higher PIO's. That being said, it's also the case that the ideal SQL statement has not yet been written, including those I author. But at least I try. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, December 23, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Cary's being diplomatic, as well as engaging in some wishful thinking. :) They appear to be quite prevalent. There are other microcosms of Oracle users that you will find from time to time that realize how Oracle works, and how to go about fixing performance problems. If though you consider the widespread use of texts that propagate ancient tuning advice, you must come to the conclusion that it is still in the mainstream. Jared On Tue, 2003-12-23 at 10:54, Cary Millsap wrote: I hope not, but I think so. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 12:29 PM To: Multiple recipients of list ORACLE-L are there really that many people who use hit ratio? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/12/23 Tue AM 11:49:33 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Hit Ratio Yong, Connor's script is not a joke, it's a proof by counterexample that the advice You SQL is tuned if and only if it has a high hit ratio is rubbish. The buffer cache hit ratio is a tool. Used properly, nobody's objecting. It's proper use? To answer the question, What percentage of LIO calls can be satisfied without an OS read call? The correct point that many on this list make over and over again, is that this is often the wrong question to be asking. (And actually, the conventional BCHR=(L-P)/L formula doesn't answer that question very well anyway; see Steve Adams's site for more detail.) It's not the ratio that needs condemning, it's the advice about how to use the ratio. The ratio just happens to be the emblem on the flag. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Yong Huang Sent: Tuesday, December 23, 2003 9:29 AM To: Multiple recipients of list ORACLE-L Hi, Carel-Jan and Rich, Connor's script to bump up buffer cache hit ratios is meant to be a humor. Only if you carefully comtemplate it will you see that there's no relevance of the fact that you can get any hit ratio to the fact that hit ratios are insufficient in performance tuning. It would be equally easy to write scripts to bump up some wait event times. If you need very long db file reads, create a big table and keep scanning it. If you need long enqueue waits, create a table and insert a row. Create 10 or 100 sessions (depending on your patience) and delete from that table and wait. The fact that you can get arbitary wait times does not reduce the efficacy of wait event interface as a performance tuning tool. Buffer cache or library cache hit ratios are not sufficient, very insufficient used alone, to tune the database. The reason is that they don't contain enough information to tune the system with. This is the only reason we should not solely rely on them; in fact, not
RE: Spam: RE: Risk of knowing password hash value (Was: OEM permi
I wonder if there is a pre-encrypted list available of all Monty Python words, Babylon 5 words, Star Trek words, etc. etc. Actually, I can't think of any better password than LEXADOIG. -Original Message- Not really... you could easily compile a list of passwords and their associated hashes. Once this is done, it's just a simple matter of matching the hashes. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: RE: does CPU usage matter when scaling?
High LIO's require create a lot of latch activity, which can result in high CPU usage if there is latch contention, so reducing LIO's will reduce CPU usage. Jared On Tue, 2003-12-23 at 12:34, [EMAIL PROTECTED] wrote: the cpu right now is high because they are not-using bind variables. Which is being fixed. So I was ignoring CPU usage. Ive read that article. I dont remember any CPU material in there. I just focused on LIOs and didnt realize I needed to monitor CPU. However, high LIOs will lead to high CPU right? So in order to tune, wouldnt I concentrate on lowering LIOs and as a result that will bring down CPUs. I take CPU as a consequence of something, but not as a means to an ends. or am I wrong here? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/12/23 Tue PM 01:59:32 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: does CPU usage matter when scaling? Ryan, Take a look at Why you should focus on LIOs instead of PIOs at www.hotsos.com/e-library. If you have access to the book Optimizing Oracle Performance, check out chapter 9. If an application is written not to crash into some kind of serialization barrier (latch free, enqueue, etc.), then the thing it *should* get stuck spending most of its time doing is consuming CPU service. Applications that consume less CPU service scale better than apps that consume more. I would then say that it's FANTASTIC that your software engineers are looking at CPU consumption as they design, build, and test their code. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 12:39 PM To: Multiple recipients of list ORACLE-L The softwrae engineers are measuring CPU usage. I usually ignore this and dont care about that value. I dont have any docs on it. anyone have any docs on this? or am I wrong? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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.net -- Author: Cary Millsap 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.net -- Author: [EMAIL PROTECTED] 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.net -- Author: Jared Still 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: Risk of knowing password hash value (Was: OEM permissions)
Actually, the concatenated string of userid and password is hashed. So if
that is same, you got yourself the same hashed password.
Consider this:
SQL create user ABC identified by DEF;
User created.
SQL create user ABCD identified by EF;
User created.
SQL select password from dba_users where username in ('ABC','ABCD');
PASSWORD
--
016811C1486D026B
016811C1486D026B
They have the same password hash, even though the password is different.
It's a trick we use in auditing for security holes in the database.
HTH.
Arup
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, December 23, 2003 3:59 PM
No. Two different users with the same password would have different hash
values.
So you would have to loop through a dictionary list for each user within
your local database. Once you got a match, then you could logon to the
target database with that user/password combo.
-
Alan Davey
Senior Analyst/Project Leader
Oracle 9i OCA; 3/4 OCP
w) 973.267.5990 x458
w) 212.295.3458
-Original Message-
Sent: Tuesday, December 23, 2003 3:29 PM
To: Multiple recipients of list ORACLE-L
Not really... you could easily compile a list of passwords and their
associated hashes. Once this is done, it's just a simple matter of
matching
the hashes.
-Original Message-
Jared Still
Sent: Tuesday, December 23, 2003 1:24 PM
To: Multiple recipients of list ORACLE-L
As long Oracle can manage to keep its modified DES algorithm
secret, this should make it somewhat difficult to crack passwords
in the manner that can be done with unix passwords.
It could still be done, but the time required would make
it just too time consuming IMO.
Jared
On Tue, 2003-12-23 at 09:44, [EMAIL PROTECTED] wrote:
When I brought the issue up, I didn't know if one could in fact
maliciously
use that info. And, as I originally stated, it was something I had not
tried. But paranoia (healthy, I think) dictates there's gotta be a way.
When one looks at the Unix password world which brought about the
necessity
for a shadow file, and the evils of the old NIS where ypcat was
available,
you have to wonder why allowing access to the encrypted passwords for
Unix
is considered a dumb thing to do, but somehow in Oracle it would be an
OK
thing to do. I'm inclined to say that Oracle restricted access to the
views
and underlying tables for reasons more substantial than just to
frustrate
non-privileged users. And, if I'm not mistaken, the specs on the views
are
subject to change without notice. I have enough to do without trying
to
stay on top of every stinkin' view in Oracle in every stinkin' release
and
how one might use that view in naughty ways.
For what it's worth, after haggling and fussing, we were able to
compromise
on this. We haven't tried to tear each of these apart to see how it
might
be abused. If any of you have some warnings to provide, please do!
-- Must run this as SYS
create role DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$PROCESS to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SESSION to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LATCH to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LATCHNAME to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LATCHHOLDER to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LOCK to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SESSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$MYSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SYSSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$STATNAME to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$ACCESS to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$FILESTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$ROLLNAME to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$ROLLSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SGA to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$PARAMETER to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$ROWCACHE to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LIBRARYCACHE to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$INSTANCE to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$DISPATCHER to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SQLAREA to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SQLTEXT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SQLTEXT_WITH_NEWLINES to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$OPEN_CURSOR to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$PQ_SYSSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SGASTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SHARED_SERVER to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$DATAFILE to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$TABLESPACE to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SESS_IO to DBARTISAN_USER_ROLE;
grant SELECT on SYS.ALL_OBJECTS to DBARTISAN_USER_ROLE;
grant SELECT on SYS.DBA_ROLLBACK_SEGS to
Re: seq
Try running the 2nd statement first, just after
logon to the database, and see what happens.
Then read the fine manual for sequences. ;)
Jared
On Tue, 2003-12-23 at 12:49, [EMAIL PROTECTED] wrote:
Hello list , why does :
insert into test values (mysequence.nextval, mysequence.currval ) ;
have the same effect as
insert into test values (mysequence.currval, mysequence.nextval ) ;
where
1. mysequence is
create sequence mysequence increment by 1 start with 1 maxvalue 1000
nocycle nocache ;
and
2. test is
Name Null?Type
ID NUMBER(7)
ID2NUMBER
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
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.net
--
Author: Jared Still
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: Risk of knowing password hash value (Was: OEM permissions)
You could conceivably do this, much like lopht or crack. It would take a rather large password database, and a cracker with some intelligence. This is the same reason that unix now uses shadow passwords. Jared On Tue, 2003-12-23 at 12:29, Norris, Gregory T [ITS] wrote: Not really... you could easily compile a list of passwords and their associated hashes. Once this is done, it's just a simple matter of matching the hashes. -Original Message- Jared Still Sent: Tuesday, December 23, 2003 1:24 PM To: Multiple recipients of list ORACLE-L As long Oracle can manage to keep its modified DES algorithm secret, this should make it somewhat difficult to crack passwords in the manner that can be done with unix passwords. It could still be done, but the time required would make it just too time consuming IMO. Jared On Tue, 2003-12-23 at 09:44, [EMAIL PROTECTED] wrote: When I brought the issue up, I didn't know if one could in fact maliciously use that info. And, as I originally stated, it was something I had not tried. But paranoia (healthy, I think) dictates there's gotta be a way. When one looks at the Unix password world which brought about the necessity for a shadow file, and the evils of the old NIS where ypcat was available, you have to wonder why allowing access to the encrypted passwords for Unix is considered a dumb thing to do, but somehow in Oracle it would be an OK thing to do. I'm inclined to say that Oracle restricted access to the views and underlying tables for reasons more substantial than just to frustrate non-privileged users. And, if I'm not mistaken, the specs on the views are subject to change without notice. I have enough to do without trying to stay on top of every stinkin' view in Oracle in every stinkin' release and how one might use that view in naughty ways. For what it's worth, after haggling and fussing, we were able to compromise on this. We haven't tried to tear each of these apart to see how it might be abused. If any of you have some warnings to provide, please do! -- Must run this as SYS create role DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PROCESS to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESSION to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCH to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCHNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LATCHHOLDER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LOCK to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$MYSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SYSSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$STATNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ACCESS to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$FILESTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROLLNAME to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROLLSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SGA to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PARAMETER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$ROWCACHE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$LIBRARYCACHE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$INSTANCE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$DISPATCHER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLAREA to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLTEXT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SQLTEXT_WITH_NEWLINES to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$OPEN_CURSOR to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$PQ_SYSSTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SGASTAT to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SHARED_SERVER to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$DATAFILE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$TABLESPACE to DBARTISAN_USER_ROLE; grant SELECT on SYS.V_$SESS_IO to DBARTISAN_USER_ROLE; grant SELECT on SYS.ALL_OBJECTS to DBARTISAN_USER_ROLE; grant SELECT on SYS.DBA_ROLLBACK_SEGS to DBARTISAN_USER_ROLE; grant SELECT on SYS.PRODUCT_COMPONENT_VERSION to DBARTISAN_USER_ROLE; grant SELECT on SYS.DBA_EXTENTS to DBARTISAN_USER_ROLE; grant DBARTISAN_USER_ROLE to USER_WE_DONT_LIKE; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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.net
RE: Risk of knowing password hash value (Was: OEM permissions)
Very Nice. I didn't know how the 2 values were used within the hashing
algorithm.
I would have thought it was a little more complex.
-
Alan Davey
Senior Analyst/Project Leader
Oracle 9i OCA; 3/4 OCP
w) 973.267.5990 x458
w) 212.295.3458
-Original Message-
Sent: Tuesday, December 23, 2003 4:45 PM
To: Multiple recipients of list ORACLE-L
Actually, the concatenated string of userid and password is hashed. So if
that is same, you got yourself the same hashed password.
Consider this:
SQL create user ABC identified by DEF;
User created.
SQL create user ABCD identified by EF;
User created.
SQL select password from dba_users where username in ('ABC','ABCD');
PASSWORD
--
016811C1486D026B
016811C1486D026B
They have the same password hash, even though the password is different.
It's a trick we use in auditing for security holes in the database.
HTH.
Arup
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, December 23, 2003 3:59 PM
No. Two different users with the same password would have different hash
values.
So you would have to loop through a dictionary list for each user within
your local database. Once you got a match, then you could logon to the
target database with that user/password combo.
-
Alan Davey
Senior Analyst/Project Leader
Oracle 9i OCA; 3/4 OCP
w) 973.267.5990 x458
w) 212.295.3458
-Original Message-
Sent: Tuesday, December 23, 2003 3:29 PM
To: Multiple recipients of list ORACLE-L
Not really... you could easily compile a list of passwords and their
associated hashes. Once this is done, it's just a simple matter of
matching
the hashes.
-Original Message-
Jared Still
Sent: Tuesday, December 23, 2003 1:24 PM
To: Multiple recipients of list ORACLE-L
As long Oracle can manage to keep its modified DES algorithm
secret, this should make it somewhat difficult to crack passwords
in the manner that can be done with unix passwords.
It could still be done, but the time required would make
it just too time consuming IMO.
Jared
On Tue, 2003-12-23 at 09:44, [EMAIL PROTECTED] wrote:
When I brought the issue up, I didn't know if one could in fact
maliciously
use that info. And, as I originally stated, it was something I had not
tried. But paranoia (healthy, I think) dictates there's gotta be a way.
When one looks at the Unix password world which brought about the
necessity
for a shadow file, and the evils of the old NIS where ypcat was
available,
you have to wonder why allowing access to the encrypted passwords for
Unix
is considered a dumb thing to do, but somehow in Oracle it would be an
OK
thing to do. I'm inclined to say that Oracle restricted access to the
views
and underlying tables for reasons more substantial than just to
frustrate
non-privileged users. And, if I'm not mistaken, the specs on the views
are
subject to change without notice. I have enough to do without trying
to
stay on top of every stinkin' view in Oracle in every stinkin' release
and
how one might use that view in naughty ways.
For what it's worth, after haggling and fussing, we were able to
compromise
on this. We haven't tried to tear each of these apart to see how it
might
be abused. If any of you have some warnings to provide, please do!
-- Must run this as SYS
create role DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$PROCESS to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SESSION to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LATCH to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LATCHNAME to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LATCHHOLDER to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LOCK to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SESSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$MYSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SYSSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$STATNAME to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$ACCESS to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$FILESTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$ROLLNAME to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$ROLLSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SGA to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$PARAMETER to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$ROWCACHE to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LIBRARYCACHE to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$INSTANCE to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$DISPATCHER to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SQLAREA to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SQLTEXT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SQLTEXT_WITH_NEWLINES to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$OPEN_CURSOR to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$PQ_SYSSTAT to DBARTISAN_USER_ROLE;
grant SELECT
RE: ora-4030 pga memory allocation running wild
Hi, I'm using oracle 9.2.0.4. I put it off tonight with the statement You mentioned and unfortunately no success. Maybe interesting to know that I started without the event 4030 set And I get the following ora-600 in my alert file: Tue Dec 23 16:46:42 2003 Errors in file /var/opt/oracle/product/admin/VU_2/udump/vu_2_ora_15251.trc: ORA-00600: internal error code, arguments: [17271], [instantiation space leak], [], [], [], [], [], This one is reproducible without the event set and a pga_aggregate_target set either 250Mb or 160Mb With the event set I got the following error Errors in file /var/opt/oracle/product/admin/VU_2/udump/vu_2_ora_10264.trc: ORA-04030: out of process memory when trying to allocate 2464 bytes (cursor work he,rworalo : rwordops) Tue Dec 23 14:24:40 2003 Errors in file /var/opt/oracle/product/admin/VU_2/udump/vu_2_ora_10249.trc: ORA-00600: internal error code, arguments: [17271], [instantiation space leak], [], [], [], [], [], [] ORA-04030: out of process memory when trying to allocate 32 bytes (callheap,allocator state) This second tracefile lead me to the sql-statement which explained with a very nice result When I issued the statement to set off auto handling I did not get any such error in my alert file but my batch returned again after an hour With ERROR at line 1: ORA-04030: out of process memory when trying to allocate 56 bytes (callheap,PESBLT space) Could a UX kernel parameter be of any influence here, like max data segment? Could it help to increase this to say 3Gb. Note that we have 4Gb physical memory and 4Gb swap configured. I used to run this in an oracle 7 enviroment on hpux 10.20 and now we moved To 64bit hpux11.11. I can imagine oracle is using more memory here than compared to oracle 7 with the same program such that in the old environment we might stayed below 2Gb and now we are exceeding this. For what it might be worth, this batch is quite big. Sofar this seems to be the only program having memory problems. I have put back workare_size_policy=auto back to be on the safe default side. I hope you can give some more leads because this is quite confusing And causing me headaches because it is causing troubles in my production environment. By the way we tested the migration ofcourse but this batch was not included in the test. Regards, Jeroen -Oorspronkelijk bericht- Van: Jared Still [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 23 december 2003 18:34 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: ora-4030 pga memory allocation running wild I'm using auto pga allocation on 9.2.0.3 without any problem. You don't mention which version. You can turn it off with 'alter system set workarea_size_policy=manual; Jared On Tue, 2003-12-23 at 07:24, Jeroen van Sluisdam wrote: Hi, I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has 4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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
Re: STATSPACK interpretation
Notes in-line. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 8:44 PM Jonathan, Wouldn't bind variable issue that prevents cursor from sharing be visible in bind_mismatch? I would certainly hope so - but I remember playing around with v$sql_shared_cursor when it first came out and find cases where un-shared cursors came up with a full set of N's in the view. How can one simulate this? var v varchar2(1) begin select count(5) into :v from dual; end; / select address, sql_text from v$sql where sql_text like '%count(5)%'; ADDRESS SQL_TEXT -- - 6DE92A74 SELECT count(5) from dual 6DE960D0 begin select count(5) into :v from dual; end; -- Change a bind variable size: var v varchar2(30) begin select count(5) into :v from dual; end; / -- same output, no change, both sql and pl/sql wrapper cursors are still shared Nicely done. I think I'd run event 10046 at level 4 as well to get the bind variable dumps and check if the the SQL (or pl/sql) environment was ignoring the MAXLEN value for your variables. There are a few places where 'special optimisations' exist in Oracle's internal coding. You might also try it with the most extreme case - it may be (for example) that Oracle rounds up varchar2() variables to 32 bytes - I'd go for 1 and 4000 - just in case. -- Change a bind variable type: var v number begin select count(5) into :v from dual; end; / ADDRESS SQL_TEXT -- -- 6DE92A74 SELECT count(5) from dual 6DE960D0 begin select count(5) into :v from dual; end; 6DE960D0 begin select count(5) into :v from dual; end; -- ok, here pl/sql parent (dep=0) cursor is no longer shared [EMAIL PROTECTED] select * from v$sql_shared_cursor where kglhdpar = '6DE960D0'; ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 6DE95B54 6DE960D0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 6DE86F94 6DE960D0 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N 2 rows selected. -- yep, bind variables mismatch Thanks, Boris Dali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: RE: Hit Ratio
Why do people still talk about THE buffer cache hit ratio ? There are lots of them. The one you can get from v$sysstat, the ones you can get from v$buffer_pool_statistics, and the ones you can get from v$segstat. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 6:29 PM are there really that many people who use hit ratio? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Risk Of Knowing Password Hash Value
Take a look at Metalink note 227010.1. It provides a script with the hash values for the default passwords. It's good for checking database security. Dennis -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dennis Heisler 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: Risk of knowing password hash value (Was: OEM permissions)
Looks like they're using VMS's algorithm. *That's* a shocker!
-Original Message-
Sent: Tuesday, December 23, 2003 3:45 PM
To: Multiple recipients of list ORACLE-L
Actually, the concatenated string of userid and password is hashed. So if
that is same, you got yourself the same hashed password.
Consider this:
SQL create user ABC identified by DEF;
User created.
SQL create user ABCD identified by EF;
User created.
SQL select password from dba_users where username in ('ABC','ABCD');
PASSWORD
--
016811C1486D026B
016811C1486D026B
They have the same password hash, even though the password is different.
It's a trick we use in auditing for security holes in the database.
HTH.
Arup
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, December 23, 2003 3:59 PM
No. Two different users with the same password would have different hash
values.
So you would have to loop through a dictionary list for each user within
your local database. Once you got a match, then you could logon to the
target database with that user/password combo.
-
Alan Davey
Senior Analyst/Project Leader
Oracle 9i OCA; 3/4 OCP
w) 973.267.5990 x458
w) 212.295.3458
-Original Message-
Sent: Tuesday, December 23, 2003 3:29 PM
To: Multiple recipients of list ORACLE-L
Not really... you could easily compile a list of passwords and their
associated hashes. Once this is done, it's just a simple matter of
matching
the hashes.
-Original Message-
Jared Still
Sent: Tuesday, December 23, 2003 1:24 PM
To: Multiple recipients of list ORACLE-L
As long Oracle can manage to keep its modified DES algorithm
secret, this should make it somewhat difficult to crack passwords
in the manner that can be done with unix passwords.
It could still be done, but the time required would make
it just too time consuming IMO.
Jared
On Tue, 2003-12-23 at 09:44, [EMAIL PROTECTED] wrote:
When I brought the issue up, I didn't know if one could in fact
maliciously
use that info. And, as I originally stated, it was something I had not
tried. But paranoia (healthy, I think) dictates there's gotta be a way.
When one looks at the Unix password world which brought about the
necessity
for a shadow file, and the evils of the old NIS where ypcat was
available,
you have to wonder why allowing access to the encrypted passwords for
Unix
is considered a dumb thing to do, but somehow in Oracle it would be an
OK
thing to do. I'm inclined to say that Oracle restricted access to the
views
and underlying tables for reasons more substantial than just to
frustrate
non-privileged users. And, if I'm not mistaken, the specs on the views
are
subject to change without notice. I have enough to do without trying
to
stay on top of every stinkin' view in Oracle in every stinkin' release
and
how one might use that view in naughty ways.
For what it's worth, after haggling and fussing, we were able to
compromise
on this. We haven't tried to tear each of these apart to see how it
might
be abused. If any of you have some warnings to provide, please do!
-- Must run this as SYS
create role DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$PROCESS to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SESSION to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LATCH to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LATCHNAME to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LATCHHOLDER to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LOCK to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SESSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$MYSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SYSSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$STATNAME to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$ACCESS to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$FILESTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$ROLLNAME to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$ROLLSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SGA to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$PARAMETER to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$ROWCACHE to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$LIBRARYCACHE to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$INSTANCE to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$DISPATCHER to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SQLAREA to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SQLTEXT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SQLTEXT_WITH_NEWLINES to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$OPEN_CURSOR to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$PQ_SYSSTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SGASTAT to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$SHARED_SERVER to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$DATAFILE to DBARTISAN_USER_ROLE;
grant SELECT on SYS.V_$TABLESPACE to
RE: STATSPACK interpretation
FWIW. The database crashed again.I managed to get in a 10466: BEGIN GENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ; END; call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2737 1213.001184.60 0 0 0 0 Execute 2737 28.57 28.08 0 0 0 2737 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 5474 1241.571212.68 0 0 0 2737 Looks like maybe that it's an anonymous PL/SQL call just as Jonathan described? Thanks for the replies. -Original Message- Jared Still Sent: Tuesday, December 23, 2003 2:34 PM To: Multiple recipients of list ORACLE-L Thanks Jonathan. I was wondering about those memory columns possibly acting in that manner. I've never used them to track memory though, and wasn't sure how they might act. Google and MetaLink didn't turn up anything too useful in the regard. Now that you mention it, I recall reading recently somewhere that different size bind variables could cause multiple versions. Probably James, but I can't recall where I saw it. Google, Metalinkm mailing lists and a plethora of books and white papers have made it quite difficult, at least for me, to always remember the source of tidbits such as this. Back to the memory, I was somehow able to cause 2 version of the same SQL, as mentioned earlier, but the reason for it did not appear in v$sql_shared_cursor. Jared On Tue, 2003-12-23 at 10:54, Jonathan Lewis wrote: I recall James Morle saying something about code not being sharable if the declared sizes of the bind variables don't match. If Informatica is using a 3GL to call anonymous pl/sql blocks with different bind variables every time, perhaps it is causing a bind variable mismatch. As for the 400MB - I've often noticed oddities where a new entry is created, but carries forward a report of the memory requirements of earlier variants, so if you have 10 cursors, they don't report 10 units of memory, but 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 units. It is possible that you are seeing some effect like this. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 5:09 PM Thomas, The version count is the number of child cursors present in the cache for this SQL. The cursor is not being shared for some reason with 456 versions. The 400m of memory seems a bit excessive. There is a script at Jonathan's site with some info about v$sqlarea and a script you can run that looks at the current memory requirements for a SQL statement. http://www.jlcomp.demon.co.uk/sqlarea.html Does the output match what you see in statspack? Also, the number of executions is much lower than the version count, which is rather odd. There's a bug in early 9i versions that would cause this, but was supposed to be corrected by 9.2.0.2. In experimenting with this, I managed to get 4 different sessions to create 2 versions of a cursor. I'm not sure why as it was pl/sql and variables were used for the calling parameters. A 'select * from v$sql_shared_cursor' did not reveal any reason for it. After bouncing the database and trying this again, I couldn't duplicate it. Maybe a couple of things to pursue here, but perhaps not an abundance of help. :( Jared On Tue, 2003-12-23 at 04:44, Thomas Jeff wrote: Jared, Digging into it more, I found out that it's called from an Informatica client. Apparently, the gist of the client-side algorithim is as follows: For each row in (some view) Call generate_product_keys MERGE (upsert) into product table end loop -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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
Re: STATSPACK interpretation
Thanks, Jonathan.
Of course you are right :-)
Playing with this a little longer, I can get up to 4
versions (child_number from 0 to 3) of the same pl/sql
cursor by changing bind variable sizes. It ceases
being sharable when bv size changes from 32 to 33,
from 128 to 129 and from 2000 to 2001:
1-32
33-128
129-2000
2001-4000
I wonder if this behavior can be changed by some init
settings?
Thanks,
Boris Dali.
--- Jonathan Lewis [EMAIL PROTECTED]
wrote:
Notes in-line.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick
Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- Original Message -
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Tuesday, December 23, 2003 8:44 PM
Jonathan,
Wouldn't bind variable issue that prevents cursor
from
sharing be visible in bind_mismatch?
I would certainly hope so - but I remember playing
around with v$sql_shared_cursor when it first came
out and find cases where un-shared cursors came up
with a full set of N's in the view.
How can one simulate this?
var v varchar2(1)
begin select count(5) into :v from dual; end;
/
select address, sql_text from v$sql where sql_text
like '%count(5)%';
ADDRESS SQL_TEXT
--
-
6DE92A74 SELECT count(5) from dual
6DE960D0 begin select count(5) into :v from dual;
end;
-- Change a bind variable size:
var v varchar2(30)
begin select count(5) into :v from dual; end;
/
-- same output, no change, both sql and pl/sql
wrapper
cursors are still shared
Nicely done. I think I'd run event 10046
at level 4 as well to get the bind variable dumps
and check if the the SQL (or pl/sql) environment
was ignoring the MAXLEN value for your
variables. There are a few places where 'special
optimisations' exist in Oracle's internal coding.
You might also try it with the most extreme
case - it may be (for example) that Oracle
rounds up varchar2() variables to 32 bytes -
I'd go for 1 and 4000 - just in case.
-- Change a bind variable type:
var v number
begin select count(5) into :v from dual; end;
/
ADDRESS SQL_TEXT
--
--
6DE92A74 SELECT count(5) from dual
6DE960D0 begin select count(5) into :v from dual;
end;
6DE960D0 begin select count(5) into :v from dual;
end;
-- ok, here pl/sql parent (dep=0) cursor is no
longer
shared
[EMAIL PROTECTED] select * from v$sql_shared_cursor where
kglhdpar = '6DE960D0';
ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D
L T
R I I R L I O S M U T N F
- - - - - - - - - - - - - - - -
- -
- - - - - - - - - - - - -
6DE95B54 6DE960D0 N N N N N N N N N N N N N N N N
N N
N N N N N N N N N N N N N
6DE86F94 6DE960D0 N N N N N N N N N N N N N N Y N
N N
N N N N N N N N N N N N N
2 rows selected.
-- yep, bind variables mismatch
Thanks,
Boris Dali.
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Jonathan Lewis
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).
__
Post your free ad now! http://personals.yahoo.ca
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Boris Dali
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: RE: Hit Ratio
Exactly. select * from v$statname where name like '%gets%' is simple evidence of that. Cheers Connor --- Jonathan Lewis [EMAIL PROTECTED] wrote: Why do people still talk about THE buffer cache hit ratio ? There are lots of them. The one you can get from v$sysstat, the ones you can get from v$buffer_pool_statistics, and the ones you can get from v$segstat. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 6:29 PM are there really that many people who use hit ratio? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
Database Instance
Dear : All First and foremost , if i ask stupid question pls forgive me coz i new to oralce. I have a question here. I have a server, and this server contains 5 oracle database. Each database contains 5 web application that are running on live ( production server ). As i understand abt oracle , every database that i created will have 1 instance. So since this server hold 5 database then i will have 5 instances running. Lately, my manager want me to remove all the databases and remain a single instance. I was wondering if i move everything into single database then if one of the application fail due to oracle error , then all other four application will fail also rite ? Each of our web application needs to have 2 schema and both schema have to be transparent to each other. While other application schema will be invisible to each other. Since i have 5 web app then i will need 10 schema.One major problem is all the 10 schema will contain same table name. It will be a mess putting so much app in a single db . Pls correct me if i am wrong and do let me know what are the pro and cons or maybe you can educate me with some of the best practice to setup a proper production server environment. Thank You Regards, Jkean __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kean Jacinta 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: Database Instance
Think of Object Oriented, then 1 db can be an Object, better security, easy to upgrade or downgrade, easy to change any setting as you like. Think of memory, overhead, administration, 1 instance will be better (I think, specially backup and recovery) Think of performance, you can tune each db individually and contention will quite easy to maintain. Think of risk and potential application bug, why your management want to overhaul the system? About the schema that transparent or invisible, you can search Oracle Doc (SQL Plus) with key word GRANT or REVOKE (If you decide to merge your databases) Merry X'mas Sinardy -Original Message- Sent: 24 December 2003 09:49 To: Multiple recipients of list ORACLE-L Dear : All First and foremost , if i ask stupid question pls forgive me coz i new to oralce. I have a question here. I have a server, and this server contains 5 oracle database. Each database contains 5 web application that are running on live ( production server ). As i understand abt oracle , every database that i created will have 1 instance. So since this server hold 5 database then i will have 5 instances running. Lately, my manager want me to remove all the databases and remain a single instance. I was wondering if i move everything into single database then if one of the application fail due to oracle error , then all other four application will fail also rite ? Each of our web application needs to have 2 schema and both schema have to be transparent to each other. While other application schema will be invisible to each other. Since i have 5 web app then i will need 10 schema.One major problem is all the 10 schema will contain same table name. It will be a mess putting so much app in a single db . Pls correct me if i am wrong and do let me know what are the pro and cons or maybe you can educate me with some of the best practice to setup a proper production server environment. Thank You Regards, Jkean __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kean Jacinta 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.net -- Author: Sinardy Xing 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).
