ITL waits are so common and sometimes it will kill the concurrency of the application. In general there are 1 ITL slot (caused by INITRANS) for tables and 2 slots for indexes. When a data block is formatted the ITL slot is created in the variable part of the block header as specified by INITRANS for that segment. As long as free space is available in the block, ITL slots can be grown up to MAXTRANS for any future requirements. Here plays the important role PCTFREE which can impact negatively. With the default setting 10% (more...)
Of course, one of the first places you should go is Oracle Support, but if you, like me, put off doing that and scratch your head for a while wondering why ELAPSED_TIME in V$SQL might be significantly lower than CPU_TIME when there are no obvious other explanations, then note these two bugs in 184.108.40.206:
BUG 7168259 – Millisecond elapsed_time shows as 0 : Elapsed_time for SQL statements which execute in under one millisecond can show up as 0
BUG 7561762 – Elapsed_time is under-reported : Elapsed_time can be 0 or significantly less than V$SQL.CPU_TIME if (more...)
Our Experience Tells Us So...We know from hard-fought experience that the more work we push through a system the more processing time involved. That's not rocket science... but to mathematically represent the relationship between work and time is rocket science.
Decades ago Operations Research queuing theory researchers quantitatively established the relationship between work and time. They put into math what we all have experienced. In our DBA world this means that as the system activity increases, then so does CPU consumption and eventually Oracle non-idle wait time.
Find The Big BarThe classic way to graphically represent all the (more...)
This Monday Oct 1, 2:00-2:50PM I’ll be presenting at the OakTable World which used to be called as Oracle Closed World, this year it’s a full two-day conference with awesome speakers lineup and very interesting topics… you can just watch this video by Mogens Norgaard to know more about the roots of this very technical event.
My presentation title is “Where did my CPU go?” – monitoring & capacity planning adventures on a consolidated environment .. and If you love to hack the ASH and AWR data and curious about CPU capacity planning then you’re really going to enjoy (more...)
Original Post can be viewed at Limiting I/O and CPU resources using 11g Oracle Resource Manager
Recently I was working on using Oracle DBMS_RESOURCE_MANAGER to limit resources usage by read-only queries. This was required to prevent long running ad-hoc and poorly written queries.Instead of writing custom sql /script to kill long running session, we decided to utilize Oracle Database Resource Manager. We decided using elapsed_time as criteria for cancelling sql.But during [...]
Lately I’ve been using SQL runtime execution statistics combined with SQL monitor for performance diagnostics and, honestly, almost forgot about SQL trace. So this note is not very useful to me but it might be to someone: along with ALL_EXECUTIONS option appeared in (I believe) 11gR2, there’s a new option starting with 220.127.116.11 which can significantly decrease amount of data in the trace files for STAT lines compared to ALL_EXECUTIONS, still providing some of them from time to time.
Here is a case to demonstrate:
declare n number := 0; begin dbms_session.session_trace_enable(true, true, 'all_executions'); for x (more...)
MOATS could be a answer for my needs but it required some objects to be created in database. From other side Snapper is using dynamic objects only but it is not displaying history so I can’t see at a glance if system performance has been improved or not.
I’ll be co-speaking with Randy Johnson (one of the authors of Expert Oracle Exadata) at E4 to share about the war stories and detail on a bunch of technical stuff on a Peoplesoft and BIEE consolidation project we had on one of our clients. See the abstract below:
Randy Johnson & Karl Arao
A PeopleSoft & OBIEE Consolidation Success Story
In today’s competitive business climate companies are under constant pressure to reduce costs without sacrificing quality. Many companies see database and server consolidation as the key to meeting this goal. Since its introduction, Exadata has become the obvious choice for (more...)
The effect of ASM redundancy/parity on read/write IOPS – SLOB test case! for Exadata and non-Exa environments
Last week I had a lengthy post at oracle-l that tackles Calibrate IO, Short Stroking, Stripe size, UEK kernel, and ASM redundancy effect on IOPS Exadata which you can read here
followed by interesting exchange of tweets with Kevin Closson here (see 06/21-22 tweets) which I was replying in between games at UnderwaterHockey US Nationals 2012 which we won the championship for the B division I have my awesome photo with the medal here
This post will detail on the ASM redundancy/parity effect on IOPS… if… by changing the ASM redundancy (external, normal, and high) will it decrease the workload (more...)
Most of the examples and discussions in the links above utilize with the following syntax:
SQL> exec sys.dbms_shared_pool.purge(‘&address, &hash_value’,'c’)
What's new in 11.2:A new (overloaded) procedure (more...)
I have a permanent job at the NetCracker‘s System Performance group. Recently I was offered to do one day job outside, on-site in another company, which coincidentally has an office close to NetCracker’s Moscow office. It was an opportunity to apply my skills in a completely different situation which I couldn’t miss; plus I’ve never done public presentations before and this was a good occasion to practice that. Here I’d like to write down some notes how the event went.
2 or 3 weeks before the event I’ve got the list of topics to work on. It included several (more...)
- Architects design a system and pass the specification off to the developers.
- The (more...)
Todays story is about, how dynamic sampling can effect your plan stability during upgrade and how you can recover from it as long as you know what your are doing.
A little bit of background, I’m currently in the middle of migrate and upgrade cycle for 12TB warehouse from 10.2.0.3 Solaris to 18.104.22.168 (yet) Linux.
Plan stability on this system is maintained by locking the main driving table statistics and hinting the queries in case CBO does not pick the right driving table.
The system uses temporary tables a lot for staging operations and (more...)
Just a quick note/post about the significance of COMPRESS and TCPBUFSIZE parameter in performance of a GoldenGate Extract Pump process. COMPRESS helps in compressing the outgoing blocks hence helping in better utilization of the bandwidth from source to target. GG is going to uncompress the blocks before writing them to the remote trail file on the target. Compression ratios of 4:1 or better can be achieved. Of course, use of COMPRESS may result in increased CPU usage on both the sides.
TCPBUFSIZE controls the size of the TCP buffer socket that is going to be used by the Extract. If the bandwidth allows, it (more...)
SQL patches have recently saved the day for me in a production issue where a given SQL had suddenly changed execution plan causing IO overload (a full scan was done instead of index-based read for a high-load statement). 11g allows for a quick fix in such situations (as in, stop the fire and buy time to find a more stable solution): a set of hints can be added to a given query via the use of SQL Patch.
The official Oracle documentation has not many details (more...)
Example: Measure latency for single block reads for an OLTP database, as exposed by Oracle wait event interface as 'db file sequential read'. This is used to investigate a case of performance degradation where storage behaviour is involved.
Step 1: collect GV$EVENT_HISTOGRAM (more...)
Oracle has plenty of instrumentation, statistics counters and wait event data are the bread and butter of Oracle monitoring and tuning.
However counters are typically incremented during the life of the instances/sessions, so delta values are often needed to make sense of data for analysis. AWR reports are a way to do that for instance-wide data.
A class of GV$ views that can be of help for faster/ 'online' monitoring are the V$ views that I call of (more...)