| |
 |
|
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
|
