Parallelism – where the database splits your task (Query) into multiple pieces, each running concurrently – theoretically making a lot of work happen for you, faster. Docs: How it Works.
So, now you’re probably asking, OK, so I have a query, and I really, really want it to be running in this parallel fashion, how can I know if it’s doing that or not?
One thing you check is ‘the plan.’
SQL*Plus Plan Notes
Scroll to the end of your plan, and you’ll see some notes.
For this query:
SELECT /*+ PARALLEL */ first_name, last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id;
In SQL*Plus I see this:
We’re being told a couple of things. HR.DEPARTMENTS influenced the degree of parallelism to be applied. And, said degree of parallelism was ‘2’.
In SQL Developer
Step One: Enable the ‘Other XML’ Column.
The PLAN TABLE in Oracle has been around for a long time. At a certain point, instead of adding new columns for things like, plan notes, we just added an XML column, that we could throw any old thing into.
SQL*Plus parses that information and prints it.
SQL Developer let’s you see ALL of the information in the ‘Other XML’ column, AFTER you turn it on.
So with that on, let’s get a plan, I’ll use the Cached Plan (v$sql_plan) feature.
A bit bigger, so it’s easier to read:
So again, we have the DOP, and the ‘Reason.’
This column has lots of goodies, including whenever Adaptive Plans have come into play, if an Outline or Baseline determined the plan, or what HINTS you could use to make the same plan pop out somewhere else.
