Finding Degree of Parallelism in SQL Developer Plans

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.

How it works, from the Docs.

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:

Well, I don’t see the RED box outline, I added that.

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.

It’s THIS one.

So with that on, let’s get a plan, I’ll use the Cached Plan (v$sql_plan) feature.

That’s kinda small and hard to read, so let’s blow it up…

A bit bigger, so it’s easier to read:

It’s telling us the same thing SQL*Plus told us.

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.

P.S. Thanks @SQLInterstellar for today’s topic!

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569