Oracle OCI Resize root volume Tips
The Oracle Cloud Infrastructure Block Volume service lets you expand the size of block volumes and boot volumes. You have several options to increase the size of your volumes:
Expand an existing volume in place with online resizing. See Online Resizing of Block Volumes Using the Console for the steps to do this.
Restore from a volume backup to a larger volume. See Restoring a Backup to a New Volume and Restoring a Boot Volume.
Clone an existing volume to a new, larger volume. See Cloning a Volume and Cloning a Boot Volume.
Expand an existing volume in place with offline resizing. See Offline Resizing of Block Volumes Using the Console for the steps to do this.
Oracle OCW2022 RWP Performance Up Tips
Study case
• Better cardinality estimates
• Better execution plans
• More access paths available
• Ability for the optimizer to perform many transformations and optimizations (join elimination,materialized view rewrites, In-Memory Aggregation transformation, and many more)
• Partition pruning
• Exploit other technologies for optimal performance
• Parallel Execution
• Materialized Views
• Compression
• Database In-Memory
ADW
Autonomous Database can help address some problems:
• Augments bad/stale stats
• Dynamic statistics may correct some cardinality estimates (do not solely rely on dynamic statistics)
• Stats quality (every column has a histogram)
• Use of features like IMA, Columnar Cache in Flash is available
Where Autonomous Database cannot help:
• Suboptimal schema design
• Wrong data types on join keys
• Lack of constraints
• Sub-optimal partitioning strategy
Oracle 23c New features - SQL_HISTORY_ENABLED Tips
Oracle Database 23c introduces MAX_COLUMNS parameter
Use SQL_HISTORY_ENABLED to enable or disable SQL history monitoring.
To enable SQL history monitoring, set this parameter to ON. This feature monitors user-issued SQL statements in each user session on a best effort, depending on memory capacity. DDL, DML, and query statements are monitored; SQL statements issued in the background and recursive SQL statements are excluded. The monitored information is exposed by the V$SQL_HISTORY dynamic performance view.
To disable SQL history monitoring, set this parameter to OFF.
Oracle 23c New features - smco process Tips
Improved System Monitor (SMON) Process Scalability
Queries can require large amounts of temporary space and some temporary space operations run in critical background processes, like the System Monitor (SMON) process. SMON is responsible for cleaning up temporary segments that are no longer in use. SMON checks regularly to see whether it is needed, and other processes can call SMON. Temporary space management can affect SMON’s scalability for other critical actions. This new enhancement instead uses the Space Management Coordinator (SMCO) process so that the responsibility of managing temporary space is offloaded from SMON, thereby improving its scalability.
This feature improves the overall scalability of the SMON process, particularly in a multitenant Oracle RAC cluster.
Oracle 23c New features - MAX_COLUMNS Tips
Oracle Database 23c introduces MAX_COLUMNS parameter
Increase Column Limit The maximum number of columns allowed in a database table or view has been increased to 4096. This feature allows you to build applications that can store attributes in a single table with more than the previous 1000-column limit. Some applications, such as Machine Learning and streaming IoT application workloads, may require the use of de-normalized tables with more than 1000 columns.
Oracle 23c New features - Data Quality Operators in Oracle Database Tips
Oracle Database 23c introduces the following two new string matching operators based on approximate or “fuzzy” string matching.
PHONIC_ENCODE
converts words or phrases into language-specific codes based on pronunciation.FUZZY_MATCH
, which is language-neutral, gauges the textual similarity between two strings.The new phonic encoding and fuzzy matching methods enable more sophisticated matching algorithms to be run directly on data in the database rather than only in external applications, providing improved matching performance and efficiency, for example in data de-duplication, linking or enhancement
Oracle ADB export data to Other Database Tips
Oracle 23c export table data to csv via sql Tips
Oracle 23c New features - Real-Time SQL Monitoring Enhancements Tips
Real-time SQL Monitoring works independently and concurrently across multiple PDB containers in an efficient manner. SQL statements, PL/SQL procedures and functions, and DBOPs (Database Operations) are monitored at PDB and CDB levels. You can efficiently query SQL Monitor reports across ad-hoc time ranges, DBIDs (internal database identifiers), and CON_DBIDs (CDB identifiers). This data is also accessible through SQL History Reporting.
Additionally, SQL Monitoring data can be exported along with the Automatic Workload Repository (AWR) and imported into another database or container for longer term storage and analysis.
Real-time SQL Monitoring is now supported per-PDB and CDB levels efficiently by default. As a PDBA persona, you can get a more accurate view of the monitored SQL for your application.
SQL Monitoring data can be transported through the AWR framework to a different container or database for longer term storage and offline analysis.
Oracle 23c New features - Automatic Parallel Direct Path Load Using SQL*Loader Tips
The SQLLoader client can automatically start a parallel direct path load for data without dividing the data into separate files and starting multiple SQLLoader clients. This feature prevents fragmentation into many small data extents. The data doesn’t need to be resident on the database server. Cloud users can employ this feature to load data in parallel without having to move data on to the cloud system if there is sufficient network bandwidth.
SQL*Loader can load data faster and easier into Oracle Database with automatic parallelism and more efficient data storage.
Oracle 23c New features - Evolution of Oracle Homes Tips
Oracle base home, Oracle base config, and orabasetab.
Oracle 23c New features - Control PDB Open Order Tips
Control PDB Open Order
To Set the Priority of a PDB
Use the ALTER PLUGGABLE DATABASE <databasename> Priority <value> set the priority.