Exemplo n.º 1
0
 /**
  * @param string $table
  * @param array $columns
  * @param CPerfQuery $q
  * @return boolean
  */
 public static function GatherExpressStat($table, $columns, $q)
 {
     $arColumns = explode(",", $columns);
     if (count($arColumns) != 1) {
         return false;
     }
     $column = trim($arColumns[0], "`");
     $value = trim($q->find_value($table, $arColumns[0]), "'");
     if ($value == "") {
         return false;
     }
     $tab = new CPerfomanceTable();
     $tab->Init($table);
     if ($tab->IsExists()) {
         $arTableColumns = CPerfQueryStat::GetTableColumns($table);
         if (!array_key_exists($column, $arTableColumns)) {
             return false;
         }
         //May be it is worth to ban
         if ($arTableColumns[$column]["Type"] === "char(1)") {
             if (is_array(CPerfQueryStat::_get_stat($table, $arColumns[0]))) {
                 return true;
             }
             if (CPerfQueryStat::_gather_stat($table, $arColumns[0], $value, 10 * 1024 * 1024)) {
                 return true;
             }
         }
         return false;
     } else {
         return false;
     }
 }
Exemplo n.º 2
0
 function parse($sql)
 {
     $this->sql = preg_replace("/([()=])/", " \\1 ", $sql);
     $this->sql = CPerfQuery::removeSpaces($this->sql);
     $match = array();
     if (preg_match("/^(select) /i", $this->sql, $match)) {
         $this->type = strtolower($match[1]);
     } else {
         $this->type = "unknown";
     }
     if ($this->type == "select") {
         //0 TODO replace literals with placeholders
         //1 remove subqueries from sql
         if (!$this->parse_subqueries()) {
             return false;
         }
         //2 parse from
         $this->from = new CPerfQueryFrom();
         if (!$this->from->parse($this->sql)) {
             return false;
         }
         $tables_regex = "(?:" . implode("|", $this->from->getTableAliases()) . ")";
         $this->where = new CPerfQueryWhere($tables_regex);
         if (preg_match("/ where (.+?)(\$| group | having | order )/i", $this->sql, $match)) {
             $this->where->parse($match[1]);
         }
         return true;
     } else {
         return false;
     }
 }
Exemplo n.º 3
0
$sql = htmlspecialcharsEx($sql);
$sql = preg_replace("/(" . preg_quote($arSuggest["TABLE_NAME"]) . "\\s+(?i:as\\s+)*" . $arSuggest["TABLE_ALIAS"] . ")\\s+/", "<b>\\1</b> ", $sql);
foreach (explode(",", $arSuggest["COLUMN_NAMES"]) as $column_name) {
    $sql = str_replace($arSuggest["TABLE_ALIAS"] . "." . $column_name, "<b>" . $arSuggest["TABLE_ALIAS"] . "." . $column_name . "</b>", $sql);
}
$arSuggest["FORMATTED_SQL_TEXT"] = $sql;
$arColumns = explode(",", $arSuggest["COLUMN_NAMES"]);
$arTableStat = CPerfQueryStat::GatherTableStat($arSuggest["TABLE_NAME"]);
$arIndexes = CPerfomanceTable::GetIndexes($arSuggest["TABLE_NAME"]);
$arQueries = array();
$rsQueries = CPerfomanceSQL::GetList(array("ID", "SQL_TEXT"), array("=SUGGEST_ID" => $ID), array("ID" => "ASC"), false, array("nTopCount" => 10));
while ($arQuery = $rsQueries->Fetch()) {
    $arQuery["STAT"] = array();
    $arQuery["WHERE"] = array();
    $arQuery["JOIN"] = array();
    $q = new CPerfQuery();
    if ($q->parse($q->transform2select($arQuery["SQL_TEXT"]))) {
        foreach ($arColumns as $column_name) {
            $arQuery["WHERE"][$column_name] = $q->find_value($arSuggest["TABLE_NAME"], $column_name);
            if ($arQuery["WHERE"][$column_name] == "") {
                $arQuery["JOIN"][$column_name] = $q->find_join($arSuggest["TABLE_NAME"], $column_name);
            } else {
                $arQuery["JOIN"][$column_name] = "";
            }
        }
    }
    $sql = CPerfomanceSQL::Format($arQuery["SQL_TEXT"]);
    $sql = htmlspecialcharsEx($sql);
    $sql = preg_replace("/(" . preg_quote($arSuggest["TABLE_NAME"]) . "\\s+(?i:as\\s+)*" . $arSuggest["TABLE_ALIAS"] . ")\\s+/", "<b>\\1</b> ", $sql);
    foreach (explode(",", $arSuggest["COLUMN_NAMES"]) as $column_name) {
        $sql = str_replace($arSuggest["TABLE_ALIAS"] . "." . $column_name, "<b>" . $arSuggest["TABLE_ALIAS"] . "." . $column_name . "</b>", $sql);
Exemplo n.º 4
0
 $sql_cache = array();
 while (time() < $etime) {
     $rsSQL = CPerfomanceSQL::GetList(array("ID", "SQL_TEXT", "QUERY_TIME"), array(">ID" => $last_id), array("ID" => "ASC"), false, array("nTopCount" => 100));
     while ($arSQL = $rsSQL->Fetch()) {
         $_SESSION["queries"]++;
         $go = true;
         $sql_md5 = md5(CPerfQuery::remove_literals($arSQL["SQL_TEXT"]));
         //Check if did it already on previous steps
         if (!array_key_exists($sql_md5, $sql_cache)) {
             $sql_cache[$sql_md5] = true;
             $rsInd = CPerfomanceIndexSuggest::GetList(array("SQL_MD5"), array("=SQL_MD5" => $sql_md5), array());
             if ($rsInd->Fetch()) {
                 CPerfomanceIndexSuggest::UpdateStat($sql_md5, 1, $arSQL["QUERY_TIME"], $arSQL["ID"]);
             } else {
                 $arMissedKeys = array();
                 $q = new CPerfQuery();
                 $strSQL = $q->transform2select($arSQL["SQL_TEXT"]);
                 if ($strSQL && $q->parse($strSQL)) {
                     $i = 0;
                     $arExplain = array();
                     $rsData = $DB->Query("explain " . $strSQL, true);
                     if (is_object($rsData)) {
                         while ($arRes = $rsData->Fetch()) {
                             $i++;
                             $arExplain[] = $arRes;
                             if ($arRes["type"] === "ALL" && strlen($arRes["key"]) == 0 && is_object($q) && ($i > 1 || $q->has_where($arRes["table"]))) {
                                 $missed_keys = $q->suggest_index($arRes["table"]);
                                 if ($missed_keys) {
                                     $arMissedKeys = array_merge($arMissedKeys, $missed_keys);
                                 } elseif ($q->has_where()) {
                                     //Check if it is possible to find missed keys on joined tables
Exemplo n.º 5
0
$ID = IntVal($ID);
$sTableID = "tbl_perfmon_explain";
$lAdmin = new CAdminList($sTableID);
if ($DBType == "mysql") {
    $arHeader = array(array("id" => "select_type", "content" => GetMessage("PERFMON_EXPLAIN_F_SELECT_TYPE"), "align" => "left", "default" => true), array("id" => "table", "content" => GetMessage("PERFMON_EXPLAIN_F_TABLE"), "align" => "left", "default" => true), array("id" => "type", "content" => GetMessage("PERFMON_EXPLAIN_F_TYPE"), "align" => "left", "default" => true), array("id" => "possible_keys", "content" => GetMessage("PERFMON_EXPLAIN_F_POSSIBLE_KEYS"), "align" => "left", "default" => true), array("id" => "key", "content" => GetMessage("PERFMON_EXPLAIN_F_KEY"), "align" => "left", "default" => true), array("id" => "key_len", "content" => GetMessage("PERFMON_EXPLAIN_F_KEY_LEN"), "align" => "right", "default" => true), array("id" => "ref", "content" => GetMessage("PERFMON_EXPLAIN_F_REF"), "align" => "left", "default" => true), array("id" => "rows", "content" => GetMessage("PERFMON_EXPLAIN_F_ROWS"), "align" => "right", "default" => true), array("id" => "Extra", "content" => GetMessage("PERFMON_EXPLAIN_F_EXTRA"), "align" => "left", "default" => true));
} elseif ($DBType == "oracle") {
    $arHeader = array(array("id" => "OPERATION", "content" => GetMessage("PERFMON_EXPLAIN_F_OPERATION"), "align" => "left", "default" => true), array("id" => "OBJECT_NAME", "content" => GetMessage("PERFMON_EXPLAIN_F_OBJECT_NAME"), "align" => "left", "default" => true), array("id" => "OBJECT_TYPE", "content" => GetMessage("PERFMON_EXPLAIN_F_OBJECT_TYPE"), "align" => "left", "default" => true), array("id" => "OPTIONS", "content" => GetMessage("PERFMON_EXPLAIN_F_EXTRA"), "align" => "left", "default" => true), array("id" => "CARDINALITY", "content" => GetMessage("PERFMON_EXPLAIN_F_ROWS"), "align" => "right", "default" => true), array("id" => "COST", "content" => GetMessage("PERFMON_EXPLAIN_F_COST"), "align" => "right", "default" => true));
} else {
    $arHeader = array();
}
$lAdmin->AddHeaders($arHeader);
$arPlan = false;
$cData = new CPerfomanceSQL();
$rsSQL = $cData->GetList(array("ID", "SQL_TEXT"), array("=ID" => $ID), array(), false);
$arSQL = $rsSQL->Fetch();
$strSQL = CPerfQuery::transform2select($arSQL["SQL_TEXT"]);
if ($strSQL) {
    if ($DBType == "mysql") {
        $rsData = $DB->Query("explain " . $strSQL, true);
    } elseif ($DBType == "oracle") {
        $rsData = $DB->Query("explain plan for " . $strSQL, true);
        if ($rsData) {
            $rsData = $DB->Query("select * from plan_table order by ID");
            $arPlan = $rsData->Fetch();
        }
    } else {
        $rsData = false;
    }
} else {
    $rsData = false;
}
Exemplo n.º 6
0
				$sql_md5 = md5(CPerfQuery::remove_literals($arSQL["SQL_TEXT"]));

				//Check if did it already on previous steps
				if (!array_key_exists($sql_md5, $sql_cache))
				{
					$sql_cache[$sql_md5] = true;

					$rsInd = CPerfomanceIndexSuggest::GetList(array("SQL_MD5"), array("=SQL_MD5" => $sql_md5), array());
					if ($rsInd->Fetch())
					{
						CPerfomanceIndexSuggest::UpdateStat($sql_md5, 1, $arSQL["QUERY_TIME"], $arSQL["ID"]);
					}
					else
					{
						$arMissedKeys = array();
						$q = new CPerfQuery;
						$strSQL = $q->transform2select($arSQL["SQL_TEXT"]);
						if ($strSQL && $q->parse($strSQL))
						{
							$i = 0;
							$arExplain = array();
							$rsData = $DB->Query("explain ".$strSQL, true);
							if (is_object($rsData))
							{
								while ($arRes = $rsData->Fetch())
								{
									$i++;
									$arExplain[] = $arRes;
									if (
										$arRes["type"] === "ALL"
										&& strlen($arRes["key"]) == 0