Hi Andrew, the below query will return <100 rows most of the time. Do you
mean to say even the current plan can be tweaked(through some hints) to
make it perform more efficiently?
SELECT count(*)
FROM TNI TNI, TMTD TMTD
WHERE TNI.NE <http://tni.ne/> = 'XX'
AND TNI.NID = TMTD.DID
Regards
Pap
On Mon, Jun 21, 2021 at 1:06 AM Andy Sayer <andysayer@xxxxxxxxx> wrote:
Jonathan wrote:
"
Looking at the predicate information for operation 15 you can see that it
is an ACCESS predicate (that, since it came from memory) has "lost" the
subquery bit.
This means the subquery runs one to generate a value that is effectively
then used as a constant to drive the index range scan at operation 15. The
index tad_ix2 is (presumably) on the column (date_cr), so this part of the
query selects all data for the most recent date by index range scan. (And
possibly will be doing this 17 times in the course of the query).
"
According to the plan in the original attachment, the join to TAD is done
via a nested loop. This would mean that this read of all rows in TAD where
DT_CR=MAX(DT_CR) happens 18 * (rows in TNI with NE='XX'). This plan had
notes but didn't indicate it was an adaptive plan, I reckon this difference
would easily explain the difference in run times.
Having to read rows which have already been recently read is usually going
to be logical reads so the most likely waits *sampled* are going to be do
with that, but most of the work will come under CPU.
Pap, what does
SELECT count(*)
FROM TNI TNI, TMTD TMTD
WHERE TNI.NE = 'XX'
AND TNI.NID = TMTD.DID
Return? Somewhere in the region of 60?
(Obviously, Jonathan's advise is going to be sound, but this will probably
explain your observation).
(Aside, AWR gets predicates in 21c so getting similar answers out of
historic plans is going to be much easier)
Thanks,
Andrew
On Sun, 20 Jun 2021 at 20:27, Dominic Brooks <dombrooks@xxxxxxxxxxx>
wrote:
I would think twice about materialize especially on Exadata when you can
often query the data twice (or more) much quicker than the consequences of
writing and reading back from temp.
Sent from my iPhone
On 20 Jun 2021, at 18:41, Mladen Gogala <gogala.mladen@xxxxxxxxx>wrote:
myriad of problems with gigantic SQL statements like yours. You are
Pap, as a matter of principle, your SQL is too big. I have had a
inserting into GTT using a statement with all kinds of hash group by,
sorting and full table scans. Not even Exadata can help you with that. I
would try slicing and dicing the SQL using CTE and /*+ MATERIALIZE */ hint.
facing an issue in which a reporting query(part of plsql procedure) which
Regards
On 6/18/21 1:46 AM, Pap wrote:
Hello Listers, Its version 11.2.0.4 of oracle exadata. And we are
normally finishes within ~1-2minutes runs for ~1-2hrs at times. This
happens when the same query is submitted from 5-6 multiple sessions at the
same time and is accessing the same customer data. When we kill them and
rerun them in serial they run fine without any issue and finish in the same
1-2 minutes duration.
time and the query runs long , the event its showing for the session is
Few things we observed is , when all the session submitted at same
"latch: cache buffers chains" but active session history is not showing up
any significant activity for that session and also the sql monitor is not
getting logged for that query. Which means it's not doing significant
activity while this issue occurs but kind of stuck. Why is it so? And also
due to that , I am not able to capture the current object on which it's
actually holding that latch.
temporary table. It has ~17 UNION clauses of which most look similar. So i
The query is an INSERT query which inserts data into a global
am wondering if by someway we can rewrite this query which will help us in
fixing this issue or making the situation better?
a few of the UNIONS to make it look simple) and its plan which suffers from
Attached is the sample INSERT query with UNION clauses(I have removed
"latch: cache buffers chains".
https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdbwhisperer.wordpress.com%2F&data=04%7C01%7C%7C49c907d03cf14b8ff8df08d93412aa17%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637598077036369290%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=hgONyg%2FIkfVaGCwg3htYSEXTZlrYSBS3Bbz7TUO8j5k%3D&reserved=0
Regards
Pap
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&data=04%7C01%7C%7C49c907d03cf14b8ff8df08d93412aa17%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637598077036369290%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=dAYarKUDC4QON%2F3eDiwvMaDyIEhRdBG9YYMabWLoV7c%3D&reserved=0
--
