parallel hint not working for view in oracle 19c

Question

while migrate project code from oracle 12c to 19c we are facing parallels hint issue for view. when we are using below code the parallel degree working fine

SELECT /*+ PARALLEL(8) */ 1 FROM View;

But , when we are give view column name explicitly in the select statement the parallel hint not working

SELECT /*+ PARALLEL(8) */ id,name FROM View

it working fine oracle 12c but not working in oracle 19c. How to resolve this issue?

--- Pipe row function call

SELECT * FROM TABLE(PACKAGE1.P_FILE(CURSOR(SELECT /*+ PARALLEL(8) */ C1,C2 FROM VIEW1 WHERE 1=1),'Filename.txt','directroy')) nt;

-- Function declaration

FUNCTION P_FILE
            (
             P_SOURCE    IN SYS_REFCURSOR,
             p_filename  IN VARCHAR2,
             p_directory IN VARCHAR2
            )
            RETURN v_return_pipe
            PIPELINED DETERMINISTIC
            PARALLEL_ENABLE (PARTITION p_source BY ANY)AS

PRAGMA AUTONOMOUS_TRANSACTION;

Answer 1

Your query is using parallelism where it can, which is in the two full table scan of T_... You can see the PX BLOCK ITERATOR operation as the immediate parent of those table scans, and the TQ column shows PX team assignment.

But you are then aggregating the first one to a single row with COUNT. You then use the result in another query block but as it by definition will have only 1 row after that aggregation, PX is disabled for the rest of that execution branch. And for good reason - it serves no purpose when there's only one row to process. And there's nothing more for the row output to do but stream to the client, which has to be serial anyway.

Your next query block pulls from dual, which means again only 1 row. Nothing for parallelism to do here.

The next query block pulls from T_.... again and uses parallelism for the scan.

The last block is again pulling 1 row from dual. Nothing for parallelism to do here.

I see no issue with how Oracle is employee parallelism here. It's doing it in the only place it can. The fact is, it is only doing that because you hinted it and requested parallelism. That's likely the wrong thing to do here. Since you aren't doing anything with the data downstream of these table scans (at least in the view itself), there's really no benefit to parallelism anywhere in this query. Your bottleneck will be fetching the results serially across the network to your client and consuming/displaying them there, not in anything the database is doing. Throwing parallelism at this will only result in idle but allocated PX slaves that other processes can't use. Further, even if you were doing joins and sorts and other operations that could benefit from the parallelism, your stats are saying that the only table in your query, T_... has only 13K rows. That's not enough to justify the startup time for parallel query. I would remove the hint and let this run serially.

It's quite normal for a change in database version to make sweeping changes to execution plans. Sometimes it helps, sometimes it hurts, sometimes you can't tell, but change of some kind can be expected with a major upgrade. That doesn't mean it's doing something wrong.

At the end of the day, what matters is not whether we understand or agree with what we see in the plan, but whether it actually performs acceptably. If it runs fast, no time should be spent on trying to figure out why Oracle made the decisions it made. Focus only on queries that run too slow to meet their needs, then dig into the plan to understand what mistake it is making (if any) given your knowledge of the data. In this case, parallelism isn't one of them.