Re: low buffer hit ratio
Gurelei wrote: Hi. I found a query with a buffer hit ratio about 60%: SELECT b.Name, a.Racf, c.Manager, fileds from STATS table FROM BT.Stats a, BT.Employees b, BT.Employees c WHERE a.Stats_Date Between '13-Nov-02' and '13-Nov-02' and a.Manager = c.Manager and c.racf = 'RLEWI01' and b.Racf = a.Racf and ( Product='RTI' OR Product = 'RIM' ... or Product = 'WEB') Group by b.Name, a.Racf, c.Manager Order by b.Name Here is the plan under oracle 7.3.3 rbo: 1.0 SELECT STATEMENT (, , ) 2.1 SORT GROUP BY (, , ) 3.1 NESTED LOOPS (, , ) 4.1 NESTED LOOPS (, , ) 5.1 TABLE ACCESS BY ROWID EMPLOYEES (, , ) 6.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , ) 5.2 TABLE ACCESS BY ROWID STATS (, , ) 6.1 INDEX RANGE SCAN STATS_FK2 (, , ) 4.2 TABLE ACCESS BY ROWID EMPLOYEES (, , ) 5.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , ) The largest table is STATS - 1.6 mil rows. STATS_FK2 is an index on MANAGER field. We have only 115 different managers so the index is not very selective. The stats_date field is more selective. Does it make sence for me to force Oracle to use that index in order to reduce the number of disk reads? thanks Gene Gene, First reducing the number of disk reads doesn't necessarily means that it will run faster. Except in the case of fast index scans, index blocks are usually read one-at-a-time, while in a table scan you will read batches of several blocks. If you have for instance a large number of db_file_sequential_reads, then you may find that your query will perform better with a lesser index usage. It's then a matter of knowing where the data you want is. If it happens to be physically clustered, fine (I am using 'clustered' in the general sense here, not referring to Oracle clusters); if it scattered all over the place your query is likely to be painful to run ... Not knowing your data it's difficult to be specific but here are some general guide-lines: - How many rows does your query return ? If it's a huge number I would feel more comfortable with table scans than index accesses in the plan. - You seem to feed three things into your query, racf (any relationship to the IBM product?), product (your query doesn't say from which table it comes) and the date (BTW implicit conversions like here always make me feel nervous). Which one is the most selective ? Try to have your query start with the corresponding table (I am a big fan of /*+ ORDERED */). If you have paid for the partition option,this is something to consider too for your STATS table - a good way to cluster data. A brute scan of the suitable partition is usually extremely efficient. - If you have to join tables with not-so-significant criteria, USE_HASH deserves consideration. Try different things. -- HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Naming Conventions....
Shibu MB wrote: Hi all... What are the naming conventions u guys follow when designing a database ???.Can anybody send me a general document on this. I am trying to make the attribute name unique in my database but i dunno what naming convention i have to follow for this .. I have tried all kinda combinations but some howthe names are not coming unique and because of this the developers may get confused know ... so please tell me what naming convention are followed by u DBA s Thanks Shibu Shibu, There are probably as many naming conventions as DBAs ... What matters is consistency. You may find the following URL useful : http://www.oriole.com/frameindexST.html -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: CONSISTANT GETS
It's the count of a certain type of fetch operations of blocks from the database buffer cache. See Why you should focus on LIOs instead of PIOs at www.hotsos.com/catalog for details. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- Alavi Sent: Thursday, November 14, 2002 2:10 PM To: Multiple recipients of list ORACLE-L Sorry for asking such a obvious question, but CONSISTANT GETS means calling rows from Database Thanks, -Original Message- Sent: Thursday, November 14, 2002 10:35 AM To: Multiple recipients of list ORACLE-L Hamid, I'm sorry: Unless your SQL returns fewer than about 800,000 rows to the calling application (or an aggregation of 800,000 rows), then the statement we have done all the necessary tuning on all the SQL queries is not yet true. If your SQL does actually return about 800,000 rows, then it is time to begin thinking about the mismatch between business processing requirements and the logical structure of your data. The answer to your problem is not in your instance parameters. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- Alavi Sent: Thursday, November 14, 2002 11:50 AM To: Multiple recipients of list ORACLE-L Dear List, I am monitoring a database, I findout there is a transaction which runing a long time and others are waiting for this transaction, this transaction have 8,000,000 consistant gets with only 1 Physical I/O. My question is, what I have to do except the SQL tuning to make this transaction faster, we have done all the necessary tuning on all the SQL query's. Here is a copy of ora.ini: Oracle 8.1.7.4 on sun solaris 2.8 background_dump_dest = /oracle/admin/cmstst/bdump compatible = 8.1.7.4 control_files = /cmsdb/cmstst/control02.ctl control_files = /oralogs1/cmstst/control03.ctl control_files = /oracle/oradata/cmstst/control01.ctl core_dump_dest = /oracle/admin/cmstst/cdump db_block_buffers = 1??? this need to increase? db_block_lru_latches = 4 db_block_size = 8192 db_file_multiblock_read_count = 16 db_name = cmstst hash_area_size = 2048000??? need tuning ??? instance_name = cmstst java_pool_size = 20971520 large_pool_size = 614400 log_archive_dest_1 = location=/archlogs/cmstst log_archive_format = arch%s.arc log_archive_start = TRUE log_buffer = 262144 ?? this log buffer is enough?? log_checkpoint_interval = 1 ?? log_checkpoint_timeout = 1800 max_enabled_roles = 30 open_cursors = 300 optimizer_index_caching = 90 optimizer_index_cost_adj = 35 os_authent_prefix = processes = 100 remote_login_passwordfile = EXCLUSIVE session_cached_cursors = 100 shared_pool_size = 134217728 sort_area_retained_size = 262144 sort_area_size = 262144 timed_statistics = TRUE I realy appreciate your help and assistant. I am getting confused, just want to know changing any of these parameter help the performance to reduce the number of CONSISTANT GETS or NOT??? Thanks in advance. Hamid Alavi Office 818 737-0526 Cell818 416-5095 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
How to set client's characterset on session level?
Hello guys, I need to use two (or more) different client applications from the same client workstation. Applications use different charactersets WE8PC858 and WE8ISO8859P1. That's a sort of having different NLS_LANG for each session. Maybe I can create a specific connect description in listener? Or I can put something in AFTER LOGIN trigger? Or put some parameters when establishing connection? I can change NLS_TERRITORY but not characterset. I am a little bit confused because cannot resolve this rather simple (I thought) case. Any advice? TIA, Alex -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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).
Import from FoxPro to Oracle
Title: Import from FoxPro to Oracle Hi ALL, Can WE IMPORT DATA from DBF files of Fox Pro into Oracle 8.1.7. If yes how. TIA Hussain
Re: Import from FoxPro to Oracle
Hussain Ahmed Qadri wrote:
Hi ALL,
Can WE IMPORT DATA from DBF files of Fox Pro into Oracle 8.1.7.
If yes how.
TIA
Hussain
I have done something of the kind in the past by writing a program
taking a .dbf file on its standard input and writing a control file
including the data to its standard output, which was then awfully easy
to load. I no longer have the program but it took about half a day to
write, including the search on the internet to find out what the
structure of a .dbf file is, because I had no idea about it. The only
tricky thing I remember is that I wrote it to run on both Solaris and
Tru64 and in one case (Solaris, I think) some bytes had to be swapped
('man swab').
--
HTH,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
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: CONSISTANT GETS
Cary, I read your wonderful article. What is the exact difference between CR and CU, blocks fetched in Consistent and Current mode? Regards Naveen -Original Message- Sent: Friday, November 15, 2002 2:54 PM To: Multiple recipients of list ORACLE-L It's the count of a certain type of fetch operations of blocks from the database buffer cache. See Why you should focus on LIOs instead of PIOs at www.hotsos.com/catalog for details. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- Alavi Sent: Thursday, November 14, 2002 2:10 PM To: Multiple recipients of list ORACLE-L Sorry for asking such a obvious question, but CONSISTANT GETS means calling rows from Database Thanks, -Original Message- Sent: Thursday, November 14, 2002 10:35 AM To: Multiple recipients of list ORACLE-L Hamid, I'm sorry: Unless your SQL returns fewer than about 800,000 rows to the calling application (or an aggregation of 800,000 rows), then the statement we have done all the necessary tuning on all the SQL queries is not yet true. If your SQL does actually return about 800,000 rows, then it is time to begin thinking about the mismatch between business processing requirements and the logical structure of your data. The answer to your problem is not in your instance parameters. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- Alavi Sent: Thursday, November 14, 2002 11:50 AM To: Multiple recipients of list ORACLE-L Dear List, I am monitoring a database, I findout there is a transaction which runing a long time and others are waiting for this transaction, this transaction have 8,000,000 consistant gets with only 1 Physical I/O. My question is, what I have to do except the SQL tuning to make this transaction faster, we have done all the necessary tuning on all the SQL query's. Here is a copy of ora.ini: Oracle 8.1.7.4 on sun solaris 2.8 background_dump_dest = /oracle/admin/cmstst/bdump compatible = 8.1.7.4 control_files = /cmsdb/cmstst/control02.ctl control_files = /oralogs1/cmstst/control03.ctl control_files = /oracle/oradata/cmstst/control01.ctl core_dump_dest = /oracle/admin/cmstst/cdump db_block_buffers = 1??? this need to increase? db_block_lru_latches = 4 db_block_size = 8192 db_file_multiblock_read_count = 16 db_name = cmstst hash_area_size = 2048000??? need tuning ??? instance_name = cmstst java_pool_size = 20971520 large_pool_size = 614400 log_archive_dest_1 = location=/archlogs/cmstst log_archive_format = arch%s.arc log_archive_start = TRUE log_buffer = 262144 ?? this log buffer is enough?? log_checkpoint_interval = 1 ?? log_checkpoint_timeout = 1800 max_enabled_roles = 30 open_cursors = 300 optimizer_index_caching = 90 optimizer_index_cost_adj = 35 os_authent_prefix = processes = 100 remote_login_passwordfile = EXCLUSIVE session_cached_cursors = 100 shared_pool_size = 134217728 sort_area_retained_size = 262144 sort_area_size = 262144 timed_statistics = TRUE I realy appreciate your help and assistant. I am getting confused, just want to know changing any of these parameter help the performance to reduce the number of CONSISTANT GETS or NOT??? Thanks in advance. Hamid Alavi Office 818 737-0526 Cell818 416-5095 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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
Can OCI TAF and Tuxedo XA be used together in RAC enviroment?
Hi, list friends: I wonder if we are using XA to connect to rac database, can i still use oci to write it? I want to try Transparent failover with rac database so that when schedule down, i need not bring the whole site down, just reboot one db server and some middleware, so that my site can be up really 7*24. Is there someone using tuxedo xa and oci to connect ops/rac database? Please share your opinions:) Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net www.cnoug.org(Chinese Oracle User Group) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: chao_ping 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: CONSISTANT GETS
Title: RE: RE: CONSISTANT GETS Funny ... that Cary mentioned it Some developers here think that by setting some magic instance parameters we can make all RBO tuned code run well under CBO ... (I just bought a 6 pack of Mylanta yesterday ...) Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! Reply Separator Author: Cary Millsap [EMAIL PROTECTED] Date: 11/14/2002 10:34 AM Hamid, I'm sorry: Unless your SQL returns fewer than about 800,000 rows to the calling application (or an aggregation of 800,000 rows), then the statement we have done all the necessary tuning on all the SQL queries is not yet true. If your SQL does actually return about 800,000 rows, then it is time to begin thinking about the mismatch between business processing requirements and the logical structure of your data. The answer to your problem is not in your instance parameters. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: How to set client's characterset on session level?
Alexandre Gorbatchev, I think you can manually trigger alter session set nls_language ... via some button in your application, or via logon trigger(if 8i+). Or run os command export NLS_LANG= nls_lang1 etc and run your app:), export NLS_LANG=nls_lang2 and run your second app, this should do the trick. Good luck. Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net www.cnoug.org(Chinese Oracle User Group) === 2002-11-15 02:58:00 ,you wrote£º=== Hello guys, I need to use two (or more) different client applications from the same client workstation. Applications use different charactersets WE8PC858 and WE8ISO8859P1. That's a sort of having different NLS_LANG for each session. Maybe I can create a specific connect description in listener? Or I can put something in AFTER LOGIN trigger? Or put some parameters when establishing connection? I can change NLS_TERRITORY but not characterset. I am a little bit confused because cannot resolve this rather simple (I thought) case. Any advice? TIA, Alex -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: chao_ping 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).
surprising result:8CPU Sun 3500 VS 4CPU Dell 6650
Hi, dba friends: The following is what i get this afternoon and want to share my test result with your friends, and hope to get your opinion about this result. I am so surprised with my test result of Dell 6650 system vs Sun 3500.We are running RAC 9.2 on Dell 6650 dual node, and i want to apply patch to rac and converted application from rac to another database server(HA standby machine for another Sun 4500, idle in most time). The application is CPU intensive which capture snapshot from central database server and provide catalog service to web and middileware. The sun server is Sun 3500 with 8*400MCPU and 8G memory,and DELL RAC system with 4*1.4G Xeon MP 256K Cache and 4G memory.I wanted to move the application from RAC to sun(running oracle 8172),I stopped one rac node and converted the connection to sun 3500. Before i go to the next step of moving connection on the second node, i was suprised to find that the sun Server's load is 7(uptime result)!While the load on the remaining RAC node is 1! and finally i was unable to move the load on the other rac node to sun, for sun is already overloaded. I ran the application on 3500 for 4 hours. The average load on sun is 5-7, while on the Dell node, it is 1-1.5. Sar result shows that sun has average of 30% CPU idle,while Dell has 70% CPU idle. I should say that the pressure on the two db server is the same, the middleware and alton(hardware) ensure that the pressure on both server is the same. So, what is the advantage of Sun? Redhat Advanced server and 920 is also so much stable, and Sun T3 disk array is also of poor performance. CPU poor, disk array not that good, why sun? Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net www.cnoug.org(Chinese Oracle User Group) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: chao_ping 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: Import from FoxPro to Oracle
Title: RE: Import from FoxPro to Oracle
Well at least this means that it can be done. But aren't there any tools like Oracle Migration Work bench or the Oracle Migration Assistant for Access . The odbc of WIn2000 doesn't support .dbf files, it goes to .DBC for visual foxpro. So is there any possibility of simplifying the import procedure?
-Original Message-
From: Stephane Faroult [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 15, 2002 5:33 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Import from FoxPro to Oracle
Hussain Ahmed Qadri wrote:
Hi ALL,
Can WE IMPORT DATA from DBF files of Fox Pro into Oracle 8.1.7.
If yes how.
TIA
Hussain
I have done something of the kind in the past by writing a program
taking a .dbf file on its standard input and writing a control file
including the data to its standard output, which was then awfully easy
to load. I no longer have the program but it took about half a day to
write, including the search on the internet to find out what the
structure of a .dbf file is, because I had no idea about it. The only
tricky thing I remember is that I wrote it to run on both Solaris and
Tru64 and in one case (Solaris, I think) some bytes had to be swapped
('man swab').
--
HTH,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
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: RE: dumping microsoft desktop?
OH, talk about cruel and unusual punishment!! For the fish that is. Dick Goulet Reply Separator Author: Gogala; Mladen [EMAIL PROTECTED] Date: 11/14/2002 1:38 PM Well, once upon a time there was an event called Boston Tea Party which dealt with too expensive product of low quality delivered by a monopoly. I wonder whether we can expect Seattle Windows Party? Would that be too cruel to the fish in Seattle harbor? -Original Message- From: [EMAIL PROTECTED] [mailto:dgoulet;vicr.com] Sent: Thursday, November 14, 2002 3:44 PM To: Multiple recipients of list ORACLE-L Subject: Re:RE: dumping microsoft desktop? David, Just like beauty, winning or loosing in a lawsuit is in the eye of the beholder. Actually in MicroSlop's case it was the justice department that bailed and more than likely King George who sat on the judge. You got to love those political action committees and their BIG donors!! In politics money talks louder than anything else. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 11/14/2002 12:15 PM Both answers a are expected to be No. The lawsuit is expected to be dropped. But who knows. They won antitrust case after all. Nick I know it *seems* like they won, but Microsoft actually lost the antitrust case. :-( Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
FW: kproc processes owned by Oracle on AIX
All, I have shutdown my database(but not the listener). I notice that I have a number of kproc processes owned by oracle. Why? John Dunn Sefas Innovation Ltd 0117 9154267 www.sefas.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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).
analysis with capacity planner
Hi, Anybody know how to combine multiple databases metrics into one analysis chart? Thanks a lot. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Re: low buffer hit ratio
--- Stephane Faroult [EMAIL PROTECTED] wrote: - How many rows does your query return ? If it's a huge number I would feel more comfortable with table scans than index accesses in the plan. It returns 8 rows in about 2-2.5 seconds. - You seem to feed three things into your query, racf (any relationship to the IBM product?), product (your query doesn't say from which table it comes) and the date (BTW implicit conversions like here always make me feel nervous). Which one is the most selective ? Try to have your query start with the corresponding table (I am a big fan of /*+ ORDERED */). I did something like that. I have prevented Oracle from using the index STATS_FK2 (on a manager field) by comcatinating ||'' to the manager field name. It forced Oracle to use the index on the date field (more selective) and reduced the time to 0.5 sec and I think increased the bhr to 95%. Thanks for your help Gene __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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: dumping microsoft desktop?
Title: RE: RE: dumping microsoft desktop? Mladen, Actually the Boston Tea Party happened for the opposite reason. The tea on the ships was of LOWER price because it was not taxed. The 'patriots' owned tons of tea stored in warehouses that they could only sell at a profit with higher prices than what was on the ship. So they destroyed the competition's tea. As Samuel Johnson said Patriotism is the last refuge of the scoundrel. They wrapped patriotism around their actions and history bought off on it. http://odur.let.rug.nl/~usa/E/teaparty/bostonxx.htm Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Gogala, Mladen [SMTP:[EMAIL PROTECTED]] Well, once upon a time there was an event called Boston Tea Party which dealt with too expensive product of low quality delivered by a monopoly. I wonder whether we can expect Seattle Windows Party? Would that be too cruel to the fish in Seattle harbor?
HTTP Server not starting/... so is internet directory server ..ldap running on 389
Hi, I installed oracle infrastructure 9iAS 9.0.2 on a system (Redhat Advanced Server 2.1) where I am already using a Oracle9i DB. It installs successfully other than during database creation instance and shows an error saying Database Name not proper ORA-02084 But the database instance is created as iasdb and works properly. But when I go to the enterprise manager website and try to start HTTP server there it says Error oracle.sysman.emSDK.util.jdk.EMException What could be the reason? Please help me in this regards -- Tusar Missed your favourite TV serial last night? Try the new, Yahoo! TV. visit http://in.tv.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Tusar=20K.=20Nayak?= 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: Import from FoxPro to Oracle
Foxpro has a upsizewizard to move data into an oracle database. [EMAIL PROTECTED] 11/15/02 06:03AM Hi ALL, Can WE IMPORT DATA from DBF files of Fox Pro into Oracle 8.1.7. If yes how. TIA Hussain
[Q] MS ODBC for ORACLE driver connection problem!!
We are testing the ORACLE 9iR2 client server connection. On PC side, we installed ORACLE ODBC driver 9.2.0.2 and upgrade MS ODBC for ORACLE to 2.573.9001.000. After configuration, ORACLE ODBC driver work fine, but MS ODBC for ORACLE have ORA-12154 (TNS name can NOT resolve) error. I trouble shooting the problem and found MS ODBC for ORACLE ONLY check \orant\network\admin\tnsnames.ora. The path we installed ORACLE client on \oracle\9.2\. Does anyone know how to fix this problem? Thanks. _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dist cash 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).
raw versus Mounted File Systems
Any Good Docs , Links , sources ? Need to present a paper to the Managers Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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:How to set client's characterset on session level?
See alter session set nls_language in the SQL Reference manual. Reply Separator Author: Alexandre Gorbatchev [EMAIL PROTECTED] Date: 11/15/2002 2:58 AM Hello guys, I need to use two (or more) different client applications from the same client workstation. Applications use different charactersets WE8PC858 and WE8ISO8859P1. That's a sort of having different NLS_LANG for each session. Maybe I can create a specific connect description in listener? Or I can put something in AFTER LOGIN trigger? Or put some parameters when establishing connection? I can change NLS_TERRITORY but not characterset. I am a little bit confused because cannot resolve this rather simple (I thought) case. Any advice? TIA, Alex -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: low buffer hit ratio
Dennis: OEM calculates the BHR for all the queries. I presume it is being calculated using the same formula but only using the disk reads and buffer gets for the single query. I started with this query because it has the lowest bhr in the database. --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Gurelei - What do you mean by a low buffer hit ratio for a SQL statement? BHR is measured for the system, not for an individual query. Why have you singled this query out for attention? Are there complaints about its performance? Does it hit more blocks that other queries on your system? Gene __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei 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: RE: CONSISTANT GETS
Raj, I needed a 12 pack adter this one, it's from PeopleSlop: SELECT 0001560265,OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE, PID.INV_ITEM_ID,TMP.COST_ELEMENT,'04',0,0,0,0,0,0,PID.ORIG_UOM,PID.PRDN_AREA_COD E, PID.PRODUCTION_TYPE,OPL.QTY_SCRAPPED,OPL.PERCENT_COMP,' ',1,0,' ',' ',00 FROM PS_BU_ITEMS_INV INV,PS_SF_PRDNID_HEADR PID,PS_CE_OP_LIST_COPY OPL, PS_CE_OP_LIST_VW OPLIST,PS_SF_COMP_LIST CMP,PS_CE_ITEMVAR_TMP TMP WHERE INV.BUSINESS_UNIT='VICOR' AND PID.BUSINESS_UNIT='VICOR' AND OPL.BUSINESS_UNIT='VICOR' AND OPLIST.BUSINESS_UNIT='VICOR' AND CMP.BUSINESS_UNIT='VICOR' AND TMP.BUSINESS_UNIT='VICOR' AND TMP.PROCESS_INSTANCE=0001560265 AND OPL.PROCESS_INSTANCE=0001560265 AND OPLIST.PROCESS_INSTANCE=0001560265 AND OPL.PRODUCTION_ID= PID.PRODUCTION_ID AND OPL.PRODUCTION_ID= CMP.PRODUCTION_ID AND OPL.PRODUCTION_ID= OPLIST.PRODUCTION_ID AND INV.INV_ITEM_ID= PID.INV_ITEM_ID AND ( OPL.OP_SEQUENCE= CMP.OP_SEQUENCE OR (CMP.OP_SEQUENCE = 0 AND OPL.OP_SEQUENCE = OPLIST.OP_SEQUENCE)) AND PID.PROD_STATUS BETWEEN '30' AND '60' AND TMP.INV_ITEM_ID= CMP.COMPONENT_ID AND TMP.CONFIG_CODE= CMP.CONFIG_CODE AND CMP.SOURCE_CODE '5' AND CMP.NON_OWN_FLAG = 'N' AND NOT EXISTS (SELECT 'X' FROM PS_CE_SCRAPCST_TMP TMP2 WHERE TMP2.PROCESS_INSTANCE=0001560265 AND TMP2.BUSINESS_UNIT = OPL.BUSINESS_UNIT AND TMP2.PRODUCTION_ID = OPL.PRODUCTION_ID AND TMP2.OP_SEQUENCE = OPL.OP_SEQUENCE AND TMP2.COST_ELEMENT= TMP.COST_ELEMENT) GROUP BY OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE,PID.INV_ITEM_ID, TMP.COST_ELEMENT,PID.ORIG_UOM,PID.PRDN_AREA_CODE,PID.PRODUCTION_TYPE, OPL.PERCENT_COMP,OPL.QTY_SCRAPPED Reply Separator Author: Jamadagni; Rajendra [EMAIL PROTECTED] Date: 11/15/2002 5:33 AM Funny ... that Cary mentioned it Some developers here think that by setting some magic instance parameters we can make all RBO tuned code run well under CBO ... (I just bought a 6 pack of Mylanta yesterday ...) Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! Reply Separator Author: Cary Millsap [EMAIL PROTECTED] Date: 11/14/2002 10:34 AM Hamid, I'm sorry: Unless your SQL returns fewer than about 800,000 rows to the calling application (or an aggregation of 800,000 rows), then the statement we have done all the necessary tuning on all the SQL queries is not yet true. If your SQL does actually return about 800,000 rows, then it is time to begin thinking about the mismatch between business processing requirements and the logical structure of your data. The answer to your problem is not in your instance parameters. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 META NAME=Generator CONTENT=MS Exchange Server version 5.5.2654.19 TITLERE: RE: CONSISTANT GETS/TITLE /HEAD BODY PFONT SIZE=2Funny ... that Cary mentioned it /FONT /P PFONT SIZE=2Some developers here think that by setting some magic instance parameters we can make all RBO tuned code run well under CBO ... (I just bought a 6 pack of Mylanta yesterday ...)/FONT/P PFONT SIZE=2Raj/FONT BRFONT SIZE=2__/FONT BRFONT SIZE=2Rajendra Jamadagninbsp;nbsp;nbsp;nbsp;nbsp; nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; MIS, ESPN Inc./FONT BRFONT SIZE=2Rajendra dot Jamadagni at ESPN dot com/FONT BRFONT SIZE=2Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. /FONT BRFONT SIZE=2QOTD: Any clod can have facts, but having an opinion is an art!/FONT /P PFONT SIZE=2Reply Separator/FONT BRFONT SIZE=2Author: quot;Cary Millsapquot; lt;[EMAIL PROTECTED]gt;/FONT BRFONT SIZE=2Date:nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; 11/14/2002 10:34 AM/FONT /P PFONT SIZE=2Hamid,/FONT /P PFONT SIZE=2I'm sorry: Unless your SQL returns fewer than about 800,000 rows to the/FONT BRFONT SIZE=2calling application (or an aggregation of 800,000 rows), then the/FONT BRFONT SIZE=2statement quot;we have done all the necessary tuning on all the SQL queriesquot;/FONT BRFONT SIZE=2is not yet true./FONT /P PFONT SIZE=2If your SQL does actually return about 800,000 rows, then it is time to/FONT BRFONT SIZE=2begin thinking about the mismatch between business processing/FONT BRFONT SIZE=2requirements and the logical structure of your data./FONT /P PFONT SIZE=2The answer to your problem is not in your instance parameters./FONT /P BR PFONT SIZE=2Cary Millsap/FONT BRFONT SIZE=2Hotsos Enterprises, Ltd./FONT BRFONT SIZE=2A
RE: Import from FoxPro to Oracle
Tools like Powerbuilder/ Access can be used to import dbf files to Oracle
tables.
HTH
Sumathy Panicker
-Original Message-
Sent: Friday, November 15, 2002 8:54 AM
To: Multiple recipients of list ORACLE-L
Well at least this means that it can be done. But aren't there any tools
like Oracle Migration Work bench or the Oracle Migration Assistant for
Access . The odbc of WIn2000 doesn't support .dbf files, it goes to .DBC
for visual foxpro. So is there any possibility of simplifying the import
procedure?
-Original Message-
mailto:sfaroult;oriole.com ]
Sent: Friday, November 15, 2002 5:33 PM
To: Multiple recipients of list ORACLE-L
Hussain Ahmed Qadri wrote:
Hi ALL,
Can WE IMPORT DATA from DBF files of Fox Pro into Oracle 8.1.7.
If yes how.
TIA
Hussain
I have done something of the kind in the past by writing a program
taking a .dbf file on its standard input and writing a control file
including the data to its standard output, which was then awfully easy
to load. I no longer have the program but it took about half a day to
write, including the search on the internet to find out what the
structure of a .dbf file is, because I had no idea about it. The only
tricky thing I remember is that I wrote it to run on both Solaris and
Tru64 and in one case (Solaris, I think) some bytes had to be swapped
('man swab').
--
HTH,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Panicker, Thankam S.
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: dumping microsoft desktop?
David - I really hope you succeed. I feel the key is your users. As an I.S. person the differences between genuine Microsoft and these knock-offs may seem small compared to the benefits. However, if this isn't carefully explained to the users, they can easily revolt against what they view as company cheapness. Find a cooperative user and pilot the substitute with them, and carefully note any differences they notice. Good luck and let us know what lessons you learn. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- [mailto:David.Schmoldt;gazettecommunications.com] Sent: Thursday, November 14, 2002 5:40 PM To: Multiple recipients of list ORACLE-L We just received a request from our CEO to do research on using Open Office or Star Office as an alternative to Microsoft Office for our non-power users. So maybe there is an ultimate justice that can't be bought out or voted in (like the anti-trust case). -Original Message- From: Gogala, Mladen [mailto:MGogala;oxhp.com] Sent: Thursday, November 14, 2002 3:39 PM To: Multiple recipients of list ORACLE-L Subject: RE: RE: dumping microsoft desktop? Well, once upon a time there was an event called Boston Tea Party which dealt with too expensive product of low quality delivered by a monopoly. I wonder whether we can expect Seattle Windows Party? Would that be too cruel to the fish in Seattle harbor? -Original Message- From: [EMAIL PROTECTED] [mailto:dgoulet;vicr.com] Sent: Thursday, November 14, 2002 3:44 PM To: Multiple recipients of list ORACLE-L Subject: Re:RE: dumping microsoft desktop? David, Just like beauty, winning or loosing in a lawsuit is in the eye of the beholder. Actually in MicroSlop's case it was the justice department that bailed and more than likely King George who sat on the judge. You got to love those political action committees and their BIG donors!! In politics money talks louder than anything else. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 11/14/2002 12:15 PM Both answers a are expected to be No. The lawsuit is expected to be dropped. But who knows. They won antitrust case after all. Nick I know it *seems* like they won, but Microsoft actually lost the antitrust case. :-( Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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
Advanced Rep between 9.2.0 and 8.1.7?
Has anyone heard of or experienced a successful implementation of Advanced Replication between an 8.1.7 database and a 9.2.0 db? __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michael Barger 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: RE: CONSISTANT GETS
Dick, it's a beauty thing... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, November 15, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Raj, I needed a 12 pack adter this one, it's from PeopleSlop: SELECT 0001560265,OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE, PID.INV_ITEM_ID,TMP.COST_ELEMENT,'04',0,0,0,0,0,0,PID.ORIG_UOM,PID.PRDN_AREA _COD E, PID.PRODUCTION_TYPE,OPL.QTY_SCRAPPED,OPL.PERCENT_COMP,' ',1,0,' ',' ',00 FROM PS_BU_ITEMS_INV INV,PS_SF_PRDNID_HEADR PID,PS_CE_OP_LIST_COPY OPL, PS_CE_OP_LIST_VW OPLIST,PS_SF_COMP_LIST CMP,PS_CE_ITEMVAR_TMP TMP WHERE INV.BUSINESS_UNIT='VICOR' AND PID.BUSINESS_UNIT='VICOR' AND OPL.BUSINESS_UNIT='VICOR' AND OPLIST.BUSINESS_UNIT='VICOR' AND CMP.BUSINESS_UNIT='VICOR' AND TMP.BUSINESS_UNIT='VICOR' AND TMP.PROCESS_INSTANCE=0001560265 AND OPL.PROCESS_INSTANCE=0001560265 AND OPLIST.PROCESS_INSTANCE=0001560265 AND OPL.PRODUCTION_ID= PID.PRODUCTION_ID AND OPL.PRODUCTION_ID= CMP.PRODUCTION_ID AND OPL.PRODUCTION_ID= OPLIST.PRODUCTION_ID AND INV.INV_ITEM_ID= PID.INV_ITEM_ID AND ( OPL.OP_SEQUENCE= CMP.OP_SEQUENCE OR (CMP.OP_SEQUENCE = 0 AND OPL.OP_SEQUENCE = OPLIST.OP_SEQUENCE)) AND PID.PROD_STATUS BETWEEN '30' AND '60' AND TMP.INV_ITEM_ID= CMP.COMPONENT_ID AND TMP.CONFIG_CODE= CMP.CONFIG_CODE AND CMP.SOURCE_CODE '5' AND CMP.NON_OWN_FLAG = 'N' AND NOT EXISTS (SELECT 'X' FROM PS_CE_SCRAPCST_TMP TMP2 WHERE TMP2.PROCESS_INSTANCE=0001560265 AND TMP2.BUSINESS_UNIT = OPL.BUSINESS_UNIT AND TMP2.PRODUCTION_ID = OPL.PRODUCTION_ID AND TMP2.OP_SEQUENCE = OPL.OP_SEQUENCE AND TMP2.COST_ELEMENT= TMP.COST_ELEMENT) GROUP BY OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE,PID.INV_ITEM_ID, TMP.COST_ELEMENT,PID.ORIG_UOM,PID.PRDN_AREA_CODE,PID.PRODUCTION_TYPE, OPL.PERCENT_COMP,OPL.QTY_SCRAPPED Reply Separator Author: Jamadagni; Rajendra [EMAIL PROTECTED] Date: 11/15/2002 5:33 AM Funny ... that Cary mentioned it Some developers here think that by setting some magic instance parameters we can make all RBO tuned code run well under CBO ... (I just bought a 6 pack of Mylanta yesterday ...) Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! Reply Separator Author: Cary Millsap [EMAIL PROTECTED] Date: 11/14/2002 10:34 AM Hamid, I'm sorry: Unless your SQL returns fewer than about 800,000 rows to the calling application (or an aggregation of 800,000 rows), then the statement we have done all the necessary tuning on all the SQL queries is not yet true. If your SQL does actually return about 800,000 rows, then it is time to begin thinking about the mismatch between business processing requirements and the logical structure of your data. The answer to your problem is not in your instance parameters. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 META NAME=Generator CONTENT=MS Exchange Server version 5.5.2654.19 TITLERE: RE: CONSISTANT GETS/TITLE /HEAD BODY PFONT SIZE=2Funny ... that Cary mentioned it /FONT /P PFONT SIZE=2Some developers here think that by setting some magic instance parameters we can make all RBO tuned code run well under CBO ... (I just bought a 6 pack of Mylanta yesterday ...)/FONT/P PFONT SIZE=2Raj/FONT BRFONT SIZE=2__/FONT BRFONT SIZE=2Rajendra Jamadagninbsp;nbsp;nbsp;nbsp;nbsp; nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; MIS, ESPN Inc./FONT BRFONT SIZE=2Rajendra dot Jamadagni at ESPN dot com/FONT BRFONT SIZE=2Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. /FONT BRFONT SIZE=2QOTD: Any clod can have facts, but having an opinion is an art!/FONT /P PFONT SIZE=2Reply Separator/FONT BRFONT SIZE=2Author: quot;Cary Millsapquot; lt;[EMAIL PROTECTED]gt;/FONT BRFONT SIZE=2Date:nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; 11/14/2002 10:34 AM/FONT /P PFONT SIZE=2Hamid,/FONT /P PFONT SIZE=2I'm sorry: Unless your SQL returns fewer than about 800,000 rows to the/FONT BRFONT SIZE=2calling application (or an aggregation of 800,000 rows), then the/FONT BRFONT SIZE=2statement quot;we have done all the necessary tuning on all the SQL queriesquot;/FONT BRFONT SIZE=2is not yet true./FONT /P PFONT SIZE=2If your SQL does actually return about 800,000 rows, then it is time to/FONT BRFONT SIZE=2begin thinking about the mismatch between business processing/FONT BRFONT SIZE=2requirements and the logical structure of your data./FONT /P
RE: surprising result:8CPU Sun 3500 VS 4CPU Dell 6650
-Original Message- So, what is the advantage of Sun? Redhat Advanced server and 920 is also so much stable, and Sun T3 disk array is also of poor performance. CPU poor, disk array not that good, why sun? -- One thing I noticed is that you were using an older Sun. The current Suns have CPU's more than twice as fast as what you are using. It would be interesting to see the results using a new Sun rather than an old one. I have always thought the Dell PowerEdge series was an excellent value. But I have always appreciated the very well thought-out design of the Sun machines and the overall excellent package of solid hardware, very stable OS, and excellent customer service that Sun provides. Some capabilities of the Sun -- which might or might not exist on the Dell (I don't know) -- are the ability to partition the machine into domains and dynamically move resources between the domains. The Sun will run OK with a bad memory module or bad CPU's. As long as the Sun has one working CPU, it will run. I haven't done sys admin work for a while, but in the past, Sun provided a utility called Symon that displayed a detailed picture of the system boards and, if there was a problem with a component, would show you which component had failed. Whether these features are of any value to you depends on you. One other point in favor of the Sun is that Sun is excellent at maintaining backward compatibility in releases of its OS. You could, in fact, take a ten year old Sparc IPC, install Solaris on it, and use it as a web server or file server. Almost every old (in computer terms) Sun shop has those old lunch box (not pizza boxes) Sun's hanging around, still perfectly usable. Something I doubt could be said about a 10 year old Intel box. As I have mentioned in a previous post, the SunSolve CD is an excellent resource. One is tempted say worth its weight in gold, but it is actually worth more than that. As far as the preoccupation with which box can produce the best benchmark: In my personal philosophy, either a box is fast enough to run the application for which it is intended, or it is not. After that point those less tangible qualities, such as those listed about, do count and should be considered. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee 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: How to set client's characterset on session level?
Hi, I think you can manually trigger alter session set nls_language ... via some button in your application, or via logon trigger(if 8i+). No that will not set client's characterset. Or run os command export NLS_LANG= nls_lang1 etc and run your app:), export NLS_LANG=nls_lang2 and run your second app, this should do the trick. That's how I'm doing this now, but I have to mess up with environment variables on Win :( that's not *nix. Users may run not the .bat file, but .exe instead and I don't want (or I can't) change the app itself. I'm looking for other possible solutions. Thanks, Alex -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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).
How to identify objects that will fail to extend?
List,
There was a question as to how to identify objects that will fail to extend?
This is what we do.
SELECT owner, tablespace_name, segment_name, next_extent
FROM dba_segments ds
WHERE tablespace_name != 'TEMP'
AND next_extent ( SELECT max(bytes)
FROM dba_free_space
WHERE tablespace_name=ds.tablespace_name)
ORDER BY 1, 2;
-Original Message-
Sent: Thursday, November 14, 2002 4:54 PM
To: Multiple recipients of list ORACLE-L
Hi all,
Until a whole mass of astrological confluences happen, I'm stuck with
dictionary-managed tablespaces on 8.1.7 on HP/UX 11.0. And we're having
some space/growth issues right now that I want (need!) to be more
proactive
with. So, based on several factors -- most political -- I want to run a
daily report that tells me when a segment will not be able to extend
twice.
(We're already running the single extent failure hourly.)
After looking on the net, I found some queries to do this, but all I saw
were severely flawed. So, I rolled my own. The only problem I can see
with
it for dictionary TSs is when the RANK() has multiple matches for first
and
second (e.g. TS MY_BIG_TS has it's largest contiguous free spaces of
40M,
10M, and 10M). Unfortunately, I'm stumped as to how to prevent this.
Anyone care to comment on this load of SQueaL? Thx! :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI
USA
SELECT ds.owner, ds.segment_name, ds.segment_type, ds.tablespace_name,
ds.next_extent/1024 Next ext, fs2.max_free/1024 Max Free,
fs2.min_free/1024 2nd Max Free, fs2.free_spaces
FROM dba_segments ds,
(
SELECT tablespace_name, MAX(bytes) max_free, MIN(bytes)
min_free,
count(*) free_spaces
FROM
(
SELECT tablespace_name, bytes,
RANK() OVER (PARTITION BY tablespace_name
ORDER BY tablespace_name, bytes DESC)
byte_rank
FROM dba_free_space
)
WHERE byte_rank 3
GROUP BY tablespace_name
) fs2
WHERE ds.segment_type IN ('INDEX','TABLE')
AND fs2.tablespace_name = ds.tablespace_name
AND (
((ds.next_extent fs2.min_free OR fs2.free_spaces 2)
AND ds.next_extent*2 fs2.max_free)
OR ds.next_extent fs2.max_free
)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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: RE: CONSISTANT GETS
Title: RE: RE: RE: CONSISTANT GETS I've seen worse. My programmers don't know how to use NOT EXISTS even though I've explained it many times. And that's the least of my problems. Look at this mess: SELECT * FROM sar.pax_header_suspense_err_temp WHERE manifest_type || manifesting_station || fiscal_year || manifest_serial_number NOT IN ( SELECT manifest_type || manifesting_station || fiscal_year || manifest_serial_number FROM manifest_serial_number_history) Takes over an hour to run. I rewrote it as such: SELECT * FROM sar.pax_header_suspense_err_temp t WHERE NOT EXISTS (SELECT 'X' FROM manifest_serial_number_history h WHERE t.manifest_type = h.manifest_type and t.manifesting_station = h.manifesting_station and t.fiscal_year = h.fiscal_year and t.manifest_serial_number = h.manifest_serial_number ) Under a second. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Raj, I needed a 12 pack adter this one, it's from PeopleSlop: SELECT 0001560265,OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE, PID.INV_ITEM_ID,TMP.COST_ELEMENT,'04',0,0,0,0,0,0,PID.ORIG_UOM,PID.PRDN_AREA_COD E, PID.PRODUCTION_TYPE,OPL.QTY_SCRAPPED,OPL.PERCENT_COMP,' ',1,0,' ',' ',00 FROM PS_BU_ITEMS_INV INV,PS_SF_PRDNID_HEADR PID,PS_CE_OP_LIST_COPY OPL, PS_CE_OP_LIST_VW OPLIST,PS_SF_COMP_LIST CMP,PS_CE_ITEMVAR_TMP TMP WHERE INV.BUSINESS_UNIT='VICOR' AND PID.BUSINESS_UNIT='VICOR' AND OPL.BUSINESS_UNIT='VICOR' AND OPLIST.BUSINESS_UNIT='VICOR' AND CMP.BUSINESS_UNIT='VICOR' AND TMP.BUSINESS_UNIT='VICOR' AND TMP.PROCESS_INSTANCE=0001560265 AND OPL.PROCESS_INSTANCE=0001560265 AND OPLIST.PROCESS_INSTANCE=0001560265 AND OPL.PRODUCTION_ID= PID.PRODUCTION_ID AND OPL.PRODUCTION_ID= CMP.PRODUCTION_ID AND OPL.PRODUCTION_ID= OPLIST.PRODUCTION_ID AND INV.INV_ITEM_ID= PID.INV_ITEM_ID AND ( OPL.OP_SEQUENCE= CMP.OP_SEQUENCE OR (CMP.OP_SEQUENCE = 0 AND OPL.OP_SEQUENCE = OPLIST.OP_SEQUENCE)) AND PID.PROD_STATUS BETWEEN '30' AND '60' AND TMP.INV_ITEM_ID= CMP.COMPONENT_ID AND TMP.CONFIG_CODE= CMP.CONFIG_CODE AND CMP.SOURCE_CODE '5' AND CMP.NON_OWN_FLAG = 'N' AND NOT EXISTS (SELECT 'X' FROM PS_CE_SCRAPCST_TMP TMP2 WHERE TMP2.PROCESS_INSTANCE=0001560265 AND TMP2.BUSINESS_UNIT = OPL.BUSINESS_UNIT AND TMP2.PRODUCTION_ID = OPL.PRODUCTION_ID AND TMP2.OP_SEQUENCE = OPL.OP_SEQUENCE AND TMP2.COST_ELEMENT= TMP.COST_ELEMENT) GROUP BY OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE,PID.INV_ITEM_ID, TMP.COST_ELEMENT,PID.ORIG_UOM,PID.PRDN_AREA_CODE,PID.PRODUCTION_TYPE, OPL.PERCENT_COMP,OPL.QTY_SCRAPPED
RE: Import from FoxPro to Oracle
Title: Import from FoxPro to Oracle Hussain, It's easy to generate text files with delimiters from FoxPro and then load them with SQL Loader.I did it 4 years ago for FoxPro 2.6 for DOS and that still works there. I know you may do it directly from dbf's but this is not so straightforward. Regards, Alex -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Hussain Ahmed QadriSent: Friday, November 15, 2002 1:03 PMTo: Multiple recipients of list ORACLE-LSubject: Import from FoxPro to Oracle Hi ALL, Can WE IMPORT DATA from DBF files of Fox Pro into Oracle 8.1.7. If yes how. TIA Hussain
RE: How to set client's characterset on session level?
Dick, I have already thoroughly RTFMed on this issue, but didn't find anything suitable using ALTER SESSION SET NLS_* Please, correct me if I'm wrong. NLS_LAN consists of 3 parts: language, territory and characterset. Language and territory can be set/changed at session level as parameters NLS_LANGUAGE and NLS_TERRITORY respectively. But there is no alternative (at least I didn't find one) for characterset. I can ALTER SESSION SET NLS_LANGUAGE=AMERICAN. However, this does not change character encoding scheme. Thanks, Alex -Original Message- From: [EMAIL PROTECTED] [mailto:dgoulet;vicr.com] Sent: Friday, November 15, 2002 4:06 PM To: Alexandre Gorbatchev; Multiple recipients of list ORACLE-L Subject: Re:How to set client's characterset on session level? See alter session set nls_language in the SQL Reference manual. Reply Separator Subject:How to set client's characterset on session level? Author: Alexandre Gorbatchev [EMAIL PROTECTED] Date: 11/15/2002 2:58 AM Hello guys, I need to use two (or more) different client applications from the same client workstation. Applications use different charactersets WE8PC858 and WE8ISO8859P1. That's a sort of having different NLS_LANG for each session. Maybe I can create a specific connect description in listener? Or I can put something in AFTER LOGIN trigger? Or put some parameters when establishing connection? I can change NLS_TERRITORY but not characterset. I am a little bit confused because cannot resolve this rather simple (I thought) case. Any advice? TIA, Alex -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev 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: Import from FoxPro to Oracle
Hussain - One thing to be aware in generating text files from FoxPro is that Microsoft tends to be inconsistent with delimiters. Sometimes it thinks it needs a delimiter, and sometimes it doesn't write a delimiter. Inconsistency is hard to deal with. Often I find it is easier to deal with fixed-format files (sdf format). Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] mailto:dwilliams;lifetouch.com -Original Message- Sent: Friday, November 15, 2002 10:14 AM To: Multiple recipients of list ORACLE-L Hussain, It's easy to generate text files with delimiters from FoxPro and then load them with SQL Loader. I did it 4 years ago for FoxPro 2.6 for DOS and that still works there. I know you may do it directly from dbf's but this is not so straightforward. Regards, Alex -Original Message- Qadri Sent: Friday, November 15, 2002 1:03 PM To: Multiple recipients of list ORACLE-L Hi ALL, Can WE IMPORT DATA from DBF files of Fox Pro into Oracle 8.1.7. If yes how. TIA Hussain -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: Import from FoxPro to Oracle
Can WE IMPORT DATA from DBF files of Fox Pro into Oracle 8.1.7. If yes how. Take a look at the Perl DBI. You'll need the DBI package, and the DBD modules for xBase and Oracle. There are many examples of use out there on the Internet. You may also want to investigate the [somewhat helpful] O'Reilly Programming the Perl DBI book. Gary Chambers //-- // Lucent Technologies CIO/Servers/Unix // Senior Unix System Administrator // 4 Robbins Road, Westford, MA 01886 // 978-399-0481 / 888-480-6924 (Pager) // Nothing fancy and nothing Microsoft //-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gary Chambers 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).
Slow Inserts
Oracle 8.1.7.0.0 on Win2k (4 CPU, 4GB RAM) A 3rd Party app. is experiencing very slow performance on one of our databases. I think I've nailed it down to slow, row-at-a-time inserts. The same app. performs very fast on another DB with LMTs. After switching the tables and indexes in the slow DB to LMT, we still have slow performance. The extract from the SQL_Trace below is the slow statement. It actually takes about an hour to insert a few hundred rows. You can watch the trace file slowly grow with executions of this statement. There is only one User hitting this table (with its single index). The table is initially empty, so it's not extending. Anybody have any ideas as to the cause of this slow Insert activity? BTW, I ran BStat and EStat during this time and nothing jumps out at me. Also, we ran it with CHOOSE (and fresh statistics) - same slowness. INSERT INTO SASI.W_ENROLL (SCHOOLNUMBER ,STULINK ,FROMDATE , TODATE ,GRADE ,TRACK ,PEIMSTRACK ,PEIMSSCHOOLNUMBER , ADAELIGIBILITYCODE ,ISENTERDATE ) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2735 5.88 30.00 0 0 0 0 Execute 2735 1.16 1.24 3 2779 8571 2735 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 5470 7.04 31.24 3 2779 8571 2735 Misses in library cache during parse: 0 Optimizer goal: RULE Parsing user id: 399 (TXSRC) Rows Execution Plan --- --- 0 INSERT STATEMENT GOAL: RULE TIA. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
RE: surprising result:8CPU Sun 3500 VS 4CPU Dell 6650
-Original Message- I am also having a weird performance issue with a sun box - mine is a new v880 4 cpu (900mz) with 16g of ram and a 2 T hitachi san. - My first suspicion would involve the SAN. We have one system here that, for whatever reason, (sorry, I'm not the SAN expert), the computer and the storage system do not work well with each other. As a result, disk writes are VERY slow. This affects both large data inserts and updates, and large sorts (such as reports) that require the use of on-disk temp space. I've been told that the particular version of the OS (Tru64) doesn't work properly with the cache on this storage system. They are in the process of changing the version of the OS. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee 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: surprising result:8CPU Sun 3500 VS 4CPU Dell 6650
I am also having a weird performance issue with a sun box - mine is a new v880 4 cpu (900mz) with 16g of ram and a 2 T hitachi san. For example - I do an import of a table (partitioned 3 m rows ) and it takes almost 8 minutes vs 3 minutes on my laptop. both running 9.2.0 . many reports take significantly longer on the sun box than my laptop - go figure - I have a tar on it - but resolutions yet. I have uploaded statspack up to oraperf and nothing significant showed up there either. Anybody have a idea I'd be happy to try it. [EMAIL PROTECTED] 11/15/02 10:00AM -Original Message- So, what is the advantage of Sun? Redhat Advanced server and 920 is also so much stable, and Sun T3 disk array is also of poor performance. CPU poor, disk array not that good, why sun? --One thing I noticed is that you were using an older Sun. The current Sunshave CPU's more than twice as fast as what you are using. It would beinteresting to see the results using a new Sun rather than an old one. Ihave always thought the Dell PowerEdge series was an excellent value. But Ihave always appreciated the very well thought-out design of the Sun machinesand the overall excellent package of solid hardware, very stable OS, andexcellent customer service that Sun provides.Some capabilities of the Sun -- which might or might not exist on the Dell(I don't know) -- are the ability to partition the machine into "domains"and dynamically move resources between the domains. The Sun will run OKwith a bad memory module or bad CPU's. As long as the Sun has one workingCPU, it will run. I haven't done sys admin work for a while, but in thepast, Sun provided a utility called Symon that displayed a detailed pictureof the system boards and, if there was a problem with a component, wouldshow you which component had failed. Whether these features are of anyvalue to you depends on you. One other point in favor of the Sun is thatSun is excellent at maintaining backward compatibility in releases of itsOS. You could, in fact, take a ten year old Sparc IPC, install Solaris onit, and use it as a web server or file server. Almost every old (incomputer terms) Sun shop has those old "lunch box" (not pizza boxes) Sun'shanging around, still perfectly usable. Something I doubt could be saidabout a 10 year old Intel box.As I have mentioned in a previous post, the SunSolve CD is an excellentresource. One is tempted say "worth its weight in gold", but it is actuallyworth more than that.As far as the preoccupation with which box can produce the best benchmark:In my personal philosophy, either a box is fast enough to run theapplication for which it is intended, or it is not. After that point thoseless tangible qualities, such as those listed about, do count and should beconsidered.-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Stephen Lee INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Performance consequences from downgrading from 64-bit to 32-bit?
Title: Performance consequences from downgrading from 64-bit to 32-bit? We have an 8.1.7.4 64-bit 24x7 OLTP database currently hosted on an RS6000 s80 running AIX 4.3.3 The database is scheduled to be migrated to a new box, RS6000 ip680, running AIX 5.1. According to the compatibility matrix on Metalink, 64-bit 8.1.7.4 is not certified on AIX 5.1, but the 32-bit version is. So one option is to simply downgrade to 8.1.7.4 32-bit when we make the move. Would we expect to see any performance degradation as a result of moving from 64-bit to 32-bit, notwithstanding the fact the degradation could be masked by virtue of moving to a newer, faster box? Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages'
RE: surprising result:8CPU Sun 3500 VS 4CPU Dell 6650
We experienced the same problems at my work where dual P3 Xeons running at 1.133Mhz (Compaq Proliants) outperformed a SunFire280R 2:1 in I/O performance. This means that the Proliant had twice more throughput than the 5x more expensive Sun. I don't understand this and I have no explanation from Sun nor from anybody else. I'd hate to say this but Sun is on it's way out if this is the case. -- Lyndon Tiu Quoting John Shaw [EMAIL PROTECTED]: I am also having a weird performance issue with a sun box - mine is a new v880 4 cpu (900mz) with 16g of ram and a 2 T hitachi san. For example - I do an import of a table (partitioned 3 m rows ) and it takes almost 8 minutes vs 3 minutes on my laptop. both running 9.2.0 . many reports take significantly longer on the sun box than my laptop - go figure - I have a tar on it - but resolutions yet. I have uploaded statspack up to oraperf and nothing significant showed up there either. Anybody have a idea I'd be happy to try it. [EMAIL PROTECTED] 11/15/02 10:00AM -Original Message- So, what is the advantage of Sun? Redhat Advanced server and 920 is also so much stable, and Sun T3 disk array is also of poor performance. CPU poor, disk array not that good, why sun? -- One thing I noticed is that you were using an older Sun. The current Suns have CPU's more than twice as fast as what you are using. It would be interesting to see the results using a new Sun rather than an old one. I have always thought the Dell PowerEdge series was an excellent value. But I have always appreciated the very well thought-out design of the Sun machines and the overall excellent package of solid hardware, very stable OS, and excellent customer service that Sun provides. Some capabilities of the Sun -- which might or might not exist on the Dell (I don't know) -- are the ability to partition the machine into domains and dynamically move resources between the domains. The Sun will run OK with a bad memory module or bad CPU's. As long as the Sun has one working CPU, it will run. I haven't done sys admin work for a while, but in the past, Sun provided a utility called Symon that displayed a detailed picture of the system boards and, if there was a problem with a component, would show you which component had failed. Whether these features are of any value to you depends on you. One other point in favor of the Sun is that Sun is excellent at maintaining backward compatibility in releases of its OS. You could, in fact, take a ten year old Sparc IPC, install Solaris on it, and use it as a web server or file server. Almost every old (in computer terms) Sun shop has those old lunch box (not pizza boxes) Sun's hanging around, still perfectly usable. Something I doubt could be said about a 10 year old Intel box. As I have mentioned in a previous post, the SunSolve CD is an excellent resource. One is tempted say worth its weight in gold, but it is actually worth more than that. As far as the preoccupation with which box can produce the best benchmark: In my personal philosophy, either a box is fast enough to run the application for which it is intended, or it is not. After that point those less tangible qualities, such as those listed about, do count and should be considered. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lyndon Tiu 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: Slow Inserts
[EMAIL PROTECTED] wrote: Oracle 8.1.7.0.0 on Win2k (4 CPU, 4GB RAM) A 3rd Party app. is experiencing very slow performance on one of our databases. I think I've nailed it down to slow, row-at-a-time inserts. The same app. performs very fast on another DB with LMTs. After switching the tables and indexes in the slow DB to LMT, we still have slow performance. The extract from the SQL_Trace below is the slow statement. It actually takes about an hour to insert a few hundred rows. You can watch the trace file slowly grow with executions of this statement. There is only one User hitting this table (with its single index). The table is initially empty, so it's not extending. Anybody have any ideas as to the cause of this slow Insert activity? BTW, I ran BStat and EStat during this time and nothing jumps out at me. Also, we ran it with CHOOSE (and fresh statistics) - same slowness. INSERT INTO SASI.W_ENROLL (SCHOOLNUMBER ,STULINK ,FROMDATE , TODATE ,GRADE ,TRACK ,PEIMSTRACK ,PEIMSSCHOOLNUMBER , ADAELIGIBILITYCODE ,ISENTERDATE ) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2735 5.88 30.00 0 0 0 0 Execute 2735 1.16 1.24 3 2779 8571 2735 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 5470 7.04 31.24 3 2779 8571 2735 Misses in library cache during parse: 0 Optimizer goal: RULE Parsing user id: 399 (TXSRC) Rows Execution Plan --- --- 0 INSERT STATEMENT GOAL: RULE TIA. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Any idea why you have as many parses as executes ? That's where all the elapsed time is, parsing. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: RE: dumping microsoft desktop?
On Fri, Nov 15, 2002 at 05:59:06AM -0800, [EMAIL PROTECTED] wrote: OH, talk about cruel and unusual punishment!! For the fish that is. It is interesting that so many people feel this way and yet the topic is reduced to joking, as if it is not possible (or really desirable) to replace technology. MVS systems programmers couldn't envision life without an IBM mainframe a few years ago. I asked the desktop question because we are exploring this path due to huge budget issues and the million dollar invoice to M$ is due. I wanted to see if the Oracle world had anything going on the topic. Guess not. Star and Open Office advances seem to be milestones in this arena. I believe we will move some % of our admin desktops to linux this year as a pilot. Departments can't afford the price of upgrading their office software, they will try the open versions to see what happens. Interesting bullets: The EU is studying the conversion of member goverment desktops: www.globetechnology.com/servlet/ArticleNews/einsider/RTGAM/20021104/gtopenms/einsider/ The European Union awarded on Thursday a $249,000 (U.S.) contract to U.K.-based system-integrator Netproject to study the feasibility of moving the information systems of several member countries' governments to the Linux operating system from Microsoft's Windows OS. www.netproject.co.uk/opendesktop.html The USA National Security Agency's white paper 'The Inevitability of Failure: The Flawed Assumption of Security in Modern Computing Environments' should be read by all who are concernd with achieving secure systems that enable e-business. This is at www.nsa.gov/selinux/inevit-abs.html. ...waiting for the 9.2.0.2 patch to finish on my Mandrake 9 desktop (see, it's not off topic! ;) Dick Goulet Reply Separator Author: Gogala; Mladen [EMAIL PROTECTED] Date: 11/14/2002 1:38 PM Well, once upon a time there was an event called Boston Tea Party which dealt with too expensive product of low quality delivered by a monopoly. I wonder whether we can expect Seattle Windows Party? Would that be too cruel to the fish in Seattle harbor? -Original Message- From: [EMAIL PROTECTED] [mailto:dgoulet;vicr.com] Sent: Thursday, November 14, 2002 3:44 PM To: Multiple recipients of list ORACLE-L Subject: Re:RE: dumping microsoft desktop? David, Just like beauty, winning or loosing in a lawsuit is in the eye of the beholder. Actually in MicroSlop's case it was the justice department that bailed and more than likely King George who sat on the judge. You got to love those political action committees and their BIG donors!! In politics money talks louder than anything else. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 11/14/2002 12:15 PM Both answers a are expected to be No. The lawsuit is expected to be dropped. But who knows. They won antitrust case after all. Nick I know it *seems* like they won, but Microsoft actually lost the antitrust case. :-( Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- 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.com -- Author: Gogala, Mladen 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
Data specs of columns
I have a request from a developer to determine if a column is computed, has a primary key has a foreign key. Im looking through the dictionary tables eg user_tab_columns but these firlds seem to be elusive. Any ideas what views to querry ? Many thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky 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: surprising result:8CPU Sun 3500 VS 4CPU Dell 6650
Stephen Lee, To tell you the truth, sun 4500 is the most high end sun i have ever touched:), so i do not have experience on concept like partition etc. And talking about that excellent High Avaliable feature like CPU/Memory corruption and the server still run,that is really something great. And i did not know it before.And i think it is impossible to implement on that kind of low end Dell PC servers, but for servers like V880, that is also something impossible i think, right? We cannot compare a product whose value is 1M$ with products whose value is 10K$:). After all, intel is still on middle-low end. Thanks for your valueable knowledge, thanks. Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net www.cnoug.org(Chinese Oracle User Group) === 2002-11-15 08:00:00 ,you wrote£º=== -Original Message- So, what is the advantage of Sun? Redhat Advanced server and 920 is also so much stable, and Sun T3 disk array is also of poor performance. CPU poor, disk array not that good, why sun? -- One thing I noticed is that you were using an older Sun. The current Suns have CPU's more than twice as fast as what you are using. It would be interesting to see the results using a new Sun rather than an old one. I have always thought the Dell PowerEdge series was an excellent value. But I have always appreciated the very well thought-out design of the Sun machines and the overall excellent package of solid hardware, very stable OS, and excellent customer service that Sun provides. Some capabilities of the Sun -- which might or might not exist on the Dell (I don't know) -- are the ability to partition the machine into domains and dynamically move resources between the domains. The Sun will run OK with a bad memory module or bad CPU's. As long as the Sun has one working CPU, it will run. I haven't done sys admin work for a while, but in the past, Sun provided a utility called Symon that displayed a detailed picture of the system boards and, if there was a problem with a component, would show you which component had failed. Whether these features are of any value to you depends on you. One other point in favor of the Sun is that Sun is excellent at maintaining backward compatibility in releases of its OS. You could, in fact, take a ten year old Sparc IPC, install Solaris on it, and use it as a web server or file server. Almost every old (in computer terms) Sun shop has those old lunch box (not pizza boxes) Sun's hanging around, still perfectly usable. Something I doubt could be said about a 10 year old Intel box. As I have mentioned in a previous post, the SunSolve CD is an excellent resource. One is tempted say worth its weight in gold, but it is actually worth more than that. As far as the preoccupation with which box can produce the best benchmark: In my personal philosophy, either a box is fast enough to run the application for which it is intended, or it is not. After that point those less tangible qualities, such as those listed about, do count and should be considered. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: chao_ping 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: RE: CONSISTANT GETS
Jerry, I suspect that the improvments are more likely due to your rewriting the WHERE clause rather than the use of NOT EXISTS. Especially if the database were 9i, where NOT IN actually seems get a better execution path than NOT EXISTS. That original WHERE clause is really a piece of work. Jared Whittle Jerome Contr NCI [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/15/2002 08:21 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: RE: CONSISTANT GETS I've seen worse. My programmers don't know how to use NOT EXISTS even though I've explained it many times. And that's the least of my problems. Look at this mess: SELECT * FROM sar.pax_header_suspense_err_temp WHEREmanifest_type || manifesting_station || fiscal_year || manifest_serial_number NOT IN ( SELECTmanifest_type || manifesting_station || fiscal_year || manifest_serial_number FROM manifest_serial_number_history) Takes over an hour to run. I rewrote it as such: SELECT * FROM sar.pax_header_suspense_err_temp t WHERE NOT EXISTS (SELECT 'X' FROM manifest_serial_number_history h WHERE t.manifest_type = h.manifest_type and t.manifesting_station = h.manifesting_station and t.fiscal_year = h.fiscal_year and t.manifest_serial_number = h.manifest_serial_number ) Under a second. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- Raj, I needed a 12 pack adter this one, it's from PeopleSlop: SELECT 0001560265,OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE, PID.INV_ITEM_ID,TMP.COST_ELEMENT,'04',0,0,0,0,0,0,PID.ORIG_UOM,PID.PRDN_AREA_COD E, PID.PRODUCTION_TYPE,OPL.QTY_SCRAPPED,OPL.PERCENT_COMP,' ',1,0,' ',' ',00 FROM PS_BU_ITEMS_INV INV,PS_SF_PRDNID_HEADR PID,PS_CE_OP_LIST_COPY OPL, PS_CE_OP_LIST_VW OPLIST,PS_SF_COMP_LIST CMP,PS_CE_ITEMVAR_TMP TMP WHERE INV.BUSINESS_UNIT='VICOR' AND PID.BUSINESS_UNIT='VICOR' AND OPL.BUSINESS_UNIT='VICOR' AND OPLIST.BUSINESS_UNIT='VICOR' AND CMP.BUSINESS_UNIT='VICOR' AND TMP.BUSINESS_UNIT='VICOR' AND TMP.PROCESS_INSTANCE=0001560265 AND OPL.PROCESS_INSTANCE=0001560265 AND OPLIST.PROCESS_INSTANCE=0001560265 AND OPL.PRODUCTION_ID= PID.PRODUCTION_ID AND OPL.PRODUCTION_ID= CMP.PRODUCTION_ID AND OPL.PRODUCTION_ID= OPLIST.PRODUCTION_ID AND INV.INV_ITEM_ID= PID.INV_ITEM_ID AND ( OPL.OP_SEQUENCE= CMP.OP_SEQUENCE OR (CMP.OP_SEQUENCE = 0 AND OPL.OP_SEQUENCE = OPLIST.OP_SEQUENCE)) AND PID.PROD_STATUS BETWEEN '30' AND '60' AND TMP.INV_ITEM_ID= CMP.COMPONENT_ID AND TMP.CONFIG_CODE= CMP.CONFIG_CODE AND CMP.SOURCE_CODE '5' AND CMP.NON_OWN_FLAG = 'N' AND NOT EXISTS (SELECT 'X' FROM PS_CE_SCRAPCST_TMP TMP2 WHERE TMP2.PROCESS_INSTANCE=0001560265 AND TMP2.BUSINESS_UNIT = OPL.BUSINESS_UNIT AND TMP2.PRODUCTION_ID = OPL.PRODUCTION_ID AND TMP2.OP_SEQUENCE = OPL.OP_SEQUENCE AND TMP2.COST_ELEMENT= TMP.COST_ELEMENT) GROUP BY OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE,PID.INV_ITEM_ID, TMP.COST_ELEMENT,PID.ORIG_UOM,PID.PRDN_AREA_CODE,PID.PRODUCTION_TYPE, OPL.PERCENT_COMP,OPL.QTY_SCRAPPED -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Re: Slow Inserts
The Parse statistics are what caught my eye. The statement is being parsed once per execution, which is puzzling because it does use bind variables. Of more concern, though, is the difference between CPU and elapsed times for the parsing, indicating that there is a lot of waiting going on. I'd suggest looking at v$session_event to see what are the top wait events for the session. break on sid col event format a30 set pages 99 select se.event, se.total_waits, se.time_waited, se.average_wait from v$session s, v$session_event se where s.sid = se.sid and s.username is not null and s.sid = sid order by se.time_waited / Run this before and after executing the insert statement, and compute the delta for each wait event seen. HTH Paul Baumgartel --- [EMAIL PROTECTED] wrote: Oracle 8.1.7.0.0 on Win2k (4 CPU, 4GB RAM) A 3rd Party app. is experiencing very slow performance on one of our databases. I think I've nailed it down to slow, row-at-a-time inserts. The same app. performs very fast on another DB with LMTs. After switching the tables and indexes in the slow DB to LMT, we still have slow performance. The extract from the SQL_Trace below is the slow statement. It actually takes about an hour to insert a few hundred rows. You can watch the trace file slowly grow with executions of this statement. There is only one User hitting this table (with its single index). The table is initially empty, so it's not extending. Anybody have any ideas as to the cause of this slow Insert activity? BTW, I ran BStat and EStat during this time and nothing jumps out at me. Also, we ran it with CHOOSE (and fresh statistics) - same slowness. INSERT INTO SASI.W_ENROLL (SCHOOLNUMBER ,STULINK ,FROMDATE , TODATE ,GRADE ,TRACK ,PEIMSTRACK ,PEIMSSCHOOLNUMBER , ADAELIGIBILITYCODE ,ISENTERDATE ) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2735 5.88 30.00 0 0 0 0 Execute 2735 1.16 1.24 3 2779 8571 2735 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 5470 7.04 31.24 3 2779 8571 2735 Misses in library cache during parse: 0 Optimizer goal: RULE Parsing user id: 399 (TXSRC) Rows Execution Plan --- --- 0 INSERT STATEMENT GOAL: RULE TIA. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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: RE: RE: CONSISTANT GETS
Tom, There aren't no beauty where the sun don't shine!! And that's where I parked this one!! *-) Dick Goulet Reply Separator Author: Mercadante; Thomas F [EMAIL PROTECTED] Date: 11/15/2002 7:54 AM Dick, it's a beauty thing... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, November 15, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Raj, I needed a 12 pack adter this one, it's from PeopleSlop: SELECT 0001560265,OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE, PID.INV_ITEM_ID,TMP.COST_ELEMENT,'04',0,0,0,0,0,0,PID.ORIG_UOM,PID.PRDN_AREA _COD E, PID.PRODUCTION_TYPE,OPL.QTY_SCRAPPED,OPL.PERCENT_COMP,' ',1,0,' ',' ',00 FROM PS_BU_ITEMS_INV INV,PS_SF_PRDNID_HEADR PID,PS_CE_OP_LIST_COPY OPL, PS_CE_OP_LIST_VW OPLIST,PS_SF_COMP_LIST CMP,PS_CE_ITEMVAR_TMP TMP WHERE INV.BUSINESS_UNIT='VICOR' AND PID.BUSINESS_UNIT='VICOR' AND OPL.BUSINESS_UNIT='VICOR' AND OPLIST.BUSINESS_UNIT='VICOR' AND CMP.BUSINESS_UNIT='VICOR' AND TMP.BUSINESS_UNIT='VICOR' AND TMP.PROCESS_INSTANCE=0001560265 AND OPL.PROCESS_INSTANCE=0001560265 AND OPLIST.PROCESS_INSTANCE=0001560265 AND OPL.PRODUCTION_ID= PID.PRODUCTION_ID AND OPL.PRODUCTION_ID= CMP.PRODUCTION_ID AND OPL.PRODUCTION_ID= OPLIST.PRODUCTION_ID AND INV.INV_ITEM_ID= PID.INV_ITEM_ID AND ( OPL.OP_SEQUENCE= CMP.OP_SEQUENCE OR (CMP.OP_SEQUENCE = 0 AND OPL.OP_SEQUENCE = OPLIST.OP_SEQUENCE)) AND PID.PROD_STATUS BETWEEN '30' AND '60' AND TMP.INV_ITEM_ID= CMP.COMPONENT_ID AND TMP.CONFIG_CODE= CMP.CONFIG_CODE AND CMP.SOURCE_CODE '5' AND CMP.NON_OWN_FLAG = 'N' AND NOT EXISTS (SELECT 'X' FROM PS_CE_SCRAPCST_TMP TMP2 WHERE TMP2.PROCESS_INSTANCE=0001560265 AND TMP2.BUSINESS_UNIT = OPL.BUSINESS_UNIT AND TMP2.PRODUCTION_ID = OPL.PRODUCTION_ID AND TMP2.OP_SEQUENCE = OPL.OP_SEQUENCE AND TMP2.COST_ELEMENT= TMP.COST_ELEMENT) GROUP BY OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE,PID.INV_ITEM_ID, TMP.COST_ELEMENT,PID.ORIG_UOM,PID.PRDN_AREA_CODE,PID.PRODUCTION_TYPE, OPL.PERCENT_COMP,OPL.QTY_SCRAPPED Reply Separator Author: Jamadagni; Rajendra [EMAIL PROTECTED] Date: 11/15/2002 5:33 AM Funny ... that Cary mentioned it Some developers here think that by setting some magic instance parameters we can make all RBO tuned code run well under CBO ... (I just bought a 6 pack of Mylanta yesterday ...) Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! Reply Separator Author: Cary Millsap [EMAIL PROTECTED] Date: 11/14/2002 10:34 AM Hamid, I'm sorry: Unless your SQL returns fewer than about 800,000 rows to the calling application (or an aggregation of 800,000 rows), then the statement we have done all the necessary tuning on all the SQL queries is not yet true. If your SQL does actually return about 800,000 rows, then it is time to begin thinking about the mismatch between business processing requirements and the logical structure of your data. The answer to your problem is not in your instance parameters. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 META NAME=Generator CONTENT=MS Exchange Server version 5.5.2654.19 TITLERE: RE: CONSISTANT GETS/TITLE /HEAD BODY PFONT SIZE=2Funny ... that Cary mentioned it /FONT /P PFONT SIZE=2Some developers here think that by setting some magic instance parameters we can make all RBO tuned code run well under CBO ... (I just bought a 6 pack of Mylanta yesterday ...)/FONT/P PFONT SIZE=2Raj/FONT BRFONT SIZE=2__/FONT BRFONT SIZE=2Rajendra Jamadagninbsp;nbsp;nbsp;nbsp;nbsp; nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; MIS, ESPN Inc./FONT BRFONT SIZE=2Rajendra dot Jamadagni at ESPN dot com/FONT BRFONT SIZE=2Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. /FONT BRFONT SIZE=2QOTD: Any clod can have facts, but having an opinion is an art!/FONT /P PFONT SIZE=2Reply Separator/FONT BRFONT SIZE=2Author: quot;Cary Millsapquot; lt;[EMAIL PROTECTED]gt;/FONT BRFONT SIZE=2Date:nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; 11/14/2002 10:34 AM/FONT /P PFONT SIZE=2Hamid,/FONT /P PFONT SIZE=2I'm sorry: Unless your SQL returns fewer than about 800,000 rows to the/FONT BRFONT SIZE=2calling application (or an aggregation of 800,000 rows), then the/FONT BRFONT SIZE=2statement quot;we have done all the necessary tuning on all the SQL queriesquot;/FONT BRFONT SIZE=2is not yet true./FONT
Re: Performance consequences from downgrading from 64-bit to 32-bit?
Thomas Jeff£¬ÄúºÃ£¡ The main problem i think is that 32bit oracle has its sga size limit, if i remember it correctly, it is only 1.75GB, which is not big enough for oracle running on S80? Maybe you can relink it to enlarge sga, i ever see document about enlarge sga via relink on solaris ,but not on aix. To move the server, i think install oracle on the new node and copy datafile and run $ORACLE_HOME/rdbms/admin/utlrp.sql should work. Good luck 2002-11-15 08:59:00 ÄúÔÚÀ´ÐÅÖÐдµÀ£º We have an 8.1.7.4 64-bit 24x7 OLTP database currently hosted on an RS6000 s80 running AIX 4.3.3 The database is scheduled to be migrated to a new box, RS6000 ip680, running AIX 5.1. According to the compatibility matrix on Metalink, 64-bit 8.1.7.4 is not certified on AIX 5.1, but the 32-bit version is. So one option is to simply downgrade to 8.1.7.4 32-bit when we make the move. Would we expect to see any performance degradation as a result of moving from 64-bit to 32-bit, notwithstanding the fact the degradation could be masked by virtue of moving to a newer, faster box? Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages' = = = = = = = = = = = = = = = = = = = = = = Ö Àñ£¡ chao_ping [EMAIL PROTECTED] ¡¡2002-11-16 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: chao_ping 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).
Invalid Objects but no errors
I checked for invalid objects and two of them came up. When I tried to recompile them it says there was compilation errors. Yet show errors doesn't show anything. Does anyone have familiarity with this particular package or have an idea on how to fix this so it is valid? SYSDBMS_REPCAT_RGTPACKAGE BODY SVRMGR alter package dbms_repcat_rgt compile body; MGR-00072: Warning: PACKAGE DBMS_REPCAT_RGT created with compilation errors. SVRMGR show errors No errors for PACKAGE DBMS_REPCAT_RGT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon 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: How to identify objects that will fail to extend?
Thanks, but the next extent is the easy one. As I mentioned, I'm already
running a similar query hourly.
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
From: [EMAIL PROTECTED] [mailto:Govind.Arumugam;alltel.com]
Sent: Friday, November 15, 2002 10:14 AM
To: Multiple recipients of list ORACLE-L
Subject: How to identify objects that will fail to extend?
List,
There was a question as to how to identify objects that will
fail to extend?
This is what we do.
SELECT owner, tablespace_name, segment_name, next_extent
FROM dba_segments ds
WHERE tablespace_name != 'TEMP'
AND next_extent ( SELECT max(bytes)
FROM dba_free_space
WHERE tablespace_name=ds.tablespace_name)
ORDER BY 1, 2;
-Original Message-
Sent: Thursday, November 14, 2002 4:54 PM
To: Multiple recipients of list ORACLE-L
Hi all,
Until a whole mass of astrological confluences happen, I'm stuck with
dictionary-managed tablespaces on 8.1.7 on HP/UX 11.0. And
we're having
some space/growth issues right now that I want (need!) to be more
proactive
with. So, based on several factors -- most political -- I
want to run a
daily report that tells me when a segment will not be able to extend
twice.
(We're already running the single extent failure hourly.)
After looking on the net, I found some queries to do this,
but all I saw
were severely flawed. So, I rolled my own. The only problem
I can see
with
it for dictionary TSs is when the RANK() has multiple matches
for first
and
second (e.g. TS MY_BIG_TS has it's largest contiguous free spaces of
40M,
10M, and 10M). Unfortunately, I'm stumped as to how to prevent this.
Anyone care to comment on this load of SQueaL? Thx! :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International,
Sussex, WI
USA
SELECT ds.owner, ds.segment_name, ds.segment_type, ds.tablespace_name,
ds.next_extent/1024 Next ext, fs2.max_free/1024 Max Free,
fs2.min_free/1024 2nd Max Free, fs2.free_spaces
FROM dba_segments ds,
(
SELECT tablespace_name, MAX(bytes) max_free, MIN(bytes)
min_free,
count(*) free_spaces
FROM
(
SELECT tablespace_name, bytes,
RANK() OVER (PARTITION BY tablespace_name
ORDER BY tablespace_name, bytes DESC)
byte_rank
FROM dba_free_space
)
WHERE byte_rank 3
GROUP BY tablespace_name
) fs2
WHERE ds.segment_type IN ('INDEX','TABLE')
AND fs2.tablespace_name = ds.tablespace_name
AND (
((ds.next_extent fs2.min_free OR fs2.free_spaces 2)
AND ds.next_extent*2 fs2.max_free)
OR ds.next_extent fs2.max_free
)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
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: surprising result:8CPU Sun 3500 VS 4CPU Dell 6650
-Original Message- And talking about that excellent High Avaliable feature like CPU/Memory corruption and the server still run Maybe I should clarify. If you lose a memory module, the box will almost certainly reboot itself and come back up with the memory module taken offline. You can't suddenly have a chunk of memory disappear from the OS and the OS continue as if nothing happened. I think the same thing is true for sudden CPU failure. What I have actually seen, with my own eyes, was a situation where a Sparc 4000 was put under a table and between two other computers where the flow of air through the 4000 was blocked. The box had 6 CPU's, as it began to overheat, it shutdown 4 of the CPU's. One could run the Symon tool on a remote box and see the CPU's in the color red on the pictures of the system boards. On the low end of servers, the mainline Unix vendors (such as Sun) have chosen not to jump into the middle of the vicious competition there. But as you move to larger servers, I think you see that the boxes from vendors such as Sun become more and more competitve as the server size increases. So, no, my first choice for a 2-CPU box would probably not be a Sun. I haven't checked prices for a few months now, but the last time I checked, an 8-CPU Sun was fully competitive in pricing with an equivalent 8-Xeon Dell. Of course, pricing changes constantly so my info could be out of date now. And, when I checked pricing on the web sites, I was assuming that you could still count on a 20% discount off list price from a Sun reseller, since that was always the case in the past. Also, in the past, if you spent $1M in a year, you could buy directly from Sun for a 30% discount. I don't know if those discounts still work that way today. One thing is for sure: There is certainly a lot to consider! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee 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: How to set client's characterset on session level?
Alex, Humm, it would appear that you are right. After looking through the manuals and MetaLink it appears that the only why you can change the charecterset is at the client by updating the nls_lang environment (or registry if windoes) setting. Seems that characterset is specifically for how the client displays things. Damn nasty. Dick Goulet Reply Separator Author: Alexandre Gorbatchev [EMAIL PROTECTED] Date: 11/15/2002 4:44 PM Dick, I have already thoroughly RTFMed on this issue, but didn't find anything suitable using ALTER SESSION SET NLS_* Please, correct me if I'm wrong. NLS_LAN consists of 3 parts: language, territory and characterset. Language and territory can be set/changed at session level as parameters NLS_LANGUAGE and NLS_TERRITORY respectively. But there is no alternative (at least I didn't find one) for characterset. I can ALTER SESSION SET NLS_LANGUAGE=AMERICAN. However, this does not change character encoding scheme. Thanks, Alex -Original Message- From: [EMAIL PROTECTED] [mailto:dgoulet;vicr.com] Sent: Friday, November 15, 2002 4:06 PM To: Alexandre Gorbatchev; Multiple recipients of list ORACLE-L Subject: Re:How to set client's characterset on session level? See alter session set nls_language in the SQL Reference manual. Reply Separator Subject:How to set client's characterset on session level? Author: Alexandre Gorbatchev [EMAIL PROTECTED] Date: 11/15/2002 2:58 AM Hello guys, I need to use two (or more) different client applications from the same client workstation. Applications use different charactersets WE8PC858 and WE8ISO8859P1. That's a sort of having different NLS_LANG for each session. Maybe I can create a specific connect description in listener? Or I can put something in AFTER LOGIN trigger? Or put some parameters when establishing connection? I can change NLS_TERRITORY but not characterset. I am a little bit confused because cannot resolve this rather simple (I thought) case. Any advice? TIA, Alex -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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[2]: RE: RE: dumping microsoft desktop?
Ray, Sometimes humor is the best medecine when one feels powerless. I for one would love to dump this M$ desktop infavor of a Lindows or Linux desktop. But we all have to suffer with the lack of applications that support those platforms. Hopefully in a couple of years, now that Lindows is actually releasing a product, that may change I'll love it. Dick Goulet Reply Separator Author: Ray Stell [EMAIL PROTECTED] Date: 11/15/2002 8:59 AM On Fri, Nov 15, 2002 at 05:59:06AM -0800, [EMAIL PROTECTED] wrote: OH, talk about cruel and unusual punishment!! For the fish that is. It is interesting that so many people feel this way and yet the topic is reduced to joking, as if it is not possible (or really desirable) to replace technology. MVS systems programmers couldn't envision life without an IBM mainframe a few years ago. I asked the desktop question because we are exploring this path due to huge budget issues and the million dollar invoice to M$ is due. I wanted to see if the Oracle world had anything going on the topic. Guess not. Star and Open Office advances seem to be milestones in this arena. I believe we will move some % of our admin desktops to linux this year as a pilot. Departments can't afford the price of upgrading their office software, they will try the open versions to see what happens. Interesting bullets: The EU is studying the conversion of member goverment desktops: www.globetechnology.com/servlet/ArticleNews/einsider/RTGAM/20021104/gtopenms/ein sider/ The European Union awarded on Thursday a $249,000 (U.S.) contract to U.K.-based system-integrator Netproject to study the feasibility of moving the information systems of several member countries' governments to the Linux operating system from Microsoft's Windows OS. www.netproject.co.uk/opendesktop.html The USA National Security Agency's white paper 'The Inevitability of Failure: The Flawed Assumption of Security in Modern Computing Environments' should be read by all who are concernd with achieving secure systems that enable e-business. This is at www.nsa.gov/selinux/inevit-abs.html. ...waiting for the 9.2.0.2 patch to finish on my Mandrake 9 desktop (see, it's not off topic! ;) Dick Goulet Reply Separator Author: Gogala; Mladen [EMAIL PROTECTED] Date: 11/14/2002 1:38 PM Well, once upon a time there was an event called Boston Tea Party which dealt with too expensive product of low quality delivered by a monopoly. I wonder whether we can expect Seattle Windows Party? Would that be too cruel to the fish in Seattle harbor? -Original Message- From: [EMAIL PROTECTED] [mailto:dgoulet;vicr.com] Sent: Thursday, November 14, 2002 3:44 PM To: Multiple recipients of list ORACLE-L Subject: Re:RE: dumping microsoft desktop? David, Just like beauty, winning or loosing in a lawsuit is in the eye of the beholder. Actually in MicroSlop's case it was the justice department that bailed and more than likely King George who sat on the judge. You got to love those political action committees and their BIG donors!! In politics money talks louder than anything else. Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 11/14/2002 12:15 PM Both answers a are expected to be No. The lawsuit is expected to be dropped. But who knows. They won antitrust case after all. Nick I know it *seems* like they won, but Microsoft actually lost the antitrust case. :-( Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- 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
RE: Data specs of columns
Take a look at dba_cons_columns for primary and foreign key constraints Jay Miller -Original Message- Sent: Friday, November 15, 2002 11:59 AM To: Multiple recipients of list ORACLE-L I have a request from a developer to determine if a column is computed, has a primary key has a foreign key. Im looking through the dictionary tables eg user_tab_columns but these firlds seem to be elusive. Any ideas what views to querry ? Many thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Data specs of columns
Bob, Take a look at tables like user_constraints and user_cons_columns - the constraint_type column would definitely tell you whether it is a primary or foreign key, I am not sure about computed. HTH Sumathy -Original Message- Sent: Friday, November 15, 2002 11:59 AM To: Multiple recipients of list ORACLE-L I have a request from a developer to determine if a column is computed, has a primary key has a foreign key. Im looking through the dictionary tables eg user_tab_columns but these firlds seem to be elusive. Any ideas what views to querry ? Many thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Panicker, Thankam S. 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: Invalid Objects but no errors
In my experience, show errors is unreliable. Select from dba/all/user_errors instead. --- Eric Richmond [EMAIL PROTECTED] wrote: I checked for invalid objects and two of them came up. When I tried to recompile them it says there was compilation errors. Yet show errors doesn't show anything. Does anyone have familiarity with this particular package or have an idea on how to fix this so it is valid? SYSDBMS_REPCAT_RGTPACKAGE BODY SVRMGR alter package dbms_repcat_rgt compile body; MGR-00072: Warning: PACKAGE DBMS_REPCAT_RGT created with compilation errors. SVRMGR show errors No errors for PACKAGE DBMS_REPCAT_RGT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon 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!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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: Invalid Objects but no errors
try SHOW ERRORS PACKAGE BODY DBMS_REPCAT_RGT show errors may be showing there are no errors in the package spec! Kevin -Original Message- Sent: Friday, November 15, 2002 12:40 PM To: Multiple recipients of list ORACLE-L I checked for invalid objects and two of them came up. When I tried to recompile them it says there was compilation errors. Yet show errors doesn't show anything. Does anyone have familiarity with this particular package or have an idea on how to fix this so it is valid? SYSDBMS_REPCAT_RGTPACKAGE BODY SVRMGR alter package dbms_repcat_rgt compile body; MGR-00072: Warning: PACKAGE DBMS_REPCAT_RGT created with compilation errors. SVRMGR show errors No errors for PACKAGE DBMS_REPCAT_RGT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: Data specs of columns
Look at DBA_CONS_COLUMNS Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 15, 2002 11:59 AM I have a request from a developer to determine if a column is computed, has a primary key has a foreign key. Im looking through the dictionary tables eg user_tab_columns but these firlds seem to be elusive. Any ideas what views to querry ? Many thanks bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman 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: surprising result:8CPU Sun 3500 VS 4CPU Dell 6650
Actually, I know that IBM has some boxes with redundant (RAIDed -- their term) memory, although I don't know which class of machine. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Stephen Lee [mailto:slee;dollar.com] Sent: Friday, November 15, 2002 11:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: RE: surprising result:8CPU Sun 3500 VS 4CPU Dell 6650 -Original Message- And talking about that excellent High Avaliable feature like CPU/Memory corruption and the server still run Maybe I should clarify. If you lose a memory module, the box will almost certainly reboot itself and come back up with the memory module taken offline. You can't suddenly have a chunk of memory disappear from the OS and the OS continue as if nothing happened. I think the same thing is true for sudden CPU failure. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: Invalid Objects but no errors
I had some issues on an import lately with replication packages. I have recently gone from 8.1.7.3 to .4 and some of the packages would not compile. The solution was to run repcatr.sql, repcat.sql and utlrp.sql. -Original Message- Sent: Friday, November 15, 2002 12:10 PM To: Multiple recipients of list ORACLE-L try SHOW ERRORS PACKAGE BODY DBMS_REPCAT_RGT show errors may be showing there are no errors in the package spec! Kevin -Original Message- Sent: Friday, November 15, 2002 12:40 PM To: Multiple recipients of list ORACLE-L I checked for invalid objects and two of them came up. When I tried to recompile them it says there was compilation errors. Yet show errors doesn't show anything. Does anyone have familiarity with this particular package or have an idea on how to fix this so it is valid? SYSDBMS_REPCAT_RGTPACKAGE BODY SVRMGR alter package dbms_repcat_rgt compile body; MGR-00072: Warning: PACKAGE DBMS_REPCAT_RGT created with compilation errors. SVRMGR show errors No errors for PACKAGE DBMS_REPCAT_RGT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: RE: CONSISTANT GETS
Hold the press. NOT IN better than NOT EXISTS? Is this theory or fact? If so is there any supporting evidence out there? This is the first I have heard of this. Thanks! -Original Message- Sent: Friday, November 15, 2002 11:35 AM To: Multiple recipients of list ORACLE-L Jerry, I suspect that the improvments are more likely due to your rewriting the WHERE clause rather than the use of NOT EXISTS. Especially if the database were 9i, where NOT IN actually seems get a better execution path than NOT EXISTS. That original WHERE clause is really a piece of work. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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).
Query elapsed time
Quick question, Does anyone know of a location in the V$ tables where the elapsed time of the current query is stored?? Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
RE: CONSISTANT GETS
Naveen,
A CU block is fetched as-is; whatever's in the block at the time of
the read is what comes back from the LIO. CU mode is used when read
consistency is not an issue; for example, when reading your own
(private) sort blocks. I think if you DELETE without a WHERE clause,
you'll see CU blocks (not sure).
Reading in CR mode includes potentially more codepath that can
reconstruct the block as of a specified point in history. It's how the
Oracle kernel does read consistency. The mechanism includes checking the
ITL in the header of the block being read to understand whether the
version of the block is appropriate for the SCN of the query requesting
the block. It goes kind of like this:
while SCN of block is newer than SCN of query {
clone (i.e., copy) the block in the buffer cache;
LIO the undo block referenced by the most recent ITL entry (i.e., LIO
a rollback segment in CR mode);
apply the undo to the block (resulting in a new, older SCN for the
block);
}
I've never known whether the maximum number of clone operations per CR
read is the number of undo applications, or maybe it's just 1. The
algorithm might actually be this:
if SCN of block is newer than SCN of query then clone the block;
while SCN of block is newer than SCN of query {
LIO the undo block referenced by the most recent ITL entry;
Apply the undo to the block;
}
Jonathan Lewis was once on the trail of figuring this out, but I'm not
sure he ever satisfied himself with an answer. Has anyone on the list
constructed a test to reveal the answer?
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic, Dec 9-11 Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas
-Original Message-
Nahata
Sent: Friday, November 15, 2002 6:48 AM
To: Multiple recipients of list ORACLE-L
Cary,
I read your wonderful article. What is the exact difference between CR
and
CU, blocks fetched in Consistent and Current mode?
Regards
Naveen
-Original Message-
Sent: Friday, November 15, 2002 2:54 PM
To: Multiple recipients of list ORACLE-L
It's the count of a certain type of fetch operations of blocks from the
database buffer cache. See Why you should focus on LIOs instead of
PIOs at www.hotsos.com/catalog for details.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic, Dec 9-11 Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas
-Original Message-
Alavi
Sent: Thursday, November 14, 2002 2:10 PM
To: Multiple recipients of list ORACLE-L
Sorry for asking such a obvious question, but CONSISTANT GETS means
calling
rows from Database
Thanks,
-Original Message-
Sent: Thursday, November 14, 2002 10:35 AM
To: Multiple recipients of list ORACLE-L
Hamid,
I'm sorry: Unless your SQL returns fewer than about 800,000 rows to the
calling application (or an aggregation of 800,000 rows), then the
statement we have done all the necessary tuning on all the SQL queries
is not yet true.
If your SQL does actually return about 800,000 rows, then it is time to
begin thinking about the mismatch between business processing
requirements and the logical structure of your data.
The answer to your problem is not in your instance parameters.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic, Dec 9-11 Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas
-Original Message-
Alavi
Sent: Thursday, November 14, 2002 11:50 AM
To: Multiple recipients of list ORACLE-L
Dear List,
I am monitoring a database, I findout there is a transaction which
runing
a long time and others are waiting for this transaction, this
transaction
have 8,000,000 consistant gets with only 1 Physical I/O.
My question is, what I have to do except the SQL tuning to make this
transaction faster, we have done all the necessary tuning on all the SQL
query's.
Here is a copy of ora.ini:
Oracle 8.1.7.4 on sun solaris 2.8
background_dump_dest = /oracle/admin/cmstst/bdump
compatible = 8.1.7.4
control_files = /cmsdb/cmstst/control02.ctl
control_files = /oralogs1/cmstst/control03.ctl
control_files = /oracle/oradata/cmstst/control01.ctl
core_dump_dest = /oracle/admin/cmstst/cdump
db_block_buffers = 1??? this need to
increase?
db_block_lru_latches = 4
db_block_size = 8192
db_file_multiblock_read_count = 16
db_name = cmstst
hash_area_size = 2048000??? need tuning
???
instance_name = cmstst
java_pool_size = 20971520
large_pool_size = 614400
log_archive_dest_1 = location=/archlogs/cmstst
log_archive_format = arch%s.arc
log_archive_start = TRUE
log_buffer = 262144 ?? this log
buffer
is enough??
RE: Slow Inserts
And a note: Using bind variables is a necessary step but not a sufficient step toward eliminating parse CALLS. Using bind variables will only reduce the number of misses in the library cache (parse count (hard)). If the application still puts the parse call inside a loop, it still gets the parse once per execute behavior shown in the trace file. When the application says parse (prepare or whatever), the Oracle kernel will parse (unless something on the client side stops the call from going to SQL*Net). See Scaling applications to massive user counts at www.hotsos.com/catalog. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- Baumgartel Sent: Friday, November 15, 2002 11:20 AM To: Multiple recipients of list ORACLE-L The Parse statistics are what caught my eye. The statement is being parsed once per execution, which is puzzling because it does use bind variables. Of more concern, though, is the difference between CPU and elapsed times for the parsing, indicating that there is a lot of waiting going on. I'd suggest looking at v$session_event to see what are the top wait events for the session. break on sid col event format a30 set pages 99 select se.event, se.total_waits, se.time_waited, se.average_wait from v$session s, v$session_event se where s.sid = se.sid and s.username is not null and s.sid = sid order by se.time_waited / Run this before and after executing the insert statement, and compute the delta for each wait event seen. HTH Paul Baumgartel --- [EMAIL PROTECTED] wrote: Oracle 8.1.7.0.0 on Win2k (4 CPU, 4GB RAM) A 3rd Party app. is experiencing very slow performance on one of our databases. I think I've nailed it down to slow, row-at-a-time inserts. The same app. performs very fast on another DB with LMTs. After switching the tables and indexes in the slow DB to LMT, we still have slow performance. The extract from the SQL_Trace below is the slow statement. It actually takes about an hour to insert a few hundred rows. You can watch the trace file slowly grow with executions of this statement. There is only one User hitting this table (with its single index). The table is initially empty, so it's not extending. Anybody have any ideas as to the cause of this slow Insert activity? BTW, I ran BStat and EStat during this time and nothing jumps out at me. Also, we ran it with CHOOSE (and fresh statistics) - same slowness. INSERT INTO SASI.W_ENROLL (SCHOOLNUMBER ,STULINK ,FROMDATE , TODATE ,GRADE ,TRACK ,PEIMSTRACK ,PEIMSSCHOOLNUMBER , ADAELIGIBILITYCODE ,ISENTERDATE ) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2735 5.88 30.00 0 0 0 0 Execute 2735 1.16 1.24 3 2779 8571 2735 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 5470 7.04 31.24 3 2779 8571 2735 Misses in library cache during parse: 0 Optimizer goal: RULE Parsing user id: 399 (TXSRC) Rows Execution Plan --- --- 0 INSERT STATEMENT GOAL: RULE TIA. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City
RE: CONSISTANT GETS
Title: RE: CONSISTANT GETS Jared, I'm still on 7.3.4 but I'm sure that you are right about the WHERE clause in this case. It went from an INDEX FULL SCAN to an INDEX UNIQUE SCAN on the same index once the blasted concatenations were removed. One programmer says he likes to write it that way because it's simpler and nicer looking! Still I've seen some nice speed gains when just converting some WHERE statements from NOT IN to NOT EXISTS. YMMV Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Jerry, I suspect that the improvments are more likely due to your rewriting the WHERE clause rather than the use of NOT EXISTS. Especially if the database were 9i, where NOT IN actually seems get a better execution path than NOT EXISTS. That original WHERE clause is really a piece of work. Jared Whittle Jerome Contr NCI [EMAIL PROTECTED] I've seen worse. My programmers don't know how to use NOT EXISTS even though I've explained it many times. And that's the least of my problems. Look at this mess: SELECT * FROM sar.pax_header_suspense_err_temp WHERE manifest_type || manifesting_station || fiscal_year || manifest_serial_number NOT IN ( SELECT manifest_type || manifesting_station || fiscal_year || manifest_serial_number FROM manifest_serial_number_history) Takes over an hour to run. I rewrote it as such: SELECT * FROM sar.pax_header_suspense_err_temp t WHERE NOT EXISTS (SELECT 'X' FROM manifest_serial_number_history h WHERE t.manifest_type = h.manifest_type and t.manifesting_station = h.manifesting_station and t.fiscal_year = h.fiscal_year and t.manifest_serial_number = h.manifest_serial_number ) Under a second. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145
Re: Invalid Objects but no errors
Is something dropped from that database relating to that package body? Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Fri, 15 Nov 2002 09:40:22 -0800 I checked for invalid objects and two of them came up. When I tried to recompile them it says there was compilation errors. Yet show errors doesn't show anything. Does anyone have familiarity with this particular package or have an idea on how to fix this so it is valid? SYSDBMS_REPCAT_RGTPACKAGE BODY SVRMGR alter package dbms_repcat_rgt compile body; MGR-00072: Warning: PACKAGE DBMS_REPCAT_RGT created with compilation errors. SVRMGR show errors No errors for PACKAGE DBMS_REPCAT_RGT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon 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). _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: M Rafiq 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).
catrepad - how tell if run
How can you tell if catrepad.sql has already been run on a database? I am sure that catrep has already been run, but not catrepad. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon 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: How to identify objects that will fail to extend?
If PCT_INCREASE is set to 0, then can't we simply compare
next_extent*2 ( sub-query )?
-Original Message-
Sent: Friday, November 15, 2002 12:40 PM
To: Multiple recipients of list ORACLE-L
Thanks, but the next extent is the easy one. As I mentioned, I'm already
running a similar query hourly.
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
From: [EMAIL PROTECTED] [mailto:Govind.Arumugam;alltel.com]
Sent: Friday, November 15, 2002 10:14 AM
To: Multiple recipients of list ORACLE-L
Subject: How to identify objects that will fail to extend?
List,
There was a question as to how to identify objects that will
fail to extend?
This is what we do.
SELECT owner, tablespace_name, segment_name, next_extent
FROM dba_segments ds
WHERE tablespace_name != 'TEMP'
AND next_extent ( SELECT max(bytes)
FROM dba_free_space
WHERE tablespace_name=ds.tablespace_name)
ORDER BY 1, 2;
-Original Message-
Sent: Thursday, November 14, 2002 4:54 PM
To: Multiple recipients of list ORACLE-L
Hi all,
Until a whole mass of astrological confluences happen, I'm stuck with
dictionary-managed tablespaces on 8.1.7 on HP/UX 11.0. And
we're having
some space/growth issues right now that I want (need!) to be more
proactive
with. So, based on several factors -- most political -- I
want to run a
daily report that tells me when a segment will not be able to extend
twice.
(We're already running the single extent failure hourly.)
After looking on the net, I found some queries to do this,
but all I saw
were severely flawed. So, I rolled my own. The only problem
I can see
with
it for dictionary TSs is when the RANK() has multiple matches
for first
and
second (e.g. TS MY_BIG_TS has it's largest contiguous free spaces of
40M,
10M, and 10M). Unfortunately, I'm stumped as to how to prevent this.
Anyone care to comment on this load of SQueaL? Thx! :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International,
Sussex, WI
USA
SELECT ds.owner, ds.segment_name, ds.segment_type, ds.tablespace_name,
ds.next_extent/1024 Next ext, fs2.max_free/1024 Max Free,
fs2.min_free/1024 2nd Max Free, fs2.free_spaces
FROM dba_segments ds,
(
SELECT tablespace_name, MAX(bytes) max_free, MIN(bytes)
min_free,
count(*) free_spaces
FROM
(
SELECT tablespace_name, bytes,
RANK() OVER (PARTITION BY tablespace_name
ORDER BY tablespace_name, bytes DESC)
byte_rank
FROM dba_free_space
)
WHERE byte_rank 3
GROUP BY tablespace_name
) fs2
WHERE ds.segment_type IN ('INDEX','TABLE')
AND fs2.tablespace_name = ds.tablespace_name
AND (
((ds.next_extent fs2.min_free OR fs2.free_spaces 2)
AND ds.next_extent*2 fs2.max_free)
OR ds.next_extent fs2.max_free
)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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: Data specs of columns
Look at DBA_CONS_COLUMNS Igor Neyman, OCP DBA [EMAIL PROTECTED] I see, thank you select uc.constraint_type, uc.constraint_name, ucc.column_name, uc.search_condition from user_constraints uc, user_cons_columns ucc where uc.owner = 'BOB' and uc.constraint_name = ucc.constraint_name and uc.table_name = 'ANAME' ; # I'm guessing the computed is not available?? select table_name, comments from dict where comments like '%comp%' ; select column_name from user_col_comments where column_name like '%COMPU%' ; Thanks again bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky 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).
Replication - snaphot/asynchronous
I read that any given object can be in only one replication group. We already have SNAPSHOT replication running from Server A to Server B. Now we want to set up ASYNCHRONOUS replication to run from Server A to Server C. How can that be set up so there isn't a conflict if the objects can only be in one replication group? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon 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: CONSISTANT GETS
Whittle Jerome Contr NCI wrote: Jared, I'm still on 7.3.4 but I'm sure that you are right about the WHERE clause in this case. It went from an INDEX FULL SCAN to an INDEX UNIQUE SCAN on the same index once the blasted concatenations were removed. One programmer says he likes to write it that way because it's simpler and nicer looking! Still I've seen some nice speed gains when just converting some WHERE statements from NOT IN to NOT EXISTS. YMMV Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 Jerry, The efficiency of NOT IN vs NOT EXISTS depends on the other criteria. I guess that in your case, where there is no other criteria, the table where you were fetching was not enormous. Basically this is an existence test. If you have a very selective criterion besides and the existence test is just an additional check, NOT EXISTS is what should be used. However, if this is the only criterion, the query is correlated - it means that it must be evaluated for each row. On a very large table it may be extremely costly. I don't remember if HASH_AJ and MERGE_AJ are available hints with 7.3. If they are, just try to rewrite the query where (a, b, c, d) not in (select /*+ HASH_AJ */ e, f, g, h from ...) and then try again with MERGE_AJ (with set autotrace turned on). Check the number of logical reads, you may be surprised (or maybe not :-)). The hint is required with 8.x, but Oracle9 is smarter - hence Jared's remark. Personally, I am pretty fond of an outer join with and additional IS NULL condition when I can, it follows more or less the same plan without any need for an hint. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: CONSISTANT GETS
Jerry, I'll take better performing over nicer looking anyday. Dick Goulet Reply Separator Author: Whittle Jerome Contr NCI [EMAIL PROTECTED] Date: 11/15/2002 11:34 AM Jared, I'm still on 7.3.4 but I'm sure that you are right about the WHERE clause in this case. It went from an INDEX FULL SCAN to an INDEX UNIQUE SCAN on the same index once the blasted concatenations were removed. One programmer says he likes to write it that way because it's simpler and nicer looking! Still I've seen some nice speed gains when just converting some WHERE statements from NOT IN to NOT EXISTS. YMMV Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Jerry, I suspect that the improvments are more likely due to your rewriting the WHERE clause rather than the use of NOT EXISTS. Especially if the database were 9i, where NOT IN actually seems get a better execution path than NOT EXISTS. That original WHERE clause is really a piece of work. Jared Whittle Jerome Contr NCI [EMAIL PROTECTED] I've seen worse. My programmers don't know how to use NOT EXISTS even though I've explained it many times. And that's the least of my problems. Look at this mess: SELECT * FROM sar.pax_header_suspense_err_temp WHEREmanifest_type || manifesting_station || fiscal_year || manifest_serial_number NOT IN ( SELECTmanifest_type || manifesting_station || fiscal_year || manifest_serial_number FROM manifest_serial_number_history) Takes over an hour to run. I rewrote it as such: SELECT * FROM sar.pax_header_suspense_err_temp t WHERE NOT EXISTS (SELECT 'X' FROM manifest_serial_number_history h WHERE t.manifest_type = h.manifest_type and t.manifesting_station = h.manifesting_station and t.fiscal_year = h.fiscal_year and t.manifest_serial_number = h.manifest_serial_number ) Under a second. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD TITLERE: CONSISTANT GETS/TITLE /HEAD BODY !-- Converted from text/rtf format -- PSPAN LANG=en-usFONT COLOR=#FF FACE=ArialJared,/FONT/SPAN /P PSPAN LANG=en-usFONT COLOR=#FF FACE=ArialI'm still on 7.3.4 but I'm sure that you are right about the WHERE clause in this case. It went from an INDEX FULL SCAN to an INDEX UNIQUE SCAN on the same index once the blasted concatenations were removed. One programmer says he likes to write it that way because it's simpler and nicer looking!/FONT/SPAN/P PSPAN LANG=en-usFONT COLOR=#FF FACE=ArialStill I've seen some nice speed gains when just converting some WHERE statements from NOT IN to NOT EXISTS./FONT/SPAN /P PSPAN LANG=en-usFONT COLOR=#FF FACE=ArialYMMV/FONT/SPAN /P PSPAN LANG=en-usFONT FACE=ArialJerry Whittle/FONT/SPAN BRSPAN LANG=en-usFONT FACE=ArialACIFICS DBA/FONT/SPAN BRSPAN LANG=en-usFONT FACE=ArialNCI Information Systems Inc./FONT/SPAN BRSPAN LANG=en-usFONT FACE=Arial[EMAIL PROTECTED]/FONT/SPAN BRSPAN LANG=en-usFONT FACE=Arial618-622-4145/FONT/SPAN /P UL PSPAN LANG=en-usFONT SIZE=1 FACE=Arial-Original Message-/FONT/SPAN BRSPAN LANG=en-usBFONT SIZE=1 FACE=ArialFrom:nbsp;nbsp;/FONT/B FONT SIZE=1 FACE=Arial[EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]/FONT/SPAN /P PSPAN LANG=en-usFONT SIZE=2 FACE=ArialJerry,/FONT/SPAN /P PSPAN LANG=en-usFONT SIZE=2 FACE=ArialI suspect that the improvments are more likely due to your/FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=Arialrewriting the WHERE clause rather than the use of NOT EXISTS./FONT/SPAN /P PSPAN LANG=en-usFONT SIZE=2 FACE=ArialEspecially if the database were 9i, where NOT IN actually/FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=Arialseems get a better execution path than NOT EXISTS./FONT/SPAN /P PSPAN LANG=en-usFONT SIZE=2 FACE=ArialThat original WHERE clause is really a piece of work./FONT/SPAN /P PSPAN LANG=en-usFONT SIZE=2 FACE=ArialJared/FONT/SPAN /P PSPAN LANG=en-usFONT SIZE=2 FACE=Arialquot;Whittle Jerome Contr NCIquot; lt;[EMAIL PROTECTED]gt;/FONT/SPAN /P PSPAN LANG=en-usFONT SIZE=2 FACE=ArialI've seen worse. My programmers don't know how to use NOT EXISTS even /FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=Arialthough I've explained it many times. And that's the least of my problems. /FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=ArialLook at this mess:/FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=Arialnbsp;nbsp; SELECT * /FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=Arialnbsp;nbsp;nbsp;nbsp; FROM sar.pax_header_suspense_err_temp /FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2 FACE=Arialnbsp;nbsp;nbsp; WHEREnbsp;nbsp;nbsp; manifest_type /FONT/SPAN BRSPAN LANG=en-usFONT SIZE=2
RE: catrepad - how tell if run
Why not open the script up find an object created with that script ... then log onto your database and see if the object is there. ? -Original Message- Sent: Friday, November 15, 2002 1:39 PM To: Multiple recipients of list ORACLE-L How can you tell if catrepad.sql has already been run on a database? I am sure that catrep has already been run, but not catrepad. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange 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).
Export from Previous Release
Hi all, My developers are using a 9iR2 client and need to run exports against a 9iR1 instance. Has anyone seen anything like this? I seem to remember a descussion at one point saying that it was possible to run the catexp from a later version in an earlier release and have this work. We would not need to use the 9iR2 exp/imp utilities, just the 9iR2 ones. I am in a Solaris8/Veritas Clustered environment, so I am trying to avoid having to install multiple clients (I need the 9iR2 client for access to OEM). TIA, John P Weatherman Database Administrator Replacements Ltd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman 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: Urgent : Shell Script is needed !!!!!
Re: Replication - snaphot/asynchronous
You can run asynchronos rep from server A to C, then run a snapshot to B off of C (have C be the master to B) Make sure you have conflict resolution set-up! --- Eric Richmond [EMAIL PROTECTED] wrote: I read that any given object can be in only one replication group. We already have SNAPSHOT replication running from Server A to Server B. Now we want to set up ASYNCHRONOUS replication to run from Server A to Server C. How can that be set up so there isn't a conflict if the objects can only be in one replication group? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric Richmon 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!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michael Barger 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: How to identify objects that will fail to extend?
Nope. If a segment has a NEXT EXTENT of 20M and the two largest contiguous
free spaces in it's TS are 30M and 15M, the second extent (i.e. two extends
to that segment) would fail, but would not show up in the query. That's
what spawned the complexity of my SQL.
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
From: [EMAIL PROTECTED] [mailto:Govind.Arumugam;alltel.com]
Sent: Friday, November 15, 2002 1:44 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: How to identify objects that will fail to extend?
If PCT_INCREASE is set to 0, then can't we simply compare
next_extent*2 ( sub-query )?
-Original Message-
Sent: Friday, November 15, 2002 12:40 PM
To: Multiple recipients of list ORACLE-L
Thanks, but the next extent is the easy one. As I mentioned,
I'm already
running a similar query hourly.
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International,
Sussex, WI USA
-Original Message-
From: [EMAIL PROTECTED] [mailto:Govind.Arumugam;alltel.com]
Sent: Friday, November 15, 2002 10:14 AM
To: Multiple recipients of list ORACLE-L
Subject: How to identify objects that will fail to extend?
List,
There was a question as to how to identify objects that will
fail to extend?
This is what we do.
SELECT owner, tablespace_name, segment_name, next_extent
FROM dba_segments ds
WHERE tablespace_name != 'TEMP'
AND next_extent ( SELECT max(bytes)
FROM dba_free_space
WHERE tablespace_name=ds.tablespace_name)
ORDER BY 1, 2;
-Original Message-
Sent: Thursday, November 14, 2002 4:54 PM
To: Multiple recipients of list ORACLE-L
Hi all,
Until a whole mass of astrological confluences happen, I'm
stuck with
dictionary-managed tablespaces on 8.1.7 on HP/UX 11.0. And
we're having
some space/growth issues right now that I want (need!) to be more
proactive
with. So, based on several factors -- most political -- I
want to run a
daily report that tells me when a segment will not be able to extend
twice.
(We're already running the single extent failure hourly.)
After looking on the net, I found some queries to do this,
but all I saw
were severely flawed. So, I rolled my own. The only problem
I can see
with
it for dictionary TSs is when the RANK() has multiple matches
for first
and
second (e.g. TS MY_BIG_TS has it's largest contiguous
free spaces of
40M,
10M, and 10M). Unfortunately, I'm stumped as to how to
prevent this.
Anyone care to comment on this load of SQueaL? Thx! :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International,
Sussex, WI
USA
SELECT ds.owner, ds.segment_name, ds.segment_type,
ds.tablespace_name,
ds.next_extent/1024 Next ext, fs2.max_free/1024 Max Free,
fs2.min_free/1024 2nd Max Free, fs2.free_spaces
FROM dba_segments ds,
(
SELECT tablespace_name, MAX(bytes) max_free, MIN(bytes)
min_free,
count(*) free_spaces
FROM
(
SELECT tablespace_name, bytes,
RANK() OVER (PARTITION BY tablespace_name
ORDER BY tablespace_name, bytes DESC)
byte_rank
FROM dba_free_space
)
WHERE byte_rank 3
GROUP BY tablespace_name
) fs2
WHERE ds.segment_type IN ('INDEX','TABLE')
AND fs2.tablespace_name = ds.tablespace_name
AND (
((ds.next_extent fs2.min_free OR fs2.free_spaces 2)
AND ds.next_extent*2 fs2.max_free)
OR ds.next_extent fs2.max_free
)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
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).
.NET and Oracle
Hi, I read somewhere that .NET application will only be supported with SQL Server. Is this true or it can be run with any other database like Oracle? Thanks --Harvinder
RE: Data specs of columns
This is what I ended up doing... select decode(constraint_type, 'C', 'Check Constraint', 'P', 'Primary Key', 'R', 'Foreign Key', 'U', 'Unique ', 'V', 'With Check Option for view', 'undefined') constraint_name, column_name, ucc.table_name from user_constraints, user_cons_columns ucc where ucc.constraint_name = user_constraints.constraint_name ; Look at DBA_CONS_COLUMNS Igor Neyman, OCP DBA [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky 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: why is this happening
Catching up on e-mails ...as usual :-( and I noticed no one had answered this question. The ORA-3232 is usually not related to tablespaces but to the relationship between the size of your temp tablespace INITIAL and NEXT extent in relation to sort_area_size (and hash joins). This hit me not too long ago when I had a really small temp extent size for a sandbox I was planning with. For me, I just created my sandbox temp space with larger initial extent. There are also Metalink Notes about changing the HASH_MULTIBLOCK_IO_COUNT to resolve this. When you sort_area_size=20M, all sorts were done in memory so you didn't trigger this condition. - Better late than never Babette -Original Message- George Sent: Thursday, November 07, 2002 10:59 AM To: Multiple recipients of list ORACLE-L The following query is causing the following error ERROR at line 1: ORA-03232: unable to allocate an extent of 22 blocks from tablespace 3 select count(l.processid) from tmslog l, tmslogtimeout t where l.processid = t.processid and l.statifiedflag='Y' and t.processcompleteflag='Y' Tablespace #3 is temp, 800 MB, 128K extent size locally managed. The user is also set to use temp. If I do a alter session set sort_area_size=20M then it completes. Currently the sort_area_size is set via the init file as 5 mb. Ideas ? George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- Sent: 07 November 2002 15:24 PM To: Multiple recipients of list ORACLE-L Thanks Kevin, good to hear from you. As usual you're Johnie on spot with TFM. It's interesting that this can be overcome with the inline view technique posted earlier by Raj. Steve -Original Message- Sent: Wednesday, November 06, 2002 5:23 AM To: Multiple recipients of list ORACLE-L Directly from TFM Notes on Hierarchical Queries: If you specify a hierarchical query and also specify the ORDER BY clause, the ORDER BY clause takes precedence over any ordering specified by the hierarchical query, unless you specify the SIBLINGS keyword in the ORDER BY clause. The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join: * If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing. * Oracle applies any non-join predicates (that is, all predicates if the WHERE clause does not contain a join) after doing the CONNECT BY processing without affecting the other rows of the hierarchy. -Original Message- Sent: Tuesday, November 05, 2002 3:29 PM To: Multiple recipients of list ORACLE-L I was also able to confirm this works on O9i. -Original Message- Sent: Tuesday, November 05, 2002 11:14 AM To: Multiple recipients of list ORACLE-L I get an error on 8.1.7.2. Is siblings new? SQL l 1 SELECT LEVEL, treenode.* 2FROM treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* ORDER SIBLINGS BY PARENTid , nodeorder SQL / ORDER SIBLINGS BY PARENTid , nodeorder * ERROR at line 5: ORA-00924: missing BY keyword -Original Message- Sent: Tuesday, November 05, 2002 11:02 AM To: '[EMAIL PROTECTED]'; Orr, Steve SELECT LEVEL, treenode.* FROM treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid ORDER SIBLINGS BY PARENTid , nodeorder Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item
Oracle on MVS able to submit JCL ??
Does anyone on this listserv using Oracle on a mainframe? I was asked today if I can use Oracle's extproc feature on the mainframe to submit JCL. I said that I doubted it because the OS structure is so much different than UNIX or NT, but I would look into it. Has anyone tried to somehow have Oracle PL/SQL trigger or submit JCL?? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babette Turner-Underwood 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).
New development in Cobol or PL/SQL - please help
I just found out today that we have a major development initiative that is starting and they are planning on using Pro*Cobol to develop the application. (my head is still shaking in disbelief!!!) So we will have a Java front-end, invoking MQ series that will go across to the mainframe for MQ series to invoke Pro*Cobol programs that will then do the processing (accessing data and doing calculations) and then return data. If anyone has been in this or a similar situation, please help. I need some really good arguments as to why we should put the business logic into PL/SQL instead of Pro*Cobol. I understand the reason we are using Oracle is that the director has 15 years experience with it and loves it. Aaargh!!! thanks Babette -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babette Turner-Underwood 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: Call for a featured speaker for St. Louis Oracle Users Gr
We have done something similar at our Ottawa Oracle User group (for our DBA / Developer Day). Over the past few years we have had many excellent speakers come and present. However, in thinking about them, I doubt they would have felt comfortable in responding to this kind of e-mail. (perhaps modesty) We found that by attending IOUG Live conference and then asking people we had seen present to be a very effective method for getting speakers. I can't recall anyone ever turning us down when approached. If you did not get the response you were expecting frmo the e-mail, perhaps give my approach a try. (or contact me offline for a list of my personal favourites ). Babette -Original Message- Sent: Wednesday, November 13, 2002 1:25 PM To: Multiple recipients of list ORACLE-L You're right...I should have modified my list to include just good speakers as well. Jim [EMAIL PROTECTED] wrote: Jim, Come on, give some of the new folks a shot at it! We'll run out of presenters otherwise. Dick Goulet NOUG past presenter Reply Separator Subject:Call for a featured speaker for St. Louis Oracle Users Group Author: [EMAIL PROTECTED] (Jim Hawkins) Date: 11/13/2002 8:10 AM Hello all, We are currently looking for someone to feature at our St. Louis Oracle Users Group meeting on Thursday, December 12th from Noon to 5PM. We would, of course, pick up all travel expenses, and this can easily be just a day-trip itenerary. Is anyone on this list available and would like to work something out? Obviously, we are looking for the authors, Guru's, IOUG presenters, other User Group presenters, etc. Thanks, Jim -- â-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ- ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ -ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ª Jim Hawkins OracleAc / MS SQL*ServerAc Database Administration St. Louis, MO A 63167 â-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ- ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ -ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ªâ-ª __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jim Hawkins 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). -- Jim Hawkins Oracle© / MS SQL*Server© Database Administration St. Louis, MO 63167 __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jim Hawkins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babette Turner-Underwood 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).
Too many db calls
A while back someone mentioned that the two main causes of slow SQL are excesive LIO's and excesscive database calls, which show up as excessive CPU use and excessive network traffic, respectively. Regarding the database calls, is there a classic reason for this problem? My best guess is it's caused by an SQL statement in a PL/SQL loop, which could be rewritten as a single SQL statement. But is this the single, commonly seen cause for this problem, or are there other common ways this inefficiency is introduced? Thanks in advance for help in understanding this. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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: RE: CONSISTANT GETS
Hold the press. NOT IN better than NOT EXISTS? If so is there any supporting evidence out there? I think you're joking, but if not there's a nice comparison chart of several tests in Harrison, p. 268. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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: How to identify objects that will fail to extend?
Okay. Let me try this!
The largest column will have the biggest extent size that the tablespace can
accommodate next time. You might save this information in a temp. table and have the
other query to check against this.
select substr(a.tablespace_name,1,20) tablespace,
round(sum(a.total1)/1024/1024, 1) Total,
round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1) used,
round(sum(a.sum1)/1024/1024, 1) free,
round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1) pct_free,
round(sum(a.maxb)/1024/1024, 1) largest,
max(a.cnt) fragments
from
(select tablespace_name, 0 total1, sum(bytes) sum1,
max(bytes) MAXB,
count(bytes) cnt
from dba_free_space
group by tablespace_name
union
select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files
group by tablespace_name) a
group by a.tablespace_name
-Original Message-
Sent: Friday, November 15, 2002 4:54 PM
To: Multiple recipients of list ORACLE-L
Nope. If a segment has a NEXT EXTENT of 20M and the two largest contiguous
free spaces in it's TS are 30M and 15M, the second extent (i.e. two extends
to that segment) would fail, but would not show up in the query. That's
what spawned the complexity of my SQL.
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
From: [EMAIL PROTECTED] [mailto:Govind.Arumugam;alltel.com]
Sent: Friday, November 15, 2002 1:44 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: How to identify objects that will fail to extend?
If PCT_INCREASE is set to 0, then can't we simply compare
next_extent*2 ( sub-query )?
-Original Message-
Sent: Friday, November 15, 2002 12:40 PM
To: Multiple recipients of list ORACLE-L
Thanks, but the next extent is the easy one. As I mentioned,
I'm already
running a similar query hourly.
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International,
Sussex, WI USA
-Original Message-
From: [EMAIL PROTECTED] [mailto:Govind.Arumugam;alltel.com]
Sent: Friday, November 15, 2002 10:14 AM
To: Multiple recipients of list ORACLE-L
Subject: How to identify objects that will fail to extend?
List,
There was a question as to how to identify objects that will
fail to extend?
This is what we do.
SELECT owner, tablespace_name, segment_name, next_extent
FROM dba_segments ds
WHERE tablespace_name != 'TEMP'
AND next_extent ( SELECT max(bytes)
FROM dba_free_space
WHERE tablespace_name=ds.tablespace_name)
ORDER BY 1, 2;
-Original Message-
Sent: Thursday, November 14, 2002 4:54 PM
To: Multiple recipients of list ORACLE-L
Hi all,
Until a whole mass of astrological confluences happen, I'm
stuck with
dictionary-managed tablespaces on 8.1.7 on HP/UX 11.0. And
we're having
some space/growth issues right now that I want (need!) to be more
proactive
with. So, based on several factors -- most political -- I
want to run a
daily report that tells me when a segment will not be able to extend
twice.
(We're already running the single extent failure hourly.)
After looking on the net, I found some queries to do this,
but all I saw
were severely flawed. So, I rolled my own. The only problem
I can see
with
it for dictionary TSs is when the RANK() has multiple matches
for first
and
second (e.g. TS MY_BIG_TS has it's largest contiguous
free spaces of
40M,
10M, and 10M). Unfortunately, I'm stumped as to how to
prevent this.
Anyone care to comment on this load of SQueaL? Thx! :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International,
Sussex, WI
USA
SELECT ds.owner, ds.segment_name, ds.segment_type,
ds.tablespace_name,
ds.next_extent/1024 Next ext, fs2.max_free/1024 Max Free,
fs2.min_free/1024 2nd Max Free, fs2.free_spaces
FROM dba_segments ds,
(
SELECT tablespace_name, MAX(bytes) max_free, MIN(bytes)
min_free,
count(*) free_spaces
FROM
(
SELECT tablespace_name, bytes,
RANK() OVER (PARTITION BY tablespace_name
ORDER BY tablespace_name, bytes DESC)
byte_rank
FROM dba_free_space
)
WHERE byte_rank 3
GROUP BY tablespace_name
) fs2
WHERE ds.segment_type IN ('INDEX','TABLE')
AND fs2.tablespace_name = ds.tablespace_name
AND (
((ds.next_extent fs2.min_free OR fs2.free_spaces 2)
AND ds.next_extent*2 fs2.max_free)
OR ds.next_extent fs2.max_free
)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services--
Event 10046 and Performance
Title: Event 10046 and Performance
Okay ... here is a curved ball ...
This AM an application support person called and mentioned that we have a form that has become slow compared to 2 days ago. So we saw the slow behavior and then I did a 10046 trace on it.
After analysis, found that one query is taking about 90% of total time and isolated it to be worked on.
On our day-old instance (refreshed daily from prod), this form runs very fast. After comparing everything, I found that for some test we have db_file_multiblock_read_count = 4 whereas it is 32 (don't ask) on production.
So after changing this variable in my session to 4 (on production), I checked the explain plan and it seems to be okay, picking up the right indexes etc.
So, I asked the support person to add following line in pre-form trigger on the form and run a test
forms_ddl('alter session set db_file_multiblock_read_count=4');
ran the form, no change in the performance. So I requested following like to be addes as well
forms_ddl('alter session set events ''10046 trace name context forever, level 8''');
The form runs as fast as it can.
So we thought maybe flushing the shared pool might help, so we flushed the shared pool (again don't ask, this is prod env, lunch time, light load).
Still same, with 10046 event set, the form runs as fast as it can, but if I take it out, it slows down.
I am confused ... does anyone have a plausible explanation on why this is happening?
Oracle 9201, AIX 5l
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*2
discrepancy between capacity planner and statspack
Hi, Has anybody experience the discrepancy between capacity planner and statspack? Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
RE: .NET and Oracle
Harvinder - My experience is that Microsoft will try to imply that, just like an Oracle sales rep will try to imply that Oracle works better with Oracle9i AS. But the fact is that if Microsoft enforced that rule, it would be excluded from a lot of big accounts that have a lot more committed to Oracle than they do to .NET. However, my experience is that the Microsoft data interfaces like ADO frequently have poor performance. Since Microsoft and Oracle aren't real chummy, there are few documents to help you out. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] mailto:dwilliams;lifetouch.com -Original Message- Sent: Friday, November 15, 2002 3:59 PM To: Multiple recipients of list ORACLE-L Hi, I read somewhere that .NET application will only be supported with SQL Server. Is this true or it can be run with any other database like Oracle? Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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: RE: CONSISTANT GETS
Ethan, A NOT EXISTS can be very expensive if returning large numbers of rows from the driving table. It's a correlated subquery as Stephane F. pointed out in another post. Jared Post, Ethan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/15/2002 10:32 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: RE: CONSISTANT GETS Hold the press. NOT IN better than NOT EXISTS? Is this theory or fact? If so is there any supporting evidence out there? This is the first I have heard of this. Thanks! -Original Message- Sent: Friday, November 15, 2002 11:35 AM To: Multiple recipients of list ORACLE-L Jerry, I suspect that the improvments are more likely due to your rewriting the WHERE clause rather than the use of NOT EXISTS. Especially if the database were 9i, where NOT IN actually seems get a better execution path than NOT EXISTS. That original WHERE clause is really a piece of work. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: kproc processes owned by Oracle on AIX
Are you using AIO? Then these may be AIX processes that are started by the kernel (kproc - kernel procs) on behalf of 'oracle'. Are you sure that they are owned by oracle. If so, can you shut the server down? John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: John Dunn [mailto:john.dunn;sefas.co.uk] Sent: Friday, November 15, 2002 6:04 AM To: Multiple recipients of list ORACLE-L Subject: FW: kproc processes owned by Oracle on AIX All, I have shutdown my database(but not the listener). I notice that I have a number of kproc processes owned by oracle. Why? John Dunn Sefas Innovation Ltd 0117 9154267 www.sefas.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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).
Oracle World was great
Arrived on the last day, in time to see (free): -- Ellison's talk -- The Quest booth and a good demo of their products -- Oak Table (I guess it was oak) and meet Mogens N., who was basically a nice guy, but did play a little trick when he pointed out the elaborate high-tech security system that surrounded their booth ... which turned out to be a piece of string. :-) For people that attended the entire event, does anyone have a favorite paper that was presented? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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: Too many db calls
Greg, I believe that the cultural root cause of the excessive LIO problem is the conception that physical I/O is what makes databases slow. Disk I/O certainly *can* make a system slow, but in about 598 of 600 cases we've seen in the past three years, it hasn't. [Why you should focus on LIOs instead of PIOs at www.hotsos.com/catalog] The fixation on PIO of course focuses people's attention on the database buffer cache hit ratio (BCHR) metric for evaluating efficiency. The problem is that the BCHR is a metric of INSTANCE efficiency, not SQL efficiency. However, many people mistakenly apply it as a metric of SQL efficiency anyway. Of course, if one's radar equates SQL efficiency with the BCHR's proximity to 100%, then a lot of really bad SQL is going to show up on your radar wrongly identified as really good SQL. [Why a 99% buffer cache hit ratio is not okay at www.hotsos.com/catalog] One classic result is that people go on search and destroy missions for all full-table scans. They end up producing more execution plans that look like this than they should have: NESTED LOOPS TABLE ACCESS BY INDEX ROWID INDEX RANGE SCAN TABLE ACCESS BY INDEX ROWID INDEX RANGE SCAN This kind of plan produces great hit ratios because it tends to revisit the same small set of blocks over and over again. This kind of plan is of course appropriate in many cases. But sometimes it is actually less work in the database to use full-table scans. [When to use an index at www.hotsos.com/catalog.] Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas -Original Message- Sent: Friday, November 15, 2002 4:39 PM To: Multiple recipients of list ORACLE-L A while back someone mentioned that the two main causes of slow SQL are excesive LIO's and excesscive database calls, which show up as excessive CPU use and excessive network traffic, respectively. Regarding the database calls, is there a classic reason for this problem? My best guess is it's caused by an SQL statement in a PL/SQL loop, which could be rewritten as a single SQL statement. But is this the single, commonly seen cause for this problem, or are there other common ways this inefficiency is introduced? Thanks in advance for help in understanding this. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Event 10046 and Performance
Title: Event 10046 and Performance
Ah, the secret power of
event 10046 reaches the light of day at last!
J Just kidding Is there a chance that its coincidence,
that some workload variation that causes poor performance just happens not to
have occurred since you instrumented the form?
My advice is to be patient and leave the
instrumentation there until the problem manifests itself again. Then youll
have everything you need to fully diagnose the issue in the sessions
trace file.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic, Dec
911 Honolulu
- 2003 Hotsos Symposium on
Oracle System Performance, Feb 912 Dallas
- Jonathan Lewis'
Optimising Oracle, Nov 1921 Dallas
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of Jamadagni,
Rajendra
Sent: Friday, November 15, 2002
4:49 PM
To: Multiple recipients of list
ORACLE-L
Subject: Event 10046 and
Performance
Okay ... here is a curved ball ...
This AM an application support person called and mentioned that
we have a form that has become slow compared to 2 days ago. So we saw the slow
behavior and then I did a 10046 trace on it.
After analysis, found that one query is taking about 90% of
total time and isolated it to be worked on.
On our day-old instance (refreshed daily from prod), this form
runs very fast. After comparing everything, I found that for some test we have
db_file_multiblock_read_count = 4 whereas it is 32 (don't ask) on production.
So after changing this variable in my session to 4 (on
production), I checked the explain plan and it seems to be okay, picking up the
right indexes etc.
So, I asked the support person to add following line in pre-form
trigger on the form and run a test
forms_ddl('alter session set db_file_multiblock_read_count=4');
ran the form, no change in the performance. So I requested
following like to be addes as well
forms_ddl('alter session set events ''10046 trace name context
forever, level 8''');
The form runs as fast as it can.
So we thought maybe flushing the shared pool might help, so we
flushed the shared pool (again don't ask, this is prod env, lunch time, light
load).
Still same, with 10046 event set, the form runs as fast as it
can, but if I take it out, it slows down.
I am confused ... does anyone have a plausible explanation on
why this is happening?
Oracle 9201, AIX 5l
Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra
dot Jamadagni at ESPN dot com
Any
opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD:
Any clod can have facts, but having an opinion is an art!
Re: .NET and Oracle
Microsoft has a .Net provider as well as Oracle ODP (OTN under Windows). In my experience if you want to use the following data types use Oracles, if not use Microsofts. 1) CLOB 2) BLOB 3) BFILE 4) XML Data type The Oracle ODP does not allow you to build the Procedures or Functions Parameters so you must Prepare each in.ou/in out parameter. Microsoft allows you to build the parameter collection, similar to the COM (regular ADO Providers). If you need some examples let me know. Regards, Jay - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 15, 2002 6:13 PM Harvinder - My experience is that Microsoft will try to imply that, just like an Oracle sales rep will try to imply that Oracle works better with Oracle9i AS. But the fact is that if Microsoft enforced that rule, it would be excluded from a lot of big accounts that have a lot more committed to Oracle than they do to .NET. However, my experience is that the Microsoft data interfaces like ADO frequently have poor performance. Since Microsoft and Oracle aren't real chummy, there are few documents to help you out. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] mailto:dwilliams;lifetouch.com -Original Message- Sent: Friday, November 15, 2002 3:59 PM To: Multiple recipients of list ORACLE-L Hi, I read somewhere that .NET application will only be supported with SQL Server. Is this true or it can be run with any other database like Oracle? Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay 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).
RMAN backup
Hello All, Is resizing the datafiles the only way of reducing the size of an RMAN full backup? Oracle Version 8.0.6. We take RMAN hot backups to disk, and the size of the backup has grown considerably. There's one large table which we were considering truncating. But looks like that would not reduce the size of the backup. Dropping the table will call for an production outage. I am considering moving the table to another tablespace, and dropping the existing one. Any ideas? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Oracle Developer Application Tuning Resources/Guidlines.
Hi All ! I am new to Oracle Developer Application Tuning i have the following queries: 0) where how to start?? the key areas in application tuning. 1) the tips techniques.. 2)useful tools scripts 3) othere resources [docs, websites etc] Best Regards Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site
