Recent Posts by Abel Macias

SQLT has been tested and modified to work on 12.2.0.1 and even a little bit for the future 18.1 version. All About the SQLT Diagnostic Tool (Doc ID 215187.1)

"My Customer" has a ADG db that is used for reporting. Upgraded from 11gR2 to 12c. Some queries fail with an ORA-29771 process USER (OSID 335621) blocks LGWR (OSID 314841) for more than 70 seconds The underlying problem has been there forever, it was a time bomb. here is why First what is the same in 11g and 12c A SQL when it parses grabs a shared lock in DBINSTANCE state object.When the SCN is advanced by the recovery in an ADG the LGWR locks the DBINSTANCE state object in...

During an escalation where WORKLOAD statistics end up being corrupted and producing aberrant execution plans I was made aware of a blog post about to "Never gather WORKLOAD stats on Exadata…" . The blog post goes ahead to identify that the statistic "physical read total multi block requests" does not appears to count correctly the multi-block requests. The discussion continued on the Oracle Community Forum . I wanted to know what this statistic really measures. The Documentation...

I am sorry that I left this blog unattended for so long.  So many things happened in my personal life that had to take precedence. Still I am not out of the woods but this year I will start to do some postings.  I am sorry that I left this blog unattended for so long.  So many things happened in my personal life that had to take precedence. Still I am not out of the woods but this year I will start to do some postings.  I am sorry that I left this blog unattended for so long.  So...

A new version of SQLT has been published. Version 12.1.12. (see 215187.1 ) The new thing included are several matrices to display the three reasons of SQL Plan Directives : Table Cardinality Misestimate Directives Group By Cardinality Misestimate Directives Join Cardinality Misestimate Directives And segregates them by the two states Usable Superseded The "Single Table" ones are displayed in the "Columns" Section, and the  "Group by" and "Join" Directives are displayed on their own...

A follow up on the case of "Same SELECT used in an INSERT has different Execution Plan" The query has the following predicate : CH.CONTACTDATETIME > SYSDATE-5 The testcase I have has table,column,index statistics from 27-jun-2013.If I run *today* the insert with the SYSDATE clause on my testcase with stats from 27-jun-2013 then I get a very poor cardinality estimation on access to CH because the stats reflect data there is little or no data present from after 27-jun-2013....

Recently I have seen a raising trend of issues about chained rows so this post is to show a not so obvious side effect.  I see no reason to explain what a chained row is and examples of them as in the internet there are very good postings that talk about them like http://www.akadia.com/services/ora_chained_rows.html Migrated Rows is just a special case of chained rows. I am going to use a similar testcase as the article to show my point. Similar table, just bigger columns as the...

More parses in 12.1 ? Some customers upgrading from 11.2.0.3 to 12.1 might get alarmed because they see a spike in total number of parse count. But not to worry, it is not a problem in 12.1. The problem is in 11.2.0.3. Lets take the following example : select a.name, b.value     from v$statname a, v$mystat b    where a.statistic# =  b.statistic#      and ( a.name like '%parse count%'         or a.name like 'session cursor cache%')      and value>0;   begin   for n in 1..100 loop   begin   ...

A customer complained that a query and its INSERT counterpart had different execution plans, and of course, the INSERT was slower. First lets look at the SELECT : SELECT ua_tr_rundatetime,         ua_ch_treatmentcode,         ua_tr_treatmentcode,         ua_ch_cellid,         ua_tr_cellid  FROM   (SELECT DISTINCT CH.treatmentcode AS UA_CH_TREATMENTCODE,                          CH.cellid        AS UA_CH_CELLID          FROM    CH,                  DL         WHERE  ...