public override ExplainPlanData GetExplainPlan([NotNull] DatabaseConnection databaseConnection, [NotNull] string sql) { if (databaseConnection == null) { throw new ArgumentNullException("databaseConnection"); } if (sql == null) { throw new ArgumentNullException("sql"); } var table = new DataTable(); using (var connection = databaseConnection.CreateNewConnection()) { connection.OpenIfRequired(); using (var command = connection.CreateCommand()) { command.CommandText = "SET SHOWPLAN_ALL ON"; command.ExecuteNonQuery(); command.CommandText = sql; using (var dr = command.ExecuteReader()) { table.Load(dr); } } } table.Columns["StmtText"].ReadOnly = false; foreach (DataRow row in table.Rows) { var value = ((string)row["StmtText"]).Trim(); while (value.StartsWith("|") || value.StartsWith("-")) { value = value.Substring(1).Trim(); } row.SetField("StmtText", value); } // Parse table var explainPlanData = new SqlServerExplainPlanData(); var rootRow = table.Rows.Cast <DataRow>().Where(x => ((int)x["Parent"]) == 0).ToList(); if (rootRow.Count == 0) { throw new Exception("Could not find a starting point in the execution plan."); } var explainPlanRow = new ExplainPlanRow(rootRow.First()); explainPlanData.Rows.Add(explainPlanRow); ParseExplainPlan(explainPlanRow, table, "NodeId", "Parent"); return(explainPlanData); }
private static ExplainPlanRow AddExplainPlanRow(ICollection <ExplainPlanRow> childRows, DataTable table, object[] relOp) { var row = table.NewRow(); row.ItemArray = relOp; var explainPlanRow = new ExplainPlanRow(row); childRows.Add(explainPlanRow); return(explainPlanRow); }
protected static void ParseExplainPlan(ExplainPlanRow parentRow, DataTable table, string idColumnName, string parentIdColumnName) { var parentOperatorId = int.Parse(parentRow.Row[idColumnName].ToString()); var childRows = table.Rows.Cast <DataRow>().Where(x => !x.IsNull(parentIdColumnName) && ((int.Parse(x[parentIdColumnName].ToString()))) == parentOperatorId).ToList(); if (childRows.Count == 0) { return; } foreach (var childRow in childRows) { var childExplainRow = new ExplainPlanRow(childRow); parentRow.ChildRows.Add(childExplainRow); ParseExplainPlan(childExplainRow, table, idColumnName, parentIdColumnName); } }
public override ExplainPlanData GetExplainPlan([NotNull] DatabaseConnection databaseConnection, [NotNull] string sql) { if (databaseConnection == null) { throw new ArgumentNullException("databaseConnection"); } if (sql == null) { throw new ArgumentNullException("sql"); } var table = new DataTable(); using (var connection = databaseConnection.CreateNewConnection()) { connection.OpenIfRequired(); using (var transaction = connection.BeginTransaction()) { using (var command = connection.CreateCommand()) { command.Transaction = transaction; try { command.CommandText = "SELECT * FROM PLAN_TABLE"; command.ExecuteReader(CommandBehavior.SchemaOnly); } catch (Exception ex) { throw new Exception("Could not SELECT from PLAN_TABLE. " + ex.Message); } command.CommandText = "DELETE FROM PLAN_TABLE"; command.ExecuteNonQuery(); command.CommandText = "EXPLAIN PLAN SET STATEMENT_ID = 'stats' FOR " + sql; command.ExecuteNonQuery(); command.CommandText = " SELECT "+ Environment.NewLine; command.CommandText += " ID, "+ Environment.NewLine; command.CommandText += " PARENT_ID, "+ Environment.NewLine; command.CommandText += " OPERATION || ' ' || NVL(OPTIONS, '') as OPERATION, "+ Environment.NewLine; command.CommandText += " OBJECT_OWNER, "+ Environment.NewLine; command.CommandText += " OBJECT_NAME, "+ Environment.NewLine; command.CommandText += " ACCESS_PREDICATES, "+ Environment.NewLine; command.CommandText += " FILTER_PREDICATES, "+ Environment.NewLine; command.CommandText += " CARDINALITY, "+ Environment.NewLine; command.CommandText += " BYTES, "+ Environment.NewLine; command.CommandText += " COST, "+ Environment.NewLine; command.CommandText += " IO_COST, "+ Environment.NewLine; command.CommandText += " CPU_COST "+ Environment.NewLine; command.CommandText += " FROM "+ Environment.NewLine; command.CommandText += " PLAN_TABLE "+ Environment.NewLine; command.CommandText += " ORDER BY "+ Environment.NewLine; command.CommandText += " ID "+ Environment.NewLine; using (var dr = command.ExecuteReader()) { table.Load(dr); } } transaction.Rollback(); } } // Parse table var explainPlanData = new OracleExplainPlanData(); var rootRow = table.Rows.Cast <DataRow>().Where(x => x.IsNull("PARENT_ID")).ToList(); if (rootRow.Count == 0) { throw new Exception("Could not find a starting point in the execution plan."); } var explainPlanRow = new ExplainPlanRow(rootRow.First()); explainPlanData.Rows.Add(explainPlanRow); ParseExplainPlan(explainPlanRow, table, "ID", "PARENT_ID"); return(explainPlanData); }
private static void ParseScalarType(ExplainPlanRow row, DataTable table, object item) { var scalarType1 = item as ScalarType; if (scalarType1 != null) { ParseScalarType(row, table, scalarType1.Item); return; } var logical = item as LogicalType; if (logical != null && logical.ScalarOperator != null) { foreach (var scalarType in logical.ScalarOperator) { ParseScalarType(row, table, scalarType); } return; } var subqueryType = item as SubqueryType; if (subqueryType != null && subqueryType.RelOp != null) { var childRow = AddExplainPlanRow(row.ChildRows, table, new object[] { "Subquery", "Subquery", null, null, null, null, null }); ParseRelOpBaseType(subqueryType.RelOp.Item, childRow, table); if (subqueryType.ScalarOperator != null) { ParseScalarType(childRow, table, subqueryType.ScalarOperator); } return; } var aggregateType = item as AggregateType; if (aggregateType != null && aggregateType.ScalarOperator != null) { foreach (var scalarType in aggregateType.ScalarOperator) { ParseScalarType(row, table, scalarType); } return; } var arithmeticType = item as ArithmeticType; if (arithmeticType != null && arithmeticType.ScalarOperator != null) { foreach (var scalarType in arithmeticType.ScalarOperator) { ParseScalarType(row, table, scalarType); } return; } var assignType = item as AssignType; if (assignType != null) { foreach (var scalarType in new [] { assignType.Item as ScalarType, assignType.ScalarOperator }.Where(x => x != null).ToList()) { ParseScalarType(row, table, scalarType); } return; } var compareType = item as CompareType; if (compareType != null) { foreach (var scalarType in new[] { assignType.Item as ScalarType, assignType.ScalarOperator }.Where(x => x != null).ToList()) { ParseScalarType(row, table, scalarType); } return; } var convertType = item as ConvertType; if (convertType != null && convertType.ScalarOperator != null) { ParseScalarType(row, table, convertType.ScalarOperator); return; } /* * [System.Xml.Serialization.XmlElementAttribute("Aggregate", typeof(AggregateType))] * [System.Xml.Serialization.XmlElementAttribute("Arithmetic", typeof(ArithmeticType))] * [System.Xml.Serialization.XmlElementAttribute("Assign", typeof(AssignType))] * [System.Xml.Serialization.XmlElementAttribute("Compare", typeof(CompareType))] * [System.Xml.Serialization.XmlElementAttribute("Const", typeof(ConstType))] * [System.Xml.Serialization.XmlElementAttribute("Convert", typeof(ConvertType))] * [System.Xml.Serialization.XmlElementAttribute("IF", typeof(ConditionalType))] * [System.Xml.Serialization.XmlElementAttribute("Identifier", typeof(IdentType))] * [System.Xml.Serialization.XmlElementAttribute("Intrinsic", typeof(IntrinsicType))] * [System.Xml.Serialization.XmlElementAttribute("Logical", typeof(LogicalType))] * [System.Xml.Serialization.XmlElementAttribute("MultipleAssign", typeof(MultAssignType))] * [System.Xml.Serialization.XmlElementAttribute("ScalarExpressionList", typeof(ScalarExpressionListType))] * [System.Xml.Serialization.XmlElementAttribute("Sequence", typeof(ScalarSequenceType))] * [System.Xml.Serialization.XmlElementAttribute("Subquery", typeof(SubqueryType))] * [System.Xml.Serialization.XmlElementAttribute("UDTMethod", typeof(UDTMethodType))] * [System.Xml.Serialization.XmlElementAttribute("UserDefinedAggregate", typeof(UDAggregateType))] * [System.Xml.Serialization.XmlElementAttribute("UserDefinedFunction", typeof(UDFType))] */ }
private static void ParseRelOpBaseType(RelOpBaseType relOp, ExplainPlanRow explainPlanRow, DataTable table) { if (relOp == null) { return; } var nestedLoopOp = relOp as NestedLoopsType; if (nestedLoopOp != null) { foreach (var op1 in nestedLoopOp.RelOp) { var childExplainPlanRow = AddExplainPlanRow(explainPlanRow.ChildRows, table, op1); if (childExplainPlanRow != null && op1.Item != null) { ParseRelOpBaseType(op1.Item, childExplainPlanRow, table); } } return; } var indexScan = relOp as IndexScanType; if (indexScan != null && indexScan.Object.Length > 0) { explainPlanRow.Row.SetField("ObjectName", indexScan.Object[0].Index); return; } var mergeType = relOp as MergeType; if (mergeType != null) { foreach (var op1 in mergeType.RelOp) { var childExplainPlanRow = AddExplainPlanRow(explainPlanRow.ChildRows, table, op1); if (childExplainPlanRow != null && op1.Item != null) { ParseRelOpBaseType(op1.Item, childExplainPlanRow, table); } } return; } var sortType = relOp as SortType; if (sortType != null) { var op1 = sortType.RelOp; var childExplainPlanRow = AddExplainPlanRow(explainPlanRow.ChildRows, table, op1); if (childExplainPlanRow != null && op1.Item != null) { ParseRelOpBaseType(op1.Item, childExplainPlanRow, table); } return; } var computeScalarType = relOp as ComputeScalarType; if (computeScalarType != null) { var op1 = computeScalarType.RelOp; var childExplainPlanRow = AddExplainPlanRow(explainPlanRow.ChildRows, table, op1); if (childExplainPlanRow != null && op1.Item != null) { ParseRelOpBaseType(op1.Item, childExplainPlanRow, table); } return; } var hashType = relOp as HashType; if (hashType != null) { foreach (var op1 in hashType.RelOp) { var childExplainPlanRow = AddExplainPlanRow(explainPlanRow.ChildRows, table, op1); if (childExplainPlanRow != null && op1.Item != null) { ParseRelOpBaseType(op1.Item, childExplainPlanRow, table); } } return; } var updateType = relOp as UpdateType; if (updateType != null) { var op1 = updateType.RelOp; var childExplainPlanRow = AddExplainPlanRow(explainPlanRow.ChildRows, table, op1); if (childExplainPlanRow != null && op1.Item != null) { ParseRelOpBaseType(op1.Item, childExplainPlanRow, table); } return; } var simpleUpdateType = relOp as SimpleUpdateType; if (simpleUpdateType != null && simpleUpdateType.Object.Length > 0) { explainPlanRow.Row.SetField("ObjectName", simpleUpdateType.Object[0].Index); return; } var tableScanType = relOp as TableScanType; if (tableScanType != null && tableScanType.Object.Length > 0) { explainPlanRow.Row.SetField("ObjectName", tableScanType.Object[0].Index); return; } var filterType = relOp as FilterType; if (filterType != null) { var op1 = filterType.RelOp; var childExplainPlanRow = AddExplainPlanRow(explainPlanRow.ChildRows, table, op1); if (childExplainPlanRow != null && op1.Item != null) { ParseRelOpBaseType(op1.Item, childExplainPlanRow, table); } var predicate = filterType.Predicate; if (predicate != null && predicate.ScalarOperator != null) { ParseScalarType(explainPlanRow, table, predicate.ScalarOperator.Item); } return; } /* * [System.Xml.Serialization.XmlIncludeAttribute(typeof(NestedLoopsType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(MergeType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(ConcatType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(CollapseType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(BitmapType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(SortType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(TopSortType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(StreamAggregateType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(ParallelismType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(ComputeScalarType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(HashType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(TableValuedFunctionType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(RowsetType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(ScalarInsertType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(CreateIndexType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(UpdateType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(SimpleUpdateType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(IndexScanType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(TableScanType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(ConstantScanType))] * [System.Xml.Serialization.XmlIncludeAttribute(typeof(FilterType))] */ }
public override ExplainPlanData GetExplainPlan([NotNull] DatabaseConnection databaseConnection, [NotNull] string sql) { if (databaseConnection == null) { throw new ArgumentNullException("databaseConnection"); } if (sql == null) { throw new ArgumentNullException("sql"); } var table = new DataTable(); using (var connection = databaseConnection.CreateNewConnection()) { connection.OpenIfRequired(); using (var transaction = connection.BeginTransaction()) { var user = databaseConnection.DatabaseServer.GetUserId(databaseConnection.ConnectionString); using (var command = connection.CreateCommand()) { command.Transaction = transaction; try { command.CommandText = "SELECT * FROM " + user + ".EXPLAIN_INSTANCE"; using (var dr = command.ExecuteReader(CommandBehavior.SchemaOnly)) { dr.Read(); } } catch (Exception ex) { throw new Exception("Could not SELECT from " + user + ".EXPLAIN_INSTANCE table. " + ex.Message); } command.CommandText = "DELETE FROM " + user + ".EXPLAIN_INSTANCE"; command.ExecuteNonQuery(); command.CommandText = "EXPLAIN PLAN FOR " + sql; command.ExecuteNonQuery(); command.CommandText = " SELECT O.OPERATOR_ID, S2.TARGET_ID AS PARENT_OPERATOR_ID, S2.SOURCE_ID, O.OPERATOR_TYPE, "+ Environment.NewLine; command.CommandText += " S.OBJECT_NAME, S.OBJECT_SCHEMA, S2.COLUMN_NAMES, O.IO_COST, O.CPU_COST, O.FIRST_ROW_COST, O.BUFFERS, O.TOTAL_COST COST "+ Environment.NewLine; command.CommandText += " FROM "+ user + ".EXPLAIN_OPERATOR O "+ Environment.NewLine; command.CommandText += " LEFT OUTER JOIN "+ user + ".EXPLAIN_STREAM S2 " + Environment.NewLine; command.CommandText += " ON O.Operator_ID=S2.Source_ID "+ Environment.NewLine; command.CommandText += " LEFT OUTER JOIN "+ user + ".EXPLAIN_STREAM S "+ Environment.NewLine; command.CommandText += " ON O.Operator_ID = S.Target_ID "+ Environment.NewLine; command.CommandText += " AND O.Explain_Time = S.Explain_Time "+ Environment.NewLine; command.CommandText += " AND S.Object_Name IS NOT NULL "+ Environment.NewLine; command.CommandText += " ORDER BY O.Explain_Time ASC, Operator_ID ASC; "+ Environment.NewLine; using (var dr = command.ExecuteReader()) { table.Load(dr); } } transaction.Rollback(); } } // Parse table var explainPlanData = new Db2ExplainPlanData(); var rootRow = table.Rows.Cast <DataRow>().Where(x => x.IsNull("PARENT_OPERATOR_ID")).ToList(); if (rootRow.Count == 0) { throw new Exception("Could not find a starting point in the execution plan."); } var explainPlanRow = new ExplainPlanRow(rootRow.First()); explainPlanData.Rows.Add(explainPlanRow); ParseExplainPlan(explainPlanRow, table, "OPERATOR_ID", "PARENT_OPERATOR_ID"); return(explainPlanData); }