关于Oracle实时数据库的优化思路

                                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();

                                }

不过在这种情况下，发现交互后，速度仍然不够理想。

四、查询在执行过程中，哪个语句及应用比较慢。

select a.seconds_in_wait, a.* fromv$session_wait a where a.wait_class<>‘Idle‘ order by a.seconds_in_waitdesc--查到的应用慢

查询到了操作，执行了日志操作，并频繁的进行的commit操作导致。

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

查到了相应应用的相关信息，就是用传统的数据提交方式，因为.net下的每一个ExecutNonquery相当于一次语句并一次提交，所以需要变成事务型，多条语句一次提交从而化对应程序的操作方式。

五、减少提交次数，累计事务

  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"));

                                    }

                                }

以这样的方式每5000条提交一次，节省了大量的频繁交互，速度就有了很大的提升。

版权声明：本文为博主原创文章，未经博主允许不得转载。

关于Oracle实时数据库的优化思路

标签：