Oracle Join on V$ views becomes extremely slow when a temp table is used

I am trying to get some information from the Oracle V$ views

SELECT bpd.handle,bpd.media,bpd.copy#, bpd.bytes, bpd.session_key, bsjd.operation,bsjd.start_time, bsjd.end_time,bpd.completion_time,  
bsd.controlfile_included
FROM v$backup_piece_details bpd 
INNER JOIN v$rman_backup_subjob_details bsjd ON bpd.SESSION_KEY = bsjd.session_key
INNER JOIN v$backup_set_details bsd ON bpd.set_stamp = bsd.set_stamp AND bpd.set_count = bsd.set_count
INNER JOIN 
(SELECT ibpd.session_key, count (ibpd.handle) count FROM v$backup_piece_details ibpd 
INNER JOIN v$rman_backup_subjob_details rbsd ON IBPD.session_key = rbsd.SESSION_KEY
GROUP BY IBPD.session_key, IBPD.handle) hc
ON hc.session_key = bpd.SESSION_KEY

PLAN_TABLE_OUTPUT

https://pastebin.com/raw/QrkzeHH7

If I remove

INNER JOIN 
(SELECT ibpd.session_key, count (ibpd.handle) count FROM v$backup_piece_details ibpd 
INNER JOIN v$rman_backup_subjob_details rbsd ON IBPD.session_key = rbsd.SESSION_KEY
GROUP BY IBPD.session_key, IBPD.handle) hc
ON hc.session_key = bpd.SESSION_KEY

the query completes in under 17 seconds, while including the inner join with temporary table hc results in the query completing in 5 minutes
Running frequently even causes

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

I have tried adding /*+ use_hash(bpd hc) */ and /*+ use_hash(ibpd rbsd) */ as hints to force using hash join instead of nested loops but the problem persists.

It's also not clear why the query plan shows left outer joins with recid column when I don't try to join using it.

Can someone throw some light on the query plan?

With dictionary queries, you are hitting a bunch of (sometimes complex views) and view merging can really wreak havoc on a plan. My suggestion with these, assuming you have the right joins and there aren't any many-to-many joins here, is to disable view merging (see the two lines of hints below, one in the subquery):

SELECT /*+ NO_MERGE(bpd) NO_MERGE(bsjd) NO_MERGE(bsd) */
      bpd.handle,
       bpd.media,
       bpd.copy#,
       bpd.bytes,
       bpd.session_key,
       bsjd.operation,
       bsjd.start_time,
       bsjd.end_time,
       bpd.completion_time,
       bsd.controlfile_included
  FROM v$backup_piece_details bpd
   INNER JOIN v$rman_backup_subjob_details bsjd
                  ON bpd.SESSION_KEY = bsjd.session_key
   INNER JOIN v$backup_set_details bsd
                  ON bpd.set_stamp = bsd.set_stamp
                     AND bpd.set_count = bsd.set_count
   INNER JOIN (SELECT /*+ NO_MERGE NO_MERGE(ibpd rbsd) */
                     ibpd.session_key,
                     COUNT (ibpd.handle) COUNT
                FROM v$backup_piece_details ibpd
                 INNER JOIN v$rman_backup_subjob_details rbsd
                                ON IBPD.session_key = rbsd.SESSION_KEY
            GROUP BY IBPD.session_key,
                     IBPD.handle) hc
                  ON hc.session_key = bpd.SESSION_KEY

Secondly, it is sometimes necessary to gather fixed object stats (dbms_stats.gather_fixed_objects_stats).

I do not have access to your views so cannot test whether this would give the same output but you could try using an analytic COUNT function so that you do not need to join the tables a second time:

SELECT s.*,
       bsd.controlfile_included
FROM   ( SELECT bpd.handle,
                bpd.media,
                bpd.copy#,
                bpd.bytes,
                bpd.session_key,
                bsjd.operation,
                bsjd.start_time,
                bsjd.end_time,
                bpd.completion_time,
                COUNT(bpd.handle) OVER (PARTITION BY bpd.session_key, bpd.handle)
                  AS handle_count,
                bpd.set_stamp
         FROM   v$backup_piece_details bpd 
                INNER JOIN v$rman_backup_subjob_details bsjd
                ON bpd.SESSION_KEY = bsjd.session_key
       ) s
       INNER JOIN v$backup_set_details bsd
       ON ( s.set_stamp = bsd.set_stamp AND s.set_count = bsd.set_count )

> It's also not clear why the query plan shows left outer joins with recid column when I don't try to join using it.

A VIEW is just a query with a pre-defined SQL statement. When you query from a view then Oracle's SQL engine will rewrite your statement to select from the underlying tables that the view queries and if the statement the view is based on uses a LEFT OUTER JOIN on a recid column then the execution plan will show that LEFT OUTER JOIN on the recid column between the underlying tables when you query that view. fiddle

I agree what said about the hint and the statistics but it seems there is another issue becuase of the error ORA-01652: unable to extend temp segment by 128 in tablespace TEMP. this error means the data you are selecting has bigger size then on tablespace temp, what you should do either select few data or increase the tablespace . you can split the query to two