This is the second part of a series of blogpost on Oracle database PGA usage. See the first part here. The first part described SGA and PGA usage, their distinction (SGA being static, PGA being variable), the problem (no limitation for PGA allocations outside of sort, hash and bitmap memory), a resolution for Oracle 12 (PGA_AGGREGATE_LIMIT), and some specifics about that (it doesn’t look like a very hard limit).
But this leaves out Oracle version (more...)
First things first: Thou shalt not explicitly set AQ_TM_PROCESSES=0 ! Unless, of course, you want to disable the Queue Manager Process (QMNC in Oracle 11.x). Which you may want to do during database upgrades to prevent Streams or Advanced Queueing from interfering with the upgrade process. However, if you don’t reset this parameter afterwards, you might run […]
If you're using dynamic SQL to source your menus and you're utilising the apex_application_pages dictionary view to build you list of menu options - I suggest you consider using Materialized Views
We have a reporting application that does just this; and the query also links to an authorisation table that decides what types of users see which reports.
You can create a snapshot (materialized view) using something as simple as
create materialized view apx_application_pages
One of the presentations I went to at the DOAG conference earlier on this month was called “PL/SQL Tuning, finding the perf. bottleneck with hierarchical profiler” by Radu Parvu from Finland. If you do a lot of PL/SQL programming and haven’t noticed the dbms_hprof package yet make sure you take a good look at it.
A peripheral question that came up at the end of the session asked about problems with line numbers in pl/sql procedures; why, when (more...)
In Oracle database version 18.104.22.168, Oracle introduced the Oracle In-Memory Database option. It is possible…
APPEND hint in oracle is very useful, there are many benefits
Fragmented data loading – data will clustered above the HWM so that similar data will be together. Otherwise Oracle will place the records in blocks using PCT_FREE rule
- No undo will be generated – in large direct path insert will free up large amount of data blocks
- Less CBC latches (because of no UNDO blocks), less CPU usage
- Readers need not undo the records, (more...)
I suppose it had to happen eventually but one of my presentations has ended up on YouTube. It’s a recent presentation I did for the Oracle Midlands user group in September.
The topic is (as the title of this blog post hints at!)Boosting select performance by clustering data. The video consists of the slides I presented, changing as the presentation progresses, with my audio over the top. It goes on for a bit, (more...)
This post is about tracing logical and physical reads in Oracle using systemtap. You will find a few examples illustrating the main mechanisms used by Oracle to do physical and logical I/O and you will learn how to build systemtap scripts to further explore and troubleshoot Oracle I/O.Introduction:
Performing input/output to and from the storage media, i.e. physical I/O, is one of the critical tasks underlying all database engines. When accessing data (more...)
There have been rumblings from the HPC community indicating a general suspicion of and disdain for Big Data technology which would lead one to believe that whatever Google, Facebook and Twitter do with their supercomputers is not important enough to warrant seriousness—that social supercomputing is simply not worthy. A little of this emotion seems to […]
Recently I’ve seen an issue with CPU usage on a server running Windows 2003 Server in a VMware. This is a small Virtual Machine with just 2 cores allocated (which are possibly mapped to “threads” on a host level but I don’t know the details). For some reason very high System CPU time was reported in a Statspack report.
Here is how it looks like in a 1 hour Statspack report:
Host CPU (CPUs: 2 (more...)
Yes, direct path reads again :) No worries I’m already a bit bored from digging in this algorithm, so I think this is going to be my last post about it. Till they change it again of course. And yes, in 22.214.171.124 they did. For introduction to DPR, you can check my previous post, because […]
Latest Packt Publishing Java EE 7 books are all around performance and tuning. I had the pleasure to review another book, the "Java EE 7 Performance Tuning and Optimization
" by Osama Oransa.Abstract
With the expansion of online enterprise services, the performance of an enterprise application has become a critical issue. Even the smallest change to service availability can severely impact customer satisfaction, which can cause the enterprise to incur huge losses. Performance tuning (more...)
Chasing cost efficiency, business often cuts back on money spent on UAT boxes used for performance testing. More often than not, this is a bad-decision, because the only thing worse than not having a UAT environment is having a UAT environment that is nothing like production. It gives a false sense of security while exposing your application to all sorts of nasty surprises. In this post I tried to summarize a few typical configuration differences (more...)
This post is about using SystemTap
for investigating and troubleshooting Oracle RDBMS. In particular you will learn how to probe Oracle
processes and their userspace
functions. These techniques aim to be useful
as well as fun
to learn for those keen into peeking under the hood
of the technology and improve
their effectiveness in troubleshooting and performance
is a very powerful technique that can be used to complement the available (more...)
In Oracle Database 12c we can find many new and shiny things… So many that we can miss the little good things really easy. I think that this one, is one of them. Previously I made a post “All About HCC“, describing how HCC is working and some of the issues that we can hit […]
When trying to understand why the optimiser might choose not to use an index to read rows from a table, I find the following diagram helpful.
The axis along the bottom represents the selectivity of the query (0% being no rows selected, 100% being all rows selected), and the axis up the side represents the cost of the chosen operation (more costly = more work to do).
When reading from a table without using an (more...)
Here is a step-by-step analysis of the deadlock which occurs due to unindexed foreign keys. This analysis was performed on Oracle XE 126.96.36.199 – results may vary on other versions.
A typical deadlock graph arising from an unindexed foreign key is as follows.
Resource Name process session holds waits process session holds waits
TM-0000508a-00000000 27 101 SX 28 12 SSX
TX-00090013-0000019b 28 12 X 27 101 S
This type of (more...)