How to optimize real-time Oracle databases

How to optimize real-time Oracle databases
Background of optimization ideas for real-time Databases

There are about 168 heat exchange station units, each of which has nearly 400 points, and more than 60 thousand points need to be updated in real time. There is a monitoring parameter table (yxjk_jkcs) in the database. Each point attribute is stored in it, and a field CS_VALUE is used to store updated real-time data.

Symptom

It takes about 10 minutes for all data to be updated, but less than two minutes for real-time data to be updated. In addition, during the update process, the resource utilization of the server is very large.

Analysis ideas and processes: 1. Use the select * from v $ sgainfo statement to view the global region of the system.

View Buffer Cache Size

And Shared Pool Size. One is responsible for the cache Size of the result set, and the other is responsible for storing and parsing SQL statements.

Select * from dict where table_name like '% SGA %'-Global Cache

Select * from v $ version; -- database version

Select * from v $ sgastat where pool = 'shared pooled 'order by bytes desc;

SELECT * from v $ SGAINFO;

Select * from v $ sgastat;

Select * from v $ sga;

Select * from v $ sga_value;

Select * from v $ sgastat where pool = 'shared pooled'

Selectvalue/1024/1024 from v $ parameter where name like '% sga_target % ';

Judging from the return results of the statement, the size of the cache area is 400 mb, while the 32-bit system, oracle can support up to 1.7 GB. Based on this result, the cache area is set to 1.4 GB to ensure memory usage.

Ii. Use select * from v $ SQL to view SQL statement execution logs

Select * from v $ SQL wherelower (SQL _text) like lower ('% yxjk_jkcs % ');

Then, based on the preceding statements, the statements are executed many times, indicating that no pre-compilation is executed.

Declare I number;

Begin

For I in 1 .. 60000 loop

Update yxjk_jkcs set CS_VALUE = '{experience site 154. tyzx154.scyx _ echysx}' where JKD_ID = 'tyzx154' and CS_ID = 'scyx _ echysx ';

End loop;

Rollback;

End;

Using oracle itself to experiment with cyclic pre-compilation, we found that it takes only a few seconds to execute 60 thousand times. At the same time, it is found that the two query conditions JKD_ID and CS_ID involved in this table are separated indexes. Therefore, the two fields are combined to create an index. The speed has increased.

Iii. Pre-compiled program execution under. net

Required STR = @ "updateyxjk_jkcs set CS_VALUE =: value where JKD_ID =: jkdid and CS_ID =: jkdcs ";

OracleCommand cmd = newOracleCommand (writable STR, conn );

Cmd. Parameters. Clear ();

OracleParameter opvalue = newOracleParameter (": value", OracleType. VarChar, 100 );

// Opvalue. OracleType = OracleType. VarChar;

// Opvalue. ParameterName = "@ value ";

Cmd. Parameters. Add (opvalue );

OracleParameter opjkd = newOracleParameter (": jkdid", OracleType. VarChar, 32 );

Cmd. Parameters. Add (opjkd );

OracleParameteropjkcs = new OracleParameter (": jkdcs", OracleType. VarChar, 200 );

Cmd. Parameters. Add (opjkcs );

For (int I = 0; I <60000; I ++)

{

Cmd. Parameters [": value"]. Value = I. ToString ();

Cmd. Parameters [": jkdid"]. Value = "jdyj01 ^ jdyj01 ";

Cmd. Parameters [": jkdcs"]. Value = "L_T_two_supply_Y3 ";

Cmd. ExecuteNonQuery ();

}

However, in this case, the interaction speed is still unsatisfactory.

4. query which statement and application are slow during execution.

Select a. seconds_in_wait, a. * fromv $ session_wait a where a. wait_class <> 'idle' order by a. seconds_in_waitdesc -- slow application Query

Operations are queried, log operations are performed, and frequent commit operations are performed.

Select a. * from v $ session a wherea. sid = 138

The information about the corresponding application is found in the traditional data submission method, because. net, each ExecutNonquery is equivalent to one statement and one commit, so it needs to be converted into a transaction type. Multiple statements are submitted once to form the corresponding program operation method.

5. Reduce the number of submissions and accumulate transactions

OracleConnection myConnection = new OracleConnection (connstr );

MyConnection. Open ();

OracleCommand myCommand = myConnection. CreateCommand ();

OracleTransactionmyTrans;

MyTrans = myConnection. BeginTransaction (IsolationLevel. ReadCommitted );

MyCommand. Transaction = myTrans;

Required STR = @ "update yxjk_jkcs set CS_VALUE =: value where JKD_ID =: jkdidand CS_ID =: jkdcs ";

MyCommand. CommandText = comment STR;

MyCommand. Parameters. Clear ();

OracleParameter opvalue = new OracleParameter (": value", OracleType. VarChar, 100 );

MyCommand. Parameters. Add (opvalue );

OracleParameter opjkd = newOracleParameter (": jkdid", OracleType. VarChar, 32 );

MyCommand. Parameters. Add (opjkd );

OracleParameter opjkcs = newOracleParameter (": jkdcs", OracleType. VarChar, 200 );

MyCommand. Parameters. Add (opjkcs );

For (int p = 0; p <myllsls. Length; p ++)

{

Stringonesql = myllsls [p];

Int fs = onesql. IndexOf ("where JKD_ID = ");

String jkdidstr = onesql. Substring (fs + 14 );

String [] jkdcs = jkdidstr. Split (new string [] {"'and CS_ID ='"}, StringSplitOptions. RemoveEmptyEntries );

String jkd = jkdcs [0];

String jkcs = jkdcs [1]. ToString (). Replace ("'\ r ","");

MyCommand. Parameters [": value"]. Value = p. ToString () + "##########";

MyCommand. Parameters [": jkdid"]. Value = jkd. Replace ("'","");

MyCommand. Parameters [": jkdcs"]. Value = jkcs. Replace ("'","");

MyCommand. ExecuteNonQuery ();

If (p % 5000 = 0)

{

MyTrans. Commit ();

MyTrans = myConnection. BeginTransaction (IsolationLevel. ReadCommitted );

Console. WriteLine (p. ToString () + "" + DateTime. Now. ToString ("yyyy-MM-dd HH: mm: ss "));

Mydialog. WriteLine (p. ToString () + "" + DateTime. Now. ToString ("yyyy-MM-dd HH: mm: ss "));

}

}

In this way, every 5000 submissions are submitted, which saves a lot of frequent interactions and greatly improves the speed.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.