An optimization approach to Oracle Real-time database

On the background of optimization of real-time database

About 168 heat exchange station units, each set of nearly 400 points, the overall more than 60,000 points need to be updated in real time. In the database there is a monitoring parameter table (YXJK_JKCS), each point attribute is stored inside, there is a field cs_value is to store the updated real-time data.

Phenomenon

All data is updated one time, about 10 minutes, and less than 2 minutes to update the effect of real-time data requirements. And the data in the process of updating, the resource utilization of the server is very large.

Analysis thought and process: first, use SELECT * from V$sgainfo; Statement View system global Zone

Viewed buffer Cache Size

And the shared Pool size, which is the cache size responsible for the result set, and one that is responsible for storing the parsed SQL statement

Select *from dict where table_name like '%sga% '-Global cache

Select *from v$version;--database version status

Select *from v$sgastat WHERE pool= ' shared POOL ' 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 pool '

selectvalue/1024/1024 from V$parameter where name like '%sga_target% ';

Judging from the return result of the statement, the buffer byte size is 400 trillion, while the 32-bit system, Oracle can support up to 1.7G. According to this result, the buffer was set up to 1.4G, to ensure the use of memory space.

Ii. using SELECT * from V$sql to view the logs executed by the SQL statement

SELECT * from V$sql wherelower (sql_text) like lower ('%yxjk_jkcs% ');

Then, according to the above statement, the statement is executed more frequently, stating that no precompilation is performed.

declare i number;

Begin

For I in 1.. 60000 Loop

Update YXJK_JKCS set cs_value= ' {Experience heat exchange station 154.TYZX154.SCYX_ECHYSX} ' where jkd_id= ' tyzx154 ' and cs_id= ' scyx_echysx ';

End Loop;

Rollback

End

Using the Oracle itself, we experimented with cyclic precompilation and found that it took only a few seconds to execute 60,000 times. At the same time, it is found that the two query conditions of participation in this table jkd_id and cs_id are separate indexes. The two fields are then joined together to create an index. The speed has increased a bit faster.

C. NET to pre-compile program execution

Cmdstr = @ "Updateyxjk_jkcs set Cs_value=:value where Jkd_id=:jkdid and Cs_id=:jkdcs";

oraclecommand cmd =newOracleCommand(CMDSTR, 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 ();

}

In this case, however, the speed is still not ideal after the interaction is found.

The query in the execution process, which statement and application is relatively slow.

Select A.seconds_in_wait, A.* fromv$session_wait a where a.wait_class<> ' Idle ' ORDER by A.seconds_in_ waitdesc--found application is slow

The operation was queried, a log operation was performed, and a frequent commit operation was caused.

Select A.* from V$session a wherea.sid=138

The relevant information of the corresponding application is found, that is, the traditional way of data submission, because each of the executnonquery in. NET is equivalent to one statement and one commit at a time, so it needs to become a transaction type, and multiple statements are submitted at once to make the corresponding program operation mode.

V. Reduce the number of submissions, cumulative transactions

OracleConnection myconnection = new OracleConnection (CONNSTR);

Myconnection.open ();

OracleCommand mycommand= Myconnection.createcommand ();

Oracletransactionmytrans;

mytrans= myconnection.begintransaction (isolationlevel.readcommitted);

Mycommand.transaction =mytrans;

CMDSTR = @ "Update YXJK_JKCS set cs_value=:value where Jkd_id=:jkdidand Cs_id=:jkdcs";

Mycommand.commandtext= Cmdstr;

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 5,000 commits once, save a lot of frequent interaction, the speed has a great increase.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

An optimization approach to Oracle Real-time database