CODEWITHSUNDEEP
oracle-databaseperformancesql-tuningdatabase-tuning
Ajay Shetty
Ajay Shetty

Reputation: 3

Performance tuning tips -Plsql/sql-Database

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

Upvotes: 0

Views: 245

Answers (1)

Gary Myers
Gary Myers

Reputation: 35401

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."

Upvotes: 1

Related Questions