Lin Hong's TECH Blog!

【学习】 2025年阅读清单

Oracle 23c awr_cdb_wr_control Tips

2023-01-19

Oracle

Oracle 23c Tips

Oracle 23c awr_cdb_wr_control Tips

Read All
MySQL dbdeployer tool Tips

2023-01-13

MySQL

MySQL Tips

MySQL dbdeployer tool Tips

DBdeployer is a tool that deploys MySQL database servers easily. This is a port of MySQL-Sandbox, originally written in Perl, and re-designed from the ground up in Go.

Read All
MySQL Linux Slow Io Simulate Tips

2023-01-13

MySQL

MySQL Tips

MySQL Linux Slow Io Simulate Tips

Read All

MySQL Functional Index Tips

2023-01-13

MySQL

MySQL Tips

MySQL Functional Index Tips

Database indexes are used to help query performance. Database indexes typically contain information about data in a specific column of the database table. With the introduction of functional indexes in MySQL 8.0.13, we can now create indexes based on the result of an expression or function.

Functional Indexes in MySQL

The Rules Functional indexes can increase query performance without having to rewrite the query to address any bottlenecks. However, there are some rules that we need to follow.

Expressions MUST be contained in parentheses to differentiate them from columns.
    INDEX((col1 + col2)) vs INDEX( col1, col2)
    We can create an index that has functional and non-functional definitions.
        INDEX((col1 + col2), col1)
Functional index definitions cannot contain only column names.
    INDEX ((col1), (col2)) will throw an error.
Functional index definitions are not allowed in foreign key columns.
The index will only be used when a query uses the same expression.
    select count(*) from test_data where col1 - col2 = 0 will NOT use the index we created.
        We would need to create a new index using the expression (col1-col2).

Read All

Oracle OCI Resize root volume Tips

2023-01-03

Oracle

Oracle OCI Tips
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.
Read All

Oracle OCW2022 RWP Performance Up Tips

2022-12-21

Oracle

Oracle Performance Tips

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

Read All

Oracle 23c New features - SQL_HISTORY_ENABLED Tips

2022-12-21

Oracle

Oracle 23c Features Tips

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.

Read All
Oracle 23c New features - smco process Tips

2022-12-21

Oracle

Oracle 23c Features Tips

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.

Read All
Oracle 23c New features - MAX_COLUMNS Tips

2022-12-21

Oracle

Oracle 23c Features Tips

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.

Read All
Oracle 23c New features - Data Quality Operators in Oracle Database Tips

2022-12-21

Oracle

Oracle 23c Features Tips
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
Read All
Oracle ADB export data to Other Database Tips

2022-12-20

Oracle

Oracle 23c Tips

Oracle ADB export data to Other Database Tips

Read All
Oracle 23c export table data to csv via sql Tips

2022-12-20

Oracle

Oracle 23c Tips

Oracle 23c export table data to csv via sql Tips

Read All

5/37