Description The Average Active Sessions (AAS) metric is a very good indicator of the database activity. This metric represents the number of sessions, either working or waiting for a resource at a specific point in time. Idle sessions are not included in the calculation of this metric. To calculate AAS, we need another metric called [...]
So far in the joins series we’ve looked at the effect removing joins (via denormalization) has on performance. We’ve seen that joins can cause primary key looks to do more work. Lowering the normalization level to remove these can negatively impact “search” style queries though. More importantly, we’ve seen the real cost of denormalizing to remove joins is when updating records, potentially leading to concurrency waits and application bugs.
So are joins always “good”?
Description This query will show you repartition percentage between I/O, WAITS and CPU from the v$active_session_history view Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it. Instance activity repartition [crayon-533ee1b43b0b3323059005/] This query returns percentage of I/O, CPU and waits consumed by [...]
In the previous article in the joins series we compared query performance between a third normal form schema and the same schema denormalized to second normal form. We then extended it the example so our denormalized schema was in just first normal form.
The normalized approach performed better overall. The differences were small though – generally just a few consistent gets and all the queries executed in under a second. As Jeff Atwood points out (more...)
Just a quick heads-up for anyone who likes to play around with the Keep and Recycle caches.
In 11g Oracle introduced the option for serial direct path reads for tablescans on tables that was sufficiently large – which meant more than the small_table_threshold – provided the table wasn’t already sufficient well cached. (The rules mean that the choice of mechanism can appear to be a little random in the production environment for tables that are near (more...)
I’m honoured to have been accepted to speak at this year’s Rittman Mead BI Forum, the sixth year of this expert-level conference that draws some of the best Oracle BI/DW minds together from around the world. It’s running May 8th-9th in Brighton, and May 15-16th in Atlanta, with an optional masterclass from Cloudera’s Lars George the day before the conference itself at each venue.
My first visit to the BI Forum was in 2009 (more...)
Continuing the series on joins, I’m going to look at denormalization. This process reduces the number of joins necessary to return results for a schema.
One of the big arguments against normalizing data is “for performance”. The process of normalization creates new tables as relations are decomposed according to their functional dependencies. This means (more) joins are necessary to return the same results.
As usual the latest version can be downloaded here.
These are the notes from the change log:
- More info for RAC Cross Instance Parallel Execution: Many sections now show a GLOBAL aggregate info in addition to instance-specific data
- The Parallel Execution Server Set detection and ASSUMED_DEGREE info now makes use of the undocumented PX_STEP_ID and PX_STEPS_ARG info (bit mask part (more...)
Description This query return the top 10 queries by resource consumption (CPU+IO+WAIT) in the last hour from v$active_session_history. Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it. top 10 queries from v$active_session_history [crayon-533b91631cebb871999454/] This query return top queries by resources consumed, [...]
nqcmd ships with all installations of OBIEE and includes some very useful hidden functionality – the ability to generate load tests against OBIEE. There are lots of ways of generating load against OBIEE, but most require third party tools of varying degrees of complexity to work with.
It’s easy to try this out. First set the OBIEE environment: [I'm using SampleApp v309R2 as an example; your FMW_HOME path will vary]
and then the (more...)
Understanding what is going on inside OBIEE is important for being able to diagnose issues that arise, monitor its health, and dig deep into its behaviour under stress in a load test. OBIEE exposes a set of metrics through the Dynamic Monitoring Service (DMS) and viewable through Enterprise Manager (EM) Fusion Middleware Control. EM is a great tool but doesn’t meet all requirements for accessing these metrics, primarily because it doesn’t (more...)
OraLatencyMap is a SQL*Plus tool, with a core written in PL/SQL, aimed at studying Oracle random I/O by displaying the latency drill-down of the wait event 'db file sequential read' using heatmaps. The tool can also be used to collect and display event latency histograms for any other Oracle wait (more...)
three good articles about this modernism, and like me you may learn the word skeuomorphic.
How does this affect APEX? Well of course all the icons have changed again.
How does this affect (more...)
Performance tuning of the Oracle Database has always been a great thing to learn and a valuable skill for any database administer. In a previous post, I talked about how to use the Active Workload Repository (AWR) and Active Session History (ASH) to performance turn a database. In this post, I want to take a look at the Active Session History (ASH) Analytics that can be found in Oracle Enterprise Manager 12c.
At my current assignment, I’ve gone to great lengths to improve the Oracle SOA Enterprise Manager Console performance. One of the actions I took, was purging about 700 gigabytes of data from the dehydration store. Although that solved lots of other problems, the EM still wasn’t very responsive. Loading composite details could take op to 20 minutes, it they ever loaded at all. Apparently, the database was ok, because there was no load at all (more...)
Oracle monitoring and performance tuning has grown over the years. With every release of the Oracle database the monitoring capabilities that have been provided have evolved. In Oracle11g, monitoring with statpack has given way to Automatic Workload Repository (AWR) and Active Session History (ASH) for near real time monitoring of items within the Oracle database. Adding AWR and ASH to your performance tool kit can yield huge savings of time to tuning (more...)
Random thoughts on a Friday afternoon…
We’ve all got problems. More to the point, every IT department or team has problems of some kind. It’s why we hire consultants, buy products, start long and arduous journeys into the great unknown depths of root cause analysis, and so on.
What fascinates me is the level at which we come to identify with our problems. When I’ve gone into an environment to deliver recommendations, the conversation usually (more...)
SQL> col name format a20
SQL> select * from v$sysstat where name = 'logons cumulative'
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
0 logons (more...)