Oracle database operating system memory allocation management for PGA – part 2: Oracle 11.2

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...)

Create Histograms On Columns That Already Have One

RSS content

The default value of METHOD_OPT from  10g onwards is ‘FOR ALL COLUMNS SIZE AUTO’.

The definition of AUTO as per Oracle documentation is  :
AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

This basically implies that Oracle will automatically  create histograms on those  columns which have skewed data distribution and there are  SQL statements  referencing those columns.

However, this gives rise to (more...)

How To Set EAR Upload Size for Oracle Enterprise Manager

By default, there is a limit for EAR upload size (~40 MB) in Enterprise Manager. If your ADF application is fairly big, you may face this limitation and will not be able to deploy through Enterprise Manager. Deployment for large EAR would work through WebLogic console, WLST script or directly from JDEV, however sometimes you may need to deploy through Enterprise Manager. I will describe in this post, how you could increase the limit - (more...)

Oracle Cloud Friendly – Red Samurai ADF Performance Audit

I have deployed our tool for ADF performance monitoring to Oracle Java Cloud service. It runs perfectly on the cloud, monitors slow performance and allows to analyse collected performance data. All data is stored in Oracle Database Cloud.

You can access performance monitoring dashboard using this address. Access will be available for a month or so, until my trial account will expire. You would need to use following login credentials - user: redsam, password (more...)

Oracle SOA Suite 12c tips – Tuning the SOA infrastructure thread pool

One of the new capabilities of Oracle SOA Suite 12c is the ability to control the SOA infrastructure thread pools, except the resource pools for EDN and the adapters, with Oracle WebLogic Server work managers. Each partition will has its own work managers defined. This allows you to separate services in partitions and, to some extent, tune them separately based on for example specific SLA requirements.

Well, this blog posting is not about explaining the (more...)

Troubleshoot: perdiendo el control

Me encontraba sumergido en un proyecto de diseño e implementación de un Oracle RAC 11gR2, en las instalaciones de uno de mis clientes regulares, cuando fui interrumpido por el jefe del proyecto. Me empezó a comentar sobre sobre un problema de desempeño que venían experimentando con la base de datos principal, que aún se encontraba en Oracle RAC 10gR2, y que como parte del proyecto sería actualizada a 11gR2. Habían ya identificado que (more...)

Troubleshoot: optimizador confundido

Unos días atrás me reportaron un problema de mal desempeño de una aplicación bancaria. Resulta que si bien el número de transacciones no era alto, el valor monetario si lo era, por lo que era prioritario que se completase casi instantáneamente. Con el pasar del tiempo estas transacciones iban demorando más y más, al punto de que eventualmente alguna de ellas era dada por fallida y debía ser reiniciada, poniendo nerviosos a todos los involucrados (more...)

Book Review – High Performance MySQL 3rd Edition

Add to Technorati Favorites Ver este articulo en Español

This is THE MySQL performance book. Period!

Every chapter is very well crafted, with a precise balance between theory and practice, and full of invaluable nuggets, sometimes transcending the MySQL arena and applicable to any database! Such cases are Chapter 2-Benchmarking MySQL and Chapter 3-Profiling Server Performance, very solid foundations for the reading ahead.

All over the text, authors propose tools, examples of use and proven diagnostic techniques, that will greatly improve your performance firefighter skills and enhance your knowledge of MySQL internals. Nevertheless, what I liked the most from this book is taking into (more...)

Las paradojas del tiempo

En esta ocasión les voy a contar sobre un problema muy singular y la forma en que fue resuelto, estoy seguro que muchos usuarios de AIX 6.1, y quizás de otras plataformas, deben tener este problema y no lo han notado aún.

La historia se remonta a unos meses atrás, había concluido el upgrade de una base de datos desde Oracle hacia Oracle, (more...)

Performance Enlightening – Craig Effect (Tropa de Elite)

Add to Technorati Favorites Ver este articulo en Español
During the past week I had the opportunity to assist both the Oracle Performance Firefighting and the 
Advanced Oracle Performance Analysis and only can say: awesome!!!
Craig Shallahamer is a great teacher and has a lot of resources to effectively share knowledge, even if 
the subject is complex like buffer cache structures or arid like the mathematical foundation required for 
perf analysis. That is not all, he provides valuable tips of the trade or anecdote nuggets, dipped on a 
very fresh and sometimes humorist perspective.
The result: we started searching for those  (more...)

SQL Monitor details for later tuning.

Tuning has always being good fun and something like a challenge for me.

From time to time we are being asked to find out why something did run slow while you are sleeping; answering this question is, in most cases, a challenge.

The problem:

My batch did run slow last night, can you let us know why? Or why did this query run slow? Are questions we, as DBAs, have to answer from time to time.

The solution:

Oracle has provided us with many tools to dig out information about past operations. We have EM, AWR, ASH, dba_hist_* tables, scripts (more...)

Explain Plan, Autotrace and Diff

A SQL statement can be executed in many different ways, such as full table scans, index scans, nested loops, and hash joins. The query optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time.

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, (more...)

UKOUG 2011

I'm speaking next week at the UK Oracle User Group at the ICC in Birmingham. The topic will be one I've posted several times about which is Tuning, Refactoring and Instrumentation.   Have a look at the agenda, and if you are in town, come along.  You can click on the image to go to the conference site and check out the agenda.

I Can Help You Trace It

The first product I ever created after leaving Oracle Corporation in 1999 was a 3-day course about optimizing Oracle performance. The experiences of teaching this course from 2000 through 2003 (heavily revising the material each time I taught it) added up to the knowledge that Jeff Holt and I needed (more...)

Catching up…presenting and taking part in Challenges

| Jun 14, 2010

I regret not being able to write new things more often, but in fact it only means I am OK: very busy with work, doing private stuff (which is important since life is not all about working), enjoying the three young kids and trying to achieve some good results in track and field. And for that last goal, I can say I did. After a few months of struggling to find a tiny piece of shape comparable to last years shape, just in time I found it. I won the gold medal in the Dutch Championships 400 metres hurdles. OK, be (more...)

Modifying an execution plan inside a View using a Query Block Hint (QB_NAME hint)

Optimizer Hints like Index, Cardinality, Ordered ... are local settings to a SQL statement. Local means the context given is the context of the SQL statement, the scope is local. If you query a View you cannot use for example an Index hint because the table names and aliases are hidden behind the view. So normal hinting would not work without  changing the view to add a hint.

nmon for linux (Fedora 12, x86_64)

At my current client site, I use AIX on IBM PowerPC kit. There is a neat little systems monitoring tool called “nmon” on AIX, which I quite like. I noticed recently that it’s available on Linux now, so I installed it on my machine, which runs Fedora 12. (more...)

No pruning for MIN/MAX of partition key column

Recently, I wanted to work out the maximum value of a column on a partitioned table. The column I wanted the maximum value for, happened to be the (single and only) partition key column. The table in question was range partitioned on this single key column, into monthly partitions for (more...)

El query dinámico de los pobres

Luego de casi dos semanas sin postear, les presento un caso curioso que me ocurrió en estos días. Pues ocurre que como parte de una tarea de afinamiento de aplicaciones que me solicitó un cliente, encontré con ayuda de dbms_monitor y tkprof, que la sentencia responsable del problema de desempeño era una muy peculiar, se trataba de un query que siendo estático estaba escrito de tal forma que pretendía satisfacer varios criterios de búsqueda, (more...)

¿Query dinámico?, pero sin perder de vista el contexto

Es relativamente frecuente que tengamos que recurrir a SQL dinámico cuando no sabemos en tiempo de compilación el texto completo de la sentencia. Si bien en los tiempos de Oracle 7 la única forma de lograrlo era usando dbms_sql, en la actualidad la forma preferida de procesar SQL dinámico es con execute immediate, el problema radica en que no se hace buen uso de él y se termina generando código no reutilizable y (more...)