In below BIO- comes from - A5 i.e table COX,
COL1 comes from A2 i.e table BOS
and CT1 comes from A5 i.e table COX
and I_DT comes from A2 i.e table BOS.
WHERE "A1"."BI0" = :b1
OR "A1"."COl1" = :b2
AND "A1"."I_DT" IS NULL
AND ( "A1"."BI0" IS NOT NULL
AND "A1"."CT1" = 'XXX'
OR "A1"."BI0" IS NULL AND "A1"."CT1" = 'YYY')
On Thu, Dec 24, 2020 at 1:04 AM Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
Hi Lok,
You removed columns projections from the query, so I can't understand
where those columns come from:
WHERE "A1"."BI0" = :b1
OR "A1"."COl1" = :b2
AND "A1"."I_DT" IS NULL
AND ( "A1"."BI0" IS NOT NULL
AND "A1"."CT1" = 'XXX'
OR "A1"."BI0" IS NULL AND "A1"."CT1" = 'YYY')
On Wed, Dec 23, 2020 at 10:22 PM Lok P <loknath.73@xxxxxxxxx> wrote:
I was rather thinking if as a short term fix, we should try parallel hint
forced through a profile to make it faster. Not sure if that is a good idea?
On Thu, 24 Dec 2020, 12:48 am Lok P, <loknath.73@xxxxxxxxx> wrote:
I was trying to force index hint but it was not taking that so I thought
may be some restrictions wrt full outer join is causing this . Isn't this
true?
Also even current plan estimation are accurate so shouldn't the
optimizer by default go for index + nested loop path?
On Thu, 24 Dec 2020, 12:43 am Laurentiu Oprea, <
laurentiu.oprea06@xxxxxxxxx> wrote:
yes an active report gives more info, like how you benefited from a
smart scan, you can compute the I/O latency, etc.
I wanted to see a completed report because I was curious about the
returned number of rows.
I see it is 2 rows, It is possible that indexing will help . Try to do
test runs and understand what are the columns that have the best
selectivity and are doing the filtration here. it might be the case
that they are good candidates for an index.
În mie., 23 dec. 2020 la 20:52, Lok P <loknath.73@xxxxxxxxx> a scris:
Attached is the one i had captured for the completed one, but it's in
text only.
I will try to capture the html version next time, Out of curiosity,
does html/flash give some additional information to perform analysis which
the text one won't provide?
On Thu, Dec 24, 2020 at 12:09 AM Laurentiu Oprea <
laurentiu.oprea06@xxxxxxxxx> wrote:
Do you have the Sql Monitor where execution is completed? (maybe in
html/flash format)
În mie., 23 dec. 2020 la 20:23, Lok P <loknath.73@xxxxxxxxx> a scris:
Table COX has PK on column (EID,BID,OID,ETYP) and table CS has
index on column (EID) and table BOS has index on (BID, OID). But none of
the index is getting used in the query path, is it because the FULL
OUTER
JOIN cant use index+ nested loop path here. Is this understanding
correct?
On Wed, Dec 23, 2020 at 11:36 PM Lok P <loknath.73@xxxxxxxxx> wrote:
Forgot to attach the sql monitor which is better formatted.
On Wed, Dec 23, 2020 at 11:31 PM Lok P <loknath.73@xxxxxxxxx>
wrote:
Hello ,
This database is on version 11.2.0.4 of Oracle Exadata. We have
below query which was running in few seconds , but its now running for
15minutes. No change in execution path happened, but after lookingdetails, we saw its the increase in tempspace spill during HASH JOIN
into
on
plan_line_id - 6 and plan_line_id- 2 causing the query to run longer
these
days. During the slow execution most of the time its spending at line
no-6
followed by line no- 2 in the execution path with wait event "Direct
path
read temp". So wanted to understand , what are the possible way to
make
this query run faster?
Users are expecting the query to finish in same time ,within
around <30 seconds. But it seems that one of these three table might
have
the volume increased slightly more these days so the hash table
spilling to
temp multiplying overall execution time for the query.
I see , if i am setting the work area_size_policy to MANUAL and
hash_area_size to 2GB, we can get those execution happened fully in
memory
and the query is finishing in ~>15 seconds, but that would be a code
change
and also considering this query sometimes executing concurrently from
many
sessions(~50+ executions) , so that seems risky as that may eat-up
significant memory.
I doubt that the tempspace consumption may be because of the
number of columns we are exposing out from the table in the query,
but i
see all of those are getting fetched and there are no unnecessary
column in
the projection list. Again i also was trying to see if i can use some
indexed path + nested loop, but perhaps because of full outer join
index
hint is not working too.
Is there any other option we can opt to fix this issue in short
term using some hints(a better path may be) and then may opt for some
code
change for long term fix?
*Below is the query and also the sql monitor for same, also
attached the sql monitor as the body was not appearing properly *
*formatted** here:-*
SELECT ....~58 columns projected...
FROM (SELECT ....~60 columns projected
FROM "USER1"."BOS" "A2"
FULL OUTER JOIN
(SELECT ...~41 columns projected from A4 and A5
FROM "USER1"."CS" "A4"
FULL OUTER JOIN "USER1"."COX" "A5"
ON "A5"."EID" = "A4"."EID") "A3"
ON "A2"."BI" = "A3"."BID1"
AND "A2"."OID" = TO_NUMBER ("A3"."OID2")) "A1"
WHERE "A1"."BI0" = :b1
OR "A1"."COl1" = :b2
AND "A1"."I_DT" IS NULL
AND ( "A1"."BI0" IS NOT NULL
AND "A1"."CT1" = 'XXX'
OR "A1"."BI0" IS NULL AND "A1"."CT1" = 'YYY')
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
SQL Execution ID : 16777403
Execution Started : 12/22/2020 10:08:16
First Refresh Time : 12/22/2020 10:08:20
Last Refresh Time : 12/22/2020 10:26:15
Duration : 1080s
Global Stats
==================================================================================
| Elapsed | Cpu | IO | Buffer | Read | Read | Write | Write
| Cell |
| Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Reqs |
Bytes | Offload |
==================================================================================
| 1124 | 120 | 1004 | 81196 | 808K | 92GB | 7100 | 832MB
| -2.04% |
==================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2475526405)
=======================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time |
Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp |
Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) |
Active | | (Actual) | Reqs | Bytes | Reqs | Bytes
| | | (%) | (# samples) | |
=======================================================================================================================================================================================================================
| 0 | SELECT
STATEMENT | | | | | | 1
| | | | | | | | | | |
| -> 1 | VIEW | VW_FOJ_0 | 2M | 42883 | 1072
| +8 | 1 | 0 | | | | | | | |
| |
| -> 2 | HASH JOIN FULL OUTER | | 2M | 42883
| 1079 | +2 | 1 | 390K | 147K | 17GB | 2979 | 349MB | 1M |
559M
| 17.55 | Cpu (7) | 8% |
| | | | | | | | | |
| | | | | | |
direct path read temp (180) | |
| | | | | | | | | |
| | | | | | |
direct path write temp (2) | |
| 3 | TABLE ACCESS STORAGE FULL | BOS | 1M | 3044
| 1 | +4 | 1 | 1M
| | | | | | | | | |
| 4 | VIEW | | 2M | 16655 | 888 | +8
| 1 | 2M | | | | | | | | | |
| 5 | VIEW | VW_FOJ_1 | 2M | 16655 | 888
| +8 | 1 | 2M
| | | | | | | | | |
| 6 | HASH JOIN FULL OUTER | | 2M | 16655
| 892 | +4 | 1 | 2M | 661K | 76GB | 2583 | 303MB | | |
82.45
| Cpu (56) | |
| | | | | | | | | |
| | | | | | |
direct path read temp (829) | |
| | | | | | | | | |
| | | | | | |
direct path write temp (3) | |
| 7 | TABLE ACCESS STORAGE FULL | CS | 944K | 2683
| 3 | +4 | 1 | 948K
| | | | | | | | | |
| 8 | TABLE ACCESS STORAGE FULL | COX | 2M | 3213
| 3 | +6 | 1 | 2M
| | | | | | | | | |
=======================================================================================================================================================================================================================
1 - filter(("A3"."BID1"=:B1 OR ("A2"."BI"=:B2 AND "A2"."I_DT" IS
NULL
AND (("A3"."BID1" IS NOT NULL AND "A3"."CT3"='XXX') OR
("A3"."BID1" IS
NULL AND "A3"."CT3"='YYY')))))
2 - access("A2"."BI"="A3"."BID1" AND
"A2"."OID"=TO_NUMBER("A3"."OID2"))
6 - access("A5"."EID"="A4"."EID")
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
