Oracle Open World 2017 presentation

Hi,
I will be presenting about ASM internals in Oracle Open World 2017 conference on Sunday October 1st. Following are the details:

Session ID: SUN5682

Session Title: Oracle Automatic Storage Management and Internals

Room: Moscone South – Room 155 Date: 10/01/17
Start Time: 12:45:00 PM
End Time: 01:30:00 PM

See you there!

Update: Added the presentation file. ASM_internals_Riyaj_OOW2017

IOUG Collaborate 2017

I have a few presentations scheduled in IOUG Collaborate 2017, Las Vegas.

1. Session: 621: RAC Clusterware internals
**Date/time: Mon, Apr 03, 2017 (01:30 PM – 02:30 PM) : Jasmine A

2. Session: RAC cache fusion internals. ( OakTable track )
**Date/time: Wed, Apr 05, 2017 (09:45 AM – 10:45 AM) Room: South Seas C

3. Session: 479: An in-depth review of ASM and internals
**Date/time: Wed, Apr 05, 2017 (02:45 PM – 03:45 PM) : Palm B

Presentation files ( Updated after the presentations).

Thank you for coming to my presentation(s).

ASM review and internals
clusterware internals
RAC_cache_fusion_internals
rac_training_scripts

Come to Vegas 🙂

( Hopefully, I will have better luck in Blackjack tables too. That reminds me, I need to practice in the blackjack simulator 🙂 )

RMOUG Training days 2017

I will be speaking about the following topics in Rocky Mountain Oracle User group Training days (RMOUG, Denver) February 7-9, 2017.

Come to my presentations and say Hi to me 🙂

1. RAC Cache Fusion Internals and Performance Tuning RAC Wait Events – Tuesday 3:15PM to 5:15PM

2. Index and Redo Internals – Wednesday 4-5PM

3. Database In-Memory Internals – Thursday 11:15-12:15PM

RMOUG training days 2017

Here are the scripts and presentations as promised 🙂 Of course, no implied or explicit warranty.

rac_performance-wait-events pdf

rac_cache_fusion_internals pdf

inmemory_internals_riyaj pdf

index_and_redo_internals pdf

index_and_redo_internals scripts zip

rac_training_scripts zip

Disclaimer: These are my opinions based upon research and data, it doesn’t reflect the views of my employer.

Accessing HCC compressed objects using an index

Problem

I came across another strange SQL performance issue: Problem was that a SQL statement was running for about 3+ hours in an User Acceptance (UA) database, compared to 1 hour in a development database. I ruled out usual culprits such as statistics, degree of parallelism etc. Reviewing the SQL Monitor output posted below, you can see that the SQL statement has already done 6 Billion buffer gets and steps 21 through 27 were executed 3 Billion times so far.

Statistics and execution plan

Please refer to the execution plan. Due to the functionality of the SQL statement, the execution plan seems acceptable. 3 Billion executions in to the view (at step 21) is also expected, however, why would the SQL statement runs much slower in the UA database ? [ Table names are changed for security reasons. ].

library cache lock on BUILD$ object

I was testing an application performance in 12c, and one job was constantly running slower than 11g. This post is to detail the steps. I hope the steps would be useful if you encounter similar issue.

Problem

In an one hour period, over 90% of the DB time spent on waiting for library cache lock waits. Upon investigation, one statement was suffering from excessive waits for ‘library cache lock’ event. We recreated the problem and investigated it further to understand the issue.

Following is the output of wait_details_rac.sql script (that I will upload here) and there are many PX query servers are waiting for ‘library cache lock’ wait event.

   SID PID        EVENT                          USERNAME   OSUSER     STATE               WAIT_TIME   WIS P1_P2_P3_TEXT
------ ---------- ------------------------------ ---------- ---------- ------------------- --------- ----- ----------------------------------------
                                                                                                            2163

   276  12445     library cache lock             TST_USR    test       WAITING                     0     1 handle address 399021346904-lock address
                                                                                                            2147

   288  12449     library cache lock             TST_USR    test       WAITING                     0     4 handle address 399021346904-lock address
                                                                                                            2136

   303  12453     library cache lock             TST_USR    test       WAITING                     0     4 handle address 399021346904-lock address
                                                                                                            2136

   315  12457     library cache lock             TST_USR    test       WAITING                     0     4 handle address 399021346904-lock address
 ...Snipped..                                                                                                           2152

OTNYathra2016

Over the last many years, some of you have invited me to attend conferences in India, and talk about Oracle RAC and performance. I have not had an opportunity to make it to conferences in India, until now 🙂

I am excited to announce that I will be participating in OTN sponsored Oracle ACE Director’s tour in India (April 23rd to May 2nd 2016), and presenting ( with deep dive demos ) about RAC, performance, and in-memory. This is a golden opportunity for you to learn some of the internal stuff that I talk about in my class too.

Refer http://otnyathra.com for further details.

I am also excited to be in the tour with Connor McDonald, Biju Thomas, Sai, and many others. Don’t forget to send an email to Sai, the conference rooms will fill out quickly.

Did I mention deep dive demos? 🙂

Update 1: Updating the presentation slides and scripts. Do ask if I miss any script that you are looking for 🙂

india_2016_files_v1

RMOUG Training Days 2015

I will be talking in Rocky Mountain Oracle User Group Training Days 2015( http://www.rmoug.org), with live demos (hopefully there will be no failures in the demo). My topics are:

Feb 17: Deep dive: 3:15PM to 5:15PM – RAC 12c optimization: I will discuss RAC global cache layer in detail with a few demos. You probably can’t find these deep Global Cache layer details anywhere else 🙂

Feb 19: Wednesday: 2:45PM to 3:45PM – Advanced UNIX tools: I will discuss both Solaris and Linux advanced tools to debug deep performance issues.

Feb 19: Wednesday: 12:15PM – 1:15PM – Exadata SIG panel with Alex Fatkulin.

Come to Denver. Come on, it won’t be cold ( I think 🙂 )

Uploading presentation and scripts of the presentation files: RMOUG_2015_Riyaj_RAC_12c_optim files

Presentation files for Unix tools demos: RMOUG_2015_Riyaj_Unix_tools

In-memory pre-population speed

While presenting at Oaktable World 2014 in San Fransisco, I discussed the in-memory pre-population speed and indicated that it takes about 30 minutes to 1 hour to load ~300GB of tables. Someone asked me “Why?” and that was a fair question. So, I profiled the in-memory pre-population at startup.

Profiling methods

I profiled all in-memory worker sessions using Tanel’s snapper script and also profiled the processes in OS using Linux perf tool with 99Hz sample rate. As there is no other activity in the database server, it is okay to sample everything in the server. Snapper output will indicate where the time is spent; if the time is spent executing in CPU, then the perf report output will tell us the function call stack executing at that CPU cycle. Data from these two profiling methods will help us to understand the root cause of slowness.

1. @snapper.sql out,gather=stw 600 10 “select sid from v$session where program like ‘%W00%'”
2. Perf tool : perf record -F 99 -u oracle -g sleep 3600

Oaktable world 2014 is on!

Many Oaktable members are planning to talk about deep technical topics in Oaktable world 2014. Looking at the agenda, I am excited, so many deep topics are planned. I will be talking about in-memory internals on Monday morning at 9AM, 9/29/2014, right after Mogens’ Keynote speech. You can find all details here: Oaktable world 2014. I will post my presentation slides after the presentation.

Start your open world week presentation with mine :). Sorry, no beers planned at that time, it is 9AM, after all!

Thanks for attending my presentation at Oaktable World 2014. You can download the slides : In-memory_internals.pdf.

Also, our book Expert Oracle RAC 12c has been translated to Chinese language. You can find details about that book in one of the translator’s blog: Alex lizx.

inmemory: Why did that table was not populated in the column store?

I enabled an huge 70G table for inmemory population, I expected the inmemory population to take a while, but the population didn’t complete even after letting it run for a day. Why?

ASH data

Initial review of the server shows no issues, no resource starvation. This must be a problem with Oracle processes itself. I started digging further, and ASH data shows that in numerous samples the process was seen reading block using single block I/I calls. Also object_id matches with the table I was trying to populate.

   select * from (
    select start_time, end_time, sql_id,event, current_obj#,  cnt_on_cpu + cnt_waiting tot_cnt,
  	rank () over ( order by (cnt_on_cpu + cnt_waiting) desc ) rnk
    from  (
    select
  	  min(start_time) start_time,
  	  max(end_time)   end_time,
  	  sql_id,event,current_obj#,
  	  sum(decode(session_state,'ON CPU',1,0))  cnt_on_cpu,
 	  sum(decode(session_state,'WAITING',1,0)) cnt_waiting
    from
     ( select
 	first_value(sample_time) over( order by sample_time ) start_time,
 	last_value(sample_time) over( order by sample_time
 				rows between unbounded preceding and unbounded following ) end_time,
 	sql_id,event, session_state, current_obj#
	from
 	     (select * from v$active_session_history ash where session_id= &&sid and session_serial#=&&serial_number)
     )
   group by sql_id, event, current_obj#
    )
  )
  where rnk 
/
START_TIME		  END_TIME		    SQL_ID	  EVENT 			 CURRENT_OBJ#	 TOT_CNT	RNK
------------------------- ------------------------- ------------- ------------------------------ ------------ ---------- ----------
18-AUG-14 08.42.03.702 AM 18-AUG-14 09.02.06.463 AM		  db file sequential read	       168967	     990	  1
												       168967	     156	  2
								  direct path read		       168967	      50	  3
						    bdwtqttka2w2y					   -1	       3	  4
						    bdwtqttka2w2y direct path read		       168967	       1	  5
						    24uqc4aqrhdrs				       168967	       1	  5
													   -1	       1	  5