Peter Ramm's Oracle performance analysis stuff

If only following columns of an index are used as filter conditions and the first columns of the index are missing in the filter, Oracle's optimizer may use the INDEX SKIP SCAN operation. At SQL execution the DB will iterate in that case over all distinct values of the skipped column and proceed with B-tree access for the columns used as access criteria. The efficiency of a SKIP SCAN therefore depends on the number of distinct values for the skipped column(s). If the skipped column has only one distinct value, then the SKIP SCAN operation will succeed with 3-5 buffer gets similar to a regular RANGE SCAN. If the skipped column has lots of distinct values, then the B-tree access with the filter criteria will be executed as many times as the number of distinct values, resulting in thousands or millions of buffer gets for a single index access instead of 3-5. So far, this is mostly known for SKIP SCAN. But also if the SQL plan states an INDEX RANGE SCAN, this possibly problemati...

Sometimes it is useful to know about the network latency of client connections to database. Normally this would be done with the established network tools tnsping, ping, traceroute, tcptraceroute etc. from client or DB server side. But what if there is only SQL access to the DB server and no access to the client? This post shows how the network latency can be estimated using the recordings from ASH if the application uses a certain access pattern. The network latency between client and database server can be estimated by the number of number of SQL executions of a single session between two ASH snapshots. If an application executes the same very short running SQL against the DB over and over again in a loop, then: it is a bad architecture approach for the application because the network latency will cause a significant overhead for application performance however, this behavior gives a possibility for a weak estimation of the network latency between client and database ser...

Why Oracle DB allows retrospective evaluation of workload of active sessions by Active Session History (ASH) if you have Enterprise Edition and Diagnostics Pack. The default retention time for ASH is 7 days back from now, usual settings for production are around 30 days. But sometimes you need to analyze the evolution of database load over longer periods like over many years (e.g. as base for hardware planning and capital budgeting). There are less builtin alternatives yet: Increase the ASH retention time up to years leads to a huge amount of data, cumbersome or impossible to handle Using the AWR-warehouse function in an EM Cloud Control installation may fit your needs, but especially for single or less database instances this causes an inappropriate effort. What Panorama-Sampler allows you to extract some information from ASH and store it compressed as summary other periods between one hour and one day. The Panorama-GUI contains table-views and diagrams for evaluatio...