Performance optimization of Oracle

  • 2020-05-09 19:31:28
  • OfStack

The ORACLE tutorial you are looking at is :Oracle performance optimizations.

Linux Journal has published an article to optimize the Oracle database. This is a brief summary of Linux's use in enterprise databases.

Currently, HP, Compaq, Dell, IBM, and Oracle are all speeding up their embrace of Linux, the open source operating system. According to the statistics of eWeek, the sales volume of Linux servers accounted for about 30% of Compaq, 13.7% of Dell and 13.5% of IBM last year. And IBM spent $1 billion on Linux in 2001. Intel's latest 64-bit Itanium CPU only supports four operating systems: Windows, Linux, AIX and HP-UX. Let's also not forget that Oracle's 9i database, Linux, is one month older than Windows.

Although Linux runs on servers running from IBM S/390 to Sun SPARC, Intel is Linux's most popular platform for most people. This article is about how you can improve the performance of Oracle by 1000% through simple performance tuning.

The test environment used in this paper was 1 Compaq 4 CPU, 512 MB, 8 servers with 7200 rpm SCSI disks, and then the same single CPU Athlon system was tested, with 1 memory sample, but only 1 Ultra 100 IDE disk of 7200 rpm. Although the final result is not the same as the percentage obtained, the observed performance improvement is 1.

For simplicity, our test environment USES the TPC benchmark, which is widely used for load testing on OLTP. Quest has a tool called Benchmark Factory that makes testing as easy as sending an E-mail.

Below, we will see the test results through the adjustment of DB and OS respectively.

The initialization parameter 1 for DB1 is not common, so to illustrate, we use these parameters as a benchmark.

DB1: Initial Database
2 K Database Block Size
64 M SGA Buffer Cache
64 M SGA Shared Pool
4 M SGA Redo Cache
4 M Redo Log Files
Tablespaces Dictionary
TPC Results Load Time (Seconds) 49.41
Transactions/Second 8.152

Obviously, the size of SGA needs to be increased. Let's look at the results of DB2:

DB2: Cache & Pool
2 K Database Block Size
128 M SGA Buffer Cache
128 M SGA Shared Pool
4 M SGA Redo Cache
4 M Redo Log Files
Tablespaces Dictionary
TPC Results Load Time (Seconds) 48.57
Transactions/Second 9.147

Related articles: