Monthly Archives: October 2014

Silver Pockets Full –

Yesterday, I came across Chet Justice’s blog post on the phenomenon of “Silver Pockets Full”. Interesting!!!
It claims that – a month with 5 Fridays and 5 Saturdays and 5 Sundays – occurs once in every 823 years.

So, the problem statement was to find out the correctness of this supposition. And, also to find out the next months which satisfies this criteria.

I believe, it can be done through any language (technical, of course) be it – SQL or Java or PERL or Python or etc. I prefer SQL and got a query ready.

Let’s try it out.

with t1 as
	select trunc(sysdate,'yyyy')+level-1 lvl
	from dual
	connect by level <= 50000
, t2 as
		to_char(lvl,'yyyymm') mx, 
		sum(decode(to_char(lvl,'dy'),'sun',1,'sat',1,'fri',1,0)) count_x
	from t1
	group by to_char(lvl,'yyyymm')
select to_char(to_date(mx,'yyyymm'),'yyyy-Month') yyyy_Month
from t2
where count_x = 15
order by mx;



... rest removed for brevity

138 rows selected 

Oracle database bookmarks –

General Oracle db concepts –

1. Why there is implicit commit before and after executing DDL Statements
2. Implicit commit with DDLs — AskTom
3. Why not use Redo log for consistent read
4. Difference between redo logs and undo tablespace
5. What does redo log files hold?
6. Why Hash Join goes slower?
7. TKPROF: high SQL*Net message from client + excessive parsing
8. What is the meaning of SQL*Net message from client — Ask Tom
9. Will Oracle lock the whole table while performing a DML statement or just the row? — Stack Overflow
10. Developing applications in Oracle APEX
11. What is High Water Mark (HWM) ?
12. Concept of High Water Mark (HWM) — Ask Tom
13. SQL Hints – Oracle 11.1 docs
14. Do I need to create indexes on foreign keys? — Stack Overflow

 SQL and PL/SQL – 

1. Query – merging intervals — Aketi Jyuuzou
2. Group By preserving the order — Aketi Jyuuzou
3. Counting continuous years — using Tabibitosan method
4. Tabibitosan method — Aketi Jyuuzou
5. REGEXP_REPLACE – Is this strange Or Am I Missing something? — Jeneesh


How did you learn… Cary Millsap ?

One not-so-fine evening, while at work, I was stuck with a SQL performance issue. Over the internet, I found an article by Cary Millsap titled Why You Should Focus on LIOs Instead of PIOs. Going through the content, I got some further doubts. Finally, as always, I resorted to the OTN forums and posted my doubts.

For those who are not aware about OTN, it stands for Oracle Technology Network. A technical discussion forum where technologists and enthusiasts meet and discuss their concerns.

Back to the topic…

In no time, I was helped by some of the most renowned database experts including the author himself, Cary. :)

After my doubts were cleared, overwhelmed by his knowledge and efforts in helping me, I contacted him. Then I asked him few further technical doubts and finally, a peculiar (silly!) question: “How did you learn so much stuff about Oracle”. :P

And, here was his reply –

A complete post on his blog. :) Thanks a lot, Cary!!!

Learning about ASH and AWR –

Lately, I have become a l’il more serious and concerned about SQL (and database) performance tuning, which at some point or the other, becomes an absolute necessity.
So, to take the tuning activity to the next higher level, beyond the execution plans, I thought of delving into the concepts of AWR (Automatic Workload Repository) and ASH (Active Session History).

I believe, this is the point, where a Developer meets a DBA course of activity.

So, my quest for some good starter material, halted on this amazing video tutorial on YouTube “Performance Tuning with ASH and AWR data” by Kellyn Pot’vin, a former Oracle ACE Director.

Towrads the end, she advised to go through some nice resources/blogs, and I have noted the same here for my future reference purpose:

1) Karl Arao —

2) Tyler Muth —

3) Kyle Hailey, John Beresniewicz, Graham Wood —

4) Kellyn Pot’vin —

Really appreciate Kellyn for the wonderful video tutorial. Thanks!!!