I have been working on a PeopleSoft Financials application that we have converted from a stand-alone database to be the only pluggable database (PDB) in an Oracle 19c container database (CDB).  We have been getting ORA-4031 (unable to allocate shared memory) errors in the PeopleSoft application.   It has taken a while to solve and test, and I have to acknowledge quite a lot of advice from my friends.   If you are wondering why you should be involved with your local Oracle user group, and regularly attend their meetings, this is an example: So you can ask people who hav ..read more

This post is the last part of a series that discusses table clustering in Oracle. Introduction and Ancient History Cluster & Cluster Key Design Considerations Populating the Cluster with DBMS_PARALLEL_EXECUTE Checking the Cluster Key Using the Cluster Key Index instead of the Primary/Unique Key Index Testing the Cluster & Conclusion (TL;DR) Testing We did get improved performance with the clustered tables.  More significantly, we encountered less inter-process contention, and so were able to run more concurrent processes, and the overall elapsed time of all the process ..read more

This post is part of a series that discusses table clustering in Oracle. Introduction and Ancient History Cluster & Cluster Key Design Considerations Populating the Cluster with DBMS_PARALLEL_EXECUTE Checking the Cluster Key Using the Cluster Key Index instead of the Primary/Unique Key Index Testing the Cluster & Conclusion (TL;DR) In my test case, the cluster key index is made up of the first 7 columns of the unique key index.  One side-effect of this similarity of the keys is that the optimizer may choose to use the cluster key index where previously it used the uniq ..read more

This post is part of a series that discusses table clustering in Oracle. Introduction and Ancient History Cluster & Cluster Key Design Considerations Populating the Cluster with DBMS_PARALLEL_EXECUTE Checking the Cluster Key Using the Cluster Key Index instead of the Primary/Unique Key Index Testing the Cluster & Conclusion (TL;DR) This query calculates the frequency of each number of distinct blocks per cluster key.  It uses DBMS_ROWID to get the block number from the ROWID.  The query counts the number of distinct blocks per cluster key, and the number of times that ..read more

This post is part of a series that discusses table clustering in Oracle. Introduction and Ancient History Cluster & Cluster Key Design Considerations Populating the Cluster with DBMS_PARALLEL_EXECUTE Checking the Cluster Key Using the Cluster Key Index instead of the Primary/Unique Key Index Testing the Cluster & Conclusion (TL;DR) The result tables being clustered are also large, containing hundreds of millions of rows.  Normally, when I have to rebuild these as non-clustered tables, I would do so in direct-path mode and with both parallel insert and parallel query. However, t ..read more

This post is part of a series that discusses table clustering in Oracle. Introduction and Ancient History Cluster & Cluster Key Design Considerations Populating the Cluster with DBMS_PARALLEL_EXECUTE Checking the Cluster Key Using the Cluster Key Index instead of the Primary/Unique Key Index Testing the Cluster & Conclusion (TL;DR) At the beginning of each PeopleSoft payroll calculation process, all the previously calculated result data that is about to be recalculated by that process is deleted; one delete statement for each result table.  The new result data is inserted as e ..read more

This post is the first part of a series that discusses table clustering in Oracle. Links will appear as sections are posted. Introduction and Ancient History Cluster & Cluster Key Design Considerations Populating the Cluster with DBMS_PARALLEL_EXECUTE Checking the Cluster Key Using the Cluster Key Index instead of the Primary/Unique Key Index Testing the Cluster & Conclusion (TL;DR) Introduction Table clustering and table partitioning are very different technologies.  However, they both create a relationship between the logical value of the data and its physical location.  ..read more

Parallel Query If you are using RAC, and you have in-memory objects populated across nodes (i.e. distribution by ROWID range) or you have objects populated in only 1 node (i.e. distribution by partition or sub-partition) then you need to use parallel query to access data populated on a node to which the query is not connected.  There is no cache fusion with Database In-Memory.  Oracle does not ship In-Memory Compression Units (IMCUs) across the RAC interconnect. Similarly, if you have set PARALLEL_FORCE_LOCAL=TRUE the parallel query will not be able to access the remote nodes. In ..read more

I have a requirement to run several concurrent jobs, and then, only when they have all finished, I want to run another job.  Rather than create several stand-alone jobs, I can create a chain of sub-jobs. Each step in the chain maps to a program that can invoke either a PL/SQL procedure, a PL/SQL block, a SQL script, or an external program. Each step has a rule that includes a condition that determines when it starts.  Thus, it can be after one or more other steps have been completed or succeeded. A priority can be specified on the program that will determine the order in which progr ..read more

The maximum number of database scheduler jobs that can run concurrently on each Oracle instance is primarily controlled by the parameter JOB_QUEUE_PROCESSES. The default value is the lesser of 20*CPU_COUNT or SESSIONS/4. I think 20 jobs per CPU is usually far too high because gives the scheduler the potential to swamp the CPU. Therefore, I usually reduce this parameter, often setting it to the same value as CPU_COUNT, so if you have 10 vCPUs per instance, you can run 10 concurrent jobs on each instance.   However, this is a database-wide parameter.  What if you want to restric ..read more