To analyse the SQL generated by the BI Server you will most likely use the Explain Plan; this blog hopes to increase your understanding and ultimately help resolve performance issues.
What is an Explain Plan
An execution plan, sometimes called a row source tree, is logically a tree of row source operators, where each operator is just a function written in C. The functions are mutually exclusive; they have no knowledge of one another. Some operators are hidden, such as the STATISTICS operator, and are not visible in the Explain Plan. Some operators also have options, affecting their behaviour; such as the INDEX operation which has options such as UNIQUE, RANGE SCAN, SKIP and SCAN, etc.
We can run and display an Explain Plan to help us understand which functions are being chosen by the Oracle Optimizer. Through experience you will learn that some functions do certain jobs better than others; we can manipulate the SQL or DB settings to ensure the Optimizer chooses the better function.
Importantly, the Explain Plan contains the following information regarding your query.
Running an Explain Plan
A table exists, the Plan Table; it is set up automatically, that stores the Explain Plans for all users. To submit a query to the table we must precede the query with the command EXPLAIN PLAN FOR, as in the example below. More information is available in abundance online for those interested.
Displaying the Explain Plan
What is an Explain Plan
An execution plan, sometimes called a row source tree, is logically a tree of row source operators, where each operator is just a function written in C. The functions are mutually exclusive; they have no knowledge of one another. Some operators are hidden, such as the STATISTICS operator, and are not visible in the Explain Plan. Some operators also have options, affecting their behaviour; such as the INDEX operation which has options such as UNIQUE, RANGE SCAN, SKIP and SCAN, etc.
We can run and display an Explain Plan to help us understand which functions are being chosen by the Oracle Optimizer. Through experience you will learn that some functions do certain jobs better than others; we can manipulate the SQL or DB settings to ensure the Optimizer chooses the better function.
Importantly, the Explain Plan contains the following information regarding your query.
- Ordering of the tables referenced
- Access method for each table
- Join method for each join operation
- Data operations, such as filter, sort or aggregation
- Optimization (Cost and Cardinality)
- Partitioning
- Parallel Execution
Running an Explain Plan
A table exists, the Plan Table; it is set up automatically, that stores the Explain Plans for all users. To submit a query to the table we must precede the query with the command EXPLAIN PLAN FOR, as in the example below. More information is available in abundance online for those interested.
Submitting a query to the Plan Table
Once we have asked for an Explain Plan to be made available through through the Plan Table. We need to display to query to read it. The command below is one way to display the contents of the Plan Table. The command below will get you by, but there are more options available; again information available all over the Interent.
Displaying Plan Table
Understanding the Plan Table Output
No comments:
Post a Comment