Oracle statistics invalidating sql Adult sites for c2c chatting

6854933580_2c8b688306_z

On one system, it happened after a reload on the same day because of a wrong data delivery.

In the other customer project it was because data is delivered in multiple files that are loaded sequentially into the DWH database.

In this case, it would use the execution plan of the previous load – but 12 nested loops joins on a driving table with 30000 rows are probably not a good idea. To avoid this, we should explicitly define no_invalidate = FALSE as in the example above.

In this case, a new (and better) execution plan is calculated for each of the successive ETL jobs.

It helps to avoid a lot of parse time directly after running the gathering jobs.

But for an ETL job as described above, this behavior is not what we want.

In this demo I call DBMS_STATS to create a histogram and I expected that dependent cursors would be marked INVALID afterwards but this simply didn’t happen.

I almost lost my believe/confidence in myself and Oracle ;-) Somehow I forgot, or maybe completely missed, the fact that cursors are invalidated in a rolling fashion since the introduction of Oracle10g.

oracle statistics invalidating sql-50oracle statistics invalidating sql-81oracle statistics invalidating sql-65

The result is a much better performance of the ETL jobs.The next step is to gather object statistics again using DBMS_STATS and let DBMS_STATS invalidate dependant cursors by setting the parameter NO_INVALIDATE to FALSE: select child_number,executions,parse_calls,loads,invalidations 2 from v$sql where sql_id = '5dkz0zau0v8yh'; CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS ------------ ---------- ----------- ---------- ------------- 0 2 2 1 1 select child_number,executions,parse_calls,loads,invalidations 2 from v$sql where sql_id = '5dkz0zau0v8yh'; CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS ------------ ---------- ----------- ---------- ------------- 0 1 1 2 1 Based on the output above we can conclude that the child cursor has been hard parsed again, after being marked INVALID, and that this new incarnation of the child cursor has been executed once.All of the above will also happen if you still use the deprecated ANALYZE .. select child_number,executions,parse_calls,loads,invalidations 2 from v$sql where sql_id = '5dkz0zau0v8yh'; CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS ------------ ---------- ----------- ---------- ------------- 0 1 1 2 2 select child_number,executions,parse_calls,loads,invalidations 2 from v$sql where sql_id = '5dkz0zau0v8yh'; CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS ------------ ---------- ----------- ---------- ------------- 0 1 1 3 2 Like we concluded in the case of DBMS_STATS, we can conclude that the child cursor has been hard parsed again and re-executed after being marked INVALID.As long as you just load data from one table to another without any transformations, filters or lookups, this is not crucial.But when an ETL step joins several tables, filters some of the rows or evaluates key/code values from multiple lookup tables, the join order and join methods can be quite important for the performance of the ETL job.My recommendation for gathering statistics in an ETL job is to always set the parameter no_invalidate to FALSE: Ok, you could argue that the example is not what you have in your DWH environments.

You must have an account to comment. Please register or login here!