that link contains 2 pdf files one with NLI ON and other with NLI OFF.
summary:
NLI ON average run time is 20 minutes; index writes 380 thousand
NLI OFF average run time is 30 minutes; index writes 700 thousand
how can it execute sample plan but different index writes and the claim that not logging is improving performance is not acceptable if double the amount of index writes are happening.
conceptually it does not make sense. any clarifications inputs are welcome.
https://drive.google.com/drive/folders/1guc_7NO_4b5n28pH0EPiRSaq9HoOKedC?usp=sharing
thanks,
harish pathnagay
------------------------------
Harishkumar Pathangay
------------------------------
Original Message:
Sent: Fri March 29, 2024 01:33 AM
From: Harishkumar Pathangay
Subject: Not Logged Initially is running things differently?
Hi,
Activate not logged initially takes 22 minutes for completion of update query with 500 thousand index writes.
If I run as a simple update query provides sufficient log space it takes 60 minutes with 2 million index writes.
So the culprit here is the number of index writes for the same query execution under two different scenarios.
Why is the question?
The performance gain of NLI is not from less transaction logging but rather over head in index writes or the way the query is executing itself internally.
I am yet to generate explain from section and compare the results.
Please confirm if someone has faced similar issues.
------------------------------
Harishkumar Pathangay
------------------------------