WHERE aa.OBJECT_ID IN (select object_id from ALL_OBJECTS where object_id=15199343 ) ORDER BY aa.PACKAGE_NAME, aa.OBJECT_NAME) WHERE ROWNUM < 102 SELECT * FROM (SELECT DISTINCT aa.PACKAGE_NAME, aa.OBJECT_NAME, aa.OVERLOAD, aa.OBJECT_ID, aa.OWNER FROM ALL_ARGUMENTS aa I am seeing a sys lob segment of this table being scanned but this table is not used in the sql, why optimizer is trying to access this segment/table. If you want to drop the patch after playing around with this example a call to execute dbms_sqldiag.drop_sql_patch(name=>’validate_fk’) will suffice.ĭo you have any notes on WRI$_EMX_FILES. In a production environment creating a hash table from the parent keys and probing it with the child keys may reduce the CPU usage and random I/O quite dramatically.īear in mind that the best possible plan may depend on many factors, such as the number of child rows per parent, the degree to which the parent and child keys arrive in sorted (or random) order, and then you have to remember that Oracle gets a little clever with the original anti-join (note that there are only 10,000 probes for 100,000 child rows – there’s an effect similar to the scalar subquery caching going on there), so trying to patch the plan the same way for every parent/child pair may not be the best strategy. Rerunning the validation test with the patch in place I got the following plan – clearly the patch had had an effect.Ġ 0 0 HASH JOIN RIGHT ANTI (cr=246 pr=242 pw=0 time=96212 us starts=1 cost=39 size=22000 card=1000)ġ0000 10000 10000 INDEX FAST FULL SCAN PAR_PK (cr=24 pr=23 pw=0 time=1599 us starts=1 cost=4 size=50000 card=10000)(object id 73235)ġ00000 100000 100000 INDEX FAST FULL SCAN CHI_FK_PAR (cr=222 pr=219 pw=0 time=27553 us starts=1 cost=32 size=1700000 card=100000)(object id 73237)ĭon’t worry too much about the fact that in my tiny example, and with a very new, nicely structured, data set the original plan was a little faster. Without the patch I got the following plan. The patch that the script creates simply tells Oracle to ignore the embedded hints (in particular I don’t want that orderedhint), but I’ve left a few other options in the text, commenting them out. This is what the statement for my parent/child pair looked like (cosmetically adjusted): The SQL_ID represents the query for my specific tables, of course, so you will have to do a test run to find the query and SQL_ID for the validation you want to do. I’ve tested this on 12.2.0.1 and 19.3.0.0, but for earlier versions of Oracle, and depending what patches you’ve applied, you will need to modify the code. Hint_text => 'ignore_optim_embedded_hints' Here’s a little script I ran to test a few variations on the theme: I did say that the strategy might not work for SQL optimised by SYS, but it turned out that it did. In that article I suggested (before testing) that you could create an SQL patch for the generated SQL to over-ride the plan taken by Oracle – a plan dictated to some extent by hints (including a “deprecated” ordered hint) embedded in the code. This note is a short follow-up to a note I wrote some time ago about validating foreign key constraints where I examined the type of SQL Oracle generates internally to do the validation between parent and child tables.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |