Using Git with PL/SQL in a Shared Development Database

Gerger Consulting - Mon, 2022-11-21 06:18


With Gitora 6, developers can work on the same code base (i.e. a package, procedure etc…) in the same database without blocking each other.

Many development teams use a single database for development. Many of them use the same database for testing, as well. They achieve this by using different schemas for different purposes. Gitora 6 enables these teams to create Git repos for these schemas and pull updates between them.

With Gitora 6, you can even create a different schema for every developer and have them merge their code using Git.

Gitora 6 enables you to implement any modern development workflow in a single database.

How does Gitora 6 work?

Gitora 6 introduces a new repo type called Single Schema Repo (SSR). As its name suggests an SSR manages database objects from a single schema. The DDL scripts in SSR’s don’t contain the schema prefixes so that Gitora can execute them in other schemas in the same database.

This enables developers to work on the same package, procedure, function, view etc… (i.e. anything that can be created with the CREATE OR REPLACE command) at the same time, in the same database in different schemas.

An Example

Let’s go through an example: Let’s assume that the team is working on a logistics software and have a schema named LOGISTICS that stores all their database objects. The team can create a schema (or already have one) called LOGISTICS_TEST in the same database. Here are the steps the team needs to follow so that they can pull their changes to LOGISTICS_TEST.

  1. Create a single schema Gitora repo that manages the LOGISTICS schema. Let’s call it the REPO_LOGISTICS.
  2. Add all relevant database objects in the LOGISTICS schema to the LOGISTICS_REPO.
  3. Create another single schema Gitora repo that manages the LOGISTICS_TEST schema. Let’s call it the REPO_LOGISTICS_TEST

That’s it. That’s all there is to it. From this point on, any change you make to the code in the LOGISTICS schema can be pulled to the LOGISTICS_TEST schema using Gitora (and vice versa).

Single Schema Repos can also be used to create separate development environments in the same database for every developer in the team.

Multiple Developers, Same Database

Assuming we already have the LOGISTICS schema and the REPO_LOGISTICS repo from the previous example, here is how that would work:

  1. Create a schema for each developer: LOGISTICS_JOE, LOGISTICS_MARY, LOGISTICS_PAUL.
  2. Create a single schema Gitora repo for each schema. Let’s call them REPO_LOGISTICS_JOE, REPO_LOGISTICS_MARY, REPO_LOGISTICS_PAUL respectively.

From this point on, all three developers can work in their own schema and edit any package, procedure, view etc… freely, without overwriting each other’s changes or being affected by them. Using Gitora, they can create new branches in their own repo, for features or bugs they work on without affecting anyone else.

When the time comes to put it all together, they can use Gitora to merge their changes.

We wrote a few articles to get you started:

Creating Single Schema Repos.

How to implement agile development in a single database

How to manage changes to tables with Single Schema Repos

Before Gitora 6, agile database development with Gitora required multiple databases to implement. With Gitora 6 you can achieve it in a single database.

We are incredibly excited to release Gitora 6. We believe it will bring a massive productivity boost to Oracle database development teams worldwide. We can’t wait for you to try it.

Gitora 6 is available now. You can download it from this link.

Categories: Development

Are You Using Version Control for Your Database or Are You Ticking a Box?

Gerger Consulting - Wed, 2022-11-09 09:45


You use a version control system to solve certain problems, to accomplish certain tasks. These are:

  • Keep track of who changes what, when and why.
  • If necessary, use previous versions of the code base for testing, development etc…
  • Move changes between environments faster, with fewer errors during deployment.
  • Improve productivity of the team (i.e. less time coordinating manually and more time developing. For example different developers working on the same parts of the code base simultaneously and merging their changes at a later time, hopefully mostly automatically.).

If you are using a version control system but not accomplishing any of the things above, are you really using a version control system?

Here is a perfectly reasonable scenario that I’ve seen in the real world. I’ve seen this scenario with teams using Git with SQL Developer, SVN/Git with another Oracle PL/SQL IDE. Invariably, they all end up here:

  1. In the DEV database, you make changes to the package PKG_HIRING in the HR schema and then commit the package under /HR/PKG_HIRING.sql file in Git.
  2. Next, another developer makes changes to the same package. He commits his changes under /HR/next_version/PKG_HIRING.sql, because… why not? It’s not like there is any enforcement.
  3. A third developer commits his changes to /HR/PKG_HIRING.pkb because for some reason, the tool he uses for editing uses the .pkb suffix for packages.
  4. The fourth developer named John, decides to copy PKG_HIRING to a file and work on it. He thinks he can move his changes to the database later. So he creates /HR/JOHN_DO_NOT_TOUCH/PKG_HIRING.sql
  5. A fifth developer, makes changes to the HR.PKG_HIRING package but forgets to commit the package to Git. Hey it happens…
  6. A sixth developer makes even more changes. He commits the package to Git under HR/PKG_HIRING.sql thereby committing not only his changes but also the fifth developers changes.

Three months later, you are tasked with deploying the latest version of the HR application to the TEST database. Which PKG_HIRING package are you going to deploy to TEST?

I actually know the answer to this question. You will deploy the one in the HR schema. It’s the safest option. I cannot help but wonder tough.. in these past three months, did John save his changes to the database? If so, how did he do it? Did he simply overwrite the package? What if some changes were lost?

At this point, I have to ask… Why do you even have a version control system? Why does that Git/SVN repo even exist? Is it useful at all or are you just doing all this just to tick a box and to be able to say that you are using a version control system?

Gitora solves all these problems above. It creates a version control system around Git and enforces it at the database level.

Let’s repeat the same steps for the six developers but this time let them use Gitora:

  1. In DEV, you make changes to the PKG_HIRING package in the HR schema. When you are done, you open the Gitora web app, you see the list of database objects you changed, you select the ones you want to commit (in this case PKG_HIRING) you add a comment to the commit (such as the ticket number) and click the commit button.
  2. The second developer follows the same process as you. He cannot make any changes to the package until you are done with it. Once you are done, he can make his changes. He opens the Gitora App, commit his changes to Git and is done. Note that, he doesn’t get to choose which folder he saves the PKG_HIRING.sql file to. Gitora creates a well structured working directory with reasonable folder and file names for database objects. The locations of these files are also standard. When there is human decision involved with naming and putting files in the file system, a lot of errors can occur. Gitora prevents these errors.
  3. The third developer’s IDE used the .pkb extension for his file. Since developers don’t get to name the files or choose the folders they save database object scripts to, the third developer also has no choice but to follow the same process as the first two developers. By the way, Gitora works with any IDE. You can use SQL Developer, SQL Navigator, TOAD or PL/SQL Developer. It doesn’t matter. Everyone in your team can use a different editor if they choose to do so. Gitora still works.
  4. The fourth developer was impatient. He created a new file under JOHN_DO_NOT_TOUCH and saved his changes to that file. With Gitora, he has no direct access to Git and the Git working folder. (Gitora is a server side solution. You don’t install Git to developer’s computers.) So he cannot do that. However, if he wants to do mischief, he still can copy the PKG_HIRING package to a new file in his computer and make changes there. Then, he can simply overwrite everything in the PKG_HIRING package with his version at a later time. This will cause some changes to be lost in the package depending on when he does it. He can even go ahead and commit his changes to Gitora. But hold on a second. At some point, either during development or testing, the mistake he made will be detected. In the first scenario, there is no way to know for certain who erased all the functioning code and exactly what code should be put back to the package. It will be trivial to use Git to figure out who deleted the perfectly functioning code from PKG_HIRING because Gitora always keeps track of who changed what, when and why. Since we can know when the mistake happened, we will know the correct version of the package to put back to. John will take the heat for his egregious mistake and merge his changes to the correct version of the package. No one wants that kind of heat. It is easy to see that Gitora incentivizes the right kind of behavior among teammates by keeping everyone accountable for their actions as well as making it possible to recover from critical mistakes.
  5. The fifth developer simply forgot to commit his changes to Git. With Gitora there are two ways this can be caught. First, the sixth developer who wants to edit the package will contact the fifth developer to ask when the package will be available. Second, Gitora web app always shows which objects are locked by whom. So before preparing a deployment, you can always check if there are any database objects that are locked.
  6. In the first scenario, the sixth developer committed fifth developers changes along with his. With Gitora, the sixth developer will always commit only his changes because there is no way he can gain access to the PKG_HIRING without the fifth developer releasing the package first. Gitora enforces that if an object is being edited by someone, no one else can edit it. With Gitora, version control is not something you do on the side but it is tightly integrated into your workflow and enforced.

And finally, three months later, you are tasked with preparing the deployment to TEST. How are you going to move the changes to the TEST database? With Gitora, all you need to do is to open the Gitora app, connect to the TEST database and pull the HR repo from DEV to TEST and you are done.

Going back to the beginning of the article; Gitora makes Git a useful version control system for the Oracle Database.

As shown in the second scenario with Gitora you can:

  • Keep track of who changes what, when and why.
    We caught John misbehaving.
  • If necessary, use previous versions of the code base for testing, development etc…
    We recovered an old version of PKG_HIRING and have John merge his changes to it.
  • Move changes between environments faster, with fewer errors during deployment.
    Done via point and click Gitora Web Application.
  • Improve productivity of the team (i.e. less time coordinating manually and more time developing. For example different developers working on the same parts of the code base simultaneously and merging their changes at a later time, hopefully mostly automatically.).
    The second scenario reduced manual coordination significantly. Gitora can do a lot more. You can have different teams work on the same code base at the same time and merge their changes just like Java, JavaScript developers. However that’s beyond the scope of this article. Please read this article and this article to learn more about how you can implement parallel development with Gitora.
Categories: Development


Greg Pavlik - Sun, 2022-08-28 15:05

 Full live performance. I've been enjoying these funk-folk-jazz songs for some time. Allegedly the music is in "old Ukrainian" - I don't speak Ukrainian or the closely related Carpatho-Russian dialect of Transcarpathia, so I can't really discern what that means - but consider the vocals as instrumentation in any case. In any case, this music is great fun at times and always stirs the human emotion. 

A barely related side note: as a case study in creative folk-culture appropriation to modern forms, I recommend the Ukrainian musical film Hutsulka Ksenya. The plot revolves a young American whose late father leaves him his fortune on the condition he marry a Ukrainian woman. Distinctly unenthusiastic he visits the Carpathian region (presumably Zakarpattia Oblast, from which the Pavlik family emigrated) and falls for a young Hutsul woman.... I won't say more, its a terribly creative film and thoroughly enjoyable.

Hutsuls, as an aside, are not strictly speaking Ukrainians in the ethno-cultural sense, but in the broader sense of Ukraine as a multi-cultural nation. 

Isaac of Syria in Dostoevsky

Greg Pavlik - Wed, 2022-08-17 11:32
My second (and more careful) read of Brothers Karamazov (this time the Pevear/Volokhonsky translation) established it as the most influential piece of world literature I have to date encountered. I regard it as the pinnacle of modern forms, but also amongst the most piercing of critiques of modernity as such. For years I have re-read it in part and in full, as well as numerous commentaries on the novel, Dostovesky's corpus en toto, and of course works on Dosteovsky himself more generally. Of the latter, I cannot imagine a finer literary biography than the work of the late Joseph Frank.

On recurring theme is an attempt to uncover the influences behind the portrait of the staretz Zosima. Many figures have been sited, including the famous Tikon of Zadonsk, of which there is an entire book dedicated to the topic. To my mind, however, the most obvious parallel to the teachings of Zosima is the 7th century ascetic Isaac of Syria. There could not be more clear parallels between his ascetic writings and the long chapter on Zosima's homilies in Karamazov. I liberally quote from two sites with supporting details and illustrations

There is an interesting connection between St Isaac of Syria and Dostoevsky. The latter owned an 1858 edition of the Slavonic translation of the Homilies by St Paisius Velichkovsky (Victor Terras, A Karamazov Companion: Commentary on the Genesis, Language, and Style of Dostoevsky’s Novel [Madison: U of Wisconsin, 1981], p. 22). Furthermore, Dostoevsky mentions St Isaac’s Ascetical Homilies by name twice in The Brothers Karamazov. The first time is in Part I, Book III, Chapter 1, ‘In the Servants’ Quarters’, where the narrator observes that Grigory Vasilievich, Fyodor Karamazov’s manservant, ‘somewhere obtained a copy of the homilies and sermons of “Our God-bearing Father, Isaac the Syrian”, which he read persistently over many years, understanding almost nothing at all of it, but perhaps precisely for that reason prizing and loving it all the more’ (Fyodor Dostoevsky, The Brothers Karamazov, trans. Richard Pevear and Larissa Volokhonsky [NY: Vintage, 1991], p. 96). Dostoevsky then mentions the book again in 4.11.8, this time in the rather more sinister context of Ivan’s third meeting with Smerdyakov, when the latter 'took from the table that thick, yellow book, the only one lying on it, the one Ivan had noticed as he came in, and placed it on top of the bills. The title of the book was The Homilies of Our Father among the Saints, Isaac the Syrian. Ivan Fyodorovich read it mechanically' (Dostoevsky, p. 625).

But more importantly, Victor Terras has pinpointed a number of St Isaac’s teachings that make a definite appearance in the words of Elder Zosima in II.VI.3, especially in (g) ‘Of Prayer, Love, and the Touching of Other Worlds’ (Dostoevsky, pp. 318-20), and (i) ‘Of Hell and Hell Fire: A Mystical Discourse’ (Dostoevsky, pp. 322-4). Terras quotes the following passage from ‘Homily Twenty-Seven’ as being ‘important for the argument of The Brothers Karamazov’ (Terras, p. 23):

Sin, Gehenna, and Death do not exist at all with God, for they are effects, not substances. Sin is the fruit of free will. There was a time when sin did not exist, and there will be a time when it will not exist. Gehenna is the fruit of sin. At some point in time it had a beginning, but its end is not known. Death, however, is a dispensation of the wisdom of the Creator. It will rule only a short time over nature; then it will be totally abolished. Satan’s name derives from voluntarily turning aside [the Syriac etymological meaning of satan] from the truth; it is not an indication that he exists as such naturally. (Ascetical Homilies, p. 133)
Terras may, however, be on the wrong trail with this particular passage, though not perhaps with the rest of his parallels, since according to a note in the translation, this particular homily only exists in Syriac (Ascetical Homilies, p. 133), and does not appear to have been available in any translation Dostoevsky would have read (Introduction, Ascetical Homilies, pp. lxxvi-lxxvii). Another interesting, though less important, discrepancy, is that Pevear and Volokhonsky, in their note on the name of St Paisius (he is referenced in I.I.5 [Dostoevsky, p. 27], and footnoted on p. 780 of Pevear’s and Volokhonsky’s translation), date Dostoevsky’s edition of the Elder’s translation of St Isaac to 1854 rather than 1858. Furthermore, J.M.E. Featherstone lists among St Paisius's works, Svjatago otca našego Isaaka Sirina episkopa byvšago ninevijskago, slova duxovno-podvižničeskija perevedennyja s grečeskago.... (Moscow, 1854), thus making Pevear and Volokhonsky's date more likely, it would seem ('Select Bibliography', The Life of Paisij Velyčkovs'kyj, trans. J.M.E. Featherstone [Cambridge, MA: Harvard U, 1989], p. 163 ).

I just wanted to highlight briefly this interesting connection. At an even deeper level, however, it has been picked up on, for one, by Archimandrite Vasileios of Iveron. Having considered the ‘artistic’ gifts of St Isaac and the spiritual insight of Dostoevsky, he concludes, ‘Thus, whether you read Abba Isaac, or Dostoevsky, in the end you get the same message, grace and consolation’ (‘Από τον Αββά Ισαάκ’, p. 100).


Though the teachings of Elder Zosima from Dostoevsky’s Brothers Karmazov seem exotic to many western readers and possibly unorthodox, they in fact show a remarkable similarity to those of a favorite 7th century eastern saint, St Isaac the Syrian.   We know that Dostoevsky owned a newly-available translation of St Isaac’s Ascetical Homilies, and this volume is in fact mentioned by name twice in the novel, though in seemingly inconsequential contexts.  Dostoevsky was no doubt deeply affected by the saint’s spirituality, and I think Zosima’s principle views in fact reflect and are indebted to those of St Isaac.  Below I will list some of these distinctive views, with illustrating quotes from both the fictional Elder Zosima and St Isaac himself. (And note: these were simply the quotes that I could find very easily; I’m sure more digging would find even more striking parallels)

Love for all creation:

Elder Zosima: “Love God’s creation, love every atom of it separately, and love it also as a whole; love every green leaf, every ray of God’s light; love the animals and the plants and love every inanimate object. If you come to love all things, you will perceive God’s mystery inherent in all things; once you have perceived it, you will understand it better and better every day.  And finally you will love the whole world with a total, universal love.”

St Isaac: “What is a merciful heart? It is a heart on fire for the whole of creation, for humanity, for the birds, for the animals, for demons, and for all that exists. By the recollection of them the eyes of a merciful person pour forth tears in abundance. By the strong and vehement mercy that grips such a person’s heart, and by such great compassion, the heart is humbled and one cannot bear to hear or to see any injury or slight sorrow in any in creation. For this reason, such a person offers up tearful prayer continually even for irrational beasts, for the enemies of the truth, and for those who harm her or him, that they be protected and receive mercy. And in like manner such a person prays for the family of reptiles because of the great compassion that burns without measure in a heart that is in the likeness of God.”

 Responsibility for all:

Elder Zosima: “There is only one salvation for you: take yourself up, and make yourself responsible for all the sins of men. For indeed it is so, my friend, and the moment you make yourself sincerely responsible for everything and everyone, you will see at once that it is really so, that it is you who are guilty on behalf of all and for all. Whereas by shifting your own laziness and powerlessness onto others, you will end by sharing in Satan’s pride and murmuring against God. ”

St Isaac: “Be a partaker of the sufferings of all…Rebuke no one, revile no one, not even those who live very wickedly. Spread your cloak over those who fall into sin, each and every one, and shield them. And if you cannot take the fault on yourself and accept punishment in their place, do not destroy their character.”

 Love is Paradise on Earth:

Elder Zosima: “”Gentlemen,” I cried suddenly from the bottom of my heart, “look at the divine gifts around us: the clear sky, the fresh air, the tender grass, the birds, nature is beautiful and sinless, and we, we alone, are godless and foolish, and do not understand that life is paradise, for we need only wish to understand, and it will come at once in all its beauty, and we shall embrace each other and weep”

St Isaac: “Paradise is the love of God, wherein is the enjoyment of all blessedness, and there the blessed Paul partook of supernatural nourishment…Wherefore, the man who lives in love reaps life from God, and while yet in this world, he even now breathes the air of the resurrection; in this air the righteous will delight in the resurrection. Love is the Kingdom, whereof the Lord mystically promised His disciples to eat in His Kingdom. For when we hear Him say, “Ye shall eat and drink at the table of my Kingdom,” what do we suppose we shall eat, if not love? Love is sufficient to nourish a man instead of food and drink.”

Non-literal ‘fire’ of hell:

Elder Zosima: “Fathers and teachers, I ask myself: “What is hell?” And I answer thus: “The suffering of being no longer able to love.”…People speak of the material flames of hell. I do not explore this mystery, and I fear it, but I think that if there were material flames, truly people would be glad to have them, for, as I fancy, in material torment they might forget, at least for a moment, their far more terrible spiritual torment. And yet it is impossible to take this spiritual torment from them, for this torment is not external but is within them”

St Isaac: “As for me I say that those who are tormented in hell are tormented by the invasion of love. What is there more bitter and violent than the pains of love? Those who feel they have sinned against love bear in themselves a damnation much heavier than the most dreaded punishments. The suffering with which sinning against love afflicts the heart is more keenly felt than any other torment. It is absurd to assume that the sinners in hell are deprived of God’s love. Love is offered impartially. But by its very power it acts in two ways. It torments sinners, as happens here on earth when we are tormented by the presence of a friend to whom we have been unfaithful. And it gives joy to those who have been faithful.  That is what the torment of hell is in my opinion: remorse”


Agile PL/SQL Development

Gerger Consulting - Tue, 2022-05-10 01:52

Gitora 6, the latest version of the source control tool for the Oracle Database, enables PL/SQL developers implement agile development practices in a single database.

With Gitora 6, developers can work on the same code base (packages, procedures etc...) at the same time, in the same database.

Learn more at

Categories: Development

Intentionality Mind and Nature

Greg Pavlik - Mon, 2022-04-18 15:52
Below I quote at length DB Hart on intentionality and the horizon of rational agency. If you take a moment to think about this point, it really ought to be obvious that it is almost certainly correct. And the fact that we can share a meaning and interest in "correctness" as an end - ie, we care about that which is true - underscores the point directly. The only reason this may seem abstruse is the unconscious prejudices we carry from living in a late modern culture founded on reductive models of consciousness that are almost certainly irrational - meaning, wrong. 

"Neither doctrine nor metaphysics need be immediately invoked to see the impossibility of rational agency within a sphere of pure nature; a simple phenomenology of what it is we do when we act intentionally should suffice. The rational will, when freely moved, is always purposive; it acts always toward an end: conceived, perceived, imagined, hoped for, resolved upon. Its every act is already, necessarily, an act of recognition, judgment, evaluation, and decision, and is therefore also a tacit or explicit reference to a larger, more transcendent realm of values, meanings, and rational longings. Desire and knowledge are always, in a single impulse, directed to some purpose present to the mind, even if only vaguely. Any act lacking such purposiveness is by definition not an act of rational freedom. There are, moreover, only two possible ways of pursuing a purpose: either as an end in itself or for the sake of an end beyond itself. But no finite object or purpose can wholly attract the rational will in the latter way; no finite thing is desirable simply in itself as an ultimate end. It may, in relative terms, constitute a more compelling end that makes a less compelling end nonetheless instrumentally desirable, but it can never constitute an end in itself. It too requires an end beyond itself to be compelling in any measure; it too can evoke desire only on account of some yet higher, more primordial, more general disposition of reason’s appetites. Even what pleases us most immediately can be intentionally desired only within the context of a rational longing for the Good itself. If not for some always more original orientation toward an always more final end, the will would never act in regard to finite objects at all. Immanent desires are always in a sense deferred toward some more remote, more transcendent purpose. All concretely limited aspirations of the will are sustained within formally limitless aspirations of the will. In the end, then, the only objects of desire that are not reducible to other, more general objects of desire, and that are thus desirable entirely in and of themselves, are those universal, unconditional, and exalted ideals, those transcendentals, that constitute being’s abstract perfections. One may not be, in any given instant, immediately conscious that one’s rational appetites have been excited by these transcendental ends; I am not talking about a psychological state of the empirical ego; but those ends are the constant and pervasive preoccupation of the rational will in the deepest springs of its nature, the source of that “delectable perturbation” that grants us a conceptual grasp of finite things precisely by constantly carrying us restlessly beyond them and thereby denying them even a provisional ultimacy.

In fact, we cannot even possess the barest rational cognizance of the world we inhabit except insofar as we have always already, in our rational intentions, exceeded the world. Intentional recognition is always already interpretation, and interpretation is always already judgment. The intellect is not a passive mirror reflecting a reality that simply composes itself for us within our experience; rather, intellect is itself an agency that converts the storm of sense-intuitions into a comprehensible order through a constant process of interpretation. And it is able to do this by virtue of its always more original, tacit recognition of an object of rational longing—say, Truth itself—that appears nowhere within the natural order, but toward which the mind nevertheless naturally reaches out, as to its only possible place of final rest. All proximate objects are known to us, and so desired or disregarded or rejected, in light of that anticipated finality. Even to seek to know, to organize experience into reflection, is a venture of the reasoning will toward that absolute horizon of intelligibility. And since truly rational desire can never be a purely spontaneous eruption of the will without purpose, it must exhibit its final cause in the transcendental structure of its operation. Rational experience, from the first, is a movement of rapture, of ecstasy toward ends that must be understood as—because they must necessarily be desired as—nothing less than the perfections of being, ultimately convertible with one another in the fullness of reality’s one source and end. Thus the world as something available to our intentionality comes to us in the interval that lies between the mind’s indivisible unity of apprehension and the irreducibly transcendental horizon of its intention—between, that is, the first cause of movement in the mind and the mind’s natural telos, both of which lie outside the composite totality of nature."

DB Hart, You are Gods. University of Notre Dame Press, April 2022

Mother of Mercy

Greg Pavlik - Tue, 2022-03-01 12:56
I think in the western world, art that invokes sacred spaces is, to put it mildly, less than central to the art industry. But in parts of Europe, this is not entirely the case. One region that has a kind of generational explosion of sacred art is western Ukraine, an area that is culturally at an intersection between central and western Europe (and where part of my own family is from). To the extent there has been attention paid to these artists, it appears to be largely in Catholic journals - in part I suspect because the region is largely "Greek Catholic" in religion (as again were some of my ancestors), a religion which combines elements of Roman Catholicism and Eastern Orthodoxy: one might say a fusion, or, perhaps a "confusion", depending on ones perspective. But it clearly has generated a creative energy that re-considers traditional concepts of beauty and meaning in art forms that have often been bound to very strict boundaries of received archetypes.

One of those artists is the young Ivanka Demchuk, whose work invokes centers on abrupt symbolism that interleaves openness, geometric constructs and sparse, but striking, colorations. Shown here is her work Mother of Mercy, a rendering of the Virgin Mary receiving the prayers of her people.

Note the striking use of red within the circle that surrounds the Holy Mother. Here she seems at once in fully incorporated into the very cosmos that encompasses and surrounds us. Yet the interior red also invokes dried blood, as if she is interceding for - absorbing, really - the pain of her people. Whether intentional or not, it certainly is evocative, especially at this time of trauma and fratricidal war impacting Ukraine this image seems poignant, yet terribly relevant. We need more sacred art, not less.

If you read this, please consider donating to humanitarian and non sectarian relief for Ukraine, especially to help refugees. These posts are around for years, so if you stumble on this some time after it is posted, the need is still there and any aid is meaningful.


More tricks with OPT_PARAM

XTended Oracle SQL - Tue, 2021-11-30 18:24

Did you know you can set most parameters for the execution of a single statement without using an Alter Session by using an OPT_PARAM hint? For example, regular parameters (here forcing the storage clause in the query plan):

SQL> select /*+ OPT_PARAM('cell_offload_plan_display' 'always') */ col1 From table1;

and underscore parameters:

SQL>  select /*+ OPT_PARAM('_arch_comp_dbg_scan',2048) */ n_name from nation;

However if you try conflicting settings that set a different value in an inner query block, the value you set in the outermost query block will prevail. In this trvial example _arch_comp_dbg_scan=2048 will prevail:

SQL> select /*+ OPT_PARAM('_arch_comp_dbg_scan',2048) */ n_name from nation
where n_nationkey = (
    select /*+ OPT_PARAM('_arch_comp_dbg_scan',0) */ min(r_regionkey)
    from region where n_name < r_name); 

Another point to remember is that not all parameters get their value from the cursor environment. For example the buffer cache gets the raw value of _serial_direct_read so it cannot be overruled in this way: 

SQL> select /*+ OPT_PARAM('_serial_direct_read', always) */ n_name from nation; 

 will not force a DR scan of the table but an alter session will.

Just one more tool to keep in your toolkit for when you need it.


Categories: Development

Improvements to HCC with wide tables in 12.2

XTended Oracle SQL - Tue, 2021-11-30 18:20
HCC Compression Unit Sizing 

Since the beginning Oracle has provided four compression levels to offer a trade-off between the compression ratio and various other factors including table scans and the performance of single-row retrieval. I can not emphasize enough that the various trade offs mean that YMMV with the different levels and you should always test what works best with your application and hardware when choosing the level. Historically people have rarely used Query Low since the fast compression with reduced compression ratio means that the extra disk I/O is slower than the cost of decompression with Query High. The one time that Query Low makes sense on spinning disks is if you still have a significant number of row retrieval operations (including from index access joins).

NMVe Flash

X5 introduced NVMe technology which means that the extra I/O from Query Low is faster than ZLIB decompression which makes Query Low beneficial. So we needed to reassess the sizing of Compression Units. from to the sizing guidelines are as follows:

Name Target
 Minimum Size
 Maximum Size
 CompressionQuery Low 1000 to 8000 32 kb 32 kb LZOQuery High 1000 to 8000 32 kb 64 kb ZLIBArchive Low 8000 64 kb 256 kb ZLIBArchive High 8000 256 kb 256 kb BZ2

So, for example, Compress for Query High aims to pivot around at least 1000 rows and create a minimum compression unit size of 32 kb and a maximum of 64 kb. Using I ran these against a TPC-H Lineitem table than contained between 1 and 6 copies of each column.

For Query Low fixed 32 kb CUs this gave us the following:

 Additional copies of lineitem Rows per 32 kb CU 0 2797 1 580 2 318 3 216 4 162 5 129

and for Query High 32 to 64 kb CUs this gave us:

 Additional copies of lineitem Rows per 32 kb CU CU ave size 0 5031 32 1 1010 32 2 936 51 3 794 63 4 595 67 5 476 63

so we see that the CU size remains as 32 kb as long as we are getting a 1000 rows or more then increases in size to 64 kb to try and fit in at least 1000 rows.

It became clear that this size range was inadequate for wide tables so to get more efficient compression and longer column runs for faster predicate performance (and also better CELLMEMORY rewrites) we removed the fixed size for Query Low and increased the max:

Query Low: 32 kb to 64 kb

Query High:  32 kb to 80 kb

This will not affect narrow tables at all but wider tables should see better table compression and faster scans at the cost of slightly slower single row retrieval for data loaded by 12.2 RDBMS. If you have HCC wide tables and typically cache them on flash cache you should consider re-evaluating Query Low for data loaded in 12.2 (or use Alter Table Move Compress to recompress existing data).


Categories: Development

How to tell if the Exadata column cache is fully loaded

XTended Oracle SQL - Tue, 2021-11-30 18:11

When a background activity is happening on the cell you typically can’t use RDBMS v$ views to monitor it in the same way. One such question is how to tell if a segment is fully loaded in the Exadata column cache since this does not appear in the equivalent In-Memory v$ views.

When a segment is scanned by Smart Scan sufficiently often to be eligible the AUTOKEEP pool (typically that means at least twice an hour), the eligible 1MB chunks are written to flash in style format, and put on a background queue. Lower priority tasks pick up the queued 1MB format chunks from the flash cache, run them though the In-Memory loader, and rewrite the pure columnar representation in place of the old style column cache chunks.

The easiest way that I know of to tell when this completes is to monitor that background activity is to use the following query until it shows zero:

select name, sum(value) value from (
      select extractvalue(value(t),'/stat/@name') name,
            extractvalue(value(t),'/stat') value
      from v$cell_state cs,
                                     '//stats[@type="columnarcache"]/stat'))) t
     where statistics_type='CELL')
     where name in ('outstanding_imcpop_requests')
     group by name;

Categories: Development

External Tables Part 1 – Project Columns All vs Referenced

XTended Oracle SQL - Tue, 2021-11-30 18:09

I normally blog about table scans on Oracle native data but Oracle also supports a wide variety of features for scanning external tables and I need to cover these too. One of the ways I learn new things is being sent a bug report and saying to myself “Oh! I didn’t know you could do that”. So today I’m going to start with the grammar:

Alter Table <xt> Project Columns [ All | Referenced ]  

This DDL changes the value in the Property column displayed in user_external_tables:

SQL> select property
2    from user_external_tables
3    where table_name='C_ET';


Here we have an external table defined using the legacy driver ORACLE_LOADER. This driver defaults to projecting all the columns in the base table rather than just those needed to satisfy the query (i.e. the referenced columns) and discarding rows that have data conversion issues up to the reject limit. 

So for example we have a DATE column in our external table that contains dirty data that won’t convert cleanly to Oracle internal dates using the supplied mask we can either import it as a VARCHAR2 to not lose values or import it as a date but lose rows even on queries that don’t need that date column. We can change the behaviour to only project the referenced columns by 

SQL> alter table c_et project column referenced;
Table altered.
SQL> select property 
2    from user_external_tables
3    where table_name='C_ET';

The driver will now ignore unreferenced columns and if the date column is not used we will get all the rows in the external data and the query will run faster since datatype conversion is expensive but we may get an inconsistent number of rows depending on which columns are used in any given query. This is OK if the DBA knows a priori that there are no conversion errors or if they are willing to live with inconsistency in the number of rows returned.

The big data drivers such as ORACLE_HIVE have a different default behaviour which is to only project referenced columns and to replace data with conversion errors with NULL values and i.e. they default to returning a consistent number of rows with best performance.

Take away: In order to get consistency and fast table scan performance with ORACLE_LOADER,  the trick is to define the external table with the ‘convert_error store_null’ option and switch to ‘Project Column Referenced’. For example:

  c0 varchar(10),
  c1 varchar(10)
  TYPE oracle_loader
    CHARACTERSET  al32utf8
    FIELDS CSV WITH EMBEDDED terminated by ',' enclosed by '|'
    nullif equal blanks
    convert_error store_null
      "C0" CHAR(10),
      "C1" CHAR(10)
parallel 4;

If you want more information on handling conversion errors when scanning an external table please check the Utilities Guide section on each driver type

Categories: Development

Examining the new Columnar Cache with v$cell_state

XTended Oracle SQL - Tue, 2021-11-30 18:03 introduced the new Columnar Flash Cache where 1MB of  blocks that are all in HCC format are rewritten so as to make each column from each CU contiguous. This works by first writing all the block headers to an array, then writing all the CU headers to an array, finally writing all the Column 1 column-level-CUs, then writing all the Column2 column-level-CUs etc.

The flash cache hash table maintains a simple mapping of column numbers to 64KB flash pages so, for any given query, we can simply do asynchronous disk I/O of the minimum set of 64KB pages required to cover the referenced columns.

Within the “flashcache” cell stats there is a new nested cell stat called “columnarcache” that enables you to track how it is working.

> set long 50000000
> set pagesize 10000
> select xmltype(STATISTICS_VALUE).getclobval(2,2) from v$cell_state;


  <stats type="flashcache">
    <stats type="columnarcache">
      <stat name="columnar_cache_size">0</stat>
      <stat name="columnar_cache_keep_size">0</stat>
      <stat name="columnar_cache_keep_read">0</stat>
      <stat name="columnar_cache_keep_read_bytes">0</stat>
      <stat name="columnar_attempted_read">0</stat>
      <stat name="columnar_attempted_read_bytes">0</stat>
      <stat name="columnar_cache_hits">0</stat>
      <stat name="columnar_cache_hits_read_bytes">0</stat>
      <stat name="columnar_cache_hits_saved_bytes">0</stat>
      <stat name="columnar_cache_pop">0</stat>
      <stat name="columnar_cache_pop_bytes">0</stat>
      <stat name="columnar_cache_pop_throttled">0</stat>
      <stat name="columnar_cache_pop_invalid_buf">0</stat>
      <stat name="columnar_cache_pop_size_align">0</stat>
      <stat name="columnar_cache_pop_findchdrfailure_mainchdr">0</stat>
      <stat name="columnar_cache_pop_findchdrfailure_childchdr">0</stat>

I typically spool the output of this to wade through it an editor but if we want to monitor how it is working with some workload, we need to extract individual stats, for example I bounced the cells and verified the cache was empty:

> select xmlcast(xmlquery('/cell_stats/stats/stats/stat[@name="columnar_cache_size"]' passing xmltype(STATISTICS_VALUE) returning content) as varchar2(200) ) "COLUMNAR CACHE SIZE"
from v$cell_state
where xmlexists('/cell_stats/stats/stats[@type="columnarcache"]' passing xmltype(STATISTICS_VALUE));


I am using the 1GB TPC-H schema which takes a little more 400MB on disk when compressed with Query Low:

SQL> select sum(bytes) from user_segments where SEGMENT_NAME in ('SUPPLIER','PARTSUPP','LINEITEM','ORDERS','PART','CUSTOMER');


and checking the columnar cache again shows about half of the data has been rewritten into columnar cache format instead of caching raw blocks:

SQL> select xmlcast(xmlquery('/cell_stats/stats/stats/stat[@name="columnar_cache_size"]' passing xmltype(STATISTICS_VALUE) returning content) as varchar2(200) ) "COLUMNAR CACHE SIZE"
from v$cell_state
where xmlexists('/cell_stats/stats/stats[@type="columnarcache"]' passing xmltype(STATISTICS_VALUE));


So let’s look at how the cache helped:

         passing xmltype(STATISTICS_VALUE) 
         returning content
      ) as varchar2(200) 
from v$cell_state
where xmlexists('/cell_stats/stats/stats[@type="columnarcache"]' passing xmltype(STATISTICS_VALUE));


         passing xmltype(STATISTICS_VALUE)
         returning content
      ) as varchar2(200) 
from v$cell_state
where xmlexists('/cell_stats/stats/stats[@type="columnarcache"]' passing xmltype(STATISTICS_VALUE));


which shows we were able to eliminate about 35% of the disk I/O for this query run!

We could, of course, have gotten that information more simply with the regular stat “cell physical IO bytes saved by columnar cache” but I wanted to show how to pull values from v$cell_state for use in scripts.

Many people only use Query High compression as they find the increased disk I/O from Query Low more than offsets the savings from cheaper decompression costs. However, with the columnar cache in place, those trade-offs have changed. It may be worth re-evaluating the decision as to when user Query Low vs. Query High particularly on CPU-bound cells.

Addendum: performance bug 20525311 affecting the columnar cache with selective predicates is fixed in the next rpm.

Roger MacNicol
Oracle Data Storage Technology 
Categories: Development

Create External Table as Select

XTended Oracle SQL - Tue, 2021-11-30 17:51

I was looking through a test script and saw something I didn’t know you could do in Oracle. I mentioned it to an Oracle ACE and he didn’t know it either. I then said to the External Table engineers “Oh I see you’ve added this cool new feature” and he replied dryly – “Yes, we added it in Oracle 10.1”. Ouch! So just in case you also didn’t know, you can create an External Table using a CTAS and the ORACLE_DATAPUMP driver.

This feature only work with the ORACLE_DATAPUMP access driver (it does NOT work with with the LOADER, HIVE, or HDFS drivers) and we can use it like this:

SQL> create table cet_test organization external
  2  (
  4    default directory T_WORK
  5    location ('xt_test01.dmp','xt_test02.dmp')
  6  ) parallel 2
  7  as select * from lineitem
Table created.

Checking the results shows us

-rw-rw---- ... 786554880 Mar 9 10:48 xt_test01.dmp 
-rw-rw---- ... 760041472 Mar 9 10:48 xt_test02.dmp

This can be a great way of creating a (redacted) sample of data to give to a developer to test or for a bug repro to give to support or to move between systems. 

Categories: Development

Correct syntax for the table_stats hint

XTended Oracle SQL - Tue, 2021-11-30 17:33

A friend contacted me to ask why they were having problems using the table_stats hint to influence optimizer decision making and also to influence the decision to use direct read or buffer cache scan so this is just a quick blog post to clarify the syntax as it is not well documented.

table_stats(<table_name> <method> {<keyword>=<value>} )

Method is one of: DEFAULT, SET, SCALE, SAMPLE

Keyword is one of: BLOCKS, ROWS, ROW_LENGTH

The most useful methods are SET which does for statement duration what dbms_stats.set_table_stats does globally; and SCALE which acts to scale up the current size of the segment and can therefore be used to try what if scenarios on the segment growing on performance

For example:

select /*+  table_stats(scott.emp set rows=14 blocks=1  row_length=10)  */ * from scott.emp;
Effect of the table_stats hint on table scans

Since this is a table scan blog, let’s look at the impact on table scans. Using the Scale 1 customers table with 150,000 rows

SQL> exec dbms_stats.gather_table_stats(USER,'RDM');

SQL> select sum(BLOCKS) from user_segments where segment_name='RDM';

and use trace events

event="trace[NSMTIO] disk medium"                  # Direct I/O decision making
event="10358 trace name context forever, level 2"  # Buffer cache decision making

We see this segment is smaller than the small table threshold for this buffer cache (kcbstt=9458) and so decision making is short-circuited and will use the buffer cache :

kcbism: islarge 0 next 0 nblks 1689 type 2, bpid 3, kcbisdbfc 0 kcbnhl 8192 kcbstt 9458 keep_nb 0 kcbnbh 461198 kcbnwp 1 kcbpstt 0, BCRM_ON 0
NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 1689 (blocks), Threshold: MTT(46119 blocks),

Now let’s try the same query with the hint shown in the example above:

kcbism: islarge 1 next 0 nblks 66666666 type 2, bpid 3, kcbisdbfc 0 kcbnhl 8192 kcbstt 9458 keep_nb 0 kcbnbh 461198 kcbnwp 1 kcbpstt 0, BCRM_ON 0
kcbimd: nblks 66666666 kcbstt 9458 kcbnbh 46119 bpid 3 kcbisdbfc 0 is_medium 0
kcbivlo: nblks 66666666 vlot 500 pnb 461198 kcbisdbfc 0 is_large 1
NSMTIO: qertbFetch:DirectRead:[OBJECT_SIZE>VLOT]
NSMTIO: Additional Info: VLOT=2305990
Object# = 75638, Object_Size = 66666666 blocks

Now the size of the table in blocks is far larger than our small table threshold so we go on to evaluate whether it is a medium table and it is too large to be considered medium (cutoff is 10% cache i.e. kcbnbh=46119 blocks) so then it is evaluated as a very large table and that is true so direct read will be used.

Making the new value permanent

If for some reason we wanted to make some value permanent (caveat emptor) after doing experiments with the hint, we can set the table stats like this:

    ownname => 'TPCH'
  , tabname => 'RDM'
  , numrows => 2000000
  , numblks => 10000 );


---------- ---------- ------------
   2000000     10000             0

and now we see the size we decided upon after the needed experiments being used without a hint:

kcbism: islarge 1 next 0 nblks 10000 type 2, bpid 3, kcbisdbfc 0 kcbnhl 8192 kcbstt 9458 keep_nb 0 kcbnbh 461198 kcbnwp 1 kcbpstt 0, BCRM_ON 0
kcbimd: nblks 10000 kcbstt 9458 kcbnbh 46119 bpid 3 kcbisdbfc 0 is_medium 1
kcbcmt1: hit age_diff adjts last_ts nbuf nblk has_val kcbisdbfc cache_it 191 23693 23502 461198 10000 1 0 1
NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 10000 (blocks), Threshold: MTT(46119 blocks),

Our table is no longer small as 10,000 blocks is larger than STT=9458 blocks so it is a medium table but as it is smaller than the medium table threshold it will still use the buffer cache.

I hope you found this useful.


Categories: Development

Controlling the offload of specific operators

XTended Oracle SQL - Tue, 2021-11-30 17:25

One of the joys of regexp is that you can write a pattern that is painfully expensive to match and offloading these to the cell can cause significant impact on other users and overall throughput (including heartbeat issues). If you have a user who is prone to writing bad regexp expressions you as DBA can prevent regexp (or any other operator) from being offloaded to the cells.

Let’s take a very simple example using a cut down version of TPC-H Query 16 and a NOT LIKE predicate: 

SQL> explain plan for select p_brand, p_type, p_size
from part
where p_brand <> 'Brand#45'
and p_type not like 'MEDIUM POLISHED%'
and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
group by p_brand, p_type, p_size;


  |*  3 |    TABLE ACCESS STORAGE FULL| PART | 29833 |  1048K|    |   217   (2)| 00:00:01 |  1 |  8 
      3 - storage(("P_SIZE"=3 OR "P_SIZE"=9 OR "P_SIZE"=14 OR "P_SIZE"=19 
      OR "P_SIZE"=23 OR "P_SIZE"=36 OR "P_SIZE"=45 OR "P_SIZE"=49) 

Here we see all the predicates get offloaded as expected. So, for example, to stop NOT LIKE being offloaded we would need to find the operator in v$sqlfn_metadata

SQL> column descr format a18
SQL> select func_id, descr, offloadable from v$sqlfn_metadata where descr like '%LIKE%';

   FUNC_ID DESCR              OFF
---------- ------------------ ---
        26  LIKE              YES
        27  NOT LIKE          YES
        99  LIKE              NO
       120  LIKE              YES
       121  NOT LIKE          YES
       524  REGEXP_LIKE       YES
       525  NOT REGEXP_LIKE   YES
       537  REGEXP_LIKE       YES
       538  NOT REGEXP_LIKE   YES

we can ignore all but the two basic LIKE operators in this case, so to disable the offload of our LIKE predicates we use:

   FUNC_ID DESCR              OFF
---------- ------------------ ---
        26  LIKE              YES
        27  NOT LIKE          YES
        99  LIKE              NO
       120  LIKE              YES
       121  NOT LIKE          YES
       524  REGEXP_LIKE       YES
       525  NOT REGEXP_LIKE   YES
       537  REGEXP_LIKE       YES
       538  NOT REGEXP_LIKE   YES

we can ignore all but the two basic LIKE operators in this case, so to disable the offload of our LIKE predicates we use:

SQL> alter session set cell_offload_parameters="OPT_DISABLED={26,27};";

and we see this reflected in the offloadable column in v$sqlfn_metadata.

SQL> select func_id, descr, offloadable from v$sqlfn_metadata where descr like '%LIKE%';

   FUNC_ID DESCR              OFF
---------- ------------------ ---
        26  LIKE              NO
        27  NOT LIKE          NO
        99  LIKE              NO
       120  LIKE              YES
       121  NOT LIKE          YES

To re-enable them you would use:

SQL> alter session set cell_offload_parameters="OPT_DISABLED={};";

One thing to note about this param is that it doesn’t work like events (whose settings are additive), here it replaces the previous value and so every operator you want disabled has to be included in the same alter session (and the param is limited to 255 maximum characters limiting the number of operators that can be disabled). With the offload of LIKE and NOT LIKE disabled we can see the impact on the plan:

SQL> explain plan for select p_brand, p_type, p_size
from part
where p_brand <> 'Brand#45'
and p_type not like 'MEDIUM POLISHED%'
and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
group by p_brand, p_type, p_size;


 |*  3 |    TABLE ACCESS STORAGE FULL| PART | 29833 |  1048K|    |   217   (2)| 00:00:01 |  1 |  8 

     3 - storage(("P_SIZE"=3 OR "P_SIZE"=9 OR "P_SIZE"=14 OR "P_SIZE"=19 OR "P_SIZE"=23
     OR "P_SIZE"=36 OR "P_SIZE"=45 OR "P_SIZE"=49) AND "P_BRAND"<>'Brand#45')

and the NOT LIKE is no longer in the storage filter. Now lets say that you as DBA are faced with a more complex problem and want to halt all complex processing on the cells temporarily. There is a parameter that will disable everything except the simple comparison operators and NULL checks:

SQL> alter session set "_cell_offload_complex_processing"=FALSE;

Now lets see what happens:

SQL> explain plan for select p_brand, p_type, p_size
from part
where p_brand <> 'Brand#45'
and p_type not like 'MEDIUM POLISHED%'
and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
group by p_brand, p_type, p_size;


 |*  3 |    TABLE ACCESS STORAGE FULL| PART | 29833 |  1048K|    |   217   (2)| 00:00:01 |  1 |  8 

    3 - filter(("P_SIZE"=3 OR "P_SIZE"=9 OR "P_SIZE"=14 OR "P_SIZE"=19 OR "P_SIZE"=23
    OR "P_SIZE"=36 OR "P_SIZE"=45 OR "P_SIZE"=49) AND "P_BRAND"<>'Brand#45'

Well we got no storage predicates at all and we didn’t expect that because we had one simple predicate namely p_brand != 'Brand#45' and the IN predicate had been rewritten to a series of OR’ed comparisons so what happened? This parameter only permits simple predicates that are linked by AND’s and can be attached directly to one column. Disjuncts are not pushable so they are normally evaluated by an eva tree or by pcode neither of which are sent to the cell with this parameter set to FALSE. So why wasn’t our one simple predicate offloaded. Well, note where it is in the explain plan. It comes after the rewritten the IN and since the predicates are sorted by the optimizer on effectiveness we stop looking as soon as we see one that can’t be offloaded. Let’s remove the IN and see what happens:

SQL> explain plan for select  p_brand, p_type, p_size
from part
where p_brand <> 'Brand#45'
and p_type not like 'MEDIUM POLISHED%';

|*  2 |   TABLE ACCESS STORAGE FULL| PART |   190K|  6686K|   217   (2)| 00:00:01 | 1 | 8 |

    2 - storage("P_BRAND"<>'Brand#45')
       filter("P_BRAND"<>'Brand#45' AND "P_TYPE" NOT LIKE 'MEDIUM POLISHED%')

as expected the simple predicate is now offloaded. If you look at v$sqlfn_metadata you’ll see this param is reflected in the offloadable column:

SQL> select func_id, descr, offloadable from v$sqlfn_metadata where descr like '%LIKE%';

   FUNC_ID DESCR              OFF
---------- ------------------ ---
        26  LIKE              NO
        27  NOT LIKE          NO
        99  LIKE              NO
       120  LIKE              NO
       121  NOT LIKE          NO
       524  REGEXP_LIKE       NO
       525  NOT REGEXP_LIKE   NO
       537  REGEXP_LIKE       NO
       538  NOT REGEXP_LIKE   NO

I hope you never need any of this in real life but it’s good to have it in the toolbag.

Categories: Development

Alter Table Shrink Space and SmartScan

XTended Oracle SQL - Tue, 2021-11-30 17:10

There are three main tools available for cleaning up a segment (Alter Table Shrink, Alter Table Move, and export/import), but one of them isn’t as helpful as you might have thought.  

Consider the following sequence of events where we update the 256th column to cause widespread fragmentation:

SQL> update t set c256 = 'abcdefghijklmnopqrstuvwxyz'; 
2000000 rows updated.
SQL> commit;
Commit complete.

SQL> analyze table t compute statistics;
Table analyzed.

SQL> select chain_cnt from dba_tables where table_name = 'T' and owner = 'FUSION';

SQL> select sum(blocks) from user_segments where segment_name = 'T';

SQL> alter table t enable row movement;
Table altered.
SQL> alter table t shrink space cascade;
Table altered.
SQL> analyze table t compute statistics;
Table analyzed.

SQL> select chain_cnt from dba_tables where table_name = 'T' and owner = 'FUSION';


1 row selected.

Note: ‘chain_cnt” does not count chained rows, rather it counts rows whose row pieces are chained across more than one block.  A Row that is in three pieces but all three pieces are in the same block has a zero chain_cnt.

In this particular artificial scenario Shrink has not gained us much reduction in space used, and more importantly it hasn’t reduced the kind of fragmentation that affects SmartScan performance.

This is because Shrink works in two phases. In Phase 1, the segment is scanned down from the end of the segment to the beginning. Rows with their head piece in the currently scanned block are moved together with all their row pieces. The segment is scanned from beginning upwards looking for space for the entire row. When it is unable to move any more entire rows, Phase 2 starts scanning down again from the end of the segment trying to move individual row pieces to blocks with space. This meant that while Phase 1 could potentially reduce chaining for relocated rows, Phase 2 was very unlikely to reduce the chain count and could in fact increase the chain_cnt. The moral of this is that Shrink really is for freeing up blocks close to the High Water Mark and not for cleaning up fragmented rows.

Now let’s try Alter Table move with the same segment:

SQL> alter table t move; 

Table altered.

SQL> analyze table t compute statistics;
Table analyzed.

SQL> select chain_cnt from dba_tables where table_name = 'T' and owner = 'FUSION';
1 row selected.

SQL> select sum(blocks) from user_segments where segment_name = 'T';

1 row selected.

OK, that did what we hoped: more space has been reclaimed but more importantly for SmartScan, the number of fragmented rows has been reduced considerably.

With the fix for 19433200, the mechanics of Shrink have been reworked and it is now better at reducing the chain_cnt. However, even with the improvements made, when faced with heavily fragmented rows, Alter Table Move or export/import are likely to provide significantly better table scan performance with SmartScan.

Roger MacNicol

Oracle Data Storage Technology 

Categories: Development

Source Control for Oracle, a Customer Success Story

Gerger Consulting - Tue, 2021-09-28 02:20

Gitora is a version control tool for the Oracle Database. It helps DBA's, developers to manage their database code and objects using Git and enables them to use modern software development workflows.

Attend the webinar by Plexxis to learn how they are using Gitora to improve their software development process.

Click here to register.

Categories: Development

Where does the commit or rollback happen in PL/SQL code?

XTended Oracle SQL - Sat, 2021-09-11 17:01

One of the easiest ways is to use diagnostic events:

alter session set events 'sql_trace {callstack: fname xctend} errorstack(1)';
Image Image
Categories: Development

Another interesting troubleshooting case

XTended Oracle SQL - Fri, 2021-05-28 16:58

Got an interesting question today in RuOUG:

Some very simple PL/SQL procedures usually are completed within ~50ms, but sometimes sporadically longer than a second. For example, the easiest one from these procedures:

create or replace PROCEDURE XXXX (
  P_ORG_NUM IN number,
  p_result OUT varchar2, 
  p_seq OUT number 
) AS 
  p_seq := P_ORG_NUM; p_result:='';

sql_trace shows that it was executed for 1.001sec and all the time was “ON CPU”:

Trace file /opt/oracle/diag/rdbms/rdb4/rdb/trace/rdb_ora_7100.trc
Oracle Database 19c Enterprise Edition Release - Production
Build label:    RDBMS_19.
ORACLE_HOME:    /opt/oracle/product/19
System name:	SunOS
Node name:
Release:	5.11
Version:	11.3
Machine:	sun4v
Oracle process number: 137
Unix process pid: 7100, image:

*** 2021-05-26T15:38:31.321550+03:00
*** SESSION ID:(68.47940) 2021-05-26T15:38:31.321609+03:00
*** CLIENT ID:() 2021-05-26T15:38:31.321633+03:00
*** SERVICE NAME:(SYS$USERS) 2021-05-26T15:38:31.321656+03:00
*** MODULE NAME:(JDBC Thin Client) 2021-05-26T15:38:31.321679+03:00
*** ACTION NAME:() 2021-05-26T15:38:31.321703+03:00
*** CLIENT DRIVER:(jdbcthin : 2021-05-26T15:38:31.321728+03:00

PARSING IN CURSOR #18446744071368904384 len=53 dep=0 uid=51 oct=47 lid=51 tim=190436799743741 hv=804128640 ad='12345678' sqlid='aabbaabbaa123'
BEGIN AAAA.XXXX(:1 ,:2 ,:3 ); END;
PARSE #18446744071368904384:c=28,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=190436799743741
BINDS #18446744071368904384:

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=873 siz=24 off=0
  kxsbbbfp=ffffffff747c6c98  bln=22  avl=02  flg=05
  oacdty=01 mxl=32767(32767) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1400010 frm=01 csi=873 siz=32767 off=0
  kxsbbbfp=ffffffff747b8020  bln=32767  avl=00  flg=05
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1400000 frm=01 csi=873 siz=24 off=0
  kxsbbbfp=ffffffff747c6c68  bln=22  avl=00  flg=05

*** 2021-05-26T15:38:33.172899+03:00
WAIT #18446744071368904384: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=190436800744970
EXEC #18446744071368904384:c=1104,e=1001110,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=190436800745125
XCTEND rlbk=0, rd_only=1, tim=190436800745192

Originally I asked for dtrace/flamegraph and got just this:


           value  ------------- Distribution ------------- count    
       268435456 |                                         0        
       536870912 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 1        
      1073741824 |                                         0      

But truss showed much more interesting details:

 0.000037    write(11, "   B i n d # 2\n", 8)           = 8
 0.000045    write(12, " 2 0 m 7 } M X 8\n", 9)         = 9
 0.000022    lseek(11, 0, SEEK_CUR)                             = 4673
 0.000038    write(11, "     o a c d t y = 0 2  ".., 104)       = 104
 0.000040    write(12, " 2 0 m N } 5 Y e 1\n", 10)              = 10
 0.000020    lseek(11, 0, SEEK_CUR)                             = 4777
 0.000037    write(11, "     k x s b b b f p = f".., 52)        = 52
 0.000038    write(12, " 2 0 m 7 } j Y q\n", 9)         = 9
 0.000028    pathconf("/tmp/", _PC_NAME_MAX)                    = 255
 0.000029    open("/tmp/.SHMLPESLD_rdb4_26_000000047334dc80", O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0666) Err#17 EEXIST
 1.000029    nanosleep(0xFFFFFFFF7FFF5FF0, 0xFFFFFFFF7FFF5FE0) = 0
 0.000038    open("/tmp/.SHMLPESLD_rdb4_26_000000047334dc80", O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0666) = 13
 0.000023    close(13)                                  = 0
 0.000026    open("/tmp/.SHMDPESLD_rdb4_26_000000047334dc80", O_RDONLY|O_NOFOLLOW|O_CLOEXEC) = 13
 0.000028    unlink("/tmp/.SHMLPESLD_rdb4_26_000000047334dc80") = 0
 0.000052    mmap(0x00000000, 528, PROT_READ|PROT_EXEC, MAP_PRIVATE, 13, 0) = 0xFFFFFFFF74700000
 0.000020    close(13)                                  = 0
 0.000023    lseek(11, 0, SEEK_CUR)                             = 4829
 0.000044    write(11, "\n * * *   2 0 2 1 - 0 5".., 38)        = 38
 0.000040    write(12, " - T + 7 } d b k t . c *".., 16)        = 16
 0.000020    lseek(11, 0, SEEK_CUR)                             = 4867
 0.000037    write(11, " W A I T   # 1 8 4 4 6 7".., 130)       = 130
 0.000037    write(12, " 8 4 j U 1 y 5 $ o 7 2 2".., 18)        = 18
 0.000020    lseek(11, 0, SEEK_CUR)                             = 4997
 0.000037    write(11, " E X E C   # 1 8 4 4 6 7".., 105)       = 105
 0.000037    write(12, " 8 0 j d 0 y 5 $ k x s t".., 20)        = 20
 0.000022    lseek(11, 0, SEEK_CUR)                             = 5102

As you can see it calls nanosleep in the line #11 and sleeps 1 sec, but more interesting a line before and a few after nanosleep: oracle tries to open (and fails “err17”) a file "/tmp/.SHMLPESLD_rdb4_XXX” , which means that it tries to open a pl/sql procedure compiled in native mode (pesld – Plsql code Execution Shared object manager native code Loader). I’ve asked to check compilation parameters of the procedure, and it was really compiled in native mode:


So obvious workaround to recompile those procedures in the INTERPRETED mode fixed the issue.

UPDATE: Forgot to show what exactly is going on here: Finally I got normal stack:


           value  ------------- Distribution ------------- count    
       268435456 |                                         0        
       536870912 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 3        
      1073741824 |                                         0    

As you can see pesld02_Map_Object is trying to load (map) a user-defined function (native compilation) and calls shm_open which calls __pos4obj_lock and if we look at it, we can find why and when it calls sleep(which calls nanosleep internally):

__pos4obj_lock(const char *name, const char *ltype)
 char *dfile;
 int fd;
 int limit = 64;

 if ((dfile = __pos4obj_name(name, ltype)) == NULL) {
  return (-1);

 while (limit-- > 0) {
  if ((fd = __open_nc(dfile, O_RDWR | O_CREAT | O_EXCL, 0666))
      < 0) {
   if (errno != EEXIST)
   (void) sleep(1);

  (void) __close_nc(fd);
  return (1);

 return (-1);
Categories: Development

SQL*Plus tips #9: Reading traces and incident files

XTended Oracle SQL - Thu, 2021-05-27 19:57

@tracefile_read_last_by_mask filemask [regexp] [ignore_regexp]
– finds last trace by filemask and filters rows by regexp and filters out rows by ignore_regexp:

@ tracefile_by_mask.sql [mask] – finds and shows last 10 trace files by mask

@tracefile_current – shows a file name of the your session’s trace file
@tracefile_read_current – reads a trace file of the current session

@tracefile_last [n] – shows last N trace files (by default 10)

@tracefile_read filename – reads a specified trace file

@tracefile_read_full.sql <filename> – reads a specified trace file and shows it with additional columns: component_name, operation_name, file_name, function_name

@tracefile_spool <tracefilename> <dest_file> – saves tracefilename to dest_file on local machine:

Incidents, problems and incident files

@diag/problems_last.sql [N] – shows last N problems, N is optional, by default shows last 10 problems

@diag/incidents_last.sql [N] – shows last N incidents (by default 10)

@diag/incidents_by_problem.sql <problem_id> [N] – shows last N incidents by problem_id

@diag/adr_home_create.sql – creates a directory object for reading incident files:

@diag/incident_files_by_mask.sql filemask – shows incident file names by file mask

SQL> @diag/incident_files_by_mask.sql %incident%387.%

----------- ---------- ---------- ------ ------------------------------------------------------------
      26387          1          1      3 <ADR_HOME>/incident/incdir_26387/ORA19_ora_2942_i26387.trc#0

1 row selected.

@diag/incident_files_by_id.sql incident_id – shows incident files by incident_id

@diag/incident_files_spool_by_id.sql incident_id – saves an incident file on local machine:

Categories: Development


Subscribe to Oracle FAQ aggregator - Development