How can I debug this sql query to avoid timeout?

Question

I have an SQL query that actually doesn't do a lot despite giving me headaches. Honestly, I have no idea how to debug this query because whenever I run it, it's running for minutes until I finally forcely quit SQL developer. Even cancelling the query takes ages.

Any advice, help, highly appreciated!!

SELECT
    view2.some_id,
    SUM(view1.qty)
FROM
    someview view1,
    someview view2
WHERE
        view1.lot = view2.some_id
    AND view2.some_prefix = 'ABCD'
    AND view2.some_prefix = 'EFGH'
GROUP BY
    view2.some_id

I don't know why it's causing timeouts. This short statement (s2) is part of another statement:

CREATE OR REPLACE FORCE EDITIONABLE VIEW "VIEW" AS 
WITH s1 AS
        (SELECT something
           FROM sometable st
          WHERE st.side = 'TO'
          group by st.lot),
    s2 AS
        (SELECT
      view2.some_id,
      SUM(view1.qty)
  FROM
      someview view1,
      someview view2
  WHERE
          view1.lot = view2.some_id
      AND view2.some_prefix = 'ABCD'
      AND view2.some_prefix = 'EFGH'
  GROUP BY
      view2.some_id)
 SELECT 
        t2.some,
        t2.some,
        t2.some,
        s1.some,
        t2.some,
        t2.some,
        s2.some,
        s2.some / s1.some * t2.some
   FROM
        sometable t2, s1, s2
        WHERE t2.some = s1.some
        AND t2.some = s2.some
        AND t2.some = 'FROM' AND t2.some = 'VN1' AND t2.some_prefix = 'ABCD'; 

Just give me any hint please.

Update: I checked the amount of rows per table:

sometable st (s1): 2.805.809
view2 (s2): 21.877
view1 (s2): 6.144.386
t2: 121.043

Doesn't seem so much....

Answer 1

The syntax is outdated, legacy code. I think I found the reason. The original (legady code) query statement is - or uses? - CTE (common table expression) which doesn't work well with indexing. That's what I read somewhere. I don't know if that's really the reason, however, here is my version, which works:

SELECT
  some,
  some,
  some,
  some,
  some,
  some,
  some,
  some / some * some
FROM
  (
    SELECT
      *
    FROM
      (
        SELECT
          a.somelot,
          b.someqty vq
        FROM
          (
            (
              SELECT
                view2.some_id somelot,
                view2.someother_id --added this
              FROM
                someview view2
              WHERE
                view2.some_prefix = 'ABCD'
                AND view2.some_prefix = 'EFGH'
            ) a
            JOIN (
              SELECT
                view1.lot,
                SUM(view1.qty) qty
              FROM
                someview view1
              GROUP BY
                lot,
                qty
            ) b ON a.someother_id = b.lot
          )
      ) s2
      JOIN (
        SELECT
          lot,
          SUM(trans_qty) pq
        FROM
          someview
        WHERE
          side = 'TO'
        GROUP BY
          lot,
          trans_qty
      ) s1 ON s1.lot = s2.lot
      JOIN (
        SELECT
          lot,
          some,
          some,
          trans_qty trans_qty,
          some
        FROM
          someview
        WHERE
          some = 'ZZZZ'
          AND some = 'XXX'
          AND some_prefix = 'WXYZ'
      ) t2 ON t2.lot = s1.lot
      AND t2.lot = s2.lot
  )