
The /*+Go-Faster*/ Oracle Blog
17 FOLLOWERS
This blog contains things about PeopleSoft, mostly performance-related, that DBAs might find interesting. The Non-PeopleSoft Oracle stuff is at blog.go-faster.co.uk.
The /*+Go-Faster*/ Oracle Blog
2M ago
I frequently use SQL queries to extract performance data from Active Session History (ASH). I often then want to graph that data. Excel is a very effective charting tool, it is nearly always available, the charts can be copied into other applications, and it can be useful to keep historical data in spreadsheets.
SQL Developer is very effective at extracting the results of a SQL query into various formats, including as an Excel workbook file.
Tip: Make Excel the Default Export Format
From the main menu, go to Tools ? Preferences
In the prefere ..read more
The /*+Go-Faster*/ Oracle Blog
5M ago
Introduction
It is not uncommon for businesses to have policies to retain audit data for several years, but then it ought to follow that that data should be purged at the end of the retention period. A regular purge process should be put in place, otherwise, audit data will accumulate. However, it is all too common for the purge to be deferred, usually indefinitely, until it becomes a problem.
I have been working on a system that has built up 12 years of audit data. It was starting to consume significant space in the SYSAUX tablespace, and queries on the standard audit data h ..read more
The /*+Go-Faster*/ Oracle Blog
10M ago
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
The /*+Go-Faster*/ Oracle Blog
1y ago
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
The /*+Go-Faster*/ Oracle Blog
1y ago
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
The /*+Go-Faster*/ Oracle Blog
1y ago
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
The /*+Go-Faster*/ Oracle Blog
1y ago
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
The /*+Go-Faster*/ Oracle Blog
1y ago
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
The /*+Go-Faster*/ Oracle Blog
1y ago
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
The /*+Go-Faster*/ Oracle Blog
1y ago
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