An explain plan is a representation of the access path that is taken when a query is executed within Oracle.
Query processing can be divided into 7 phases:
[1] Syntactic Checks the syntax of the query
[2] Semantic Checks that all objects exist and are accessible
[3] View Merging Rewrites query as join on base tables as opposed to using views [4] Statement
Transformation
Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
[5] Optimization Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
[6] QEP Generation QEP = Query Evaluation Plan [7] QEP Execution QEP = Query Evaluation Plan
Steps [1]-[6] are handled by the parser. Step [7] is the execution of the statement.
The explain plan is produced by the parser. Once the access path has been decided upon it is stored in the library cache together with the statement itself. We store queries in the library cache based upon a hashed representation of that query. When looking for a statement in the library cache, we first apply a hashing algorithm to the statement and then we look for this hash value in the library cache. This access path will be used until the query is reparsed.
Terminology
Row Source A set of rows used in a query may be a select from a base object or the result set returned by joining 2 earlier row sources
Predicate where clause of a query Tuples rows
Driving Table This is the row source that we use to seed the query. If this returns a lot of rows then this can have a negative effect on all subsequent operations Probed Table This is the object we lookup data in after we have retrieved relevant key data from the driving table.
43
Appendix D: Parser Code
import java.util.regex.*; import java.awt.Toolkit; import java.io.*;
public class Parser {
private Database db;
private BufferedReader input; private static Console console; private String filename;
private String line; private int swId; private int intOrder; private int linenum;
public Parser (Console console, Database db) throws Exception { this.db = db;
this.console = console; this.line = "";
}
public void parseFile (File file, String wareType) throws Exception {
input = new BufferedReader(new FileReader(file)); filename = file.getName(); String name; int classId; if (wareType.equals("m") || wareType.equals("M")) { name = filename.replaceFirst("(\\w+\\.){2}","").replace(".log",""); classId = db.getClassId(filename.replaceFirst("\\..*","")); } else { name = filename.replace(".log",""); classId = 100; } swId = db.getSoftwareId(name,classId); if (swId == 0) { db.insertSoftware(name,classId); swId = db.getMaxSoftwareId(); intOrder = 0; linenum = 0; //DELETE while ((line = input.readLine()) != null) {
linenum++;
parseLine(); }
44 }
private void parseLine () throws Exception { if (isFunction()) { handleFunction(); } else if (isPageFault()) { if (line.contains("PageFault tbl process")) { handlePageFault(); } } else if (isException()) { if (line.contains("**EXCEPTION: ThreadID")) { handleException(); } } else if (isPortConnection()) { handlePortConnection(); } else if (isBindSocket()) { handleSocketBind(); } else if (isOpenNetwork()) { handleOpenNetwork(); } else if (isSendToPort()) { handleSendToPort(); } else { updateElses(); } }
private boolean isFunction() {
if (line.matches("(^0x\\w{8}=.*)|(^0x\\w{6}=.*)| (^0x\\w{7}=.*)|(^MSVBVM60!.*)")) return true; else return false; }
private boolean isPageFault() {
if (line.matches("(.*\\*\\*PAGE FAULT.*)| (.*PageFault.*)|(.*\\|offset.*)")) return true; else return false; }
private boolean isException() {
if (line.matches("(.*\\*\\*EXCEPTION.*)|(.*==>cs:eip.*)| (.*<==cs:eip.*)|(^\\s*dr0=.*)"))
45 return true; else return false; }
private boolean isPortConnection() { if (line.matches("^-connect port.*")) return true; else return false; }
private boolean isOpenNetwork() {
if (line.matches("^Open network.*")) return true; else return false; }
private boolean isBindSocket() {
if (line.matches("^-binds socket.*")) return true; else return false; }
private boolean isSendToPort() {
if (line.matches("^-sendto port.*")) return true; else return false; }
private void handlePageFault() throws Exception { intOrder++;
db.insertInteraction(swId, 100, intOrder, ""); int intId = db.getMaxInteractionId();
String[] arr = line.split("\\s");
db.insertInteractionDetail(intId, "Table Process",
Integer.toString(Integer.parseInt(arr[3].replace("0x",""),16)),1 ); db.insertInteractionDetail(intId, "Entries", Integer.toString(Integer.parseInt(arr[5].replace("0x",""),16)),2 ); }
private void handleException() throws Exception { intOrder++;
db.insertInteraction(swId, 101, intOrder, ""); int intId = db.getMaxInteractionId();
String[] arr = line.split("\\s");
46
db.insertInteractionDetail(intId, "Opcodee", arr[4], 2); db.insertInteractionDetail(intId, "SEH", arr[5].replaceFirst("SEH=",""), 3); db.insertInteractionDetail(intId, "FaultCode", arr[6].replaceFirst("FaultCode=",""), 4); db.insertInteractionDetail(intId, "EFlags", arr[7].replaceFirst("EFlags=",""), 5); }
private void handlePortConnection() throws Exception { intOrder++;
db.insertInteraction(swId, 102, intOrder, ""); int intId = db.getMaxInteractionId();
String[] arr = line.split("\\s");
db.insertInteractionDetail(intId, "Port", arr[2].replaceFirst(",$",""), 1);
db.insertInteractionDetail(intId, "Connection Type", arr[3], 2); db.insertInteractionDetail(intId, arr[4], arr[5], 3);
}
private void handleSocketBind() throws Exception { intOrder++;
db.insertInteraction(swId, 103, intOrder, ""); int intId = db.getMaxInteractionId();
String[] arr = line.split("\\s");
db.insertInteractionDetail(intId, "Socket Number", arr[2], 1); db.insertInteractionDetail(intId, "Port", arr[5], 2);
}
private void handleOpenNetwork() throws Exception { intOrder++;
db.insertInteraction(swId, 104, intOrder, ""); int intId = db.getMaxInteractionId();
db.insertInteractionDetail(intId, "Resource",
line.replaceFirst("^Open network resource ",""), 1); }
private void handleSendToPort() throws Exception { intOrder++;
db.insertInteraction(swId, 105, intOrder, ""); int intId = db.getMaxInteractionId();
String[] arr = line.split("\\s"); db.insertInteractionDetail(intId, "Port",
arr[2].replaceFirst(",$",""), 1);
db.insertInteractionDetail(intId, "To Port", arr[4], 2); }
private void handleFunction() throws Exception {
//INSERT BEHAVIOR
String function = checkFunctionEnd(); String type = "Function";
47 String detail = "DLL"; String name = ""; String detail_value = ""; if (function.charAt(10) == '=') { name = function.replaceFirst("^0x\\w{8}=\\w*\\!","").replaceAll("(\\s|) \\(.*",""); detail_value = function.replaceFirst("^0x\\w{8}=","").replaceAll("\\!.*",""); } else if (function.charAt(9) == '=') { name = function.replaceFirst("^0x\\w{7}=\\w*\\!","").replaceAll("(\\s|) \\(.*",""); detail_value = function.replaceFirst("^0x\\w{7}=","").replaceAll("\\!.*",""); } else if (function.matches("^MSVBVM60!.*")) { name = function.replaceFirst("MSVBVM60!","").replaceAll("(\\s|)\\(.*"," "); detail_value = "MSVBVM60"; } else if (function.charAt(8) == '=') { name = function.replaceFirst("^0x\\w{6}=\\w*\\!","").replaceAll("(\\s|) \\(.*",""); detail_value = function.replaceFirst("^0x\\w{6}=","").replaceAll("\\!.*",""); } else {
System.out.println("\nFUNCTION DID NOT MEET ANY CONDITIONS!\n"+function+"\n");
}
int bId = db.getBehaviorId(name,detail_value); if (bId == 0) { db.insertBehavior(name,type,detail,detail_value); bId = db.getMaxBehaviorId(); } //INSERT INTERACTION intOrder++;
Pattern pattern = Pattern.compile("\\(.*\\)"); Matcher matcher = pattern.matcher(function); String parameters;
if (matcher.find()) {
parameters = matcher.group().replaceAll("'","''"); }
else if (detail_value.equals("gdi32") && name.equals("CreateDIBitmap")) {
parameters = "Parameters incomplete"; }
48 else {
parameters = "No parameters"; }
db.insertInteraction(swId, bId, intOrder, parameters); int intId = db.getMaxInteractionId();
}
private String checkFunctionEnd() throws Exception { String current = line;
boolean append = true; while (append) { input.mark(1000);
if ((line = input.readLine()) != null && !isFunction() && !isPageFault() && !isException() && !isPortConnection() && !isBindSocket() && !isOpenNetwork() && !isSendToPort()) { if (!autoAppend(current)) { System.out.println("\n"+filename+"\n\nPrevious line: "+current+"\nNext line: "+line+"\n");
prompt();
String option = console.readLine("Enter option number: "); if (option.equals("1")) {
input.reset(); append = false; }
else if (option.equals("2")) { current = current+" "+line; append = true; } else if (option.equals("3")) { append = false; } }
else { //append without prompts current = current+" "+line; append = true;
} }
else {//is fucntion, pagefault, exception, port connection, bind socket, or open network
input.reset(); append = false; } } return current; }
49 if (line.matches("\",0x\\w{8}\\.\\.\\.\\.\\)") || !current.matches(".*\\)$") || line.equals("")) return true; else return false; }
private void prompt() {
System.out.println("1. Previous line is complete, continue parsing next line.\n" +
"2. Previous line is not complete, append next line.\n" +
"3. Next line is a new behavior type. It will be added manually; continue.\n");
} }
50
Appendix E: Combination Generator
public class Pattern3 {
private static Database db;
public static void main(String[] args) throws Exception { Database db = new Database();
int[] elements = db.getBehaviorIdPerm(); int[] indices;
int[] pattern = new int[3];
CombinationGenerator cg = new CombinationGenerator (elements.length, 3); while (cg.hasMore()) {
indices = cg.getNext();
for (int i = 0; i < indices.length; i++) { pattern[i] = elements[indices[i]]; } db.insertPattern3(pattern); } db.close(); } }
This class was created by Michael Gilleland, http://www.merriampark.com/comb.htm#Source
import java.math.BigInteger;
public class CombinationGenerator { private int[] a;
private int n; private int r;
private BigInteger numLeft; private BigInteger total;
public CombinationGenerator (int n, int r) { this.n = n;
this.r = r; a = new int[r];
BigInteger nFact = getFactorial (n); BigInteger rFact = getFactorial (r);
BigInteger nminusrFact = getFactorial (n - r);
total = nFact.divide (rFact.multiply (nminusrFact)); reset ();
}
51 public void reset () {
for (int i = 0; i < a.length; i++) { a[i] = i;
}
numLeft = new BigInteger (total.toString ()); }
public BigInteger getNumLeft () { return numLeft;
}
public boolean hasMore () {
return numLeft.compareTo (BigInteger.ZERO) == 1; }
public BigInteger getTotal () { return total;
}
private static BigInteger getFactorial (int n) { BigInteger fact = BigInteger.ONE;
for (int i = n; i > 1; i--) {
fact = fact.multiply (new BigInteger (Integer.toString (i))); }
return fact; }
public int[] getNext () {
if (numLeft.equals (total)) {
numLeft = numLeft.subtract (BigInteger.ONE); return a; } int i = r - 1; while (a[i] == n - r + i) { i--; } a[i] = a[i] + 1; for (int j = i + 1; j < r; j++) { a[j] = a[i] + j - i; }
numLeft = numLeft.subtract (BigInteger.ONE); return a;
} }
52