Statspack wierd Output
Hi Statspack exceptionally showing the following on a particular day :- Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- db file sequential read 100,106,503 41.66 db file scattered read 15,134,519 25.00 latch free 1,692,425 16.67 buffer busy waits 2,067,006 16.66 log file sync 543,5762,449,354 .00 - NOTE - Statspack taken from a Production Database for a 1 hour period on Oracle 8.1.7.4 version Application = Hybrid in nature , Banking s/w 4000 Concurrent Users connect to the Database Qs What can be the cause of the same? Qs Can anything be done about such field value Overflow i.e. ? Will provide any info required Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Standard Versus Enterprise Edition
Hi Qs Does a Pro*C application (Banking) need to be compiled with Standard Edition as Oracle Libraries are being used in the Compilation OR can it be compiled with the Enterprise Edition simply deployed elsewhere at Customer site containing the Standard Edition? NOTE Application is making OCI Calls to the DB. Does Standard Edition support the same? Qs Which are the important differences between Standard Edition of 8i/9i versus Enterprise Edition? Will provide any info needed Thanks Vivek -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Standard Vs. Enterprise Edition for Application Compilation
Hi Qs Does a Pro*C application (Banking) need to be compiled with Standard Edition as Oracle Libraries are being used in the Compilation OR can it be compiled with the Enterprise Edition simply deployed elsewhere at Customer site containing the Standard Edition? NOTE Application is making OCI Calls to the DB. Does Standard Edition support the same? Qs Which are the important differences between Standard Edition of 8i/9i versus Enterprise Edition? Will provide any info needed Thanks Vivek -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Standard Vs. Enterprise Edition for Application Compilation
Hi Qs Does a Pro-C application (Banking) need to be compiled with Standard Edition as Oracle Libraries are being used in the Compilation OR can it be compiled with the Enterprise Edition simply deployed elsewhere at Customer site containing the Standard Edition? NOTE Application is making OCI Calls to the DB. Does Standard Edition support the same? Qs Which are the important differences between Standard Edition of 8i/9i versus Enterprise Edition? Will provide any info needed Thanks Vivek -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Code Conversion from MSSQL into Oracle
Are there any TOOLs for converting Sample Code (like the following) from MSSQL into Oracle? SAMPLE :- DECLARE @entity_id char(32), @branch_id char(9) DECLARE cur_temp_GEMT CURSOR FOR select branch_id,entity_id from GEMT where other_party_name='' and entity_type='D' OPEN cur_temp_GEMT FETCH NEXT FROM cur_temp_GEMT INTO @branch_id,@entity_id WHILE @@FETCH_STATUS = 0 BEGIN UPDATE GEMT SET other_party_name=(SELECT name from GEAT where branch_id [EMAIL PROTECTED] and [EMAIL PROTECTED] and addr_type='1' and entity_type='D') where [EMAIL PROTECTED] and branch_id = @branch_id FETCH NEXT FROM cur_temp_GEMT INTO @branch_id, @entity_id END CLOSE cur_temp_GEMT DEALLOCATE cur_temp_GEMT -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Code Conversion from MSSQL into Oracle
Are there any TOOLs for converting Sample Code (like the following) from MSSQL into Oracle? SAMPLE :- DECLARE @entity_id char(32), @branch_id char(9) DECLARE cur_temp_GEMT CURSOR FOR select branch_id,entity_id from GEMT where other_party_name='' and entity_type='D' OPEN cur_temp_GEMT FETCH NEXT FROM cur_temp_GEMT INTO @branch_id,@entity_id WHILE @@FETCH_STATUS = 0 BEGIN UPDATE GEMT SET other_party_name=(SELECT name from GEAT where branch_id [EMAIL PROTECTED] and [EMAIL PROTECTED] and addr_type='1' and entity_type='D') where [EMAIL PROTECTED] and branch_id = @branch_id FETCH NEXT FROM cur_temp_GEMT INTO @branch_id, @entity_id END CLOSE cur_temp_GEMT DEALLOCATE cur_temp_GEMT
Code Conversion from MSSQL into Oracle
Are there any TOOLs for converting Sample Code (like the following) from MSSQL into Oracle? SAMPLE :- DECLARE @entity_id char(32), @branch_id char(9) DECLARE cur_temp_GEMT CURSOR FOR select branch_id,entity_id from GEMT where other_party_name='' and entity_type='D' OPEN cur_temp_GEMT FETCH NEXT FROM cur_temp_GEMT INTO @branch_id,@entity_id WHILE @@FETCH_STATUS = 0 BEGIN UPDATE GEMT SET other_party_name=(SELECT name from GEAT where branch_id [EMAIL PROTECTED] and [EMAIL PROTECTED] and addr_type='1' and entity_type='D') where [EMAIL PROTECTED] and branch_id = @branch_id FETCH NEXT FROM cur_temp_GEMT INTO @branch_id, @entity_id END CLOSE cur_temp_GEMT DEALLOCATE cur_temp_GEMT
RE: Code Conversion from MSSQL into Oracle
Are there any TOOLs for converting Sample Code (like the following) from MSSQL into Oracle? SAMPLE :- DECLARE @entity_id char(32), @branch_id char(9) DECLARE cur_temp_GEMT CURSOR FOR select branch_id,entity_id from GEMT where other_party_name='' and entity_type='D' OPEN cur_temp_GEMT FETCH NEXT FROM cur_temp_GEMT INTO @branch_id,@entity_id WHILE @@FETCH_STATUS = 0 BEGIN UPDATE GEMT SET other_party_name=(SELECT name from GEAT where branch_id [EMAIL PROTECTED] and [EMAIL PROTECTED] and addr_type='1' and entity_type='D') where [EMAIL PROTECTED] and branch_id = @branch_id FETCH NEXT FROM cur_temp_GEMT INTO @branch_id, @entity_id END CLOSE cur_temp_GEMT DEALLOCATE cur_temp_GEMT
SQL_Trace versus Statspack
We are doing Interest processing on a SET of 10,000 Bank A/cs using a Single Database connect process. SQL Query :- select field names,rowid into :b0,:b1,... from TBA_ENTITY_INTEREST_TBL where (entity_id=:b105 and entity_type=:b106) for update of same (above) field names nowait; Above SQL Query Shows DIFFERING values (taken concurrently) for:- Execute from SQL_TRACE = 2584 Executions from Statspack report = 10,000 Qs. What is the reason for this? Qs. Have we possibly missed some SQL trace files? SQL_TRACE :- call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 2584 0.55 0.55 0 10475 2783 0 Fetch 2584 0.50 0.51 0 0 0 2584 --- -- -- -- -- -- -- total 5169 1.05 1.06 0 10475 2783 2584 Statspack :- CPU Elapsd Buffer GetsExecutions Gets per Exec %Total Time (s) Time (s) Hash Value --- -- -- - -- 51,271 10,0005.1 23.0 4.31 3.95 1862033429 Module: [EMAIL PROTECTED] (TNS V1-V3) select TO_CHAR(accrued_upto_date_cr,'DD-MM- HH24:MI:SS') ,TO _CHAR(accrued_upto_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(las t_accrual_run_date_cr,'DD-MM- HH24:MI:SS') ,TO_CHAR(last_acc rual_run_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(booked_upto_d ate_cr,'DD-MM- HH24:MI:SS') ,TO_CHAR(booked_upto_date_dr,'DD NOTE - Actual SQL Query partly visible above too in statspack Output Will provide any data needed Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Trace (SQL) versus Statspack data
We are doing Interest processing on a SET of 10,000 Bank A/cs SQL Query :- select field names,rowid into :b0,:b1,... from TBA_ENTITY_INTEREST_TBL where (entity_id=:b105 and entity_type=:b106) for update of same (above) field names nowait; Above SQL Query Shows DIFFERING values (taken concurrently) for:- Execute from SQL_TRACE = 2584 Executions from Statspack report = 10,000 Qs. What is the reason for this? SQL_TRACE :- call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 2584 0.55 0.55 0 10475 2783 0 Fetch 2584 0.50 0.51 0 0 0 2584 --- -- -- -- -- -- -- total 5169 1.05 1.06 0 10475 2783 2584 Statspack :- CPU Elapsd Buffer GetsExecutions Gets per Exec %Total Time (s) Time (s) Hash Value --- -- -- - -- 51,271 10,0005.1 23.0 4.31 3.95 1862033429 Module: [EMAIL PROTECTED] (TNS V1-V3) select TO_CHAR(accrued_upto_date_cr,'DD-MM- HH24:MI:SS') ,TO _CHAR(accrued_upto_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(las t_accrual_run_date_cr,'DD-MM- HH24:MI:SS') ,TO_CHAR(last_acc rual_run_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(booked_upto_d ate_cr,'DD-MM- HH24:MI:SS') ,TO_CHAR(booked_upto_date_dr,'DD NOTE - Actual SQL Query partly visible above too in statspack Output Will provide any data needed Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Trace versus Statspack data
We are doing Interest processing on a SET of 10,000 Bank A/cs Following SQL Query Shows DIFFERING values for :- Execute from SQL_TRACE = 2584 Executions from Statspack report = 10,000 Qs. What is the reason for this? SQL_TRACE :- call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 1 0.00 0.00 0 0 0 0 Execute 2584 0.55 0.55 0 10475 2783 0 Fetch 2584 0.50 0.51 0 0 0 2584 --- -- -- -- -- -- -- total 5169 1.05 1.06 0 10475 2783 2584 Statspack :- CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --- -- -- - -- 51,271 10,000 5.1 23.0 4.31 3.95 1862033429 Module: [EMAIL PROTECTED] (TNS V1-V3) select TO_CHAR(accrued_upto_date_cr,'DD-MM- HH24:MI:SS') ,TO _CHAR(accrued_upto_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(las t_accrual_run_date_cr,'DD-MM- HH24:MI:SS') ,TO_CHAR(last_acc rual_run_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(booked_upto_d ate_cr,'DD-MM- HH24:MI:SS') ,TO_CHAR(booked_upto_date_dr,'DD Thanks
Trace versus Statspack data
We are doing Interest processing on a SET of 10,000 Bank A/cs Following SQL Query Shows DIFFERING values for :- Execute from SQL_TRACE = 2584 Executions from Statspack report = 10,000 Qs. What is the reason for this? SQL_TRACE :- call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 2584 0.55 0.55 0 10475 2783 0 Fetch 2584 0.50 0.51 0 0 0 2584 --- -- -- -- -- -- -- total 5169 1.05 1.06 0 10475 2783 2584 Statspack :- CPU Elapsd Buffer GetsExecutions Gets per Exec %Total Time (s) Time (s) Hash Value --- -- -- - -- 51,271 10,0005.1 23.0 4.31 3.95 1862033429 Module: [EMAIL PROTECTED] (TNS V1-V3) select TO_CHAR(accrued_upto_date_cr,'DD-MM- HH24:MI:SS') ,TO _CHAR(accrued_upto_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(las t_accrual_run_date_cr,'DD-MM- HH24:MI:SS') ,TO_CHAR(last_acc rual_run_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(booked_upto_d ate_cr,'DD-MM- HH24:MI:SS') ,TO_CHAR(booked_upto_date_dr,'DD Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Trace versus Statspack data
We are doing Interest processing on a SET of 10,000 Bank A/cs Following SQL Query Shows DIFFERING values for :- Execute from SQL_TRACE = 2584 Executions from Statspack report = 10,000 Qs. What is the reason for this? SQL_TRACE :- call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 1 0.00 0.00 0 0 0 0 Execute 2584 0.55 0.55 0 10475 2783 0 Fetch 2584 0.50 0.51 0 0 0 2584 --- -- -- -- -- -- -- total 5169 1.05 1.06 0 10475 2783 2584 Statspack :- CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --- -- -- - -- 51,271 10,000 5.1 23.0 4.31 3.95 1862033429 Module: [EMAIL PROTECTED] (TNS V1-V3) select TO_CHAR(accrued_upto_date_cr,'DD-MM- HH24:MI:SS') ,TO _CHAR(accrued_upto_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(las t_accrual_run_date_cr,'DD-MM- HH24:MI:SS') ,TO_CHAR(last_acc rual_run_date_dr,'DD-MM- HH24:MI:SS') ,TO_CHAR(booked_upto_d ate_cr,'DD-MM- HH24:MI:SS') ,TO_CHAR(booked_upto_date_dr,'DD Thanks
ORA-4031 errors no a high Load Database
Intermittent ORA-4031 errors Out of shared Pool :- Oracle ver 9203 Solaris 9 Concurrent Users = 6000 Shared Servers / MTS being used Listeners = 4 Application using Bind Variables Application = Banking - Hybrid in Nature Database size = 1 TB m/c = SF15K How can this issue be approached ? Should we consider moving to Oracle 9204 / higher ? large_pool_size big integer 2147483648 max_shared_servers integer 1000 mts_circuits integer 11000 mts_dispatchers string (address=(protocol=tcp)(host=1 0.16.14.236))(listener=CONSOLD GLIST)(dispatchers=7), (addres s=(protocol=tcp)(host=10.16.14 .236))(listener=OEMDGLIST)(dis patchers=7), (address=(protoco l=tcp)(host=10.16.14.236))(lis tener=BBYDGLIST6)(dispatchers= 7), (address=(protocol=tcp)(ho st=10.16.14.236))(listener=BBY DGLIST2)(dispatchers=7), (addr ess=(protocol=tcp)(host=10.16. 14.236))(listener=BBYDGLIST3)( dispatchers=7), (address=(prot ocol=tcp)(host=10.16.0.215))(l istener=BBYDGLIST4)(dispatcher s=7), mts_listener_address string mts_max_dispatchers integer 150 mts_max_servers integer 1000 mts_multiple_listeners boolean FALSE mts_servers integer 300 mts_service string bby01 mts_sessions integer 10995 shared_pool_reserved_size big integer 367001600 shared_pool_size big integer 1056964608 Will provide any Data needed Thanks
RE: Optimizer related init parameters
Gaja,List QUESTION IN CAPITALS BELOW :- Thanks indeed P.S. Welcome Back to the List . All have been missing you. -Original Message- Sent: Sunday, October 12, 2003 12:19 AM To: Multiple recipients of list ORACLE-L Vivek and list, I don't think any reasonable person will be able to say with a high-level of certainty whether the values that you have suggested, are optimal for your environment. The answer is a huge - IT DEPENDS. Having said that, here are some things you may want to take into consideration: 1) From a functionality perspective OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ were meant to do the same thing. It just happened to end up as 2 different parameters with 2 different code paths, which pretty much do the same(similar) thing. So usually, it is enought to set either one or the other, although setting both in my experience has generated no harm. So, if you want to optimizer to show bias towards index scans, then setting OPTIMIZER_INDEX_CACHING to a high value (90 or higher) will achieve that. Right now your value of 50, tells the optimizer that only 50% of the time, will it find index blocks in the DB buffer cache. This will affect the optimizer's decision making. Tim Gorman has a very simple formula to calculate the appropriate value on your system for OPTIMZER_INDEX_COST_ADJ, stated in his paper Searching for intelligence in the Oracle Optimizer (or something to that effect) on his site http://www.evdbt.com. It basically calculates a ratio of the average time for db file sequential read/db file scattered read from v$system_event, for your system. On a related topic, I think it is relevant to mention here that to carte-blanche curtail full-table-scans, may not work to the long-term benefit of your applications. However, I will assume here that you are aware of the core point - amount of logical I/O to be the most important (if not only) determinant when deciding whether FTS is better than index scans. Qs. COULD YOU GIVE SOME DETAIL ON THIS PLEASE (ABOVE PARA)? 2) John Kanagaraj did some work and testing to determine that setting OPTIMIZER_MAX_PERMUTATIONS to a low value (2000 if I remember right), has a positive impact on the plans that is generated, especially in an Oracle Apps environment. You should check it out. 3) Julian Dyke and Steve Adams have performed some good tests and research on OPTIMIZER_DYNAMIC_SAMPLING. But, I think the jury is still out on what the optimal value for this might be. I guess 4 is good enough. But, realize that this parameter is relevant when you have partial statistics in your schema. Otherwise, I don't think there is any impact of this parameter. Final notes: 1) All of these parameters can be set at the session level. I would urge you to perform extensive tests before making global init.ora changes. 2) At the end of the day, you should ask yourself, why you are embarking on this effort of changing these values. If you have enough trace data to warrant these changes, then by all means. Otherwise, you may be setting yourself up for surprises in the future. Cheers, Gaja --- VIVEK_SHARMA [EMAIL PROTECTED] wrote: How Good/advisable are the following 4 parameters' Values in a Hybrid Application? Are there any know ill-effects of the same? Application - Banking (Hybrid) Solaris 9 Oracle 9.2 optimizer_max_permutations=8000 optimizer_index_cost_adj=10 optimizer_index_caching=50 optimizer_dynamic_sampling=4 Some INFO :- Database has 6000 Concurrent Users accessing We do ONLY INDEX Scans with exceptional FTS . FTS if present occur only on SMALL Tables (a few Hundred Rows) FTS if unchecked greatly harm our performance Stripe Unit Size 64K Oracle Block Size 8K Will Give any info required Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Optimizer related init parameters
How Good/advisable are the following 4 parameters Values in a Hybrid Application? Are there any know ill-effects of the same? Application Banking (Hybrid) Solaris 9 Oracle 9.2 optimizer_max_permutations=8000 optimizer_index_cost_adj=10 optimizer_index_caching=50 optimizer_dynamic_sampling=4 Some INFO :- Database has 6000 Concurrent Users accessing We do ONLY INDEX Scans with exceptional FTS . FTS if present occur only on SMALL Tables (a few Hundred Rows) FTS if unchecked greatly harm our performance Stripe Unit Size 64K Oracle Block Size 8K Will Give any info required Thanks
Dataguard Benchmark - (re-posting)
Gogala, Folks GOAL -To Compare performances (monitored readings), of the Primary Database in various Standby modes , with each other also against a Primary database WITHOUT Standby (performance baseline) Qs Is the following approach suitable? Approach 1) On WAN, Only 2 (of 3) Physical Standby modes of Dataguard Benchmarked. 2) Application Transaction Completion Timings Transaction per Second TPS achieved ( Transactions OLTP in nature ) 3) CPU Memory Consumptions on Primary Standby Databases 4) Using a Solaris Tool (netload) Utilization on the Network Card (hme0) being monitored :- Sample Output :- Inpackets Outpackets Inbytes Outbytes Collisions Utilization 634 1300 42216 1385402 2 1.17 1170 2341 77402 2502201 3 2.12 850 1624 56394 1731692 1 1.47 5) Using ethereal Freeware Tool (www.ethereal.com) :- Volume of Data (Traffic) Flowing between the Primary Standby Database machines captured 6) On LAN, All 3 Physical Standby modes of Dataguard yet to be Benchmarked 7) Logical Standby Database Benchmark NOT being considered Qs How may the following Waits be addressed? WAN Simulator Setting for the below results :- Thruput = 2 MHz = 2 MBPS Delay = 0 ms WAN Simulator present between Primary Database machine Standby Database machine SAME Application Transaction Load Fired on the Database in the various modes NOTE All Other conditions kept Same CASE 1 RUN WITHOUT Standby Database (dataguard) Top 5 Timed Events ~~ % Total Event Waits Time (s) Ela Time --- log file sync 22,601 181 34.76 library cache pin 62 121 23.17 CPU time 110 21.10 db file parallel write 391 51 9.85 db file sequential read 9,951 28 5.34 - CASE 2 RUN with Dataguard mode set to MAXIMIZE PERFORMANCE i.e. log_archive_dest_2 = 'SERVICE=phy arch' RESULT - Average APPLICATION Transaction Time TPS is SAME as that of Without Standby Database (Case1) Top 5 Timed Events ~~ % Total Event Waits Time (s) Ela Time --- ARCH wait on SENDREQ 135 389 52.05 log file sync 18,630 165 22.07 CPU time 94 12.52 library cache pin 18 39 5.24 db file parallel write 283 17 2.31 - CASE 3 Dataguard mode Set to MAXIMIZE AVAILABILITY i.e. log_archive_dest_2 = 'SERVICE=phy LGWR Sync Affirm' RESULT - Average APPLICATION Transaction Time TPS is MUCH Higher than of Cases 1 2 Top 5 Timed Events ~~ % Total Event Waits Time (s) Ela Time --- log file sync 17,355 4,129 74.13 LGWR wait on LNS 92,198 368 6.61 LGWR-LNS wait on channel 42,991 367 6.60 LNS wait on SENDREQ 6,187 358 6.43 library cache pin 53 96 1.71 - Will Provide any data needed Thanks -Original Message- Sent: Tuesday, September 16, 2003 5:25 PM To: Multiple recipients of list ORACLE-L No. I mean the goals of the benchmark. What are you measuring and why. On 2003.09.16 06:10, VIVEK_SHARMA wrote: Hi Plan of the Benchmark = ? If you mean an action plan of important steps ? We have just started. I Shall send it to you shortly. Monitoring of Actual Bandwidth thruput (MBPS) used versus that set on WAN will be done . (other than actual application transaction thruput rate (TPS) , OS resources Utilizations ) Thanks indeed -Original Message- Sent: Tuesday, September 16, 2003 2:59 AM To: Multiple recipients of list ORACLE-L Did you plan that benchmark? What did you decide to monitor in the planning phase? -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of VIVEK_SHARMA Sent: Monday, September 15, 2003 1:49 PM To: Multiple recipients of list ORACLE-L Subject: Dataguard Benchmark We are doing a Data guard Benchmark. INFO.:- - WAN Simulator :- We have a WAN Simulator with 2 routers at either ends of it. Thruputs from 0 to 2 MBPS can be manually set as is required by the run. Application = Banking :- Transactions mainly OLTP in nature (Both DML SELECTS) . We can do CPU intensive batch Transactions too if advised by you folks Machines = 2 machines of 4 CPUs each Memory = 8 GB on each machine O.S. = Solaris 9 Oracle = 9.2 Sniffer network tool ( to get volume of bytes transferred over the WAN ) Dataguard Setup will transfer Data thru the listener services :- i.e. init.ora - LOG_ARCHIVE_DEST_2 = 'SERVICE
Dataguard Benchmark
Qs Is the following approach suitable ? Approach 1) On WAN, Only 2 (of 3) Physical Standby modes of Dataguard Benchmarked. 2) Application Transaction Completion Timings Transaction per Second TPS achieved ( Transactions OLTP in nature ) 3) CPU Memory Consumptions on Primary Standby Databases 4) Using a Solaris Tool (netload) Utilization on the Network Card (hme0) being monitored :- Sample Output :- Inpackets Outpackets Inbytes Outbytes Collisions Utilization 634 1300 42216 1385402 2 1.17 1170 2341 77402 2502201 3 2.12 850 1624 56394 1731692 1 1.47 5) Using ethereal Freeware Tool (www.ethereal.com) :- Volume of Data (Traffic) Flowing between the Primary Standby Database machines captured 6) On LAN, All 3 Physical Standby modes of Dataguard yet to be Benchmarked 7) Logical Standby Database Benchmark NOT being considered Qs How may the following Waits be addressed? WAN Simulator Setting for the below results :- Thruput = 2 MHz = 2 MBPS Delay = 0 ms WAN Simulator present between Primary Database machine Standby Database machine SAME Application Transaction Load Fired on the Database in the various modes NOTE All Other conditions kept Same CASE 1 RUN WITHOUT Standby Database (dataguard) Top 5 Timed Events ~~ % Total Event Waits Time (s) Ela Time --- log file sync 22,601 181 34.76 library cache pin 62 121 23.17 CPU time 110 21.10 db file parallel write 391 51 9.85 db file sequential read 9,951 28 5.34 - CASE 2 RUN with Dataguard mode set to MAXIMIZE PERFORMANCE i.e. log_archive_dest_2 = 'SERVICE=phy arch' RESULT - Average APPLICATION Transaction Time TPS is SAME as that of Without Standby Database (Case1) Top 5 Timed Events ~~ % Total Event Waits Time (s) Ela Time --- ARCH wait on SENDREQ 135 389 52.05 log file sync 18,630 165 22.07 CPU time 94 12.52 library cache pin 18 39 5.24 db file parallel write 283 17 2.31 - CASE 3 Dataguard mode Set to MAXIMIZE AVAILABILITY i.e. log_archive_dest_2 = 'SERVICE=phy LGWR Sync Affirm' RESULT - Average APPLICATION Transaction Time TPS is MUCH Higher than of Cases 1 2 Top 5 Timed Events ~~ % Total Event Waits Time (s) Ela Time --- log file sync 17,355 4,129 74.13 LGWR wait on LNS 92,198 368 6.61 LGWR-LNS wait on channel 42,991 367 6.60 LNS wait on SENDREQ 6,187 358 6.43 library cache pin 53 96 1.71 - Will Provide any data needed Thanks -Original Message- Sent: Tuesday, September 16, 2003 5:25 PM To: Multiple recipients of list ORACLE-L No. I mean the goals of the benchmark. What are you measuring and why. On 2003.09.16 06:10, VIVEK_SHARMA wrote: Hi Plan of the Benchmark = ? If you mean an action plan of important steps ? We have just started. I Shall send it to you shortly. Monitoring of Actual Bandwidth thruput (MBPS) used versus that set on WAN will be done . (other than actual application transaction thruput rate (TPS) , OS resources Utilizations ) Thanks indeed -Original Message- Sent: Tuesday, September 16, 2003 2:59 AM To: Multiple recipients of list ORACLE-L Did you plan that benchmark? What did you decide to monitor in the planning phase? -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of VIVEK_SHARMA Sent: Monday, September 15, 2003 1:49 PM To: Multiple recipients of list ORACLE-L Subject: Dataguard Benchmark We are doing a Data guard Benchmark. INFO.:- - WAN Simulator :- We have a WAN Simulator with 2 routers at either ends of it. Thruputs from 0 to 2 MBPS can be manually set as is required by the run. Application = Banking :- Transactions mainly OLTP in nature (Both DML SELECTS) . We can do CPU intensive batch Transactions too if advised by you folks Machines = 2 machines of 4 CPUs each Memory = 8 GB on each machine O.S. = Solaris 9 Oracle = 9.2 Sniffer network tool ( to get volume of bytes transferred over the WAN ) Dataguard Setup will transfer Data thru the listener services :- i.e. init.ora - LOG_ARCHIVE_DEST_2 = 'SERVICE=SERVICE_NAME_FROM_TNSNAMES.ORA,. . .' Execution methodology:- Run Same Transaction's Volume in BOTH Logical Physical (Maximum Protection , Maximum Availability , Maximum
Logical standby mode - Dataguard - 9.2.0.4
A Basic Qs. On Logical standby mode - Dataguard - 9.2.0.4 . Is the following statement correct ? From the Primary Database the DMLs on tables NOT having LONG columns are successfully applied across to the Logical Standby database while those tables containing LONG columns fail . Query to find Objects which are NOT supported ;- Select * from DBA_LOGSTDBY_UNSUPPORTED;
RE: Logical standby mode - Dataguard - 9.2.0.4
OR is it so that logical Standby mode is just NOT possible for the Entire Database if the Database has even 1 Table with a LONG field datatype ? -Original Message- From: VIVEK_SHARMA Sent: Monday, September 29, 2003 1:25 PM To: Multiple recipients of list ORACLE-L Subject: Logical standby mode - Dataguard - 9.2.0.4 A Basic Qs. On Logical standby mode - Dataguard - 9.2.0.4 . Is the following statement correct ? From the Primary Database the DMLs on tables NOT having LONG columns are successfully applied across to the Logical Standby database while those tables containing LONG columns fail . Query to find Objects which are NOT supported ;- Select * from DBA_LOGSTDBY_UNSUPPORTED;
RE: Logical standby mode - Dataguard - 9.2.0.4
Can anyone who has successfully used Logical Standby Database share the ENTIRE Commands set? I shall share our entire current commands set which is being used to bring up the Logical standby database ( without any errors ) with anyone who asks. NOTE Though there are NO Errors (in alert_SID.log) all Our Commands complete successfully, DMLs on Primary tables are NOT getting applied to Standby Database We have raised a few Oracle TARs too. Thanks -Original Message- From: VIVEK_SHARMA Sent: Monday, September 29, 2003 1:40 PM To: Multiple recipients of list ORACLE-L Subject: RE: Logical standby mode - Dataguard - 9.2.0.4 OR is it so that logical Standby mode is just NOT possible for the Entire Database if the Database has even 1 Table with a LONG field datatype ? -Original Message- From: VIVEK_SHARMA Sent: Monday, September 29, 2003 1:25 PM To: Multiple recipients of list ORACLE-L Subject: Logical standby mode - Dataguard - 9.2.0.4 A Basic Qs. On Logical standby mode - Dataguard - 9.2.0.4 . Is the following statement correct ? From the Primary Database the DMLs on tables NOT having LONG columns are successfully applied across to the Logical Standby database while those tables containing LONG columns fail . Query to find Objects which are NOT supported ;- Select * from DBA_LOGSTDBY_UNSUPPORTED;
RE: Dataguard Benchmark
Title: Message Hi Plan of the Benchmark = ? If you mean an action plan of important steps ? We have just started. I Shall send it to you shortly. Monitoring of Actual Bandwidth thruput (MBPS) used versus that set on WAN will be done . (other than actual application transaction thruput rate (TPS) , OS resources Utilizations ) Thanks indeed -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 16, 2003 2:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: Dataguard Benchmark Did you plan that benchmark? What did you decide to monitor in the planning phase? -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of VIVEK_SHARMA Sent: Monday, September 15, 2003 1:49 PM To: Multiple recipients of list ORACLE-L Subject: Dataguard Benchmark We are doing a Data guard Benchmark. INFO.:- - WAN Simulator :- We have a WAN Simulator with 2 routers at either ends of it. Thruputs from 0 to 2 MBPS can be manually set as is required by the run. Application = Banking :- Transactions mainly OLTP in nature (Both DML SELECTS) . We can do CPU intensive batch Transactions too if advised by you folks Machines = 2 machines of 4 CPUs each Memory = 8 GB on each machine O.S. = Solaris 9 Oracle = 9.2 Sniffer network tool ( to get volume of bytes transferred over the WAN ) Dataguard Setup will transfer Data thru the listener services :- i.e. init.ora - LOG_ARCHIVE_DEST_2 = SERVICE=SERVICE_NAME_FROM_TNSNAMES.ORA,. . . Execution methodology:- Run Same Transactions Volume in BOTH Logical Physical (Maximum Protection , Maximum Availability , Maximum performance ) standby modes Qs What readings to be particularly monitored measured? Qs What thruput bandwidths should be benchmarked? Qs Does total Size of Existent Database-in-use matter to the Benchmark? Current Database Size = 3 GB Qs Does RMAN setup add any value to the Dataguard benchmark in some way? Else we will do Without RMAN, manually altering the various modes Qs Any Sample Docs, Links on existing Dataguard Benchmarks? Qs Any else that will enable us to bring out a paper of reasonable standard?
Dataguard Benchmark
We are doing a Data guard Benchmark. INFO.:- - WAN Simulator :- We have a WAN Simulator with 2 routers at either ends of it. Thruputs from 0 to 2 MBPS can be manually set as is required by the run. Application = Banking :- Transactions mainly OLTP in nature (Both DML SELECTS) . We can do CPU intensive batch Transactions too if advised by you folks Machines = 2 machines of 4 CPUs each Memory = 8 GB on each machine O.S. = Solaris 9 Oracle = 9.2 Sniffer network tool ( to get volume of bytes transferred over the WAN ) Dataguard Setup will transfer Data thru the listener services :- i.e. init.ora - LOG_ARCHIVE_DEST_2 = SERVICE=SERVICE_NAME_FROM_TNSNAMES.ORA,. . . Execution methodology:- Run Same Transactions Volume in BOTH Logical Physical (Maximum Protection , Maximum Availability , Maximum performance ) standby modes Qs What readings to be particularly monitored measured? Qs What thruput bandwidths should be benchmarked? Qs Does total Size of Existent Database-in-use matter to the Benchmark? Current Database Size = 3 GB Qs Does RMAN setup add any value to the Dataguard benchmark in some way? Else we will do Without RMAN, manually altering the various modes Qs Any Sample Docs, Links on existing Dataguard Benchmarks? Qs Any else that will enable us to bring out a paper of reasonable standard?
Trapping of Rogue Statement
An unwanted update - del_flg=Y is happening while an application is underway with thousands of Concurrent Users We need to trap get as much info on this update which is somehow happening from somewhere in the application code . How as a DBA can we do this? Thanks
RE: Urgent INFO needed. - OFF TOPIC
Well-Spoken Indeed -Original Message- Sent: Friday, September 05, 2003 10:44 AM To: Multiple recipients of list ORACLE-L Yes, in your experience...and maybe most of the other lister's experience...majority of the time its true... That's because you people are the best DBAs I've seen... Your experiences...10...15 years...some of you have a little over 3 years and are already experts... Of course a lot of other people are less skilled than you are... But I am not sure if you represent the rest of the US DBAs...or if I represent the rest of the 3rd world DBAs... Again, as I have said...some peopler are just better than others...that's my opinion...obviously, yours is a lot different than mine... I don't know you, Joe, well enough to judge you. So I won't call you names. Joe Testa wrote: Fact of the matter is the majority of the time its true, like it or not. Those of you who know me, know I DON'T do politically correct, I call a spade a spade. Its true at the officer level in a company(and I'm there in the company I'm in now), its all about dollars and cents, especially today. Thats why you see it as much as you do. My example: I worked for an online training company, they were paying me $25/hr to do online tutoring or web based oracle DBA classes., I was with them from the beginning just like alot of other tutors from around th world. We were told in early/mid 2002, we've decided to outsource all tutoring to India, so if you're interested in teaching your replacements, we'll keep you on board for a little extra time. The end was near and someone had asked me how long I'd been doing it, how long I was a DBA and how long did I plan on doing it, i gave them the truthful answer, about 4 yrs, 10 years as a DBA and not much longer since it was all being outsourced to India, got my a$$ fired from the job before my time was up. Basically the concept was: we can go to India and pay $2.50, 10% of what we pay you and we really dont care about the quality because they will pick up their English language from previous answers you and others have submitted to students throughout the years.. You tell me I shouldn't have an attitude, you're as full of garbage as them. Joe PS: for those who want to know the company, email me direct, i'll be glad to share. Maria Aurora de la Vega wrote: Its quite unfair for DBAs to blame their job loss/fear of job loss to DBAs in India or some other countries with cheaper labor. And to say that you get what you pay for or insinuating that cheaper labor means less quality...is definitely out of line... some people are just better than others...that's it...it has nothing to do with geography or nationality... some of the best DBAs...or IT professionals in general...are in fact indians... Point is its not the indians' fault jobs just come knocking at their doors... we all want better jobs and better pay...if it comes to me i'll grab it no doubt... would I think about other DBAs who were taken off to accomodate me? of course not. I have nothing to do with their decision to outsource...and even if I stress myself worrying about it...can I do anything about it? no...so, what do I do...I'd take advantage of course... I've come to believe no one is indispensable...even if you've served 5, 10, 15 years in a company...there's always a reason to take you out no matter how good you are... And sometimes companies think...do I really need someone that good and costs a lot more? or can we do with someone quite average but can get the job done and costs a lot less? Tony Johnson wrote: All I know about it is that for every new job in India one more DBA is out of work here in the United States. -Original Message- *From:* Ora DBA [mailto:[EMAIL PROTECTED] *Sent:* Wednesday, September 03, 2003 5:30 AM *To:* Multiple recipients of list ORACLE-L *Subject:* Urgent INFO needed. Dear Friends, One of my friends has got the offer from CSC india ltd. Please respond this mail if any one from CSC india ltd? just to know some info , thats it. Any info regarding this is appreciated. Please give me your mail id and contacts.. Thanks a lot. Regards Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle Standby Database Backups.
Arup,Indy, List Some Clarifications please If the Primary Database is in ARCHIVELOG Mode (Physical Standby) archived files there from are being shipped applied to the Standby Database, What is the need to run the Standby Database in ARCHIVELOG Mode? Are you implying 9i Dataguard with a Standby which works on a mechanism Other than Log-shipping? Please give detail Thanks -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2003 12:35 AM To: Multiple recipients of list ORACLE-L Subject: Re: Oracle Standby Database Backups. Tom, You should perform backups from the Standby database, regular RMAN backups, no need to shutdown the database. Make sure you backup the archived log files from there too. Contrary to what the docs might _imply_, I use the word imply rather than state, since the docs have been kind of ambiguous, the archivedlogbackups from the standby are perfectly alright to be used for recoveries.. You could use the RMAN backup on the primary, but why? You would rather want to offload the CPU cycles for RMAN to the standby database. In case of a failure in the primary, your first option is to get the files from standby and recover them. If standby is down too (as in case of a complete disaster), you would reinstate the standby backup files to primary and you will be ok. We are using it to backup out 7 TB OLTP database. HTH. Arup - Original Message - From: Mercadante, Thomas F To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 03, 2003 2:29 PM Subject: Oracle Standby Database Backups. All, We are in the beginning stages of designing a database with Oracle Standby capability. The initial size of the database will be 600-800 Gig. The proposed database will be run on a IBM P690 with a mirrored fail-over machine. Two separate machines with separate disk.We are considering using Oracle Standby to have the database available as much as possible. Do I need to perform regular backups of the Standby database? Sounds like a silly question, but how do I do this? Using Rman? Or do I shut it down and perform a cold backup? I will definitely use Rman on the primary database. Just curious what you all would suggest. Thanks in advance! Tom Mercadante Oracle Certified Professional
RE: Urgent INFO needed. - OFF TOPIC
$2.50 is simply because of the $ to Rupee PARITY RATIO respective Cost of Living here in India. Labor in India is NOT Cheap . It is Simply this ratio which gives the impression of being cheap. Also Outsourcers get to pay LESS because of the Current prevailing Work-to-Earnings monetary scenario existing in India. Usually Tech. competence , for most , is at par amongst professionals irrespective of nationality . -Original Message- Sent: Thursday, September 04, 2003 10:30 PM To: Multiple recipients of list ORACLE-L Fact of the matter is the majority of the time its true, like it or not. Those of you who know me, know I DON'T do politically correct, I call a spade a spade. Its true at the officer level in a company(and I'm there in the company I'm in now), its all about dollars and cents, especially today. Thats why you see it as much as you do. My example: I worked for an online training company, they were paying me $25/hr to do online tutoring or web based oracle DBA classes., I was with them from the beginning just like alot of other tutors from around th world. We were told in early/mid 2002, we've decided to outsource all tutoring to India, so if you're interested in teaching your replacements, we'll keep you on board for a little extra time. The end was near and someone had asked me how long I'd been doing it, how long I was a DBA and how long did I plan on doing it, i gave them the truthful answer, about 4 yrs, 10 years as a DBA and not much longer since it was all being outsourced to India, got my a$$ fired from the job before my time was up. Basically the concept was: we can go to India and pay $2.50, 10% of what we pay you and we really dont care about the quality because they will pick up their English language from previous answers you and others have submitted to students throughout the years.. You tell me I shouldn't have an attitude, you're as full of garbage as them. Joe PS: for those who want to know the company, email me direct, i'll be glad to share. Maria Aurora de la Vega wrote: Its quite unfair for DBAs to blame their job loss/fear of job loss to DBAs in India or some other countries with cheaper labor. And to say that you get what you pay for or insinuating that cheaper labor means less quality...is definitely out of line... some people are just better than others...that's it...it has nothing to do with geography or nationality... some of the best DBAs...or IT professionals in general...are in fact indians... Point is its not the indians' fault jobs just come knocking at their doors... we all want better jobs and better pay...if it comes to me i'll grab it no doubt... would I think about other DBAs who were taken off to accomodate me? of course not. I have nothing to do with their decision to outsource...and even if I stress myself worrying about it...can I do anything about it? no...so, what do I do...I'd take advantage of course... I've come to believe no one is indispensable...even if you've served 5, 10, 15 years in a company...there's always a reason to take you out no matter how good you are... And sometimes companies think...do I really need someone that good and costs a lot more? or can we do with someone quite average but can get the job done and costs a lot less? Tony Johnson wrote: All I know about it is that for every new job in India one more DBA is out of work here in the United States. -Original Message- *From:* Ora DBA [mailto:[EMAIL PROTECTED] *Sent:* Wednesday, September 03, 2003 5:30 AM *To:* Multiple recipients of list ORACLE-L *Subject:* Urgent INFO needed. Dear Friends, One of my friends has got the offer from CSC india ltd. Please respond this mail if any one from CSC india ltd? just to know some info , thats it. Any info regarding this is appreciated. Please give me your mail id and contacts.. Thanks a lot. Regards Oracle DBA Do you Yahoo!? Yahoo! SiteBuilder http://us.rd.yahoo.com/evt=10469/*http://sitebuilder.yahoo.com - Free, easy-to-use web site design software -- Joseph S Testa Chief Technology Officer Data Management Consulting 614-791-9000 It's all about the CACHE -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command
log buffer space wait
At a Benchmark a particular Processing + DML intensive batch Job (Accounts Interest Calculation) is experiencing log buffer space wait . Config :- On Solaris 9 Cpu_count = 72 Oracle 9.2 Online Redo logfiles are on a RAID 0+1 Volume (NOT Raw or RAID 1) Log_buffer = 10 M $ sar Command Outpuit %wio only about 6 % for Volume containing the redo logfiles Database in NOARCHIVELOG mode Qs. How can we reduce this wait event ? Would RAW / RAID 1 for redo logfiles help ? Qs. Could this be a Bug ? * STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host --- --- --- BM2 3727568246 bm2 1 9.2.0.3.0 NO sleepy Snap Id Snap Time Sessions Curs/Sess Comment --- -- - --- Begin Snap: 240 01-Sep-03 03:09:41 23 1.5 End Snap: 241 01-Sep-03 04:00:55 24 1.7 Elapsed: 51.23 (mins) Top 5 Timed Events ~~ % Total Event Waits Time (s) Ela Time --- log buffer space 1,817,258 566,516 56.24 latch free 2,964,652 334,120 33.17 log file sync 55,230 43,488 4.32 buffer busy waits 62,839 26,129 2.59 CPU time 22,018 2.19 - Wait Events for DB: BM2 Instance: bm2 Snaps: 240 -241 - s - second - cs - centisecond - 100th of a second - ms - millisecond - 1000th of a second - us - microsecond - 100th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn -- -- -- log buffer space 1,817,258 139 566,516 312 54.4 latch free 2,964,652 892,594 334,120 113 88.8 log file sync 55,230 21,210 43,488 787 1.7 buffer busy waits 62,839 10,250 26,129 416 1.9 Latch Activity for DB: BM2 Instance: bm2 Snaps: 240 -241 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss -- -- -- -- -- post/wait queue 97,357 4.7 0.4 577 1,871,155 249.5 process allocation 751 7.2 3.0 10 747 0.5 process group creation 1,502 0.0 0 0 redo allocation 24,482,283 13.5 0.4 ## 0 redo copy 675,177 99.6 1.1 ## 21,986,833 708.0 init.ora Parameters for DB: BM2 Instance: bm2 Snaps: 240 -241 End value Parameter Name Begin value (if different) - - -- _db_block_lru_latches 128 audit_trail FALSE background_dump_dest /oracle/ora92-64/rdbms/log/bdump compatible 9.2.0.3 control_files /lbmdb02/bm2/data02/control_01_bm core_dump_dest /oracle/ora92-64/rdbms/log/cdump cursor_sharing EXACT cursor_space_for_time TRUE db_block_size 8192 db_cache_size 1258291200 db_file_multiblock_read_count 32 db_files 1500 db_keep_cache_size 117440512 db_name bm2 db_writer_processes 16 dml_locks 1 enqueue_resources 68 hash_join_enabled FALSE java_pool_size 16777216 large_pool_size 16777216 log_buffer 10485760 log_checkpoint_interval 0 log_checkpoint_timeout 0 log_checkpoints_to_alert TRUE max_dump_file_size 10240 max_rollback_segments 4000 nls_date_format
Simulating WAN on LAN for Dataguard Benchmark
Is it possible to simulate WAN on LAN for a Dataguard Benchmark ? Any Docs , Links ? Thanks
RE: Query results to .csv
(or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle 9i versus MS SQL 2K w.r.t. Performance
Need to give a presentation on Oracle 9i versus MS SQL Server 2K with respect to Performance primarily . Any Docs , Links ? Thanks
RE: Redo Logs Problem
Substantial Redo Generation during a BATCH process was Reduced after Converting to LMT . NOT sure of reasons though , other than reduced updates to Data Dictionaries Request the List to respond to this HTH -Original Message- From: RAJESH DAYAL [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 11:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: Redo Logs Problem Just Out of curiosity. How come changing the TS to LMT would reduce the excessive Redo generation. Regards, Rajesh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of VIVEK_SHARMA Sent: Monday, August 04, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: Redo Logs Problem Convert Tablespaces to LMT (Locally Managed) if in 8i , to reduce excessive Redo generation -Original Message- From: Munish Bajaj [mailto:[EMAIL PROTECTED] Sent: Friday, August 01, 2003 4:04 PM To: Multiple recipients of list ORACLE-L Subject: Redo Logs Problem Hi Listers, One of my remote Clients is facing a problem with Redo Logs. The Redo Logs and the Archive logs in turn have suddenly started to generate at an alarming Rate. This has suddenly started from the last 1 week without any changes to Database Configuration or any other system settings (as per client). Can anyone please help me and let me know all the reasons that could be responsible for this behavior. Any Help from u will be appreciated. Regards Munish Bajaj
RE: Redo Logs Problem
Convert Tablespaces to LMT (Locally Managed) if in 8i , to reduce excessive Redo generation -Original Message- From: Munish Bajaj [mailto:[EMAIL PROTECTED] Sent: Friday, August 01, 2003 4:04 PM To: Multiple recipients of list ORACLE-L Subject: Redo Logs Problem Hi Listers, One of my remote Clients is facing a problem with Redo Logs. The Redo Logs and the Archive logs in turn have suddenly started to generate at an alarming Rate. This has suddenly started from the last 1 week without any changes to Database Configuration or any other system settings (as per client). Can anyone please help me and let me know all the reasons that could be responsible for this behavior. Any Help from u will be appreciated. Regards Munish Bajaj
Oracle Tools Comparable with OEM ?
Any Oracle Tools Comparable with OEM having Free Evaluation Copy or Freeware for Download ? Any names , Links etc ? Thanks
High disk , query values with Global non-prefixed Local indexes
Qs Why the larger values of disk=45 , query = 525 when using Global indexes Non-prefixed local indexes VERSUS Local Prefixed indexes where dis = 0 query = 0 in the CASES below ? Qs How significantly can this affect the performance thruput ? CASE : Comparison with global partitioned index INDEX: on sol_id, tran_date, gl_sub_Head_code and crncy_code, globally partitioned on range of tran_date TABLE: GST table with 10 million rows, with 2 equal partitions on range of tran_date Query: Select queries with key, returning 500 rows. select crncy_code into :b0 from gst_part where (((sol_id=:b1 and tran_date=:b2) and gl_sub_head_code=:b3) and crncy_code=:b0) call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 1 0.12 0.35 46 526 0 0 Execute 500 0.04 0.03 0 0 0 0 Fetch 500 0.01 0.01 16 2000 0 500 --- -- -- -- -- -- -- total 1001 0.17 0.40 62 2526 0 500 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 5 (SYSTEM) Rows Row Source Operation --- --- 500 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=2000 r=16 w=0 time=10503 us) 500 INDEX UNIQUE SCAN OBJ#(26318) PARTITION: KEY KEY (cr=2000 r=16 w=0 time=9188 us)(object id 26318) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 500 PARTITION RANGE (SINGLE) PARTITION:KEYKEY 500 INDEX (UNIQUE SCAN) OF 'IDX_GL_SUB_HEAD_TRAN_PART' (UNIQUE) PARTITION:KEYKEY CASE 1: Comparison with non-prefixed index INDEX: Non-prefixed on sol_id, tran_date, gl_sub_head_code and crncy_code TABLE: GST table with 10 million rows with 2 equal partitions on range of tran_date select crncy_code into :b0 from gst_part where (((sol_id=:b1 and tran_date=:b2) and gl_sub_head_code=:b3) and crncy_code=:b0) call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 1 0.12 0.34 42 422 0 0 Execute 500 0.02 0.03 0 0 0 0 Fetch 500 0.03 0.07 14 2000 0 500 --- -- -- -- -- -- -- total 1001 0.17 0.45 56 2422 0 500 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 5 (SYSTEM) Rows Row Source Operation --- --- 500 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=2000 r=14 w=0 time=70754 us) 500 INDEX UNIQUE SCAN OBJ#(26279) PARTITION: KEY KEY (cr=2000 r=14 w=0 time=69432 us)(object id 26279) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 500 PARTITION RANGE (SINGLE) PARTITION:KEYKEY 500 INDEX (UNIQUE SCAN) OF 'IDX_GL_SUB_HEAD_TRAN_PART' (UNIQUE) PARTITION:KEYKEY CASE 2: Comparison with prefixed index INDEX: on tran_date,sol_id, gl_sub_head_code and crncy_code TABLE: GST table with 10 million rows with 2 equal partitions, on range of tran_date select crncy_code into :b0 from gst_part where (((sol_id=:b1 and tran_date=:b2) and gl_sub_head_code=:b3) and crncy_code=:b0) call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 1 0.00 0.00 0 0 0 0 Execute 500 0.02 0.02 0 0 0 0 Fetch 500 0.02 0.04 16 2000 0 500 --- -- -- -- -- -- -- total 1001 0.04 0.07 16 2000 0 500 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 5 (SYSTEM) Rows
RE: Triggers - How expensive when set on heavily loaded tables ?
CASE - If Only 1 record is found in TABLE_1 (on the average) for 8 records INSERTED into TRAN_TBL Qs Is the Overhead of Checking the condition by the trigger also a significant portion OTHER than the actual execution by the trigger of INSERT into TABLE_2 on finding a match in TABLE_1? Any percentage proportion ratios of Checking Overhead TO execution ( INSERT ) overhead ? Any Docs , Links on such ? Thanks -Original Message- Sent: Thursday, July 24, 2003 8:42 AM To: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Qs 1) Whenever an Account ID is transacted - INSERT occurs into a table TRAN_TBL as part of the transaction , Additionally the Account ID is checked for existence in another table say TABLE_1 . If found , a record is inserted into yet another table say TABLE_2 . Qs. Operations involving TABLE_1 TABLE_2 if managed using triggers , How expensive in CPU performance will it be ? NOTE - TRAN_TBL undergoes very large volumes of Concurrent OLTP INSERT transaction . Qs 2) If an alert is to be raised on addition of a new record to TABLE_2 :- a) Can trigger be used ? b) Should a cron job running every 5 min. look at TABLE_2 based on the time criteria generates the alert ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Triggers - How expensive when set on heavily loaded tables ?
Qs 1) Whenever an Account ID is transacted - INSERT occurs into a table TRAN_TBL as part of the transaction , Additionally the Account ID is checked for existence in another table say TABLE_1 . If found , a record is inserted into yet another table say TABLE_2 . Qs. Operations involving TABLE_1 TABLE_2 if managed using triggers , How expensive in CPU performance will it be ? NOTE - TRAN_TBL undergoes very large volumes of Concurrent OLTP INSERT transaction . Qs 2) If an alert is to be raised on addition of a new record to TABLE_2 :- a) Can trigger be used ? b) Should a cron job running every 5 min. look at TABLE_2 based on the time criteria generates the alert ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: piece-wise return of records from table
Scenario = To display a search result for all the employees in a org. [ Normally the query will retrieve 1000 records (say) ] 1) Query to retrieve just the first 10 of the thousand records 2) Then have a link called Next and Previous in the Web page . 3) When Link is clicked , for instance when the user click Next then show records from 11-20 .. Qs. Will the following SQL Query be EFFICIENT for such retrievals w.r.t. Performance Load ? Sorry for the incomplete details on front-end as I am located at a different GEO Location . Thanks indeed for the responses . -Original Message- Sent: Wednesday, July 23, 2003 7:30 AM To: VIVEK_SHARMA HiVivek , Use this SQL query to achive your piece wise operation... Select * from ( Select a.*, rownum r from ( Select * from tablename where condition order by column list ) a where rownum :HigerBound ) where r :LowerBound Let me know your output.. Thanks and Regards -Original Message- Sent: Tuesday, July 22, 2003 6:34 PM To: Multiple recipients of list ORACLE-L Hi, any chance that you let us know what your front end is? Web? CS-Forms? fill_in_the_appropriate? Come on, some more infos. Typing isn't that hard ;-) [EMAIL PROTECTED] 22.07.2003 14.49 Uhr CASE select * from table gives 1000 records (say) We want only first ten of these records to be returned to the front end then records 11 to 20 so on ... How may the above be optimally possible ? SQL or PL/SQL any for it ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
piece-wise return of records from table
CASE select * from table gives 1000 records (say) We want only first ten of these records to be returned to the front end then records 11 to 20 so on How may the above be optimally possible ? SQL or PL/SQL any for it ? Thanks
RE: inline views
Daniel, List Can you give more detail with an EXAMPLE please ? Thanks -Original Message- Sent: Friday, June 06, 2003 7:05 PM To: Multiple recipients of list ORACLE-L I have used an inline view to reduce network traffic when retrieiving data from a remote db. Instead of using a nested loop and making multiple trips, it made 1 trip and brought over all of the data. The query time was reduced from 30 minutes to 5 minutes. -- Daniel W. Fink http://www.optimaldba.com VIVEK_SHARMA wrote: Where are they advantageous to use where not ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RAC system Calls .......
- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito 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!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ravi Kulkarni INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
DataGuard Logical Standby Benchmark
We are Looking to do an In-house small Benchmark on DataGuard in it's various modes Logical Standby Database Oracle 9.2 Solaris 8 Any experiences of people , approach methodology , dos don'ts , Good Docs , Links ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SELECT Output Default Ordering ?
When NOT Giving any Order by Clause , How is the Output of the SELECT Clause ordered by Default ? Assuming There exists a Unique index on the Table Is some Rule followed ? NOTE Records may have been INSERTED into the Table in some manner differing from the Order of the data of the Unique index Key fields. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Migrating 8i OPS to RAC 9.2
Any Good Docs , Links ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Migrating 8i OPS to RAC 9.2
A Basic Qs Do the 2 Sets of Redo Log groups belonging to the different instances undergo Migration from 8i to 9i ? If so , how are the Online Redo Log groups individually Migrated from 8i to 9i ? Could NOT find any reference to individual Redo Log groups getting migrated in the Docs. Thanks -Original Message- Sent: Monday, June 23, 2003 2:09 PM To: Multiple recipients of list ORACLE-L Hi Vivek, In terms of technical, there is not much difference in normal database upgrade from 8i to 9i and OPS to RAC upgrade. You just need to install the 9i RAC software in the cluster and upgrade the database. But on the other side, Oracle does not see the OPS to RAC transformation as a MIGRATION or UPGRADE. They treat them as two different software components and you need to have a separate licence for RAC, that means you can not upgrade your OPS licence to RAC licence. Other than the installation/upgrade issues, RAC tuning requires deeper understandings of the cache fusion and the GCS,GES internals. For example, the cache fusion will not happen after certain number of lock converts/downgrade-upgrades and it will use DISK to tranfer the blocks between instances. Based on the nautre of the database and the workload, you may want to incrase or decrease the number of times a block can be trasfered over the wire and decide after X number of wire transfers, you can force the disk transfer. But you can still use the GC_FILES_TO_LOCKS parameters in the RAC instances if you know your application very well and I have seen the GC parameters in some of the Oracle TPC benchmarks. And the other interesting thing in RAC is the CR copies are created by the owner, not the requester. But in OPS the CR copy will be created by the requester and the owner has no responsibility other than just downgrading the locks (X to NULL). Like this there are so many small small things are changed in RAC comparing with OPS and some of the basic OPS concepts are no longer valid in RAC (and RAC Tuning). Good luck for your RAC Migration and do let us know if you have faced any of the complexities in the upgrade/migration/or whatever.. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Migration to 9.2.0.3.0 (64 Bit) on Solaris 8 issues
3,5050 TRIGGER 610.0 616.6 40 - Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RAC vs. OPS a comparison ?
RAC vs. OPS any comparison study , doc , link ? Need it for customer already on OPS , who is considering moving to RAC Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Data Guard modes performance impact
What may be the performance impact of using Different Data Guard implementation modes on the primary Standby Database ? We are Looking to do a small benchmark on the same . Any Benchmarks done , advise , best practices , must dos don'ts ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Fragmentation ?
Dennis , List What may be the OTHER forms of fragmentation ? What Number of Extents may be considered Critical warranting RE-Organization for Manually Sized Objects existing in LMTs ? Thanks for the great paper . Had read it previously though . Thanks -Original Message- Sent: Wednesday, June 11, 2003 8:25 PM To: Multiple recipients of list ORACLE-L Vivek Make sure you've read How to Stop Defragmenting and Start Living http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049 The authors point out that uniform extents stop fragmentation at the tablespace level. However they point out that there are other forms of fragmentation. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 11, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Qs What is the advantage of having dba_tablespaces.ALLOCATION_TYPE = UNIFORM OVER dba_tablespaces.ALLOCATION_TYPE = USER ? With ALLOCATION_TYPE = UNIFORM , NEXT_EXTENT Size of the Object can NOT be Manually defined in the Table Creation Script storage (NEXT Value) , which is allowed when having allocation_type=USER . Allocation_type=USER allows Objects with Different NEXT_EXTENT Sizes to be Created in the SAME LOCALLY managed Tablespace thus reduces Total Number of Extents for the respective Table. Our Application does have Objects of Dissimilar Sizes Existing tin the Same Tablespace . Does ALLOCATION_TYPE = UNIFORM automatically imply NO Fragmentation Irrespective of the Number of Extents of the Object (in a Locally Managed Tablespace) ? Does it further imply NO further need to Look at Number of Extents of an Object in a Locally Managed Tablespace ? NOTE Allocation_type can be made = USER by using the stored procedures :- dbms_space_admin.tablespace_migrate_from_local / dbms_space_admin.tablespace_migrate_to_local Am i still Lost in the World of Oracle 7 ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fragmentation ?
Qs What is the advantage of having dba_tablespaces.ALLOCATION_TYPE = UNIFORM OVER dba_tablespaces.ALLOCATION_TYPE = USER ? With ALLOCATION_TYPE = UNIFORM , NEXT_EXTENT Size of the Object can NOT be Manually defined in the Table Creation Script storage (NEXT Value) which is allowed when having allocation_type=USER . This allows Objects with Different NEXT_EXTENT Sizes to be Created in the SAME LOCALLY managed Tablespace thus reduces Total Number of Extents for the respective Table. Our Application does have Objects of Dissimilar Sizes Existing tin the Same Tablespace . Does ALLOCATION_TYPE = UNIFORM automatically imply NO Fragmentation Irrespective of the Number of Extents of the Object (in a Locally Managed Tablespace) ? Does it further imply NO further need to Look at Number of Extents of an Object in a Locally Managed Tablespace ? NOTE Allocation_type can be made = USER by using the stored procedures :- dbms_space_admin.tablespace_migrate_from_local / dbms_space_admin.tablespace_migrate_to_local Am i still Lost in the World of Oracle 7 ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
count(*) vs count(0)
Is there a performance equality between COUNT(*) COUNT(0) ? Is it same , indpendent of the Oracle Version ? Forgive the repeat raising , as this issue seems to have come before . Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
inline views
Where are they advantageous to use where not ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
library cache pin wait
Reloads dations --- -- -- -- -- BODY 3,466 11.3 3,532 17.91870 CLUSTER 12,9210.1 14,9530.1 00 INDEX 38,620 19.0 38,618 24.5 80 SQL AREA 1,627,3546.3 26,006,4720.8152,1168 TABLE/PROCEDURE1,206,3670.3 1,875,867 15.4158,7380 TRIGGER1,4050.2 1,713 44.45420 - shared_pool_reserved_size 104857600 shared_pool_size 419430400 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
library cache pin wait
5420 - shared_pool_reserved_size 104857600 shared_pool_size 419430400 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Migrating from Informix to Oracle 9.2 ( with O.S. Change from AIX to Linux )
What may be Easier ways of doing the same ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Off Topic : MSSQL Competence building
need to do MSSQL database Competence building More from the DBA's end though should have good functional knowledge of SQL prog. too Any Good BOOKS , Lists , Sites , Links , forums ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: skip scan index
') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: RMAN - Some basic Qs.
Gaja , Good indeed to see you back on the List . My Qs. in CAPITALS below :- -Original Message- Sent: Wednesday, April 02, 2003 2:14 AM To: Multiple recipients of list ORACLE-L All, The primary reason why RMAN does not generate excessive redo is because because it does not put the tablespace in hot backup mode. Thus any blocks belonging to a given tablespace that are modified before the END BACKUP command is processed, do not require block-level before and after images. Hence the reduction in redo generation. So how does RMAN backup without hot backups? RMAN is aware of the format of an Oracle datafile, and reads datafiles for the backup in DB_BLOCK_SIZE chunks. This is not the case with most OS utilities (tar, cpio, cp, dd etc.), which read files in 512-byte OS blocksize chunks. IS BACKUP DATA AS AT POINT-OF-TIME OF START OF RMAN BACKUP ? IF SO , HOW IS DATA INTEGRITY ACROSS DATAFILES MAINTAINED ? HOW ARE BLOCK DATA VALUES AS AT POINT OF BACKUP MAINTAINED WITHOUT STOPPING DMLs TO IT ? As a result, the issue of a fractured block (for which block-level before/after images are taken) on the destination where the backup is done, does not come into play in RMAN. MY UNDERSTANDING OF HOT BACKUP :- ASSUME 1 TABLESPACE HAS 2 DATAFILES DMLs HAPPENING ONLY TO FILE 2. AFTER SWITCHING TABLESPACE TO BEGIN BACKUP . ASSUMING SEQUENTIAL O.S. BACKUP OF DATAFILES , WHILE BACKUP OF THE 1ST FILE IS UNDERWAY , BEFORE IMAGES OF ALL DMLs HAPPENING TO FILE 2 ARE BEING ARCHIVED . THEREAFTER FILE 2 IS BACKED UP . FINALLY TABLESPACE END BACKUP IS ISSUED. THUS USING THE BEFORE IMAGES OF BLOCKS OF FILE 2 , THE TABLESPACE CAN BE BROUGHT TO DATA EXISTING AS AT POINT OF START OF HOT BACKUP. Hope that helps, Gaja --- Goulet, Dick [EMAIL PROTECTED] wrote: 1: RMAN does not generate excessive redo because it does not use the 'alter tablespace ... begin backup' command. It instead coordinates with dbwr, somehow, to be sure it is getting a consistent view of the tablespace. 2: The concept of an incremental backup is that you only backup the database blocks that have changed since the last incremental of the same or higher level. Somehow the date that the block changed is being managed, probably in the header somewhere. 3: If your reading a block for backup purposes, it's pretty easy to checksum it and compare with what's in the header just like the Oracle kernel does. 4: I've tried before with OmniBack and it's fairly simple. You only have to relink Oracle including a MML library file. The package you want to integrate with should have instructions. Dick Goulet -Original Message- Sent: Tuesday, April 01, 2003 10:19 AM To: Multiple recipients of list ORACLE-L Qs.1 Why is NO excessive Redo Generated wsing RMAN Backups unlike HOT Backups when taking backup of an OPEN Database which is undergoing User Transactions ? (Or am i mistaken ?) Qs.2 How does RMAN manage Incremental Database Backups ? What is the Concept behing it ? Qs.3 How does RMAN Check for Block Corruption while taking backup ? Qs.4 Is it easy to integrate 3rd part tools like Legato etc with RMAN for enabling taking of backups onto backup devices OR does it need an extensive setup ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego
RMAN - Some basic Qs.
Qs.1 Why is NO excessive Redo Generated wsing RMAN Backups unlike HOT Backups when taking backup of an OPEN Database which is undergoing User Transactions ? (Or am i mistaken ?) Qs.2 How does RMAN manage Incremental Database Backups ? What is the Concept behing it ? Qs.3 How does RMAN Check for Block Corruption while taking backup ? Qs.4 Is it easy to integrate 3rd part tools like Legato etc with RMAN for enabling taking of backups onto backup devices OR does it need an extensive setup ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
audit_trail=db Overhead ?
What would be the overhead of setting audit_trail=db in init.ora on the overall database performance ? NOTE - This is a heavily loaded Hybrid Database having 4000 concurrent users What optional settings may be better ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
POLL by Application Service process taking too Long
truss -fdD of an application service which received data in messages format passes it on to the Database gives the following output . 5.1130 poll(0xFFBFA0D8, 1, 24) = 1 0.0002 recv(17, " N 0 0 1 4 4 1".., 28, 0) = 28 0.0003 poll(0xFFBFA0D8, 1, 24) = 1 0.0001 recv(17, " + 0 0 0 2 2 U n i s e r".., 603, 0) = 603 Qs. Why is "poll" so longi.e. 5.1130seconds ? Thanks P.S. Will provide any info needed
Error SQL 02115 Code interpretation problem check COMMON_NAME usage
At a production databaseafter making a Pro C source changewe are getting the Orace error SQL-02115 "Code interpretation problem -- check COMMON_NAME usage" The same executableworks fine on another Dummy machine with atestdatabase Oracle 8.1.7 on Compaq Tru64 Unix 5.1 Production Machine model - GS Class $ oerr sql 2115 02115, 0, "Code interpretation problem -- check COMMON_NAME usage"// *Cause: With PRO*FORTRAN, this error occurs if the precompiler option// COMMON_NAME is specified incorrectly. With other Oracle// Precompilers, this error occurs when the precompiler cannot// generate a section of code.// *Action: With Pro*FORTRAN, when using COMMON_NAME to precompile two or// more source modules, make sure to specify a different common name// for each module. With other Oracle Precompilers, if the error// persists, call customer support for assistance. Thanks
RE: OEM - Automation of Start of Collection for Performance Reports
How can Start of Collection of ALL (Multiple) Performance Reports be automated in OEM 9.2 , before a Transactions' Run ? There Exist about 100 such Performance Reports in OEM . Manually starting Collection of these individually in OEM takes too much effort collection has to be started much before the actual transactions' run thereby containing lots of unnecessary data . Are we missing some way ? NOTE - Collection for All these STARTed performance reports can be STOPPED by a Single Click though in OEM . Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OEM - Automation of Start of Collection for Performance Reports
How can Start of Collection of Performance Reports be automated in OEM 9.2 , before a Transactions' Run ? There Exist about 100 such Performance Reports . Manually starting Collection of these individually takes too much effort collection has to be started much before the actual transactions' run thereby containing lots of unnecessary data . NOTE - Collection for All these STARTed performance reports can be STOPPED by a Single Click . -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
File Table Overflow on Oracle DB Server
We have the following query reg. an error on HP-UX ORacle DB server. We are encountering HPUX Error: 23: File table overflow' on the Oracle database server while executing stress tests for our application. We are not opening any files on the database server through the application still this error keeps coming after running the test for some duration. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RAID 5 impact on sys Utilization
Will sys Component of CPU Utilization be Higher on a RAID 5 Volume Versus a RAID 1+0/0+1 Volume on a Database Server ? Under peak Hybrid Loads the CPU Utilization's sys component is 50 % usr component is 50 % . Our Hybrid Application DATABASE is setup on a Hitachi Storage Box configured such that a SINGLE RAID 5 Volume spans across ALL the underlying Disks of the Storage Box . Qs. Could Hardware RAID 5 be the Cause of High sys on respective DB Server ? Qs. Any people having any experience of HIGH sys on SF15K machines with Hybrid Databases ? NOTE Application Software sits on a Different APP Server machine . Banking Application Database Oracle 8.1.7 Solaris 8 VXFS SF15K DB Server - 40 CPUs Hitachi Storage Box from HP Concurrent Oracle sessions = 5000 Will provide any info needed Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Lgwr , CKPT - Some Misc. basic Qs.
Any Brief info on following :- Qs lgwr writes the redo log buffer to disk when posted by the smon process under what conditions ? Qs What is metadata ? How are Changes to metadata managed in the log_buffer ? Qs Why does the Checkpoint process (CKPT) maintains a heartbeat with the control file at a periodic frequency of every 3 seconds ? Qs The number of Disks n in a stripe volume should preferably be a power of 2 namely (2,4,8,16 and so on) for Oracle Databases . For RAID levels 3, 5 and 7, the number of disks should preferably be n+1 . Why ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Lgwr , CKPT - Some Misc. Puzzlers ?
Any Brief info on following :- Qs lgwr writes the redo log buffer to disk when posted by the smon process under what conditions ? Qs What is metadata ? How are Changes to metadata managed in the log_buffer ? Qs Why does the Checkpoint process (CKPT) maintains a heartbeat with the control file at a periodic frequency of every 3 seconds ? Qs The number of Disks n in a stripe volume should preferably be a power of 2 namely (2,4,8,16 and so on) for Oracle Databases . For RAID levels 3, 5 and 7, the number of disks should preferably be n+1 . Why ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RAID 5 impact on sys Utilization
Will sys Component of CPU Utilization be Higher on a RAID 5 Volume Versus a RAID 1+0/0+1 Volume on a Database Server ? Under peak Hybrid Loads the CPU Utilization's sys component is 50 % usr component is 50 % . Our Hybrid Application DATABASE is setup on a Hitachi Storage Box configured such that a SINGLE RAID 5 Volume spans across ALL the underlying Disks of the Storage Box . Qs. Couls RAID 5 be the Cause of High sys ? NOTE Application Software sits on a Different APP Server machine . Banking Application Database Oracle 8.1.7 Solaris 8 VXFS SF15K DB Server - 40 CPUs Hitachi Storage Box from HP Concurrent Oracle sessions = 5000 Will provide any info needed Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Commit boundary - Stripe Unit Size Co-relation
Dennis , List A Slight Correction - The Batch Transaction does a COMMIT after INSERTS/UPDATEs Corresponding to 300 Logical Transactions . 5K is the Size of ONE Logical Transaction . Yes , The Batch job is pounding Banking Interest Transactions (Data) after Calculation (CPU-intensive) of the respective interest Amounts. Thanks -Original Message- Sent: Thursday, February 13, 2003 8:34 PM To: Multiple recipients of list ORACLE-L Vivek - I just wanted to make sure that your tests accurately reflect your production situation. The easiest tests to construct are the simplest ones. I've seen people draw wrong conclusions from those tests and even widely publish those conclusions. :-( Your original question just asked how commits related to disk writes. I just wanted to make sure you understood something about the method Oracle uses to decide when to write a block to disk. If you are just running a batch job that pounds inserts into Oracle, that is a really different situation from having many users and jobs that are doing many different things at the same time. My experience has been that those other activities very strongly affect Oracle's pattern of writing inserts and updates to disk. Mainly I have seen the insert or update job slow down a lot because it must wait for free disk blocks. Make sure you are measuring the wait statistics as you try these different tests. This also relates to the tuning of your disk subsystem by setting the stripe size. If you are just doing continual writes or updates, then it makes sense that a larger stripe size may be more efficient. And if that truly represents your production environment, go for it. There are also interactions between database block size, the operating system block size, and the disk subsystem stripe size. I have seen that discussed on this list, but I have no personal experience. ;-) Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 13, 2003 12:39 AM To: Multiple recipients of list ORACLE-L Dennis , Connor , List Further a very vague Qs. For Batch Jobs , we get extremely DIFFERENT performances when using DIFFERENT Stripe Unit Sizes of 4K , 64 K , 128K , 512K , 1M , 2M WITH 128K performing the BEST. Both SELECTs UPDATEs Hang almost indefinitely with 512K , 1M , 2M Stripe Unit Sizes . Average Size Data Inserted/Updated is 5K approx. acrross 7 Tables for one Application Transaction Thus there Seems Some Co-relation between SELECT/UPDATE/INSERT from Datafiles Different Underlying Stripe Unit Sizes . Any Comments ? Thanks -Original Message- Sent: Wednesday, February 12, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Vivek - Just to add to Connor's statements (wow am I being rash here), Oracle's strength is that it's architecture disconnects transactions from disk writes. On one hand, block may be modified several times before being written to disk (hot block, for instance). On the other hand, Oracle may need buffer space and write a block to disk before a transaction commits. But Oracle keeps track of all this and can straighten everything out if the transaction is rolled back or the system crashes. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 12:04 AM To: Multiple recipients of list ORACLE-L CASE - If Size of 1 INSERT/UPDATE Statement = 1K Stripe Unit Size is 128 K ? How will 1 COMMIT issued after 300 1K INSERT/UPDATE Statements DIFFER from 1 COMMIT issued after EACH 1K INSERT/UPDATE Statement with respect to Writing to the datafiles on the Underlying Striped Volume ? If 1 INSERT Statement Data is Written to the 1st Disk (say) of the Striped Volume , will a Repeat of the SAME INSERT Statement Write to a Different Underlying Disk of the same Striped Volume within the SAME Segment Extent ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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
RE: Commit boundary - Stripe Unit Size Co-relation
Andrew , List How does setting the parameters HASH_MULTIBLOCK_IO_COUNT and SORT_MULTIBLOCK_READ_COUNT affect the SQL Queries / Execution plans of the Optimizer ? Any experiences ? Thanks indeed -Original Message- Sent: Thursday, February 13, 2003 1:32 PM To: LazyDBA.com Discussion At the risk of talking this topic to death... Stripe size can be very important. The 'best' stripe size is usually the same as the multiple of the parameters DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT, although you might want to experiment with a value half or double this depending on your application. Also, set values of HASH_MULTIBLOCK_IO_COUNT and SORT_MULTIBLOCK_READ_COUNT so that these (x DB_BLOCK_SIZE) are related to the stripe size. The other factor that can affect things badly is the number of disks in the stripe set. A 'large' number of disks (eg 16) can severely impact write performance, but should be OK for read (each disk will store 1 8k Oracle block). Equally, a very small number of disks (eg 2 for stripe or 3 for RAID-5) is poor because of lower I/O throughput. You need to experiment to get the best, but the general recommendation is for 4 to 6 data disks per set (ie 5 to 7 for RAID5). Since Vivek's system performed best with 128K stripe size, I'd suggest that the defaults for the parameters are in use (eg DB_BLOCK_SIZE = 8192 and DB_FILE_MULTIBLOCK_READ_COUNT = 16). -Original Message- Sent: 13 February 2003 06:43 To: LazyDBA.com Discussion Dennis , Connor , List Further a very vague Qs. For Batch Jobs , we get extremely DIFFERENT performances when using DIFFERENT Stripe Unit Sizes of 4K , 64 K , 128K , 512K , 1M , 2M WITH 128K performing the BEST. Both SELECTs UPDATEs Hang almost indefinitely with 512K , 1M , 2M Stripe Unit Sizes . Average Size Data Inserted/Updated is 5K approx. acrross 7 Tables for one Application Transaction Thus there Seems Some Co-relation between SELECT/UPDATE/INSERT from Datafiles Different Underlying Stripe Unit Sizes . Any Comments ? Thanks -Original Message- Sent: Wednesday, February 12, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Vivek - Just to add to Connor's statements (wow am I being rash here), Oracle's strength is that it's architecture disconnects transactions from disk writes. On one hand, block may be modified several times before being written to disk (hot block, for instance). On the other hand, Oracle may need buffer space and write a block to disk before a transaction commits. But Oracle keeps track of all this and can straighten everything out if the transaction is rolled back or the system crashes. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 12:04 AM To: Multiple recipients of list ORACLE-L CASE - If Size of 1 INSERT/UPDATE Statement = 1K Stripe Unit Size is 128 K ? How will 1 COMMIT issued after 300 1K INSERT/UPDATE Statements DIFFER from 1 COMMIT issued after EACH 1K INSERT/UPDATE Statement with respect to Writing to the datafiles on the Underlying Striped Volume ? If 1 INSERT Statement Data is Written to the 1st Disk (say) of the Striped Volume , will a Repeat of the SAME INSERT Statement Write to a Different Underlying Disk of the same Striped Volume within the SAME Segment Extent ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Index update = Delete + insert ?
Vijay,List When Updating to the Field to the SAME (Previously Existent) Data Value , Does a DELETE RE-Insert of the Same Row to the index happen nevertheless ? Thanks -Original Message- Sent: Wednesday, February 12, 2003 12:50 PM To: VIVEK_SHARMA Hi Vivek, Index rows are first deleted and then inserted rather than update... regards, Vijaya Chander V.S -Original Message- Sent: Wednesday, February 12, 2003 12:43 PM To: LazyDBA.com Discussion Is an index Fields' update actually a DELETE followed by an INSERT of the index row ? Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Commit boundary - Stripe Unit Size Co-relation
Dennis , Connor , List Further a very vague Qs. For Batch Jobs , we get extremely DIFFERENT performances when using DIFFERENT Stripe Unit Sizes of 4K , 64 K , 128K , 512K , 1M , 2M WITH 128K performing the BEST. Both SELECTs UPDATEs Hang almost indefinitely with 512K , 1M , 2M Stripe Unit Sizes . Average Size Data Inserted/Updated is 5K approx. acrross 7 Tables for one Application Transaction Thus there Seems Some Co-relation between SELECT/UPDATE/INSERT from Datafiles Different Underlying Stripe Unit Sizes . Any Comments ? Thanks -Original Message- Sent: Wednesday, February 12, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Vivek - Just to add to Connor's statements (wow am I being rash here), Oracle's strength is that it's architecture disconnects transactions from disk writes. On one hand, block may be modified several times before being written to disk (hot block, for instance). On the other hand, Oracle may need buffer space and write a block to disk before a transaction commits. But Oracle keeps track of all this and can straighten everything out if the transaction is rolled back or the system crashes. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 12:04 AM To: Multiple recipients of list ORACLE-L CASE - If Size of 1 INSERT/UPDATE Statement = 1K Stripe Unit Size is 128 K ? How will 1 COMMIT issued after 300 1K INSERT/UPDATE Statements DIFFER from 1 COMMIT issued after EACH 1K INSERT/UPDATE Statement with respect to Writing to the datafiles on the Underlying Striped Volume ? If 1 INSERT Statement Data is Written to the 1st Disk (say) of the Striped Volume , will a Repeat of the SAME INSERT Statement Write to a Different Underlying Disk of the same Striped Volume within the SAME Segment Extent ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Commit boundary Stripe Unit Size Co-relation
CASE - If Size of 1 INSERT/UPDATE Statement = 1K Stripe Unit Size is 128 K ? How will 1 COMMIT issued after 300 1K INSERT/UPDATE Statements DIFFER from 1 COMMIT issued after EACH 1K INSERT/UPDATE Statement with respect to Writing to the datafiles on the Underlying Striped Volume ? If 1 INSERT Statement Data is Written to the 1st Disk (say) of the Striped Volume , will a Repeat of the SAME INSERT Statement Write to a Different Underlying Disk of the same Striped Volume within the SAME Segment Extent ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
vxio:vol_maxio (Veritas) impact on Database performance ?
Setting of parameter vxio:vol_maxio (Veritas) Nature of Application Database = Hybrid in Nature Application Database undergoing MOSTLY Index Scan Operations (db file sequential reads). The underlying OS Call is pread64 . Qs. Anybody has used the parameter in Production Databases ? Qs. Is there any known Overhead/down-side with using the parameter ? Qs. What is the impact on Database performance ? Qs. What Value may be considered Advisable ? Qs. How may the same be Calculated A SAMPLE ARTICLE FROM ORACLE METALINK :- * * Unix Installation/OS: RDBMS Technical Forum Displayed below are the messages of the selected thread. Thread Status: Closed RDBMS Version: 8.1.6.2 Operating System and Version: solaris 2.6 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Product Version: vxio: vol_maxio What is the recommended setting for vol_maxio (when I am using VxVm and VxFs ) ? When I set to vol_maxio=65535 (which means 32 MB ) the querries runs very fast . but is it desirable to set it that high ? How do I determine the most desirable value ? what other impacts it might have ? thanks syed - --- It depends. vol_maxio determines the maximum size of the physical IO that veritas layer can handle before breaking up the IO in to multiple IO requests. If your application uses mostly sequential read, then the application will benefit from using bigger vol_maxio value. But having a value of 32MB may not provide any benefits. maxphys kernel parameter need to be increased to take advantage of the bigger IO size request too. Further, Oracle has a limit of 1MB for the max IO size that oracle can issue(at least in 8i releases). So, any value beyond 1MB may not provide any benefit. You also need to look at your disk subsystem. If you have striped disks, then it is better to match vol_maxio value and the stripe width. Say, if the stripe size is 128KB with 4 disks (hence the stripe width 512KB) then you want to set your vol_maxio to match 512KB, but not exceeding 1MB. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*Net Connection under-performing
tnsping for tbasun Database (below) 10 ms whereas tnsping for bby01 Database (below) is 10,000 ms Qs. Is is due to the Multiple ADDRESS Entries with LOAD_BALANCE = yes for tbasun ? NOTE - tnsnames.ora file put on the Database(DB) Server itself tnsping also being issued from the DB Server itself . Thanks tnsnames.ora being used :- tbasun = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1530)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1531)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1532)) (LOAD_BALANCE = yes) ) (CONNECT_DATA = (SID = tbasun) (SRVR = DEDICATED) ) ) bby01 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1524)) (LOAD_BALANCE = yes) ) (CONNECT_DATA = (SID = bby01) (SRVR = DEDICATED) ) ) -Original Message- Sent: Tuesday, February 04, 2003 8:20 PM To: VIVEK_SHARMA Could be a DNS issue. Do you have the host name in the tnsnames.ora, or the IP address for both the databases? To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Ferenec , List tnsnames.ora has been setup on the Database Server itself tnsping is being issued also from the Database Server itself to avoid any network path. For the SAME ORACLE_HOME using the SAME tnsnames.ora file tnsping to the 2 Databases on the DB Server machine is taking 10,000 ms 10 ms respectively Can there be any overheads / other concerns with usign this parameter , as respective machines are Production Servers ? Thanks indeed -Original Message- From: Ferenc Mantfeld [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 04, 2003 1:10 AM To: Multiple recipients of list ORACLE-L Subject: Re: SQL*Net Connection under-performing Vivek I had similar issues with a large customer. I solved this by setting up pre-spawned dedicated processes in the listener. Full in structions available on Metalink. I brought down 1 minute login times to under 10 seconds, by configuring 20 - 40 prespawned connections. Let me know how it works. This can be done relatively easily even in production, configure listener.ora and then simply bounce the listener. Your initial login after that may take you a minute until all the prespawned connections are initiated. HTH. Regards : Ferenc Mantfeld Dreaming costs you nothing. Not dreaming costs you everything. - Original Message - From: VIVEK_SHARMA To: Multiple recipients of list ORACLE-L Sent: Monday, February 03, 2003 6:58 PM Subject: SQL*Net Connection under-performing SITUATION - With DB Server CPU Idle being 0 % load average 60-70 % :- tnsping Connect to one of the Databases (1) takes about 10,000 ms truss -fdD tnsping connect string 29504: 20.0006 18.8113 read(3, \0 K\0\004\0\0\0 \0\0 ?.., 2064) = 75 Thus 18 seconds taken as shown above Whereas to another Database (2) tnsping takes only 10 ms on the Same machine DETAILS :- 0) 1 GBPS network path exits from APP Servers to the Database Server. 1) Database 1 Has about 3500 Oracle processes connecting to it Database 2 Has about 1500 Oracle processes connecting to it 2) Both Databases have Different ORACLE_HOMEs 3) sqlnet.ora of Database 1 :- AUTOMATIC_IPC=OFF TRACE_LEVEL_CLIENT=OFF TRACE_LEVEL_SERVER=OFF TNSPING.TRACE_LEVEL=OFF NO sqlnet.ora exists on Database 2 4) NO process.dat , regid.dat exists in ORACLE_HOME/network/log Dir of ORACLE_HOME for Both Database 5) Multiple listener processes are Running for BOTH Databases Qs How may the issue be approached ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
vxio:vol_maxio (Veritas) impact on Database perf ?
Setting of parameter vxio:vol_maxio (Veritas) Nature of Database Hybrid in Nature Application Database undergoing MOSTLY Index Scan Operations (db file sequential reads). The underlying OS Call is pread64 . Qs. Anybody has used the parameter in Production Databases ? Qs. Is there any known Overhead/down-side with using the parameter ? Qs. What is the impact on Database performance ? Qs. What Value may be considered Advisable ? Qs. How may the same be Calculated A SAMPLE ARTICLE FROM METALINK :- ** Unix Installation/OS: RDBMS Technical Forum Displayed below are the messages of the selected thread. Thread Status: Closed RDBMS Version: 8.1.6.2 Operating System and Version: solaris 2.6 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Product Version: vxio: vol_maxio What is the recommended setting for vol_maxio (when I am using VxVm and VxFs ) ? When I set to vol_maxio=65535 (which means 32 MB ) the querries runs very fast . but is it desirable to set it that high ? How do I determine the most desirable value ? what other impacts it might have ? thanks syed It depends. vol_maxio determines the maximum size of the physical IO that veritas layer can handle before breaking up the IO in to multiple IO requests. If your application uses mostly sequential read, then the application will benefit from using bigger vol_maxio value. But having a value of 32MB may not provide any benefits. maxphys kernel parameter need to be increased to take advantage of the bigger IO size request too. Further, Oracle has a limit of 1MB for the max IO size that oracle can issue(at least in 8i releases). So, any value beyond 1MB may not provide any benefit. You also need to look at your disk subsystem. If you have striped disks, then it is better to match vol_maxio value and the stripe width. Say, if the stripe size is 128KB with 4 disks (hence the stripe width 512KB) then you want to set your vol_maxio to match 512KB, but not exceeding 1MB. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Find index row length ?
How to find approx. index row length with ease ? There is seemingly some Easy way whereby after analyze of the Index , the Column sizes which get populated in some internal object can simply be Summed up . Oracle 8.1.7 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*Net Connection under-performing
Rajesh , List tnsping for tbasun Database (below) 10 ms whereas tnsping for bby01 Database (below) is 10,000 ms Qs. Is is due to the Multiple ADDRESS Entries with LOAD_BALANCE = yes for tbasun ? NOTE - tnsnames.ora file put on the Database Server itself tnsping also being issued from the SAME (DB Server) Thanks tnsnames.ora being used :- tbasun = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1530)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1531)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1532)) (LOAD_BALANCE = yes) ) (CONNECT_DATA = (SID = tbasun) (SRVR = DEDICATED) ) ) bby01 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.14.236)(PORT = 1524)) (LOAD_BALANCE = yes) ) (CONNECT_DATA = (SID = bby01) (SRVR = DEDICATED) ) ) -Original Message- Sent: Tuesday, February 04, 2003 8:20 PM To: VIVEK_SHARMA Could be a DNS issue. Do you have the host name in the tnsnames.ora, or the IP address for both the databases? To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Ferenec , List tnsnames.ora has been setup on the Database Server itself tnsping is being issued also from the Database Server itself to avoid any network path. For the SAME ORACLE_HOME using the SAME tnsnames.ora file tnsping to the 2 Databases on the DB Server machine is taking 10,000 ms 10 ms respectively Can there be any overheads / other concerns with usign this parameter , as respective machines are Production Servers ? Thanks indeed -Original Message- From: Ferenc Mantfeld [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 04, 2003 1:10 AM To: Multiple recipients of list ORACLE-L Subject: Re: SQL*Net Connection under-performing Vivek I had similar issues with a large customer. I solved this by setting up pre-spawned dedicated processes in the listener. Full in structions available on Metalink. I brought down 1 minute login times to under 10 seconds, by configuring 20 - 40 prespawned connections. Let me know how it works. This can be done relatively easily even in production, configure listener.ora and then simply bounce the listener. Your initial login after that may take you a minute until all the prespawned connections are initiated. HTH. Regards : Ferenc Mantfeld Dreaming costs you nothing. Not dreaming costs you everything. - Original Message - From: VIVEK_SHARMA To: Multiple recipients of list ORACLE-L Sent: Monday, February 03, 2003 6:58 PM Subject: SQL*Net Connection under-performing SITUATION - With DB Server CPU Idle being 0 % load average 60-70 % :- tnsping Connect to one of the Databases (1) takes about 10,000 ms truss -fdD tnsping connect string 29504: 20.0006 18.8113 read(3, \0 K\0\004\0\0\0 \0\0 ?.., 2064) = 75 Thus 18 seconds taken as shown above Whereas to another Database (2) tnsping takes only 10 ms on the Same machine DETAILS :- 0) 1 GBPS network path exits from APP Servers to the Database Server. 1) Database 1 Has about 3500 Oracle processes connecting to it Database 2 Has about 1500 Oracle processes connecting to it 2) Both Databases have Different ORACLE_HOMEs 3) sqlnet.ora of Database 1 :- AUTOMATIC_IPC=OFF TRACE_LEVEL_CLIENT=OFF TRACE_LEVEL_SERVER=OFF TNSPING.TRACE_LEVEL=OFF NO sqlnet.ora exists on Database 2 4) NO process.dat , regid.dat exists in ORACLE_HOME/network/log Dir of ORACLE_HOME for Both Database 5) Multiple listener processes are Running for BOTH Databases Qs How may the issue be approached ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Version Controlling in Oracle database - Ideas ?
Looking for a solution to version controlling of database objects in Oracle. The scenario is like this: Multiple people simultaneously work on the same database object in TOAD. How can we implement a version controlling feature. Putting this in simple words, If X checks out a file from TOAD and Y needs to work on the same file (assuming they have the same privilege), Y should not be allowed to work on the object till X finishes working on it. We want to put a restriction on the database objects such that simultaneously no two users should be able to work it. Integrating VSS with TOAD was not acceptable since it would be file based(saving the .sqls). Qs. Is there any feature available in Oracle(with oracle 8.1.7) or any third party tool that does this ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Version Controlling in Oracle database - Ideas ?
Title: RE: Version Controlling in Oracle database - Ideas ? Is it available in the Market OR is there a special procurement Channel for it ? Any Details please , Links , Docs ? Thanks -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Friday, February 07, 2003 11:04 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Version Controlling in Oracle database - Ideas ? I believe this feature coming in new TOAD ... its available in Beta though ... I think it is called 'Team Coding ... something' Another product is Oracle SCM ... Raj -Original Message- From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]] Sent: Friday, February 07, 2003 10:59 AM To: Multiple recipients of list ORACLE-L Subject: Version Controlling in Oracle database - Ideas ? Looking for a solution to version controlling of database objects in Oracle. The scenario is like this: Multiple people simultaneously work on the same database object in TOAD. How can we implement a version controlling feature. Putting this in simple words, If X checks out a file from TOAD and Y needs to work on the same file (assuming they have the same privilege), Y should not be allowed to work on the object till X finishes working on it. We want to put a restriction on the database objects such that simultaneously no two users should be able to work it. Integrating VSS with TOAD was not acceptable since it would be file based(saving the .sqls). Qs. Is there any feature available in Oracle(with oracle 8.1.7) or any third party tool that does this ? so send the HELP command for other information (like subscribing).
9i Reliability means ?
Folks We are looking to present a Talk on :- Reliability with respect to Oracle 9i DB How does one even approach this subject ? What Sub-Topics would this envisage other than the following ? 1) Oracle Architecture of Data Storage 2) Backup Recovery Do Standby Databases Database Replication come under the subject Reliability ? Using our Application Product we have Experience on Hybrid Oracle Databases in :- 1) High End Database Performance Benchmarks 2) Regular Database Recoveries from Block Corruptions , Crashes 3) Keeping Production Databases of sizes upto 1 TB working optimally . Do you believe we can give a Talk on the Subject Reliability ? The presentation might have in attendance some of the Leading Oracle minds . Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*Net Connection under-performing
Title: Details about Tar logged Ferenec , List tnsnames.ora has been setup on the Database Server itself tnsping is being issued also from the Database Server itself to avoid any network path. Forthe SAME ORACLE_HOME using the SAMEtnsnames.ora file tnsping tothe 2 Databases on the DB Server machine is taking 10,000 ms 10 ms respectively Can there be any overheads / other concerns with usign this parameter , as respective machines are Production Servers ? Thanks indeed -Original Message-From: Ferenc Mantfeld [mailto:[EMAIL PROTECTED]]Sent: Tuesday, February 04, 2003 1:10 AMTo: Multiple recipients of list ORACLE-LSubject: Re: SQL*Net Connection under-performing Vivek I had similar issues with a large customer. I solved this by setting up pre-spawned dedicated processes in the listener. Full in structionsavailable on Metalink. I brought down 1 minute login times to under 10 seconds, by configuring 20 - 40 prespawned connections. Let me know how it works. This can be done relatively easily even in production, configure listener.ora and then simply bounce the listener. Your initial login after that may take you a minute until all the prespawned connections are initiated. HTH. Regards : Ferenc MantfeldDreaming costs you nothing. Not dreaming costs you everything. - Original Message - From: VIVEK_SHARMA To: Multiple recipients of list ORACLE-L Sent: Monday, February 03, 2003 6:58 PM Subject: SQL*Net Connection under-performing SITUATION - WithDBServerCPU Idle being 0 % load average 60-70 % :- tnsping Connect toone of the Databases (1)takes about 10,000 ms truss -fdD tnsping connect string 29504: 20.0006 18.8113 read(3, "\0 K\0\004\0\0\0 "\0\0 ?".., 2064) = 75 Thus 18 seconds taken as shown above Whereasto another Database (2) tnsping takes only 10 ms on the Same machine DETAILS :- 0) 1 GBPS network path exits from APP Servers to the Database Server. 1) Database 1 Has about 3500 Oracle processes connecting to it Database2 Has about 1500 Oracle processes connecting to it 2) Both Databases have Different ORACLE_HOMEs 3) sqlnet.oraof Database 1 :- AUTOMATIC_IPC=OFFTRACE_LEVEL_CLIENT=OFFTRACE_LEVEL_SERVER=OFFTNSPING.TRACE_LEVEL=OFF NO sqlnet.ora exists onDatabase 2 4) NO process.dat , regid.dat exists in ORACLE_HOME/network/log Dir of ORACLE_HOME for Both Database 5) Multiple listener processes are Running for BOTH Databases Qs How may the issue be approached ?
SQL*Net Connection under-performing
Title: Details about Tar logged SITUATION - WithDBServerCPU Idle being 0 % load average 60-70 % :- tnsping Connect toone of the Databases (1)takes about 10,000 ms truss -fdD tnsping connect string 29504: 20.0006 18.8113 read(3, "\0 K\0\004\0\0\0 "\0\0 ?".., 2064) = 75 Thus 18 seconds taken as shown above Whereasto another Database (2) tnsping takes only 10 ms on the Same machine DETAILS :- 0) 1 GBPS network path exits from APP Servers to the Database Server. 1) Database 1 Has about 3500 Oracle processes connecting to it Database2 Has about 1500 Oracle processes connecting to it 2) Both Databases have Different ORACLE_HOMEs 3) sqlnet.oraof Database 1 :- AUTOMATIC_IPC=OFFTRACE_LEVEL_CLIENT=OFFTRACE_LEVEL_SERVER=OFFTNSPING.TRACE_LEVEL=OFF NO sqlnet.ora exists onDatabase 2 4) NO process.dat , regid.dat exists in ORACLE_HOME/network/log Dir of ORACLE_HOME for Both Database 5) Multiple listener processes are Running for BOTH Databases Qs How may the issue be approached ?
RE: Memory Free up Failing on Solaris 8 - Off-topic
Hi
brk, sbrk functions are used for DATA segments and we have problem with HEAP. Malloc
allocates space onto heap and not in DATA segment.
Seemingly the man page imply that it should not be used in conjunction with malloc,
calloc ,
that we are using.
Pasting from man pages :-
USAGE
The behavior of brk() and sbrk() is unspecified if an appli-
cation also uses any other memory functions (such as
malloc(3C), mmap(2), free(3C)).
Qs. Is any alternative function which can be used OR am i missing something ?
Thanks
-Original Message-
Sent: Monday, February 03, 2003 1:19 PM
To: Multiple recipients of list ORACLE-L
hmm,
free() doesn't do sbrk() with a negative to reduce the process space. So yes
the space stays allocated.
Anjo.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, February 03, 2003 7:58 AM
Hi
SITUATION - On a production APP Server having about 4000 Concurrent
application processes ,
the memory allocated does NOT seem to be getting freed even though the
application program is issuing the respective call to Free the memory .
Qs Any /etc/system parameters , OS patches which should help ?
Qs Is our approach of using the pmap command Correct ?
else what Command to find the Private memory taken up by a process would
be advisable ?
Qs How to find the Total Amount of Swap Consumed ?
[ We probably do NOT know how to interpret the vmstat output Correctly ,
top/swap -s commands show an output Differing greatly from df -k output
for /tmp filesystem ]
Qs Any Body has encountered such as situation before ?
Configuration -
Solaris 2.8
Patch - Generic 108528-16 patch
Machine SF15K
CPUs = 36
RAM = 96 GB
We created a Small Sample C program which allocates FREEs memory but
found that even after
FREEing , the memory does NOT show as Freed in the pmap output . Is the
following approach correct ?
DETAILS :-
@ SAMPLE C PROGRAM @
#include stdio.h
main()
{
char *abc = NULL ;
int i = 0 ;
for (i = 1; i 10; i++){
printf(allocating [%d] bytes\n, 10*1024*(i+1));
abc = (char *)malloc(10*1024*(i+1));
memset(abc, '\0', 10*1024*(i+1));
getchar();
free(abc);
printf(Freeing [%d]\n, i);
getchar();
}
exit() ;
}
@
RUN Output :-
STEP 1 - allocating [20480] bytes
pmap -x PID of Above program process
Address Kbytes Resident Shared Private Permissions Mapped File
0001 8 8 8 - read/exec a.out
0002 8 8 - 8 read/write/exec a.out
00022000 24 24 - 24 read/write/exec [ heap ]
FF28 688 688 688 - read/exec libc.so.1
FF33C000 32 32 - 32 read/write/exec libc.so.1
FF37 16 16 16 - read/exec libc_psr.so.1
FF39 8 8 8 - read/exec libdl.so.1
FF3A 8 8 - 8 read/write/exec [ anon ]
FF3B 152 152 152 - read/exec ld.so.1
FF3E6000 8 8 - 8 read/write/exec ld.so.1
FFBEC000 16 16 - 16 read/write/exec [ stack ]
-- -- -- --
total Kb 968 968 872 96
THE Private memory allocated by the [ heap ] is 24 K
STEP 2 - Freeing the memory allocated in the above Step
Freeing [1]
pmap -x PID of Above program process
Address Kbytes Resident Shared Private Permissions Mapped File
00022000 24 24 - 24 read/write/exec [ heap ]
RESULT - THE Private memory allocated previously does NOT get Freed
[ heap ] continues to be 24 K
STEP 3 -
allocating [30720] bytes
pmap -x PID of Above Program process
Address Kbytes Resident Shared Private Permissions Mapped File
00022000 32 32 - 32 read/write/exec [ heap ]
STEP 4 - Freeing the memory allocated in the above Step
Freeing [2]
pmap -x PID of Above program process
Address Kbytes Resident Shared Private Permissions Mapped File
00022000 32 32 - 32 read/write/exec [ heap ]
RESULT - THE Private memory allocated previously in Step 3 does NOT get
Freed
[ heap ] continues to be 32 K
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: VIVEK_SHARMA
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note
Memory Free up Failing on Solaris 8 - Off-topic
Hi
SITUATION - On a production APP Server having about 4000 Concurrent application
processes ,
the memory allocated does NOT seem to be getting freed even though the application
program is issuing the respective call to Free the memory .
Qs Any /etc/system parameters , OS patches which should help ?
Qs Is our approach of using the pmap command Correct ?
else what Command to find the Private memory taken up by a process would be
advisable ?
Qs How to find the Total Amount of Swap Consumed ?
[ We probably do NOT know how to interpret the vmstat output Correctly ,
top/swap -s commands show an output Differing greatly from df -k output for /tmp
filesystem ]
Qs Any Body has encountered such as situation before ?
Configuration -
Solaris 2.8
Patch - Generic 108528-16 patch
Machine SF15K
CPUs = 36
RAM = 96 GB
We created a Small Sample C program which allocates FREEs memory but found that even
after
FREEing , the memory does NOT show as Freed in the pmap output . Is the following
approach correct ?
DETAILS :-
@ SAMPLE C PROGRAM @
#include stdio.h
main()
{
char *abc = NULL ;
int i = 0 ;
for (i = 1; i 10; i++){
printf(allocating [%d] bytes\n, 10*1024*(i+1));
abc = (char *)malloc(10*1024*(i+1));
memset(abc, '\0', 10*1024*(i+1));
getchar();
free(abc);
printf(Freeing [%d]\n, i);
getchar();
}
exit() ;
}
@
RUN Output :-
STEP 1 - allocating [20480] bytes
pmap -x PID of Above program process
Address Kbytes Resident Shared Private Permissions Mapped File
0001 8 8 8 - read/exec a.out
0002 8 8 - 8 read/write/exec a.out
00022000 24 24 - 24 read/write/exec [ heap ]
FF28 688 688 688 - read/exec libc.so.1
FF33C000 32 32 - 32 read/write/exec libc.so.1
FF37 16 16 16 - read/exec libc_psr.so.1
FF39 8 8 8 - read/exec libdl.so.1
FF3A 8 8 - 8 read/write/exec [ anon ]
FF3B 152 152 152 - read/exec ld.so.1
FF3E6000 8 8 - 8 read/write/exec ld.so.1
FFBEC000 16 16 - 16 read/write/exec [ stack ]
-- -- -- --
total Kb 968 968 872 96
THE Private memory allocated by the [ heap ] is 24 K
STEP 2 - Freeing the memory allocated in the above Step
Freeing [1]
pmap -x PID of Above program process
Address Kbytes Resident Shared Private Permissions Mapped File
00022000 24 24 - 24 read/write/exec [ heap ]
RESULT - THE Private memory allocated previously does NOT get Freed
[ heap ] continues to be 24 K
STEP 3 -
allocating [30720] bytes
pmap -x PID of Above Program process
Address Kbytes Resident Shared Private Permissions Mapped File
00022000 32 32 - 32 read/write/exec [ heap ]
STEP 4 - Freeing the memory allocated in the above Step
Freeing [2]
pmap -x PID of Above program process
Address Kbytes Resident Shared Private Permissions Mapped File
00022000 32 32 - 32 read/write/exec [ heap ]
RESULT - THE Private memory allocated previously in Step 3 does NOT get Freed
[ heap ] continues to be 32 K
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: VIVEK_SHARMA
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
MAX Length of of Name for Table / Index ?
What is the MAX possible Length of of Name for Table / Index ? Why ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RMAN backup - basic Qs
Is Excessive Redo Generated during RMAN OPEN Database backup using backup sets as happens in case of HOT Backup ? If NOT , Why ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Different Backups - A Comparartive analysis
Need to Do a Comparative analysis of General Characteristics of HOT , COLD , exp , RMAN backups What additional Headings can be Explored ? Some Feadings below :- 1) Database Status during backup - UP / Down 2) Time of Backup 3) Restoration effort time 4) Reovery facility 5) Dependency on Database Size 6) Backup Devices - Can exp be taken Directly on TAPES Extracted therefrom confidently OR does ? 7) Ease of Backup 8) De-Skilling of Backup Job 9) Dependency on Oracle Software 10) Performance Overheads during Backup 11) Incremental Backups - Do HOT / Cold Backups allow any incremental Backups ? Is incremental Backup Disabled from 8i onwards for exp ? 12) Limitations - Compress/Splitting of exp files etc ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Different Backups - A Comparartive analysis
Is SQL BackTrack a product like RMAN (from Oracle Corp. or 3rd party )? -Original Message- Sent: Tuesday, January 14, 2003 2:09 AM To: Multiple recipients of list ORACLE-L Don't know really. Just thought that it should probably be included, then I was hoping to find out... :-) - Original Message - To: [EMAIL PROTECTED]; Tim Gorman [EMAIL PROTECTED] Sent: Monday, January 13, 2003 10:49 AM On Monday 13 January 2003 06:03, Tim Gorman wrote: Another question: should SQL BackTrack be included for consideration? What does SQL BackTrack to that RMAN doesn't do? Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
2 basic Qs. on export
1) Large Export Dumps , if Directly Exported to TAPE Devices , Can Import be safely Done therefrom ? FILE=Device name OR is it advisable to compress / Split the Export Dump Files onto Storage Box thereafter backup the Same onto Tape ? 2) With Oracle 8i 9i Does export backup fired at a certain Time take ALL Objects's Data existing as at that point in time ? Assuming exp Command is issued to export a Full Database Containing many Tables at 10:00 hours . Assuming Update is Done to Some Table which is yet to be Exported at 10:01 , Will the export backup contain the Updated OR NON-Updated Data ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
rsh on Unix hangs - OT
The problem with rsh is as follows. The actual script is as follows. echo subbu su finacle -c '. /etc/b2k/ncb/com/commonenv.com;cd /finacle/ncb/b2kcomp1/3.0/bin;./stops' echo After stops sleep 5 su finacle -c '. /etc/b2k/ncb/com/commonenv.com;cd /finacle/ncb/b2kcomp1/3.0/bin;./runs' echo after runs echo subbu When I execute this script from a remote machine as rsh archie /finacle/services/failover/back110102/subbu.com The output is as follows subbu After stops after runs subbu After that that the rsh command is hanging. Actually it is running all the commands of remote script and then it is hanging. Any pointers to solve this problem. This is required as a part of failover between two machines. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Simulate Database Writes
Instead of making Actual Database Writes is it possible to Simulate Such Writes by using a suitable program in order to Check the thruput of a newly configured RAID Storage Box ? Nature of Application - Hybrid If so What should be the Size of the Writes ? Our DB_BLOCK_SIZE is 8K Is there a Dependency on the Size Volume of our Batch Job related Database Commits ? Any Other Boundary conditions need to be taken into consideration ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
MAX Number of Records in Cursor ?
A Relationship manager needs to broadcast mail to all the Customers . There are around 102,847 customer To achieve this functionality in our code we are opening a cursor, fetching each user id from a table and inserting into a mail table for each fetch. Mail is getting Generated for only 7130 Customers . There are no oracle errors reported in the log files . Is there any size limitation in oracle while opening/fetching a cursor as the No. of records to be fetched are 102,847 ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
explain plan parameter meanings
Title: Message In the explain plan what is meant by"cost","card" and"byte" ? How may they be interpreted practically ? Any Docs , Links on the Same ? Execution Plan-- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=76) 1 0 NESTED LOOPS (Cost=6 Card=1 Bytes=76) 2 1 NESTED LOOPS (Cost=5 Card=1 Bytes=50) 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'GENERAL_ACCT_MAST_TA BLE' (Cost=3 Card=1 Bytes=17) 4 3 INDEX (UNIQUE SCAN) OF 'IDX_GAM_FORACID' (UNIQUE) (Cost=2 Card=1) 5 2 TABLE ACCESS (BY INDEX ROWID) OF 'ASHT_DTD' (Cost=2 Card=5830 Bytes=192390) 6 5 INDEX (RANGE SCAN) OF 'IDX_ASHT_DTD_ACID' (NON-UNIQUE) (Cost=1 Card=5830) 7 1 TABLE ACCESS (BY INDEX ROWID) OF 'ASHT_TDTD' (Cost=1 Card=2105 Bytes=54730) 8 7 INDEX (UNIQUE SCAN) OF 'IDX_ASHT_TDTD' (UNIQUE) Thanks
RE: log file sync Wait
-- --- -- --- redo allocation kcrfwr: redo allocation 0469 510 redo allocation kcrfwi: before write 0 78 31 redo allocation kcrfwi: more space 0 20 26 redo writing kcrfsr 0970 72 redo writing kcrfss 0209 1,242 redo writing kcrfwi: after write 0170 19 redo writing kcrfwcr 0 16 32 redo writing Waiter Sleeps Value = 1,242 IS LARGER THAN THAT OF OTHER EVENTS Question: Was log file write really number two, or have you knocked out one or two lines between the two log-related waits ? log file parallel write IS INDEED THE SECOND WAIT AFTER Log file sync IN STATSPACK Log file syncs are from the sessions, log file writes are from LGWR A log file sync is a call from a session to lgwr to write some log buffer to disc. As such, you could get multiple sessions calling at about the same time - and only the first one in gets lgwr to write, the rest have to wait until lgwr returns and notices that there is now a queue and does a piggyback write. Consequently, it is possible on a highly concurrent system for log file sync to have far more WAITS then log file write, and therefore look a much bigger problem than it really is. However, in your case, the number of log file sync WAITS is about the same as the number of log file write WAITS - so the fact that the TIME is five times as long suggests that concurrency of waits is not the issue, and you may have a proper problem. I suspect that the problem is the number of processes running on your system. Session A issues a log file sync, and goes off the run queue; some time later, lgwr gets the message and writes and posts session A to allow it to go back on the run queue. Session A sits on the run queue for ages, and finally becomes runnable. Solution - look at MTS, or get more CPUs on the box. IS THERE ANY WAY TO INCREASE THE NUMBER OF LGWR PROCESSES IN A SINGLE INSTANCE DATABASE ? But having said that - do check if any sessions are actually noticing a significant loss of time due to log file sync before worry about it. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 02 January 2003 07:48 What ALL may be Done to Address the Following ? Any /etc/system , init.ora parameter Changes too ? Moving the Online Redo Logfiles onto RAID 1 NOT possible as that may warrant Additional Hardware . Moreover T3+ does NOT Support RAID 1 (Only RAID 1+ ) Concurrent Oracle processes = 1500 Approx. Statspack Taken during Mostly OLTP Operations :- Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- - --- log file sync 970,563 2,597,831 57.46 log file parallel write 831,141 484,948 10.73 log_buffer = 2MB Online Redo Logfiles Exist on RAID 1+ Storage Box is T3+ File System = UFS Application = Banking (Hybrid ) Oracle 8.1.7.4 Solaris 8 Machine Box = SF6800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Free Shared pool memory
Is it Correct to Look at FREE Memory in the Shared Pool ? Memory when used once thereafter when NO Longer in use does the FREE Memory again Come up ? Are there any ideal Values for percentage of Free memory for the Shared Pool The Respective Hybrid Application mostly uses Bind Variables Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
log file sync Wait
What ALL may be Done to Address the Following ? Any /etc/system , init.ora parameter Changes too ? Moving the Online Redo Logfiles onto RAID 1 NOT possible as that may warrant Additional Hardware . Moreover T3+ does NOT Support RAID 1 (Only RAID 1+ ) Concurrent Oracle processes = 1500 Approx. Statspack Taken during Mostly OLTP Operations :- Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- log file sync 970,5632,597,831 57.46 log file parallel write 831,141 484,948 10.73 log_buffer = 2MB Online Redo Logfiles Exist on RAID 1+ Storage Box is T3+ File System = UFS Application = Banking (Hybrid ) Oracle 8.1.7.4 Solaris 8 Machine Box = SF6800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Excessive Redo Generation
We seem to Be Generating Excessive Redo . All Tablespaces are LOCALLY Managed except SYSTEM . Size of Redo Logfile = 200 MB log_check_point_interval = 30 log_checkpoint_timeout = 0 log_buffer = 2MB NOTE - We have purposely kept increased log_check_point_interval = 30 based on past experience . Any /etc/system , init.ora parameter Changes too ? Concurrent Oracle processes = 1500 Approx. Machine Box = SF6800 Application = Banking (Hybrid ) Oracle 8.1.7.4 Solaris 8 We shall be taking Logminer Outputs Anything in particular to Look for in the Logminer Output to Check for Excessive Redo Generation ? [VIVEK_SHARMA] Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
join after saving rowid Value into a Field - For Design , Dev. Gurus
Title: Message let us suppose there are two tablesM and P. bothContain the fieldemp_id. other columnsmay bedifferent. All recordsof M also Exist in P .Table M will haverecords in the range 1-5 lakhs. P table will containAdditional Records such that the Total Number of Records in P is15-20 times the number of records in M. one way to join the two tables is to say M.emp_id = P.emp_id. but because P has high number of records the select is slower. we foundthat select of a row fromtable"P" using "rowid" columnwas veryQUICK. Is it a Correct practice :- 1) to Store the ROWID of Table P inMin a separate column(say "P_rowid") 2) Is it possible to do a Joinlikethe follows :- select field1, field2,... from M,P where M.emd_id = '6223' andlike M.P_rowid= P.rowid this way we hoped to select from M table (which has less number of records) and do a rowid based select on P table, which we found out is not allowed by ORACLE. we want a join because we want to create a view over table M and P. We do NOT want to use the following way :- select field1, field2 ..,P_rowid from M where emp_id = '6223'from a Cursor passing it to the Query as follows :- select * from P where rowid = P_rowid (selected earlier) Are there Some Standard Practices that Should be Followed during Designing Tables , Fields, SQL Writing ? Any Dos , Don'ts ?
