SQL Developer Tip: Exporting SQL Results to Excel by Default
The /*+Go-Faster*/ Oracle Blog
by David Kurtz
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
Visit website
Purging Standard and Unified Audit Data
The /*+Go-Faster*/ Oracle Blog
by David Kurtz
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
Visit website
Configuring Shared Global Area (SGA) in a Multi-tenant Database
The /*+Go-Faster*/ Oracle Blog
by David Kurtz
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
Visit website
Table Clusters: 6. Testing the Cluster & Conclusion (TL;DR)
The /*+Go-Faster*/ Oracle Blog
by David Kurtz
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
Visit website
Table Clusters: 5. Using the Cluster Key Index instead of the Primary/Unique Key Index
The /*+Go-Faster*/ Oracle Blog
by David Kurtz
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
Visit website
Table Clusters: 4. Checking the Cluster Key
The /*+Go-Faster*/ Oracle Blog
by David Kurtz
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
Visit website
Table Clusters: 3. Populating the Cluster with DBMS_PARALLEL_EXECUTE
The /*+Go-Faster*/ Oracle Blog
by David Kurtz
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
Visit website
Table Clusters: 2. Cluster & Cluster Key Design Considerations
The /*+Go-Faster*/ Oracle Blog
by David Kurtz
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
Visit website
Table Clusters: 1. An Alternative to Partitioning? - Introduction & Ancient History
The /*+Go-Faster*/ Oracle Blog
by David Kurtz
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
Visit website
Just because the execution plan says INMEMORY, doesn't mean it is using In-Memory
The /*+Go-Faster*/ Oracle Blog
by David Kurtz
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
Visit website

Follow The /*+Go-Faster*/ Oracle Blog on FeedSpot

Continue with Google
Continue with Apple
OR