示例#1
0
 function _initialize()
 {
     //暂时关闭
     exit;
     #每小时执行一次
     if (date('i') != 30) {
         exit;
     }
     if (is_writable('/dev/shm/')) {
         $change = false;
         $basefile = '/dev/shm/sql_' . VHOST;
         $sqls = unserialize(file_get_contents($basefile));
         if (empty($sqls)) {
             echo "empty sqls\n";
             $change = true;
         }
         echo "sql_count:" . count($sqls) . "\n";
         foreach ($sqls as $k => $v) {
             if ($v['type'] != 'oci' || $v['paser_txt'] || $v['vhost'] != VHOST) {
                 continue;
             }
             if (strpos($v['sql'], 'alter session') !== false) {
                 continue;
             }
             $conn_db = _ocilogon($v['db']);
             $sql = "EXPLAIN PLAN SET STATEMENT_ID='pps' FOR " . $v['sql'];
             $stmt = _ociparse($conn_db, $sql);
             _ociexecute($stmt);
             $sql = "SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','pps','BASIC'))";
             $stmt = _ociparse($conn_db, $sql);
             _ociexecute($stmt);
             $_row = array();
             $row_text = NULL;
             while (ocifetchinto($stmt, $_row, OCI_ASSOC + OCI_RETURN_LOBS + OCI_RETURN_NULLS)) {
                 echo "change:explain\n";
                 $change = true;
                 $row_text .= "\n" . $_row['PLAN_TABLE_OUTPUT'];
             }
             _ocilogoff($conn_db);
             $sqls[$k]['paser_txt'] = $row_text;
             $sql_type = NULL;
             $vv = _sql_table_txt($v['sql'], $sql_type);
             //
             $type = NULL;
             if (strpos($v['act'], 'project') !== false) {
                 $type = "(项目)";
             }
             if (strpos($row_text, 'TABLE ACCESS FULL') !== false) {
                 _status(1, VHOST . "(BUG错误)", "问题SQL", "全表扫描{$type}", "{$v['db']}.{$vv}@{$v['act']}", $v['sql'] . "\n" . $row_text);
             }
             if (strpos($row_text, ' JOIN ') !== false) {
                 _status(1, VHOST . "(BUG错误)", "问题SQL", "多表查询{$type}", "{$v['db']}.{$vv}@{$v['act']}", $v['sql'] . "\n" . $row_text);
             }
         }
         foreach ($sqls as $k => $v) {
             if (time() > strtotime($v['add_time']) + 3600) {
                 echo "change:time\n";
                 $change = true;
                 unset($sqls[$k]);
             }
         }
         if ($change) {
             echo "write file.\n";
             file_put_contents($basefile, serialize($sqls));
         }
         die("OK\n");
     }
 }
示例#2
0
/**
 * @desc   执行SQL查询语句
 * @author
 * @since  2012-04-02 09:53:56
 * @param resource $stmt 数据库句柄资源
 * @return resource $error 错误信息
 * @throws 无DB异常处理
 */
function _ociexecute($stmt, $mode = OCI_COMMIT_ON_SUCCESS)
{
    $last_oci_sql = $_SERVER['last_oci_sql'];
    $ADD_PROJECT = ADD_PROJECT;
    if (!is_resource($stmt)) {
        $debug_backtrace = debug_backtrace();
        array_walk($debug_backtrace, create_function('&$v,$k', 'unset($v["function"],$v["args"]);'));
        _status(1, VHOST . "(BUG错误)", "SQL错误", GET_INCLUDED_FILES, "非资源\$stmt | " . var_export($_SERVER['last_oci_bindname'], true) . "|" . var_export($_GET, true) . "|" . $last_oci_sql . "|" . var_export($debug_backtrace, true));
    }
    if (PROJECT_SQL === true) {
        $ADD_PROJECT = '[项目]';
    }
    $_SERVER['oci_sql_ociexecute']++;
    $t1 = microtime(true);
    ociexecute($stmt, $mode);
    $diff_time = sprintf('%.5f', microtime(true) - $t1);
    //表格与函数关联
    $sql_type = NULL;
    $v = _sql_table_txt($last_oci_sql, $sql_type);
    $out = array();
    preg_match('# in(\\s+)?\\(#is', $last_oci_sql, $out);
    if ($out) {
        $last_oci_sql = substr($last_oci_sql, 0, stripos($last_oci_sql, ' in')) . ' in....';
        _status(1, VHOST . "(BUG错误)", '问题SQL', "IN语法" . $ADD_PROJECT, "{$_SERVER['last_db_conn']}@" . GET_INCLUDED_FILES . "/{$_REQUEST['act']}", "{$last_oci_sql}");
    }
    _status(1, VHOST . '(SQL统计)' . $ADD_PROJECT, "{$_SERVER['last_db_conn']}{$sql_type}", strtolower($v) . "@" . GET_INCLUDED_FILES, $last_oci_sql, VIP, $diff_time);
    $diff_time_str = _debugtime($diff_time);
    if ($diff_time < 1) {
        _status(1, VHOST . '(SQL统计)', '一秒内', _debugtime($diff_time), "{$_SERVER['last_db_conn']}." . strtolower($v) . "@" . GET_INCLUDED_FILES . VIP, $last_oci_sql, $diff_time);
    } else {
        _status(1, VHOST . '(SQL统计)', '超时', _debugtime($diff_time), "{$_SERVER['last_db_conn']}." . strtolower($v) . "@" . GET_INCLUDED_FILES . VIP, $last_oci_sql, $diff_time);
    }
    $ocierror = ocierror($stmt);
    if ($ocierror) {
        $debug_backtrace = debug_backtrace();
        array_walk($debug_backtrace, create_function('&$v,$k', 'unset($v["function"],$v["args"]);'));
        _status(1, VHOST . "(BUG错误)", "SQL错误", GET_INCLUDED_FILES, var_export($ocierror, true) . '|' . var_export($_SERVER['last_oci_bindname'], true) . "|GET:" . var_export($_GET, true) . '|POST:' . var_export($_POST, true) . "|" . $last_oci_sql . "|" . var_export($debug_backtrace, true), VIP, $diff_time);
    }
    $_SERVER['last_oci_bindname'] = array();
    return $ocierror;
}