Hi Amit,
More important than the developer is the documentation. Do you have a
description what the View should return?
To give you an example, I created an EMP and DEPT table - similar to the
SCOTT schema.
Just for EMP I used this definition:
DEPTNO NUMBER(2) --CONSTRAINT FK_DEPTNO REFERENCES DEPT
=> there is NO FK constraint!
I have 3 queries with the same result but different plans:
with *ROWNUM=1*
select e.ename -- BX7
, (select d.dname
from dept d
where d.DEPTNO = e.deptno
and *rownum = 1* ) as dname
, (select d.loc
from dept d
where d.DEPTNO = e.deptno
and *rownum = 1* ) as loc
from EMP e
where e.sal=3000;
Plan hash value: 2353315787
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | *9
*(100)| |
|* 1 | COUNT STOPKEY | | | |
| |
|* 2 | TABLE ACCESS STORAGE FULL FIRST ROWS| DEPT | 1 | 13 |
3 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | |
| |
|* 4 | TABLE ACCESS STORAGE FULL FIRST ROWS| DEPT | 1 | 11 |
3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS STORAGE FULL | EMP | 2 | 26 |
3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
with *ANYVALUE*
select e.ename -- BX8
, (select *any_value*(d.dname)
from dept d
where d.DEPTNO = e.deptno
) dname
, (select *any_value*(d.loc)
from dept d
where d.DEPTNO = e.deptno
) as loc
from EMP e
where e.sal=3000;
Plan hash value: 2085794782
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
*11 *(100)|
|
|* 1 | HASH JOIN OUTER | | 2 | 112 | 11
(19)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 2 | 70 | 7
(15)| 00:00:01 |
|* 3 | TABLE ACCESS STORAGE FULL | EMP | 2 | 26 | 3
(0)| 00:00:01 |
| 4 | VIEW | VW_SSQ_2 | 4 | 88 | 4
(25)| 00:00:01 |
| 5 | HASH GROUP BY | | 4 | 52 | 4
(25)| 00:00:01 |
| 6 | TABLE ACCESS STORAGE FULL| DEPT | 4 | 52 | 3
(0)| 00:00:01 |
| 7 | VIEW | VW_SSQ_1 | 4 | 84 | 4
(25)| 00:00:01 |
| 8 | HASH GROUP BY | | 4 | 44 | 4
(25)| 00:00:01 |
| 9 | TABLE ACCESS STORAGE FULL | DEPT | 4 | 44 | 3
(0)| 00:00:01 |
------------------------------------------------------------------------------------------
with an *OUTER JOIN* (Oracle style)
select e.ename, d.dname, d.loc -- BX9
from emp e
, dept d
where e.deptno = d.deptno *(+)*
and e.sal=3000;
Plan hash value: 3387915970
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | *6* (100)|
|
|* 1 | HASH JOIN *OUTER *| | 2 | 66 | 6 (0)|
00:00:01 |
|* 2 | TABLE ACCESS STORAGE FULL| EMP | 2 | 26 | 3 (0)|
00:00:01 |
| 3 | TABLE ACCESS STORAGE FULL| DEPT | 4 | 80 | 3 (0)|
00:00:01 |
-----------------------------------------------------------------------------------
The rewrite of your query is similar.
best regards,
Martin
Am Mo., 18. März 2024 um 14:32 Uhr schrieb Amit Saroha <
eramitsaroha@xxxxxxxxx>:
Hi Martin,
Thank you for your feedback.
I have included the updated monitoring report. The developer who built the
view script is no longer with us, making it difficult to determine why
there were so many ROWNUM conditions. However, your argument is accurate,
and I will investigate further. I'm confused by the suggestion to delete
ROWNUM and replace it with outer join. Could you provide an example or
clarify further on this re-write?
Best Regards,
AMIT
On Sun, Mar 17, 2024 at 5:04 AM Martin Berger <martin.a.berger@xxxxxxxxx>
wrote:
Hi Amit,
As Laurentiu asked already: please be more specific and generous with
your information. If the Plan Hash Value is the same, this is an important
detail. Otherwise, the new SQL real time monitor gives others the chance to
help you.
Can you please share some thoughts and concepts the author
of XXOF_312_ASN_CF_LINES_V had in mind?
There are subselects for vendor_number, vendor_site_code and ebs_po_num.
They contain a filter ROWNUM = 1 but does *not *have an ORDER BY - this
looks similar to the idea of using a DISTINCT - and raises all the
related questions.
All these subselects share access on the tables po_headers_all poh and
xxpo01t_asn_inbound
asn_l. As PO_HEADERS_ALL seems to be critical, please translate your
query into an outer join with those 2 tables. So they are only visited once
and not 3 times - all subselects can then use their attributes for their
joins.
Does this help in your effort?
Martin
Am Fr., 15. März 2024 um 21:07 Uhr schrieb Amit Saroha <
eramitsaroha@xxxxxxxxx>:
I have created the index but no improvement in run time.
Please let me know if I can try anything else?
Best Regards,
AMIT
On Tue, Mar 12, 2024 at 12:20 PM Amit Saroha <eramitsaroha@xxxxxxxxx>
wrote:
Thank you for your feedback, I will create the index and test. Could
you also advise what do you mean by re-write?
Best Regards,
AMIT
On Tue, Mar 12, 2024 at 11:13 AM Lothar Flatz <l.flatz@xxxxxxxxxx>
wrote:
It should be possible to rewrite the statement avoiding that column
level selects.
Am 12.03.2024 um 16:09 schrieb Lothar Flatz:
Have to correct myself, line 7 is executed 23 time, thus estimate
looks ok.
Am 12.03.2024 um 16:03 schrieb Lothar Flatz:
Hi Amit,
it is both time the access on table PO_HEADERS_ALL where time is
spend.
That is in Line 7 where the selection criteria is "POH"."ATTRIBUTE1"=:B1
AND "POH"."ORG_ID"=:B2. As an easiest option an Index on (ORG_ID,
ATTRIBUTE1)
might be useful.
We see that the estimates are somewhat off, as estimated was 1 row,
whereas 23 rows were retrieved.
An improvement of statistics might be advisable.
On line 28, the same table, but this time selection criteria are
("POH"."ATTRIBUTE1"="ASN_L"."DOCUMENT_NUM"
AND "POH"."ORG_ID"="ASN_L"."ORG_ID" AND
"POH"."TYPE_LOOKUP_CODE"='STANDARD'
AND NVL("POH"."CLOSED_CODE",'OPEN')='OPEN' AND
NVL("POH"."CANCEL_FLAG",'N')='N').
As the same columns as above are included and the columns proved to be
selective in line 7 probably the same index would help.
This , if possible create an index on PO_HEADERS_ALL (ORG_ID, ATTRIBUTE1).
There might be some testing necessary of just one of the two columns
is enough etc.
However as a first attempt that might be a good startpoint.
Thanks
Lothar
Am 12.03.2024 um 15:38 schrieb Amit Saroha:
Hi All,
I have the enclosed query running rather slowly, and I'd like to
enhance it such that it finishes in a few seconds. The query is a SELECT
statement on a view and I enclosed the view text.
Please review the accompanying query and monitoring report and provide
input for improvements.
I appreciate your aid and support in advance.
Best Regards,
AMIT
