SQL Server statement takes forever while running instantly in Oracle
The Oracle optimizer uses OR Expansion to improve efficiency of the query. Quoting from the documentation:


  In OR expansion, the optimizer transforms a query with a WHERE clause
  containing OR operators into a query that uses the UNION ALL operator.
  
  The database can perform OR expansion for various reasons. For
  example, it may enable more efficient access paths or alternative join
  methods that avoid Cartesian products.


You can think of the new query as being written like this:

SELECT
(
    SELECT
        COUNT ( t_01.rsecondary_objectu ) AS selectExpr
    FROM
        PIMANRELATION t_01
        INNER JOIN PITEMREVISION t_03 ON t_01.rprimary_objectu = t_03.puid
        CROSS JOIN PITEM t_02    
    WHERE
    t_01.rrelation_typeu = 'w8INy241VJFL2B'
    AND t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
) 
+ 
(  
    SELECT
        COUNT ( t_01.rsecondary_objectu ) AS selectExpr
    FROM
        PIMANRELATION t_01
        INNER JOIN PITEM t_02 ON t_01.rprimary_objectu = t_02.puid
        CROSS JOIN PITEMREVISION t_03
    WHERE
    t_01.rrelation_typeu = 'w8INy241VJFL2B'
    AND t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
    AND LNNVL(t_01.rprimary_objectu = t_03.puid)
)
from dual;


Now there is an equality condition for both parts of the query, so Oracle can use indexes to perform an efficient nested loop join for both. It still needs to do a cross join for both parts of the query, but the size of the intermediate result sets is significantly reduced compared to doing two cross joins in the same query. For example, if PIMANRELATION has 1 relevant row and PITEMREVISION and PITEM both have a million rows then you get a trillion rows if you cross join them together. However, if you split the queries up then you only end up with a million rows for both.

The SQL Server query optimizer has a rule that can transform OR into UNION ALL: SelToIdxStrategy. There's no documentation for this and the only reference I could find is this answer. However, that rule will not be applied in this context. Instead, you get two cross joins which can only be implemented through nested loop joins. For each relevant row in PIMANRELATION, SQL Server will cross join to all rows in PITEM, then cross join to all rows in PITEMREVISION, and will finally filter out rows after that. You can easily end up with trillions of rows getting filtered down.

I have bad news for you. If you truly cannot change any part of the query text and you need that query to perform well then SQL Server probably isn't the right platform for your application. Databases have different strengths and weaknesses and you may need to change your queries to accommodate those differences.