Просторы интернета
I don't suppose many people edit and compile PL/SQL code from the SQL*Plus command line any more, but the following reminder about avoiding mistakes is probably valid in other development environments even though it may be enabled through a difference mechanism.
You can set the level of warning that you get [...]
In the previous note about a problem dropping virtual columns the “guilty party” that made it impossible to drop any columns was based on a complex data type owned by the MDSYS (Spatial) schema. This note demonstrates the same problem with a very simple example created from scratch in an [...]
Here's a problem that appeared recently on the Orace Developer forum showing one of the classic symptons of new features namely that “mix and match” often runs into problems. This example has been a long time coming so “new” is something of a misnomer, but the alternative source of unlucky [...]
Here's a little surprise that showed up in the most recent (March 2022) article that I sent to Simpletalk for the series on transformations. I had been using 19c (19.11.0.0) to create and run my little demos but the editor had used 12.2.0.1 to check the examples and questioned [...]
In one of my notes about excavating the x$ tables I said that there was a problem “hidden in plain sight” with the dynamic performance view (g)v$fixed_view_definition. If you haven't previously spotted the problem here's what the view looks like when you describe it:
SQL> desc gV$fixed_view_definition Name [...]Here's a script I've used from time to time to help me get some ideas about how to hack through some x$ tables. It's obviously something that can only be run by the SYS schema and it's not something to apply arbitrarily to every x$ table as the side effects [...]
This is a list of articles I've written that pick up some detail about using features of the ANSI syntax that are (or were) less commonly used in traditional Oracle SQL.Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.
[...]One of the “curiosity” questions that comes up from time to time on the various forums is the one about the cascade of names from synonym to view to object that Oracle uses to provide access to the dynamic performance views. Typically we see the pattern:
v$XXX is a public synonym [...]First if you want don't know what an Exadata Quarantine is read this.
Someone asked whether you can create your own Exadata Cell quarantine and, if you can, why you might ever want to do it?
The first step when you don't know how to do something is [...]
I posted a while back on how to use Tracing Hybrid Columnar Compression in an offload server so this is a quick follow up.
I have trouble remembering the syntax for setting a regular parameter in an offload server without bouncing it. Since I need to keep this [...]Exadata is designed to present a well-balanced system between disk scan rates, cell CPU processing rates, network bandwidth, and RDBMS CPU such that on average across a lot of workloads and a lot of applications there should not be no one obvious bottleneck. Good performance engineering means avoiding a design [...]
I had noted in my first post that using the highest level of tracing caused timeout issues with the offload server heartbeat monitor. Heartbeat issues can also occur with expensive (and badly formed) regexp expressions. By default the heartbeat monitor is set to 6 seconds which is the maximum permitted [...]
I've seen some blogs recommending that _partition_large_extents be set to FALSE for a variety of space conserving reasons without the authors thinking about the negative impact this is going to have on Smart Scan. Large Extents cause an INITIAL allocation of 8 MB and a NEXT allocation of 1 MB [...]
In 12.2, there is a stat “cell blocks pivoted” that points to a new optimization. When Smart Scan processes data, it has to create a new synthetic block that only contains the columns that are needed and the rows that pass the offloaded predicates.
If Smart Scan has to create a [...]
Several people have asked if there is any way to use INMEMORY on External Tables because the INMEMORY syntax is not supported in Create Table and Alter Table with Organization External (actually Create Table parses it but then ignores it).
While there is no way out of having to load the [...]
I finally found time to get back to External Tables and have a list of blog posts I need to write on this topic. Here's a brief one.
DBMS_ROWID will nicely break down a heap table's rowid for you into file number, block number, and row number but it doesn't handle [...]
This document is my attempt to bring together the available options that can be used to determine the root cause of an issue in order to create a roadmap to help support engineers narrow down the cause of concern.
It is a living document and will be edited and amended as [...]
I posted a while back on how to use Tracing Hybrid Columnar Compression in an offload server so this is a quick follow up.
I have trouble remembering the syntax for setting a regular parameter in an offload server without bouncing it. Since I need [...]I had previously commented on how to use the FPLIB facility in a trace event but the question came up today of how to trace HCC in an offload server. The facility name in this case is ADVCMP (Advanced Compression) and the hierarchy is:
ADVCMP_MAIN [...]I've noticed several people who were familiar with using trace events with cellsrv, were uncertain about how to use tracing the new offload server architecture. So whereas in the past you could have added tracing to SmartScan processing with:
> cellcli -e 'alter cell events = "trace[fplib.sage_data] disk=lowest, memory=lowest"'the new syntax [...]
Just a few thoughts I've been meaning to blog for a while:
1. Number of columns that can be offloaded ExadataVery early on in the Exadata Smart Scan implementation a restriction was added to prevent offloading more than 255 columns. This was done because of performance issues observed by customers. Smart Scan [...]
We've observed databases with very large buffer caches where Serial Scans don't make use of Smart Scan when that would have executed faster: improvements to the decision making for Serial Scans have been made under bug 31626438. This fix is back-portable.
A key difference between PQ and [...]
One of the main performance technologies underlying ADW is the In-Memory format column cache and a question that has come up several times is: how does the columnar cache handle DMLs and cache invalidations. This blog post attempts to answer that question.
The Two Columnar Cache FormatsThe original columnar cache was [...]
The RDBMS stats for Cellmemory are designed to closely follow the pattern used by the Inmemory stats
Query StatsEach column in each one MB of disk blocks will be rewritten into one IMC format Column CU in flash and a set of Column [...]
Question: do I need to know anything in this blog post?
Answer: No, it is a true cache and CELLMEMORY works automatically
Question: so why should I read this blog post?
Answer: because you like to keep a toolkit of useful ways to control the system when needed
The DDLThe Exadata engineering team [...]
Many people know that in 12.1.0.2 we introduced a ground-breaking columnar cache that rewrote 1 MB chunks of HCC format blocks in the flash cache into pure columnar form in a way that allowed us to only do the I/O for the columns needed but also to recreate the original [...]
In Part 2 we are going to look at making use of the trace events that show what was discussed in Part 1.
NB: Oracle no longer adds new numeric trace events, going forward new trace events use the Unified Tracing Service whose grammer is much simpler. The elements we [...]
I've read a number of blog entries from various people who've clearly put great diligence into trying to understand how the decision to use the buffer cache for a table scan or whether it is better to use direct read for the scan is made. Since this is critical decision [...]
I was asked a question yesterday that reminded me there are always people completely new to the topic who need an introduction – somewhere to start before the other articles make sense. So, here's my brief write-up of everything you need to know about the basic of Oracle Table [...]