Tuesday, June 10, 2008

I don't know...

I don't know if

a) I'm getting grumpier as I get older

b) people are losing the ability to phrase a question. 

It just seems to me that day by day, the quality of the questions goes down.  I don't remember people asking questions like this just 8 years ago.  I've been participating in forums since 1994.  "Back in the day", the questions were mostly thought out, with examples - with some background, with some thought.  In the last couple of years - this seems to be changing - universally.

Oh well, it is probably related to both bullet points... Anyway here is the QOD - question of the day.

Subject: query is not using particular partition and index despite use full table scan

Entire question:

SELECT count(*)
FROM
DIM_BANK RIGHT OUTER JOIN FACT_JRNL_ACTG ON (DIM_BANK.BANK_ID=FACT_JRNL_ACTG.BANK_ID)
LEFT OUTER JOIN DIM_BSA ON (FACT_JRNL_ACTG.BSA_ID=DIM_BSA.BSA_ID)
LEFT OUTER JOIN DIM_FY ON (FACT_JRNL_ACTG.FY_ID=DIM_FY.FY_ID)
LEFT OUTER JOIN DIM_APD ON (FACT_JRNL_ACTG.APD_ID=DIM_APD.APD_ID)
LEFT OUTER JOIN DIM_PSCD ON (FACT_JRNL_ACTG.PSCD_ID = DIM_PSCD.PSCD_ID)
WHERE
(
(DIM_BANK.BANK_ACCT_CD IN ('33')
OR '*' IN ('33'))
AND DIM_PSCD.PSCD_CLOS_CL_CD IN ('1', '4', '5')
AND CASE DIM_BSA.CASH_ACCT_FL WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END = 'Yes'
AND DIM_APD.PER != 0
AND DIM_APD.PER != 99
AND DIM_APD.FY < 2008
OR DIM_FY.FY = 2008
AND DIM_APD.PER <= 6
AND DIM_APD.PER != 0
AND CASE DIM_BSA.CASH_ACCT_FL WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END = 'Yes'
AND DIM_PSCD.PSCD_CLOS_CL_CD IN ('1', '4', '5')
AND (DIM_BANK.BANK_ACCT_CD IN ('33')
OR '*' IN ('33'))
);

 

That's it folks.  "My query isn't doing partition elimination (probably, I'm sort of GUESSING) and isn't using some index (on some column of some table)"

Before anyone says anything - this comes from the US.

Some wows from reading the query:

  • "(DIM_BANK.BANK_ACCT_CD IN ('33') OR '*' IN ('33')) - why would you do that?
  • "CASE DIM_BSA.CASH_ACCT_FL WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END = 'Yes'" - why would you do THAT?
  • "CASE DIM_BSA.CASH_ACCT_FL WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END = 'Yes'" - just in case you didn't believe us the first time
  • wow, more duplicated predicates...
  • outer joins to DIM_BSA, but we have that neat predicate using CASE - if we actually needed to outer join to DIM_BSA then all of the attributes would be NULL - the predicate would never be true - hence, we do not need or want to outer join to DIM_BSA
  • Same comment about DIM_FY, we outer join to DIM_FY, but if we make up a row - then DIM_FY's attributes will all be null and DIM_FY.FY = 2008 cannot be true (or false, it is unknown)
  • Ditto for DIM_APD
  • and of course DIM_PSCD
  • and just to make it 100% complete, the outer join to DIM_BANK - ditto. every single outer join in this query, should not be there.

I've responded with:

My car won't start.  Now we are even, we have shared the same level of detail regarding our respective problems.

Thursday, June 05, 2008

when others then null, redux

It keeps happening.

Over and over it keeps happening.

Here is case 124,215,412,523 for your review.

The user writes me:

... I have a situation where I have started receiving the 'Cursor is Closed' SQLException from a stored procedure, say, SP_A, suddenly for last 5-6 weeks, since 2nd week of March. This happens only intermittently. If I take the same parameters and execute the sp again after a few minutes after getting the error, it does not generate the error and returns expected results back.

...

Additional details as requested:


what ora-xxxx error are you getting

Unfortunately, it does not generate an ORA-???? error. I know that many a times, the 'Cursor is Closed' error is a coding issue, but in this case, that is not the case. I cannot create this error at will, I have not been able to create the error in our development or user acceptance environment. Even in production, I cannot recreate it with same parameters that have generated the error.

...

The reason I think this could be oracle memory issue is that we very recently moved from 9i to 10g RAC. This application - java code and stored procedures - have been working for about 5 to 6 years now. Even the errors of Cursor is closed we receive are not consistent i.e. The parameters that generate the error now will work with exact same code a few minutes later.

...

Ok, here they are - they say "there is no ora-xxxx error, Oracle is just closing cursors on us - no fair.  We know this cannot be a bug in our code, this is a bug in Oracle. 

The story ALWAYS goes that way.  I just finally gave up... closed the question.  They followed up:

 

What was found that SP_A had exception clause of WHEN OTHERS that was masking the actual error.
When we removed that error, this is the error we see

ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "SP_A"
ORA-06508: PL/SQL: could not find program unit being called: "SP_A"
ORA-06512: at "SP_B", line 317
ORA-06512: at line 1

This is what has been happening intermittently ( we just did not know, because the way the sp was
coded and the java code interacted with returned parameters, it manifested into Cursor Closed Error
).

 

and now they want "insight" into why this is happening....

 

Remember everyone, everyone remember, keep in mind:

When others not followed by RAISE or RAISE_APPLICATION_ERROR is almost certainly, with 99.999999999% degree of accuracy, a bug in your developed code.  Just say "no" to when others not followed by raise or raise_application_error!

http://www.oracle.com/technology/oramag/oracle/07-jul/o47asktom.html

Monday, June 02, 2008

Seems like a good idea...

Check it out - and comment away...  Tyler has a good idea/thought going on over there.

Also (total context switch here) this is the coolest new bit of SQL I've had the pleasure of writing for a while (11g and above only...)

 

ops$tkyte%ORA11GR1> with data
2 as
3 ( select when, thing, val
4 from t2
5 unpivot ( val for thing in
6 ( LF_ROWS, LF_BLKS, LF_ROWS_LEN, LF_BLK_LEN,
7 BR_ROWS, BR_BLKS, BR_ROWS_LEN, BR_BLK_LEN,
8 DEL_LF_ROWS, DEL_LF_ROWS_LEN, DISTINCT_KEYS,
9 MOST_REPEATED_KEY, BTREE_SPACE, USED_SPACE,
10 PCT_USED, ROWS_PER_KEY, BLKS_GETS_PER_ACCESS,
11 PRE_ROWS, PRE_ROWS_LEN, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE )
12 )
13 )
14 select THING, before, middle, coalesce, rebuild, rebuild-coalesce diff
15 from data
16 pivot( max(val) for when in ( 'before' as before, 'middle' as middle,
17 'coales' as coalesce, 'rebuil' as rebuild )
18 )
19 order by thing
20 /

THING BEFORE MIDDLE COALESCE REBUILD DIFF
-------------------- ---------- ---------- ---------- ---------- ----------
BLKS_GETS_PER_ACCESS 4 4 4 4 0
BR_BLKS 6 6 6 4 -2
BR_BLK_LEN 8028 8028 8028 8028 0
BR_ROWS 2810 2810 1409 1405 -4
BR_ROWS_LEN 33639 33639 16856 16809 -47
BTREE_SPACE 22524924 22524924 11322528 11274488 -48040
DEL_LF_ROWS 0 238415 0 0 0
DEL_LF_ROWS_LEN 0 9601917 0 0 0
DISTINCT_KEYS 499999 488415 250000 250000 0
LF_BLKS 2811 2811 1410 1406 -4
LF_BLK_LEN 7996 7996 7996 7996 0
LF_ROWS 499999 488415 250000 250000 0
LF_ROWS_LEN 20121735 19665121 10063204 10063204 0
MOST_REPEATED_KEY 1 1 1 1 0
OPT_CMPR_COUNT 0 0 0 0 0
OPT_CMPR_PCTSAVE 0 0 0 0 0
PCT_USED 90 88 90 90 0
PRE_ROWS 0 0 0 0 0
PRE_ROWS_LEN 0 0 0 0 0
ROWS_PER_KEY 1 1 1 1 0
USED_SPACE 20155374 19698760 10080060 10080013 -47

21 rows selected.


I used pivot to turn my rows into columns and then unpivot to turn some of my columns into rows (I flipped this result side on its side!)



A bit of background, I wanted to compare the effects of:




  • freshly built index (before)


  • to an index that had lots of deletes done to it (middle)


  • to an index that was just coalesced after those deletes (coalesce)


  • to a rebuilt index (rebuild)



So, in order to capture the information I just did this:



ops$tkyte%ORA11GR1> create index t_idx on t(x,object_name);
ops$tkyte%ORA11GR1> analyze index t_idx validate structure;

ops$tkyte%ORA11GR1> create table t2
2 as
3 select 'before' when, index_stats.* from index_stats;

delete data here....

ops$tkyte%ORA11GR1> analyze index t_idx validate structure;
ops$tkyte%ORA11GR1> insert into t2
2 select 'middle' when, index_stats.* from index_stats;

ops$tkyte%ORA11GR1> alter index t_idx coalesce;
ops$tkyte%ORA11GR1> analyze index t_idx validate structure;
ops$tkyte%ORA11GR1> insert into t2
2 select 'coales' when, index_stats.* from index_stats;

ops$tkyte%ORA11GR1> alter index t_idx rebuild;
ops$tkyte%ORA11GR1> analyze index t_idx validate structure;
ops$tkyte%ORA11GR1> insert into t2
2 select 'rebuil' when, index_stats.* from index_stats;


Now, index_stats has lots of columns... and my table T2 has very few rows - so, if I turn my rows into columns and columns into rows, I'd be able to better visualize this particular set of data on screen (in sqlplus of course...)



That is what that pivot/unpivot query did for me - I like it...



 



Looks better than this did :)



 



ops$tkyte%ORA11GR1> select * from t2;

WHEN HEIGHT BLOCKS NAME
------ ---------- ---------- ------------------------------
PARTITION_NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN
------------------------------ ---------- ---------- ----------- ----------
BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
---------- ---------- ----------- ---------- ----------- ---------------
DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY
------------- ----------------- ----------- ---------- ---------- ------------
BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------------- ---------- ------------ -------------- ----------------
before 3 2944 T_IDX
499999 2811 20121735 7996
2810 6 33639 8028 0 0
499999 1 22524924 20155374 90 1
4 0 0 0 0

middle 3 2944 T_IDX
488415 2811 19665121 7996
2810 6 33639 8028 238415 9601917
488415 1 22524924 19698760 88 1
4 0 0 0 0

coales 3 2944 T_IDX
250000 1410 10063204 7996
1409 6 16856 8028 0 0
250000 1 11322528 10080060 90 1
4 0 0 0 0

rebuil 3 1536 T_IDX
250000 1406 10063204 7996
1405 4 16809 8028 0 0
250000 1 11274488 10080013 90 1
4 0 0 0 0