RE: Improve Query sqlm_9csc97utkgjub

Well researched, JL.  

Since I suspect this is E-biz, the actual population beyond the “basic out of the box used columns” for OE_ORDER_LINES_ALL is probably driven by the definitions of flexfields and organizations and will vary from implementation to implementation.  

Counting all the nullable columns for not null values (possibly grouped by last_update_date trunced to month or year, which may indicate you don’t care about some column any longer) and including their column position from the dictionary will likely yield joy that the number of columns with any contents at all is less than 108 and you may be lucky that they are all near enough the beginning. Now don’t get your treadmill out, but there is a significant chance that rebuilding this table ONCE physically in the order of one of the indexes you already have and with the columns in a useful order in the table will help. The last_update_date column in E-Biz typically changes frequently for a few days, weeks, or months, and then stops changing. IF you routinely purge closed orders on a calendar basis, you may also have “empty front” where space that contains no current rows is scanned before you get to the first row for a lot of things. Checking how many blocks you read if you select <a non indexed> column from tab where rownum < 2 is a quick test for that, but you need to be certain of not running into cached blocks for this test. Empty blocks, especially if there are plenty of empty blocks, may never reach the “use me” top of the list, and it is a real thing that the front of a table can have a lot of empty blocks before you reach the first block with a row in it. Last I checked, Oracle does not mark the first block or chunk of blocks containing rows for the start of a table scan.  

Now affinity for your most used index (or index leading column, which may be the same for many indexes in E-biz) only needs to be chunkwise to nearly optimize index size and speed, so (test) it may be the case that physically reordering chunkwise with youngest chunks first, but in order within chunks does something useful. So, for example, everything with an update date in the last 40 days into the new table with the possibly new column order in last update date order, and then 41-90 days, and so forth. (IF you use partitioning for pruning by date following whatever the current E-biz rules for that are, following that partitioning. That may degrade somewhat with respect to tablescan start and I don’t know a way to add partitions telling Oracle to make the new partition the start of a tablescan.)  

Anyway, I didn’t look at your actual query, so none of these things may be applicable to your case. Several of the long term side effects of deletes that I mentioned have enhancement requests dating back to the 1990s, and I haven’t checked whether they still happen or can be avoided or repaired without a rebuild since 12.something. So take this all with a grain of salt.  

mwf      

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Monday, October 28, 2024 10:59 AM
To: ORACLE-L (oracle-l_at_freelists.org) Subject: Re: Improve Query sqlm_9csc97utkgjub    

I've found a definition of the table that has 363 columns with last_update_date at column 158. This means that if any column past column 255 holds a value then the last_update_date column will be in the second rowpiece of the table (although all sorts of nasty breakdowns can happen, the simplest case for tables with more than 255 columns (but less than 510) is for the last row piece to hold the last 255 columns and the 1st row piece to hold the first 510 - "column count".  

363 - 255 = 108, so you can expect all columns past the first 108 to be in the second row piece; and may find cases where updates to a row result in the second row piece moving to a new block ... hence the threat of single block reads on a tablescan of last_update_date

Regards

Jonathan Lewis      

On Fri, 25 Oct 2024 at 15:21, Amit Saroha <eramitsaroha_at_gmail.com> wrote:

Thank you for your detailed email and feedback. I will carefully read both the blog entries but one thing I have checked is that the last update date is not in the middle of the table, the table has 360 plus columns and the rows are not chained.  

Best Regards,

AMIT    

--
http://www.freelists.org/webpage/oracle-l