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