Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
  Oracle Tips by Burleson

SCSI and ATA Runs

The SCSI runs required 58 days to complete. During this 58 day period, 7 full query runs and a single partial run were completed. This was with queries running as close as possible to 24X7, between equipment failures and connectivity issues.

During the test the initial 2-disk SCSI array failed during the second query set run. This array failure required a database rebuild and reload on the provided ATA array. After the SCSI failure the entire initial query run had to be repeated, and compared to the SCSI results. The decision was made at that time to discard the SCSI results and continue with the ATA array.

The initial nologgingand noarchiving runs performed poorly as evidenced by the test where query 1, that required 9 minutes on the SSD array, hadn’t returned after 30 hours. The nologging and noarchivelogging set points were utilized during subsequent tests to allow the tests to complete in a reasonable amount of time. The nologging and noarchivelogging set points were used since turning on logging and archivelog would further stress the I/O subsystem and result in even poorer performance. The following issues further complicated the testing:

  • When running the queries against the 100% SCSI/ATA database, query 1 would not complete, so after 30 hours it was halted.

  • When running the queries against the 100% SCSI/ATA database, query 4 would not complete in 30 hours and it was halted.

  • When running against the 100% SCSI/ATA database, query 8 produced the error codes shown below then terminated the session causing all subsequent queries to fail in run 1 and ultimately require a restart.

Sun Jun  6 11:43:16 2004
Errors in file
/home/oracle/admin/dss/udump/dss_ora_615.trc:
ORA-00600: internal error code, arguments:
[kftts2bz_many_files], [0], [39218], [], [], [], [], []
Sun Jun  6 11:43:17 2004
Errors in file
/home/oracle/admin/dss/udump/dss_ora_615.trc:
ORA-07445: exception encountered: core dump
[kghbigasp()+289] [SIGSEGV] [Address not mapped to
object] [0x427A8] [] []
ORA-00600: internal error code, arguments:
[kftts2bz_many_files], [0], [39218], [], [], [], [], []
Sun Jun  6 11:43:18 2004
Errors in file
/home/oracle/admin/dss/udump/dss_ora_615.trc:
ORA-07445: exception encountered: core dump
[kghbigasp()+289] [SIGSEGV] [Address not mapped to
object] [0x427A8] [] []
ORA-07445: exception encountered: core dump
[kghbigasp()+289] [SIGSEGV] [Address not mapped to
object] [0x427A8] [] []
ORA-00600: internal error code, arguments:
[kftts2bz_many_files], [0], [39218], [], [], [], [], []
Sun Jun  6 11:43:18 2004
Errors in file
/home/oracle/admin/dss/udump/dss_ora_615.trc:
ORA-07445: exception encountered: core dump
[kghbigasp()+289] [SIGSEGV] [Address not mapped to
object] [0x427A8] [] []
ORA-07445: exception encountered: core dump
[kghbigasp()+289] [SIGSEGV] [Address not mapped to
object] [0x427A8] [] []
ORA-07445: exception encountered: core dump
[kghbigasp()+289] [SIGSEGV] [Address not mapped to
object] [0x427A8] [] []
ORA-00600: internal error code, arguments:
[kftts2bz_many_files], [0], [39218], [], [], [], [], []
[oracle@AMD43 bdump]$
[oracle@AMD43 bdump]$ tail -f alert_dss.log
Errors in file
/home/oracle/admin/dss/udump/dss_ora_615.trc:
ORA-07445: exception encountered: core dump
[kghbigasp()+289] [SIGSEGV] [Address not mapped to
object] [0x427A8] [] []
ORA-07445: exception encountered: core dump
[kghbigasp()+289] [SIGSEGV] [Address not mapped to
object] [0x427A8] [] []
ORA-00600: internal error code, arguments:
[kftts2bz_many_files], [0], [39218], [], [], [], [], []
Sun Jun  6 11:43:18 2004
Errors in file
/home/oracle/admin/dss/udump/dss_ora_615.trc:
ORA-07445: exception encountered: core dump
[kghbigasp()+289] [SIGSEGV] [Address not mapped to
object] [0x427A8] [] []
ORA-07445: exception encountered: core dump
[kghbigasp()+289] [SIGSEGV] [Address not mapped to
object] [0x427A8] [] []
ORA-07445: exception encountered: core dump
[kghbigasp()+289] [SIGSEGV] [Address not mapped to
object] [0x427A8] [] []
ORA-00600: internal error code, arguments: [kftts2bz_many_files], [0], [39218], [], [], [], [], []
  

  • The queries would run out of temporary and undo segment space and generate the above errors if run back-to-back with the same user information. The same settings for tablespace size and relative placement were used as in the SSD runs. The script running the queries was modified to include a new connect statement before each query, releasing previous undo segments and temp segments for reuse. This was not required in the SSD tests since each run was actually several smaller runs restarted at the point of failure.

  • During run 2 of the initial SCSI test, the array failed after approximately 8 days of 24X7 processing. This required a rebuild of the database on the new ATA array.

  • During the final runs, 2 days of downtime were encountered as a result of a complete power outage that caused a re-assignment of the ATA array address. The data was fully recovered through the automated recovery process and the testing resumed.

  • Several days were also lost due to connectivity issues.

  • Any query that required longer than ~30 hours was terminated and processing restarted at the next query in the file. This was required on queries 1, 3, 13, 13a, 17, 18 in various configurations. The resulting time was set to 1440 minutes (24 hours) for these queries and the graph axis set to logarithmic to allow Figure 4.2 to show at least some of the shorter query times.

Once the initial issues with the SCSI and then ATA performance were worked out, testing was able to resume on the ATA array. Here are the configurations that were tested.


The above book excerpt is from:

Oracle Solid State Disk Tuning

High Performance Oracle tuning with RAM disk

ISBN 0-9744486-5-6  

Donald K. Burleson & Mike Ault

http://www.rampant-books.com/book_2005_1_ssd.htm

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter