Performance tuning tips -Plsql/sql-Database

Question

We are facing performance issue in production. Mv refersh program is running for long, almost 13 to 14 hours.

In the MV refersh program is trying to refersh 5 MV. Among that one of the MV is running for long.

Below is the MV script which is running for long.

SELECT rcvt.transaction_id,
    rsh.shipment_num,
    rsh.shipped_date,
    rsh.expected_receipt_date,
    (select rcvt1.transaction_date from rcv_transactions rcvt1
    where rcvt1.po_line_id = rcvt.po_line_id
    AND rcvt1.transaction_type   = 'RETURN TO VENDOR'
    and rcvt1.parent_transaction_id=rcvt.transaction_id
    )transaction_date
  FROM rcv_transactions rcvt,
    rcv_shipment_headers rsh,
    rcv_shipment_lines rsl
  WHERE 1                     =1
  AND rcvt.shipment_header_id =rsl.shipment_header_id
  AND rcvt.shipment_line_id   =rsl.shipment_line_id
  AND rsl.shipment_header_id  =rsh.shipment_header_id
  AND rcvt.transaction_type   = 'RECEIVE';

Shipment table contains millions of records and above query is trying to extract almost 60 to 70% of the data. We are suspecting data load is the reason. We are trying to improve the performance for the above script.So we added date filter to restrict the data.

SELECT rcvt.transaction_id,
    rsh.shipment_num,
    rsh.shipped_date,
    rsh.expected_receipt_date,
    (select rcvt1.transaction_date from rcv_transactions rcvt1
    where rcvt1.po_line_id = rcvt.po_line_id
    AND rcvt1.transaction_type   = 'RETURN TO VENDOR'
    and rcvt1.parent_transaction_id=rcvt.transaction_id
    )transaction_date
  FROM rcv_transactions rcvt,
    rcv_shipment_headers rsh,
    rcv_shipment_lines rsl
  WHERE 1                     =1
  AND rcvt.shipment_header_id =rsl.shipment_header_id
  AND rcvt.shipment_line_id   =rsl.shipment_line_id
  AND rsl.shipment_header_id  =rsh.shipment_header_id
  AND rcvt.transaction_type   = 'RECEIVE'
    AND TRUNC(rsh.creation_date)  >=  NVL(TRUNC((sysdate - profile_value),'MM'),TRUNC(rsh.creation_date) );

For 1 year profile, it shows some improvement but if we give for 2 years range its more worse than previous query.

Any suggestions to improve the performance.

Pls help

Well, have you run basic profiling tools like explain plan, tkprof, and ADDM reports? Nobody here is going to diagnose your performance issue by looking at a few queries without a deep understanding of your system,environment, and many, many other details. Sorry. — OldProgrammer, Aug 16 '16 at 22:08
Are you doing an incremental refresh? Or a full refresh? The second approach will clearly need to be a full refresh since you're referencing a non-deterministic function call (`sysdate`). It's possible that the first could do an incremental refresh depending on the MV logs on the source tables (I'm just not sure about the inline subquery rather than just doing an outer join or just factoring that out). Normally, a MV would be used either to aggregate data or to replicate data from a remote database but I don't see any database links or aggregate functions. — Justin Cave, Aug 16 '16 at 22:28
@JustinCave thanks for the update. We are doing complete refresh not fast refresh. Reason is every day on an avg 10k records will insert into base tables and it will reduce the system performance if we use fast refersh. — Ajay Shetty, Aug 16 '16 at 22:40
@OldProgrammer thanks for the update. Yes we analyzed explain plan/AWR reports. Since am working in secured network i cannot share the explain plan /awr report — Ajay Shetty, Aug 16 '16 at 22:43
What are you using the materialized view for? Aggregation? Or replication? I'm not seeing any database links and I'm not seeing any aggregate functions. If you can't provide a query plan or any information about your wait events and you don't want to consider doing an incremental refresh (10,000 changes a day is a trivial number for my laptop to handle), I'm not sure what we can possibly do for you other than to suggest maybe restructuring into more than one MV. — Justin Cave, Aug 16 '16 at 22:52

score 1 · Answer 1 · answered Aug 17 '16 at 00:37

I'd pull out that scalar subquery into a regular outer join.

Costing for scalar subqueries can be poor and you are forcing it to do a lot of single record lookups (presumably via index) rather than giving it other options.

"The main query then has a scalar subquery in the select list.

Oracle therefore shows two independent plans in the plan table. One for the driving query – which has a cost of two, and one for the scalar subquery, which has a cost of 2083 each time it executes.

But Oracle does not “know” how many times the scalar subquery will run (even though in many cases it could predict a worst-case scenario), and does not make any cost allowance whatsoever for its execution in the total cost of the query."

You might consider marking your answer as relevant to 11g and earlier. In Oracle 12c, the optimizer can unnest scalar subqueries and turn them into (usually) hash joins. — Matthew McPeak, Aug 17 '16 at 00:55

Performance tuning tips -Plsql/sql-Database

1 Answers1