Share an unexpected Oracle Database Host Performance Test Result

Share an unexpected Oracle Database Host Performance Test Result

 

Recently, I have been doing some research on Oracle SQL Performance quantification. It is very complicated to implement more accurate SQL Performance quantification, and I have been advancing with caution, at the weekend, I tested two parameters of some machines:

One is how many memory hash operations can be performed in Oracle databases within 1 millisecond. This operation is mainly used to quantify the hash join time;

One is how many memory sorting comparison operations can be performed by Oracle databases within 1 ms. This is mainly used to quantify the order by time.

 

Below are the test results, which surprised me very much.

 

 

 

 

The following is the test code. The test runs with a single-session sqlplus. Therefore, the performance of a single CPU core can only be measured.

 

The following is the test code for the number of hash times:

 

Declare <br/> I integer; <br/> int1 integer; <br/> T1 number; <br/> T2 number; <br/> t_all number; <br/> t_select number; <br/> execute_count number: = 10; <br/> function getnumfromtimestamp (itime timestamp) return number is <br/> result number; <br/> v_hour number; <br/> v_minute number; <br/> v_second number; <br/> v_millisecond number; <br/> begin <br/> v_hour: = extract (hour from itime); <br/> v_minute: = extract (minute from itime); <br/> v_second: = extract (second from itime ); <br/> result: = (v_hour + 1) * 3600 + (v_minute + 1) * 60 + v_second; <br/> return (result); <br/> end; <br/> begin <br/> select/* + use_hash (a, B) */<br/> count (*) <br/> into int1 <br/> from (select rownum + 20000000 rn from dual connect by rownum <= 1000000), <br/> (select rownum + 10000000 rn from dual connect by rownum <= 1000000) B <br/> where. rn = B. rn; <br/> T1: = getnumfromtimestamp (systimestamp); <br/> for I in 1 .. execute_count loop <br/> select/* + use_hash (a, B) */<br/> count (*) <br/> into int1 <br/> from (select rownum + 20000000 rn <br/> from dual <br/> connect by rownum <= 1000000), <br/> (select rownum + 10000000 rn <br/> from dual <br/> connect by rownum <= 1000000) B <br/> where. rn = B. rn; <br/> end loop; <br/> T2: = getnumfromtimestamp (systimestamp); <br/> t_all: = t2-T1; <br/> select count (*) <br/> into int1 <br/> from (select rownum + 20000000 rn from dual connect by rownum <= 1000000 ); <br/> T1: = getnumfromtimestamp (systimestamp); <br/> for I in 1 .. execute_count loop <br/> select count (*) <br/> into int1 <br/> from (select rownum + 20000000 rn <br/> from dual <br/> connect by rownum <= 1000000 ); <br/> end loop; <br/> T2: = getnumfromtimestamp (systimestamp); <br/> t_select: = t2-T1; <br/> dbms_output.put_line ('hash _ times: '| <br/> trunc (1000/<br/> (t_all-t_select) /(2 * execute_count); <br/> end; <br/>/<br/>

 

 

The following is the test code for the number of sort times:

 

Declare <br/> I integer; <br/> int1 integer; <br/> T1 number; <br/> T2 number; <br/> t_all number; <br/> t_select number; <br/> execute_count number: = 10; <br/> function getnumfromtimestamp (itime timestamp) return number is <br/> result number; <br/> v_hour number; <br/> v_minute number; <br/> v_second number; <br/> v_millisecond number; <br/> begin <br/> v_hour: = extract (hour from itime); <br/> v_minute: = extract (minute from itime); <br/> v_second: = extract (second from itime ); <br/> result: = (v_hour + 1) * 3600 + (v_minute + 1) * 60 + v_second; <br/> return (result); <br/> end; <br/> begin <br/> select count (*) <br/> into int1 <br/> from (select rn <br/> from (select (mod (rownum, 3) * 3-1) * 1000000 + rownum rn <br/> from dual <br/> connect by rownum <= 1000000 <br/> order by RN) c <br/> where rownum + 1 <10000000); <br/> T1: = getnumfromtimestamp (systimestamp); <br/> for I in 1 .. execute_count loop <br/> select count (*) <br/> into int1 <br/> from (select rn <br/> from (select (mod (rownum, 3) * 3-1) * 1000000 + rownum rn <br/> from dual <br/> connect by rownum <= 1000000 <br/> order by RN) c <br/> where rownum + 1 <10000000); <br/> end loop; <br/> T2: = getnumfromtimestamp (systimestamp); <br/> t_all: = t2-T1; <br/> select count (*) <br/> into int1 <br/> from (select rn <br/> from (select (mod (rownum, 3) * 3-1) * 1000000 + rownum rn <br/> from dual <br/> connect by rownum <= 1000000) c <br/> where rownum + 1 <10000000); <br/> T1: = getnumfromtimestamp (systimestamp); <br/> for I in 1 .. execute_count loop <br/> select count (*) <br/> into int1 <br/> from (select rn <br/> from (select (mod (rownum, 3) * 3-1) * 1000000 + rownum rn <br/> from dual <br/> connect by rownum <= 1000000) C <br/> where rownum + 1 <10000000 ); <br/> end loop; <br/> T2: = getnumfromtimestamp (systimestamp); <br/> t_select: = t2-T1; <br/> dbms_output.put_line ('sort _ times: '| <br/> trunc (18792428/1000/(t_all-t_select); <br/> end; <br/>/<br/>

 

Experience:

1. The test results only reflect the single-core CPU performance and do not reflect the overall performance of the host. The host load has a certain impact on the test, but is relatively small.

2. The intel or AMD Single-core CPU used on PC servers is far more powerful than that of the minicomputer power5 and itanium Single-core CPU performance a few years ago, because there is no latest power6, power7, therefore, it is hard to comment on the Performance of power6 and power7.

3. The intel e8400 (3G) desktop CPU is much stronger than the Xeon e5520 (2.26g) on PC servers, thanks to the increase in CPU clock speed. At present, the single-core performance of the PC Server CPU is similar to that of the desktop CPU. As some articles have said, the server performance is much better. I understand that the server CPU stability is better and the scalability is stronger, generally, a Common Desktop Server can contain up to 2 servers, but four or more servers. This is the design support of the server architecture, on the other hand, the server CPU has considered more concurrent processing.

4. AMD's CPU performance is also very strong. AMD opteron 2378 (45nm) and AMD opteron 2356 (65nm) clock speed is similar, but the cache is much worse and the performance is doubled, we can see that AMD's CPU performance is closely related to the manufacturing process and cache.

5. The complexity of the Oracle9i sorting algorithm is about nlog (n). The sorting algorithm has been greatly improved after Oracle10g, and the complexity of the algorithm is far less than that of nlog (n ).

6. The CPU performance of minicomputers is no longer advantageous. The advantage of minicomputers is only

Stability is better (Our minicomputer has not experienced abnormal downtime, and PC servers sometimes occur );

Better Maintainability (many components of minicomputers can be maintained and upgraded online, and few PC servers can be used );

Higher Scalability (the cpu Of a minicomputer is often seen to be extended to more than 64, and the number of PC servers is usually less than 16 ).

 

Personal Opinion:

If you are a core database host for banks and securities, it makes sense to use minicomputers. If you only use enterprise-level information-based hosts, PC servers are more cost-effective. If you only use servers developed within the company, then, selecting the current high-performance desktop Intel CPU core i7 will be more practical.

 

Compared with Oracle9i, Oracle10g does not greatly improve the I/O management of databases except RAC, but does well in some details, such as the hash operation and sorting algorithm in this test, so if your host performance bottleneck is CPU computing (non-I/O wait), upgrading from Oracle9i to Oracle10g or 11g will be helpful.

 

The test results only reflect the single-core CPU performance, and do not reflect the overall performance of the host. The above test code has no reference standards and no theoretical basis. It is not strictly written by anyone, but the test results still have some reference significance. I would like to share it with you. Welcome to discuss it.