关于解决oracle数据库insert【临时办法】

由于项目需要使用Oracle数据库,之前遇到一个insert不成功。原以为官方会解决,但是到目前应该还是没解决。
以下是我自己的解决办法,这样修改不清楚有什么不良效果,但能实现功能(MYSQL不合适)

thinkphp\library\think\db\Query.php 中的insert

public function insert(array $data = [], $replace = false, $getLastInsID = false, $sequence = null)
    {
        // 分析查询表达式
        $options = $this->parseExpress();
        $data    = array_merge($options['data'], $data);
        // 生成SQL语句
        $sql = $this->builder->insert($data, $options, $replace);
        // 获取参数绑定
        $bind = $this->getBind();
        if ($options['fetch_sql']) {
            // 获取实际执行的SQL语句
            return $this->connection->getRealSql($sql, $bind);
        }
        
     // 执行操作
//$result = 0 === $sql ? 0 : $this->execute($sql, $bind);

//修改
$realsql=$this->connection->getRealSql($sql, $bind);
        $result = $this->execute($realsql);
//修改


        if ($result) {
            $sequence  = $sequence ?: (isset($options['sequence']) ? $options['sequence'] : null);
            $lastInsId = $this->getLastInsID($sequence);
            if ($lastInsId) {
                $pk = $this->getPk($options);
                if (is_string($pk)) {
                    $data[$pk] = $lastInsId;
                }
            }
            $options['data'] = $data;
            $this->trigger('after_insert', $options);

            if ($getLastInsID) {
                return $lastInsId;
            }
        }
        return $result;
    }

复制代码

 

thinkphp\library\think\db\Connection.php中的getLastInsID

public function getLastInsID($sequence = null)
    {
//        return $this->linkID->lastInsertId($sequence);
        switch($this->config['type']) {
                        case '\think\oracle\Connection':
            if(preg_match("/^\s*(INSERT\s+INTO)\s+(\w+)\s+/i", $this->queryStr, $match)) {
            $seq=$this->config['sequence_prefix'];
            $fix1=$this->config['prefix'];
            $fix=str_ireplace($fix1, "", $match[2]);
            
        $this->table = $seq.$fix;
        }
                $sequence = $this->table;
                $vo = $this->query("SELECT {$sequence}.currval currval FROM dual");
                return $vo?$vo[0]["currval"]:0;
        }
    }

 

展开阅读全文

关于oracle数据库insert执行慢

06-13

做的项目中有对数据库insert操作,在批量执行(100tps)的时候,insert操作会变得很慢,使用10046跟踪信息如下:rninsert into OPERUSERNOTEINFO(MSISDN, OPERTIME, OPERTYPE, MODIFYTYPE,PASSWORD, rn PAYTYPE, USERTYPE, USERSTATE) rnvaluesrn(:1, :2, :3, :4, :5, :6, :7, :8)rnrnrncall count cpu elapsed disk query current rowsrn------- ------ -------- ---------- ---------- ---------- ---------- ----------rnParse 153 0.00 0.00 0 0 0 0rnExecute 154 0.03 40543.05 115 199 2063 154rnFetch 0 0.00 0.00 0 0 0 0rn------- ------ -------- ---------- ---------- ---------- ---------- ----------rntotal 307 0.03 40543.05 115 199 2063 154rnrnMisses in library cache during parse: 1rnMisses in library cache during execute: 1rnOptimizer goal: ALL_ROWSrnParsing user id: 94 rnrnElapsed times include waiting on following events:rn Event waited on Times Max. Wait Total Waitedrn ---------------------------------------- Waited ---------- ------------rn buffer deadlock 6 0.00 0.00rn buffer busy waits 6 1.00 5.50rn********************************************************************************rnrn从10046日志上看,Execute elapsed值很高,应该是在等待某种资源,而且update操作没有这个现象:rnUPDATE BASETAB SET PAYTYPE = :1, PAYTYPESWITCHTIME = :2, PREPAIDSYSTEMID = :3 rnWHERErn MSISDN = :4rnrnrncall count cpu elapsed disk query current rowsrn------- ------ -------- ---------- ---------- ---------- ---------- ----------rnParse 154 0.00 0.00 0 0 0 0rnExecute 154 0.02 0.00 1 462 464 154rnFetch 0 0.00 0.00 0 0 0 0rn------- ------ -------- ---------- ---------- ---------- ---------- ----------rntotal 308 0.02 0.00 1 462 464 154rnrnMisses in library cache during parse: 1rnMisses in library cache during execute: 1rnOptimizer goal: ALL_ROWSrnParsing user id: 94 rnrnRows Row Source Operationrn------- ---------------------------------------------------rn 0 UPDATE BASETAB (cr=3 pr=0 pw=0 time=0 us)rn 1 INDEX RANGE SCAN PK_BASETAB (cr=3 pr=0 pw=0 time=0 us cost=2 size=36 card=1)(object id 83214)rnrn********************************************************************************rnrnrn请问下这种情况下可能是由于那些资源阻塞造成的,要检查和更改数据库的那些参数设置,对了,使用的是oracle 11G的数据库 论坛

没有更多推荐了,返回首页