A simple method to test oracle pressure

 

Today, I made a simple test of database pressure and shared something.

Server Status:

 

Operating system version: CentOS 5.6-64

Cpu: Intel (R) Xeon (R) CPU X5660 @ 2.80 GHz * 24

Memory: Mem: 16425876

Swap: 32764556

Database Version: oracle10gR2

Number of nodes: 2

 

The test method is as follows: Find out how much SQL is consumed in the test system through awr (which can be I/O or execution time)

The following script is written in python:

 

 

#! /Usr/bin/python

# Coding = UTF-8

 

Import cx_Oracle

Import time

 

Def hello ():

''' Hello cx_Oracle example:

 

1) print the database version information.

2) query table data .'''

 

Conn = cx_Oracle.connect ("jscn/jscn@192.168.100.199: 1521/jscn ")

Cur = conn. cursor ()

Try:

Print "Oracle Version: % s" % conn. version

Print "Table SUB_POLICY rows :"

Interger = 1

While interger <= 30000000:

SQL = "select * from product PRODUCT_ID = '0lea940 '"

Sql1 = "select * from productcategory where CATEGORY_ID = 'xhn6238 '"

Cur.exe cute (SQL)

For row in cur:

Print row

Time. sleep (1)

Cur.exe cute (sql1)

For row in cur:

Print row

Time. sleep (10)

Interger = interger + 1

Finally:

Cur. close ()

Conn. close ()

Hello ()

 

First install python and cx_Oracle. you can install these two software on your own Baidu. I will write my installation method in a few days.

Let me take a look at this python script. If the kids shoes want to use this script, they only need to modify the connection string and SQL part. In this script, first execute "SQL ", take a rest for 1 second, execute the "sql1" part, and then rest for 10. This is a loop, with a total of 30000000 cycles.

A friendly reminder is that python is especially sensitive to spaces. Be careful when copying.

If you simply execute this script, what is stress testing? In this case, ask for assistance from other children's shoes. Execute the following command in dos. In win7, it is best to use the administrator user to execute it.

 

 

-- Switch to the directory where the script is located and execute the following command

For/L % I in (1, 1, 50) do start "test % I" python test. py

 

This script runs test. py in 50 windows.

To stop, run the following command:

Taskkill/im python.exe

Now let's take a look at the database performance,

1. view the number of connections to the node, and view the number of connections to the database on the two nodes respectively.

Log on to the first node and check the number of python connections.

SQL> select count (*) from v $ session where programspon'python.exe ';

 

COUNT (*)

----------

24

Log on to the second node and check the number of python connections.

SQL> select count (*) from v $ session where programspon'python.exe ';

 

COUNT (*)

----------

26

View total connections

SQL> select count (*) from gv $ session where program='python.exe ';

 

COUNT (*)

----------

50

2. view the pga allocation size of each user

 

Select spid, Value/1024/1024 Mb

From V $ session s, V $ sesstat St, V $ statname Sn, V $ process p

Where St. Sid = s. Sid

And St. Statistic # = Sn. Statistic #

And Sn. Name Like 'session pga memory'

And p. Addr = s. Paddr and s.program='python.exe'

Order By Value Desc;

 

SPID MB

----------------------

1936 0.73026275

1906 0.73026275

1955 0.73026275

1940 0.73026275

1953 0.73026275

1946 0.73026275

1934 0.73026275

1942 0.73026275

1972 0.73026275

1959 0.73026275

1900 0.73026275

1961 0.73026275

1970 0.73026275

1968 0.73026275

1957 0.73026275

1902 0.73026275

1904 0.73026275

1919 0.73026275

1938 0.73026275

1923 0.73026275

 

SPID MB

----------------------

1921 0.73026275

1925 0.73026275

1917 0.73026275

1910 0.73026275

1908 0.73026275

1927 0.73026275

Here, the number of users is less than the size of pga.

 

3. view the memory usage of each spid on the database server (for example)

[Oracle @ rac2 ~] $ Top-p 1936,1906, 1955,1940

Top-19:30:49 up 11 days, 1 user, load average: 0.08, 0.08, 0.03

Tasks: 4 total, 0 running, 4 sleeping, 0 stopped, 0 zombie

Cpu (s): 0.3% us, 0.1% sy, 0.0% ni, 99.6% id, 0.1% wa, 0.0% hi, 0.0% si, 0.0% st

Mem: 16425876 k total, 6192932 k used, 10232944 k free, 422484 k buffers

Swap: 32764556 k total, 344 k used, 32764212 k free, 3581576 k cached

 

Pid user pr ni virt res shr s % CPU % mem time + COMMAND

1936 oracle 15 0 1681 m 26 m 22 m S 0.0 0: 00. 13 oracle

1906 oracle 15 0 1681 m 26 m 22 m S 0.0 0: 00. 14 oracle

1955 oracle 15 0 1681 m 26 m 22 m S 0.0 0: 00. 12 oracle

1940 oracle 15 0 1681 m 26 m 22 m S 0.0 0: 00. 15 oracle

 

Here, the RES value * number <total memory.

 

Well, it's over. It's easy.

 

From RuleV5's column