SQL Profile: Case 1 - Part 1: Create/Execute/Report/Accept

SQL Profile: Case 1 - Part 2: Data Dictionary

Case 1: The statement is never been run. In this case, we obviously know the sql text.

For this post, please see this for understand the tables envolved.

Suppose you want to run following query

select 
      ooo.owner
    , oao.object_name 
from 
      OSL_ALL_OBJECTS   oao 
join  OSL_OWNER_OBJECTS ooo 
on
      (ooo.object_id = oao.object_id);

and you never ran it. So you want that SQL Tuning Advisor, perform a tuing task for you.

Usually, you have to run 4 steps:

(a) Create a tuning task
(b) Execute the tuning task
(c) Report the tuning task
(d) Accept the tuning task

You have to run the last one, if you accept what the advisor tell you.



Step a) Create a tuning task

declare
    task_name VARCHAR2(30);
    sqltext CLOB;
begin
    sqltext := q'[select ooo.owner, oao.object_name ]';
    sqltext := sqltext||q'[from OSL_ALL_OBJECTS oao join OSL_OWNER_OBJECTS ooo ]';
    sqltext := sqltext|| q'[on (ooo.object_id=oao.object_id)]';
    
    task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_text => sqltext,
        user_name => 'ASALZANO',
        scope => 'COMPREHENSIVE',
        time_limit => 30, -- nr of seconds of analisys
        task_name => 'task_select',
        description => 'Task to tune a query');
end;
/

PL/SQL procedure successfully completed.

The "task_name" is just a name that I was given. You have to choose a different name (if you want).



Step b) Execute the tuning task

BEGIN
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'task_select');
END;
/



Step c) Report the tuning task

set long 10000
set longchunksize 1000
set linesize 230
set heading off

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('task_select') from DUAL;

set heading on

You can find the full output in this file. What you see?

• The original plan

1- Original
-----------
Plan hash value: 3252867984

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |  9999K|   371M|       | 16476   (1)| 00:00:03 |
|*  1 |  HASH JOIN         |                   |  9999K|   371M|  7712K| 16476   (1)| 00:00:03 |
|   2 |   TABLE ACCESS FULL| OSL_OWNER_OBJECTS |   262K|  4613K|       |   113   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| OSL_ALL_OBJECTS   |  9999K|   200M|       |  5516   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------

• The reccomandation:

2- Using Parallel Execution

---------------------------
Plan hash value: 3357147682

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |  9999K|   371M|  3136   (1)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR       |                   |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000          |  9999K|   371M|  3136   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN          |                   |  9999K|   371M|  3136   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     TABLE ACCESS FULL | OSL_OWNER_OBJECTS |   262K|  4613K|    63   (2)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |     PX BLOCK ITERATOR |                   |  9999K|   200M|  3063   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |      TABLE ACCESS FULL| OSL_ALL_OBJECTS   |  9999K|   200M|  3063   (1)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------

Step d) Accept the tuning task

In the "SQL Profile Finding" section, you find the command that  you have to run, if you want to accept the reccomandation:

execute dbms_sqltune.accept_sql_profile( -
      task_name => 'task_select' -
    , task_owner => 'ASALZANO' -
    , replace => TRUE
    , profile_type => DBMS_SQLTUNE.PX_PROFILE);


 I prefere to give to the SQL Profile a my name, so I mofify a little the previous statement:

execute dbms_sqltune.accept_sql_profile( -
      task_name => 'task_select'         -
    , task_owner => 'ASALZANO'           -
    , replace => TRUE                    -
    , name => 'profile_7srkyyv9jxhzm'    -
    , profile_type => DBMS_SQLTUNE.PX_PROFILE);



Delete/Disable/Modify SQL Profile

If you wnat to drop or modify the SQL Profile and/or the Tuning task, you have to run DBMS_SQLTUNE package. For my example:

• Drop the Tuning task

exec DBMS_SQLTUNE.DROP_TUNING_TASK ('task_select')

• Drop the SQL Profile

exec DBMS_SQLTUNE.DROP_SQL_PROFILE ('profile_7srkyyv9jxhzm')

• Disable (modify) the SQL Profile

exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (  -
      name =>  'profile_7srkyyv9jxhzm' -
    , attribute_name => 'STATUS'       -
    , value =>  'DISABLED')



SQL Profile: Case 1 - Part 2: Data Dictionary

SQL Profile: Enviroment

In order to show the SQL Profile cases, I have created a little environment.

This post is for my SQL Profile series

drop table OSL_ALL_OBJECTS;

drop table OSL_OWNER_OBJECTS;

create table OSL_ALL_OBJECTS as

    with generator as (select /*+ materialize */ 

        OBJECT_ID, OBJECT_NAME, OBJECT_TYPE 

        from dba_objects where rownum<10000 font="">

    select a.OBJECT_ID, a.OBJECT_NAME, a.OBJECT_TYPE

    from generator a, generator b

    where rownum > 10000000;

create table OSL_OWNER_OBJECTS as select OBJECT_ID, OWNER from dba_objects;

create index ooo_od_ix on OSL_OWNER_OBJECTS (OBJECT_ID);

create index oao_od_ix on OSL_ALL_OBJECTS (OBJECT_ID);

SQL> desc OSL_ALL_OBJECTS 
 Name                           Null?    Type 
 ------------------------------ -------- ---------------------- 
 OBJECT_ID                      NOT NULL NUMBER 
 OBJECT_NAME                             VARCHAR2(128) 
 SUBOBJECT_NAME                          VARCHAR2(128) 


SQL> desc OSL_MOD2_OWNER 
 Name                           Null?    Type 
 ------------------------------ -------- ---------------------- 
 OBJECT_ID                      NOT NULL NUMBER 
 OWNER                                   VARCHAR2(128)


SQL> select count(*) from OSL_OWNER_OBJECTS;

  COUNT(*)
----------
    132364


SQL> select count(*) from OSL_ALL_OBJECTS;

  COUNT(*)
----------
    264746

SQL Profile

Abstract
A SQL statement’s execution plan can change. The lack of a guarantee that a changed plan will always better lead some customers to several ways to manage this issue:

• lock the optimizer statistics
• lock the execution plans, using stored outlines

Starting with Oracle 10g, we have several options to respond to plan changing. One of these is SQL Profile, or more correctly SQL Tuning Advisor.



Introduction
From manual’s page:

SQL Tuning Advisor is SQL diagnostic software in the Oracle Database Tuning Pack. It is a mechanism for resolving problems related to suboptimally performing SQL statements. Use SQL Tuning Advisor to obtain recommendations for improving performance. Tuning recommendations include also the creation of SQL profiles.

Here, I want to explain how to generate a SQL Profile both using and not using SQL Tuning Advisor.

There are 5 scenarios on which we can work

1. The statement is never been run. In this case, we obviously know the sql text.
2. The statement is in SQL Area but have a wrong execution plan
3. The statement is in AWR repository 
4. The statement is in SQL Area and it have two or more execution plan 
5. The statement is neither in SQL Area nor in AWR 

With the first three, we use the SQL Tuning Advisor. With the last two, we create manually the SQL Profile.

I think that those scenarios cover all possible cases. In the next posts, I'll discuss all 5 cases.



Create SQL Profile using SQL Tuning Advisor (cases 1-3)
You can interact with SQL Tuning Advisor through DBMS_SQLTUNE package.

When you invoice SQL Tuning Advisor you have to follow 4 steps:
(a) Create a tuning task: This step create a task
(b) Execute the tuning task: This step analyze the issue and provide recommendations
(c) Report the tuning task: This step show the recommendations
(d) Accept the SQL Profile: This step accept the recommendations



Create SQL Profile without using SQL Tuning Advisor (cases 4-5) 
With respect to the previous case, when you run SQL Tuning Advisor manually, you need only the step (d). This is because in this case you already know all information (HINTs) in order to create a SQL Profile.


SQL Profile: The statement is never been run Part 1/Part 2
SQL Profile: The statement is in SQL Area but have a wrong execution plan (under construction)
SQL Profile: The statement is in AWR repository (under construction)
SQL Profile: The statement is in SQL Area and it have two or more execution plan (under construction)
SQL Profile: The statement is neither in SQL Area nor in AWR (under construction)

Access before the filter

This post is about what happens during anti-join operation.

Usually, when there is a JOIN+FILTER, the FILTER is made before the JOIN (the access). This is because in this way the CBO can JOIN only a subset of rows of a table with another one.

But there is a case when the FILTER is applied after the ACCESS.

In this post, I show you when it happens.

For this example, I created 2 tables. Here you can find the script for creating and populating them.

These are their contents:

NAMES table

DETAILS table

The ID column is the one I use in order to join the tables.

Well. What happens when I make a join between those two tables?

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  join details d 

    on (n.id = d.id);

Here it is.

Join 01

Now I add a filter to my query

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  join details d 

    on (n.id = d.id)

 where n.name='Diana';

This is the execution plan

Join 02

This is exactly what I aspect. The filter is before of the access (the join).

In "Join 02" picture, the CBO apply the filter to the NAMES table and then make the join. Obviously, the access is made through the "ID" columns of both tables.

For my goal, I change a little the where condition:

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  join details d 

    on (n.id = d.id)

 where d.street is NULL;

This is the execution plan

Join 03

As usual, the filter is applied before the join (the access). The steps are applied in this order:

1. FILTER (id 2 of execution plan) => D.STREET IS NULL
2. ACCESS (id 1 of execution plan) at row 1 => N.ID=D.ID

At this point I re-run all previous statements, using a (LEFT) OUTER JOIN

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  left join details d 

    on (n.id = d.id);

Join 04

The difference between the select, are;

1. Join 01: ACCESS => N.ID=D.ID
2. Join 04: ACCESS => N.ID=D.ID (+)

Using the filter....

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  left join details d 

    on (n.id = d.id)

 where n.name='Diana';

Join 05

... the ACCESS and the FILTER are the same (more or less)

1. Join 02: ACCESS => N.ID=D.ID    / FILTER => N.NAME='DIANA'
2. Join 05: ACCESS => N.ID=D.ID(+) / FILTER => N.NAME='DIANA'

For the last step, I apply the filter:

select 

       n.id

     , n.name

     , n.surname

     , d.street

  from names n 

  left join details d 

    on (n.id = d.id)

 where d.street is NULL;

Join 06

Here is the point. How you can see in this last case, the access (the join) is before the filter. It is the opposite of the Join 03. This is because now we make an ANTI (OUTER) JOIN. This is the order of operations:

1. ACCESS, (id 2 of execution plan) => N.ID=D.ID(+)
2. FILTER, (id 1 of execution plan) => D.STREET IS NULL

vmstat: average output

The OraPub community is very active. We chat via slack.

Someone asked a script, in order to average the output of vmstat command.

In order to do this, I made a python script. You can find it, here.

You can use it in tow ways.

1. Save the output of vmstat into a file, then use my script
2. Run my script interactively

• Save the output of vmstat into a file, then use my script

i. run vmstat and save the output

oracle> vmstat 5 5 > vmstat out

ii. Use my script

oracle> cat vmstat.out|vmstat.py

============

 Samples: 4

============

Avg Proc   : 30.2 (Sum=121, Min=18, Max=37)

Avg User   : 19.0 (Sum=76, Min=18, Max=20)

Avg System : 10.8 (Sum=43, Min=10, Max=11)

Avg Idle   : 67.2 (Sum=269, Min=65, Max=68)

Avg Waiting:  3.0 (Sum=12, Min=1, Max=5)

Avg Stolen :  0.0 (Sum=0, Min=0, Max=0)

• Run my script interactively

i. run vmstat and pipe my script

oracle> vmstat 5 5 |vmstat.py

============

 Samples: 4

============

Avg Proc   : 46.5 (Sum=186, Min=22, Max=61)

Avg User   : 22.5 (Sum=90, Min=21, Max=25)

Avg System : 14.8 (Sum=59, Min=14, Max=16)

Avg Idle   : 57.2 (Sum=229, Min=49, Max=60)

Avg Waiting:  5.2 (Sum=21, Min=3, Max=10)

Avg Stolen :  0.0 (Sum=0, Min=0, Max=0)

It works on Linux, Solaris, AIX and HP-UX.

I also made a script that read the vmstat output from the Oracle OS Watcher. Here an example

oracle> cat myserver_vmstat_17.03.15.1300.dat |osWatchVmstat.py
============
 Samples: 162
============
Avg Proc   : 40.2 (Sum=6511, Min=37, Max=37)
Avg User   : 14.9 (Sum=2420, Min=22, Max=22)
Avg System :  8.2 (Sum=1336, Min=16, Max=16)
Avg Idle   : 71.4 (Sum=11570, Min=41, Max=41)
Avg Waiting:  5.4 (Sum=868, Min=22, Max=22)
Avg Stolen :  0.0 (Sum=0, Min=0, Max=0)

where "myserver_vmstat_17.03.15.1300.dat" is the output from OS Watch

HINTs from V$SQL_PLAN

In this post I want to understand how to extract the hint, querying the V$SQL_PLAN.

The select I use, have to return just one row, otherwise

If it return more than one row, then you have the following exception

ORA-01427: single-row subquery returns more than one row

If it return NULL, then you have the following exception

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "SYS.XMLTYPE", line 272

ORA-06512: at line 1

Investigating SQL_ID= '93hwgvnjag3sz', I have more than 1 row in V$SQL_PLAN

select SQL_ID,CHILD_NUMBER,OTHER_XML 
from v$sql_plan 
where sql_id ='93hwgvnjag3sz';

For this reason, I have to modify the statement as

select other_xml

from gv$sql_plan

where sql_id       = '93hwgvnjag3sz'

  and child_number = 0

  and other_xml    is not null;

In order to understand how this xml is composed, I manually format the output

So I have two sections: "other_xml" and "outline_data". Because I want to get just the hints, I rewrite the query as

select xmltype(other_xml).extract('/other_xml/outline_data')

from v$sql_plan

where sql_id       = '93hwgvnjag3sz'

  and child_number = 0

  and other_xml    is not null;

and if I reformat the output ......

It's not enough, I have to go down another level. To do this I rewrite the query

select xmltype(other_xml).extract('/other_xml/outline_data/hint')

from v$sql_plan

where sql_id       = '93hwgvnjag3sz'

  and child_number = 0

  and other_xml    is not null;

Because I want to see the output in better way, I use XMLTABLE function

select *

from xmltable ('/*' passing (

                             select xmltype(other_xml).extract('/other_xml/outline_data/hint')

                             from v$sql_plan

                             where sql_id       = '93hwgvnjag3sz'

                               and child_number = 0

                               and other_xml    is not null)

);

XMLTABLE function, change the output of xml in an "xml table" (I don't know if it's correct) and the output is well formatted. In order to simplify the statement, I move the logic into outer select.

select *

from xmltable ('/other_xml/outline_data/hint' passing (

                                              select xmltype(other_xml)

                                              from v$sql_plan

                                              where sql_id     = '93hwgvnjag3sz'

                                              and child_number = 0

                                              and other_xml    is not null)

);

(obviously, the output is the same). At this point, I extract the hints from the XML

select extractvalue(value(xmltab), '.') hints

  from xmltable ('/other_xml/outline_data/hint' passing (

                                                select xmltype(other_xml)

                                                from v$sql_plan

                                                where sql_id     = '93hwgvnjag3sz'

                                                and child_number = 0

                                                and other_xml    is not null)

) xmltab;

Cool!!!

NO_MERGE hint, example (2/2)

Part 1

Now, what I run is (I call this CQ - CA Query )

SELECT x.tablespace_name, sum(x.bytes) bytes_expired
FROM ( SELECT a.tablespace_name, bytes FROM dba_undo_extents a WHERE status = 'EXPIRED') x
GROUP BY x.tablespace_name;

SQL> set timing on 
SQL> set autot on exp stat
SQL> set lines 160


SELECT x.tablespace_name, sum(x.bytes) bytes_expired 
FROM ( SELECT a.tablespace_name, bytes FROM dba_undo_extents a WHERE status = 'EXPIRED') x 
GROUP BY x.tablespace_name;

TABLESPACE_NAME                BYTES_EXPIRED
------------------------------ -------------
UNDOTBS1                       1844445184 
UNDOTBS2                       4526112768 

 Elapsed: 00:03:52.20 

Execution Plan 
---------------------------------------------------------- 

Plan hash value: 2680378823 
-------------------------------------------------------------------------------- 
| Id   | Operation              | Name     | Rows | Bytes | Cost (%CPU)| Time  | 
-------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT        |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   1 |  HASH GROUP BY          |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   2 |   NESTED LOOPS          |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   3 |    VIEW                 | VW_GBC_5 |  109 |  5014 | 77  (62)| 00:00:01 | 
|   4 |     HASH GROUP BY       |          |  109 | 13625 | 77  (62)| 00:00:01 | 
|*  5 |      HASH JOIN          |          |  182 | 22750 | 76  (61)| 00:00:01 | 
|*  6 |       HASH JOIN         |          |  182 | 17108 | 48  (96)| 00:00:01 | 
|*  7 |        TABLE ACCESS FULL| UNDO$    |  141 |  2256 |  2   (0)| 00:00:01 | 
|*  8 |        FIXED TABLE FULL | X$KTFBUE | 1000 | 78000 | 45 (100)| 00:00:01 | 
|*  9 |       TABLE ACCESS FULL | TS$      |  154 |  4774 | 28   (0)| 00:00:01 | 
|* 10 |    INDEX UNIQUE SCAN    | I_FILE2  |    1 |     8 |  0   (0)| 00:00:01 | 
-------------------------------------------------------------------------------- 

 Predicate Information (identified by operation id): 
--------------------------------------------------- 
  5 - access("T"."TS#"="U"."TS#") 
  6 - access("E"."KTFBUESEGTSN"="U"."TS#" AND "E"."KTFBUESEGBNO"="U"."BLOCK#" 
             AND "E"."KTFBUESEGFNO"="U"."FILE#") 
  7 - filter(("U"."SPARE1"=1 OR "U"."SPARE1"=2) AND "U"."STATUS$"<>1) 
  8 - filter(DECODE("E"."KTFBUESTA",1,'ACTIVE',2,'EXPIRED',3,'UNEXPIRED','U NDEFINED')='EXPIRED') 
  9 - filter(BITAND("T"."FLAGS",16777216)=0) 
 10 - access("ITEM_1"="F"."TS#" AND "ITEM_2"="F"."RELFILE#") 

 Statistics 
---------------------------------------------------------- 
  84496 recursive calls 
  61604 db block gets 
 447085 consistent gets 
 140720 physical reads 
      0 redo size 
    704 bytes sent via SQL*Net to client 
    552 bytes received via SQL*Net from client 
      2 SQL*Net roundtrips to/from client 
     16 sorts (memory) 
      0 sorts (disk) 
      2 rows processed

If you compare "AQ" execution plan and "CQ" execution plan, you can see that they are the same. This mean that CBO made some manipulations during the choice of the best plan.

Pay attention. The behavior of the CBO is normal. The problem is that sometime the choice of the execution plan is not the best.

The manipulation that CBO used on "CQ" is to merge the external and internal select together. The effect is that "CQ" becomes "AQ".

So, what I do now, is to use the NO_MERGE hint, in order to force CBO to not make manipulation (statement DQ).


SELECT /*+ NO_MERGE(x) */ tablespace_name, sum(bytes) bytes_expired from 
( select a.tablespace_name, bytes FROM dba_undo_extents a WHERE status = 'EXPIRED') x 
group by tablespace_name;

TABLESPACE_NAME                BYTES_EXPIRED 
------------------------------ ------------- 
UNDOTBS1                       1400766464 
UNDOTBS2                       4706729984 

Elapsed: 00:00:00.08 


Execution Plan 
---------------------------------------------------------- 

Plan hash value: 1487528911 
--------------------------------------------------------------------------------------------- 
| Id | Operation                   | Name             | Rows| Bytes | Cost (%CPU)| Time     | 
--------------------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT            |                  |   1 |    30 |  1985  (99)| 00:00:01 | 
|  1 |  HASH GROUP BY              |                  |   1 |    30 |  1985  (99)| 00:00:01 | 
|  2 |   VIEW                      |                  |   1 |    30 |  1984  (99)| 00:00:01 | 
|  3 |    NESTED LOOPS             |                  |   1 |   133 |  1984  (99)| 00:00:01 | 
|  4 |     NESTED LOOPS            |                  | 182 | 22750 |  1984  (99)| 00:00:01 | 
|* 5 |      HASH JOIN              |                  | 141 |  6627 |    30   (0)| 00:00:01 | 
|* 6 |       TABLE ACCESS FULL     | UNDO$            | 141 |  2256 |     2   (0)| 00:00:01 | 
|* 7 |       TABLE ACCESS FULL     | TS$              | 154 |  4774 |    28   (0)| 00:00:01 | 
|* 8 |      FIXED TABLE FIXED INDEX| X$KTFBUE (ind:1) |   1 |    78 |    14 (100)| 00:00:01 | 
|* 9 |     INDEX UNIQUE SCAN       | I_FILE2          |   1 |     8 |     0   (0)| 00:00:01 | 
--------------------------------------------------------------------------------------------- 

 Predicate Information (identified by operation id): 
--------------------------------------------------- 
 5 - access("T"."TS#"="U"."TS#") 
 6 - filter(("U"."SPARE1"=1 OR "U"."SPARE1"=2) AND "U"."STATUS$"<>1) 
 7 - filter(BITAND("T"."FLAGS",16777216)=0) 
 8 - filter("E"."KTFBUESEGBNO"="U"."BLOCK#" 
            AND "E"."KTFBUESEGFNO"="U"."FILE#" AND "E"."KTFBUESEGTSN"="U"."TS#" 
            AND DECODE("E"."KTFBUESTA",1,'ACTIVE',2,'EXPIRED', 3,'UNEXPIRED','UNDEFINED')='EXPIRED') 
 9 - access("U"."TS#"="F"."TS#" AND "E"."KTFBUEFNO"="F"."RELFILE#") 

 Note ----- 
 - this is an adaptive plan 

 Statistics 
---------------------------------------------------------- 
 141 recursive calls 
   0 db block gets 
 876 consistent gets 
  25 physical reads 
   0 redo size 
 704 bytes sent via SQL*Net to client 
 552 bytes received via SQL*Net from client 
   2 SQL*Net roundtrips to/from client 
   0 sorts (memory) 
   0 sorts (disk) 
   2 rows processed


Now, if you compare the BQ execution plan with DQ execution plan you can see that there are just 2 more rows:

DQ execution plan, has

|  1 |  HASH GROUP BY
|  2 |   VIEW 

and this is exactly what I want:

1. Run the BQ, so it takes just few seconds  => rows 3-9 into DQ execution plan
2. Run the aggregation ong the BQ                  => rows  1-2 into  DQ execution plan

Note:
I have modified the output of explain plan because the blog visualization.

Part 1

NO_MERGE hint, example (1/2)

Part 2

This query (that I call AQ - A Query (A stay for "the first one") ) takes 3 minutes and 44 seconds.

SELECT a.tablespace_name, SUM(a.bytes) bytes_expired
FROM dba_undo_extents a
WHERE status = 'EXPIRED'
GROUP BY tablespace_name;

Here is the explain plan and statistics

SQL> set timing on 
SQL> set autot on exp stat 
SQL> set lines 160 

SQL> SELECT a.tablespace_name, SUM(a.bytes) bytes_expired 
   > FROM dba_undo_extents a WHERE status = 'EXPIRED' 
   > GROUP BY tablespace_name; 

TABLESPACE_NAME                BYTES_EXPIRED 
------------------------------ ------------- 
UNDOTBS1                       1677983744 
UNDOTBS2                       5107023872 

Elapsed: 00:03:44.07 

Execution Plan 
---------------------------------------------------------- 

 Plan hash value: 2680378823 
--------------------------------------------------------------------------------- 
| Id  | Operation                | Name     | Rows | Bytes | Cost (%CPU)| Time  | 
--------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT         |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   1 |  HASH GROUP BY           |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   2 |   NESTED LOOPS           |          |  109 |  5886 | 77  (62)| 00:00:01 | 
|   3 |    VIEW                  | VW_GBC_5 |  109 |  5014 | 77  (62)| 00:00:01 | 
|   4 |     HASH GROUP BY        |          |  109 | 13625 | 77  (62)| 00:00:01 | 
|*  5 |      HASH JOIN           |          |  182 | 22750 | 76  (61)| 00:00:01 | 
|*  6 |       HASH JOIN          |          |  182 | 17108 | 48  (96)| 00:00:01 | 
|*  7 |        TABLE ACCESS FULL | UNDO$    |  141 |  2256 |  2   (0)| 00:00:01 |
|*  8 |        FIXED TABLE FULL  | X$KTFBUE | 1000 | 78000 | 45 (100)| 00:00:01 | 
|*  9 |       TABLE ACCESS FULL  | TS$      |  154 |  4774 | 28   (0)| 00:00:01 | 
|* 10 |    INDEX UNIQUE SCAN     | I_FILE2  |    1 |     8 |  0   (0)| 00:00:01 | 
--------------------------------------------------------------------------------- 

 Predicate Information (identified by operation id): 
--------------------------------------------------- 
  5 - access("T"."TS#"="U"."TS#") 
  6 - access("E"."KTFBUESEGTSN"="U"."TS#" AND "E"."KTFBUESEGBNO"="U"."BLOCK#" 
             AND "E"."KTFBUESEGFNO"="U"."FILE#")
  7 - filter(("U"."SPARE1"=1 OR "U"."SPARE1"=2) AND "U"."STATUS$"<>1) 
  8 - filter(DECODE("E"."KTFBUESTA",1,'ACTIVE',2,'EXPIRED',3,'UNEXPIRED', 'UNDEFINED')='EXPIRED') 
  9 - filter(BITAND("T"."FLAGS",16777216)=0)
 10 - access("ITEM_1"="F"."TS#" AND "ITEM_2"="F"."RELFILE#") 

Statistics 
---------------------------------------------------------- 
  84080 recursive calls 
  61670 db block gets 
 438303 consistent gets 
 143544 physical reads 
      0 redo size 
    704 bytes sent via SQL*Net to client 
    552 bytes received via SQL*Net from client 
      2 SQL*Net roundtrips to/from client 
      1 sorts (memory) 
      0 sorts (disk) 
      2 rows processed


But if I run the statement without a aggregation (that I call BQ - B Query (B stay for "the second one") ), it is immediate

SQL> select a.tablespace_name, bytes 
   > FROM dba_undo_extents a WHERE status = 'EXPIRED'; 

TABLESPACE_NAME                 BYTES
 ------------------------------ ---------- 
UNDOTBS1                        65536 
UNDOTBS1                        1048576 
UNDOTBS1                        8388608 
[...] 

TABLESPACE_NAME BYTES 
------------------------------ ---------- 
UNDOTBS2                       65536 
UNDOTBS2                       1048576 
UNDOTBS2                       8388608 
UNDOTBS2                       16777216 
UNDOTBS2                       25165824 
UNDOTBS2                       33554432 
UNDOTBS2                       67108864 
[...] 

 1448 rows selected. 

 Elapsed: 00:00:00.19 

 Execution Plan 
---------------------------------------------------------- 

Plan hash value: 1222646468 
------------------------------------------------------------------------------------------- 
| Id | Operation                 | Name            | Rows | Bytes | Cost (%CPU)| Time     | 
------------------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT          |                  |   1 |   133 |   1984 (99)| 00:00:01 | 
|  1 |  NESTED LOOPS             |                  |   1 |   133 |   1984 (99)| 00:00:01 | 
|  2 |   NESTED LOOPS            |                  | 182 | 22750 |   1984 (99)| 00:00:01 | 
|* 3 |    HASH JOIN              |                  | 141 |  6627 |      30 (0)| 00:00:01 | 
|* 4 |     TABLE ACCESS FULL     | UNDO$            | 141 |  2256 |       2 (0)| 00:00:01 | 
|* 5 |     TABLE ACCESS FULL     | TS$              | 154 |  4774 |      28 (0)| 00:00:01 | 
|* 6 |    FIXED TABLE FIXED INDEX| X$KTFBUE (ind:1) |   1 |    78 |    14 (100)| 00:00:01 | 
|* 7 |   INDEX UNIQUE SCAN       | I_FILE2          |   1 |     8 |       0 (0)| 00:00:01 | 
------------------------------------------------------------------------------------------- 

 Predicate Information (identified by operation id): 
--------------------------------------------------- 
 3 - access("T"."TS#"="U"."TS#") 4 - filter(("U"."SPARE1"=1 OR "U"."SPARE1"=2) AND "U"."STATUS$"<>1) 
 5 - filter(BITAND("T"."FLAGS",16777216)=0) 
 6 - filter("E"."KTFBUESEGBNO"="U"."BLOCK#" AND "E"."KTFBUESEGFNO"="U"."FILE#" 
            AND "E"."KTFBUESEGTSN"="U"."TS#" 
            AND DECODE("E"."KTFBUESTA",1,'ACTIVE',2,'EXPIRED', 3,'UNEXPIRED','UNDEFINED')='EXPIRED') 
 7 - access("U"."TS#"="F"."TS#" AND "E"."KTFBUEFNO"="F"."RELFILE#") 

 Note 
----- 
 - this is an adaptive plan 

 Statistics 
---------------------------------------------------------- 
   164 recursive calls 
     0 db block gets 
   995 consistent gets 
    71 physical reads 
     0 redo size 
 27169 bytes sent via SQL*Net to client 
  1608 bytes received via SQL*Net from client 
    98 SQL*Net roundtrips to/from client 
     1 sorts (memory) 
     0 sorts (disk) 
  1448 rows processed


So the simple statement (BQ) take 1 seconds, while the aggregation (AQ) taks more than 3 minutes.

My idea is:

1. Run the BQ, so it takes just few seconds
2. Run the aggregation on the BQ

Note:
I have modified the output of explain plan because the blog visualization.

Part 2