6 Query Related Problems to Avoid in Oracle Database

Insufficient database performance is a fly in every DBA’s ointment. To make matters worse, finding the root cause of performance-related problems can get really difficult.

One of the best methods to get rid of performance issues is through performance tuning. However, you may not always be sure where to begin with the optimization process.

At times, you may find your database performance lacking even after you have eliminated certain factors that adversely impact hardware and network performance, such as memory and disk space. In such cases, you need to examine your queries.

Watch Out for These 6 Query Related Mistakes!

Unoptimized queries can lead to multiple performance related difficulties for your systems. Here, we’ll analyze six common query mistakes that typically form the cause of database performance deterioration.

Prepare yourself for a good amount of tuning, if you detect any of these problems in your queries –

Query 1: Like Queries that Contain Leading Wildcards

SQL may not be able to utilize indexes the way it should due to leading wildcards. As a result, a full table scan will take place regardless of any indexed fields present in the table. Scanning each and every row in a table every single time means it’ll take more time to fetch query results. Therefore, it is advisable to remove the leading wildcard to boost efficiency.

Query 2: WHERE or GROUP BY Clauses that Have Non-Indexed Columns

When a column is indexed, it returns query results more quickly. This is because there is no need to conduct a complete table scan due to the presence of an index.

Arranging records also becomes much simpler with indexing at the time of return and ensures that the records can be uniquely identified.

Query 3: The Presence of the ‘OR’ Operator in Like Statements

The ‘OR’ operator is used while comparing fields or columns in a table. However, doing it too often in a WHERE clause is another way to invite unwanted full table scans.

If you want to improve the speed of your Oracle SQL queries, try using a union clause instead. They succeed in making SQL queries run faster, particularly in the presence of separate indexes at each end of the query. Basically, the union clause combines the results of two quick, indexed queries.

Query 4: Unavoidable Wildcard Searches

If you find that a wildcard search is unavoidable but would rather avoid the performance hit, see if you can conduct a SQL full-text search. This type of search is considerably faster than performing searches using wildcard characters. Additionally, you obtain more relevant results when the searches take place in huge databases.

Query 5: Inefficient Database Schema

The efforts you make to improve SQL query performance will only take you so far, unless you also work on optimizing your database schema. Consider the following tips to do so:

  • Normalizing Tables – Duplicate data slows down performance. So, make sure that a fact is represented just once in the database. For instance, use “cust_name” just one time if you’re referencing its data in multiple tables, use another column such as “cust_ID” for subsequent references.
  • Using Suitable Data Types – Given below are a few important points to keep in mind regarding data types:
  • Try to create tables with data types of shorter sizes, such as a “user_id” field with “TINYINT” data type – in case there are fewer than a hundred users.
  • If one or more fields will need a date value, consider using a “date_time” data type for those fields. This way, you won’t have to convert the records to date format afterwards.
  • SQL functions more favorably with integer values in comparison with text data types, including varchar.
  • Don’t Use Null Values – The presence of multiple null values in a column negatively affects your query results. Instead, it is better to set a default value for fields in which a mandatory value is not needed.
  • Try to Use Fewer Columns – Tables that contain over a hundred columns are considered too wide because they take up a considerable amount of processing power and resources. See whether you can split a wide table into smaller tables that are also logical – unless a wide table is unavoidable.
  • Optimize Joins – SQL queries that contain an excessive number of joins as well as joins that include several tables have a negative effect on performance. Aim for queries with at most twelve joins.
Query 6: Un-Cached SQL Statements

Caching SQL queries proves advantageous for websites and applications that perform a lot of select queries. SQL query caching increases performance speed when read operations are performed. This is because it caches the select statement along with the resulting data set. Also, it retrieves the information from memory rather than the disk in case the statement is carried out more than once.

Final Thoughts

Performance tuning is vital for keeping your database available and preserving user satisfaction. Unfortunately, it isn’t always immediately clear where you need to perform tuning. If you have ruled out the typical hardware and network sources for performance issues, start examining your queries.

As a DBA, you are likely to face one or multiple of the problem queries mentioned above. Now that you are aware of the possible sources of the problem, keep an eye out for these and work on your performance improvement efforts. You can do this by correcting these common query problem areas so you can make the most of Oracle SQL database query optimization.

Optimization in SQL: The Importance of Automated Tuning

Structured data is going to last, despite the growing prevalence and importance of unstructured data. In fact, it is foreseen as one of the biggest time-consumers for Database Administrators, at least for the discernable future. 

Structured data also requires to be accessed, and SQL is the chief access method, which means professionals will rely on this language for ad hoc queries and app information manipulation. Additionally, since over eighty percent of all new structured data in an institution consists of transactional information, we know that client-centric, as well as other business data of vital importance, relies on a great performance, which can be maintained through optimization in SQL and tuning.

How We Know Structured Data Necessitates Optimization in Oracle Database and SQL

So, structured data requires SQL tuning on a regular basis. But is this actually considered by your organization? Although you’re not alone, research suggests that 

  • Over seventy percent of database professionals say that optimization in Oracle database and SQL claims the greatest time commitment 
  • Hardly five percent of DBAs admit that tuning and optimization in SQL is not a part of their routine.

Apparently, SQL tuning forms one of the main tasks of DBAs and is being performed quite regularly. However, according to one of the findings, 77% of SQL tuning and optimization is performed manually

Shortcomings of Manual Tuning and Optimization in SQL

Relational databases are the predominant location for structured data. This might make you wonder why SQL tuning is so essential, especially since a majority of the popularly utilized relational database vendors provide internal, inbuilt mechanisms for optimization in SQL that manage statements internally by rewriting them where necessary, prior to execution. 

Still, there are certain deficiencies of such internal methods for optimization in SQL, including the following –

  • failure to identify a decent access plan for a SQL query
  • Scarcity of latest statistics 
  • Inaccurate cost estimation.

These create problems in precise optimization of SQL and need automated tools or professionals to get involved. Therefore, SQL tuning becomes necessary for DBAs and database developers.

What Automated SQL Tuning Tools Help Database Professionals Achieve

Yes, performance tuning for SQL can and should be automated. That’s because not only does it save the time of everyone involved in the process, but it reduces the chances of error to a great extent.

At present, there are some amazing tools by Tosska that bring multiple advantages of automation, powered by the latest technology. Here are just a few things they can do – 

  • Save the developer’s time to rewrite a problematic SQL statement to make it faster
  • Turn SQL tuning into a one-button affair, allow the pros to redirect their efforts towards more productive aspects of the organization
  • Provide on-the-job SQL training for in-house database developers
  • Offer the most optimum solution while eliminating trial and error

The amount of time saved by DBAs with these tools is perhaps one of the most significant improvements, a direct result of the quicker generation of optimized SQL statements. Of course, the actual time savings experienced by every organization may be different, because it relies on several factors specific to each environment, database caseloads, and unique objectives for optimization in SQL.

MySQL SQL Performance Tuning: Confusing MySQL Server Variables

MySQL Server contains a wide variety of variables that can be modified according to different uses or to enhance performance. However, despite their detailed documentation, there is plenty of confusion regarding which of these variables are suitable only for storage engines and which ones are utilized on SQL layer, and are applicable for all storage engines as well. 

An important factor to consider during MySQL SQL performance tuning is the storage engine in use. Keeping that in mind, this blog features a list of variables that are sometimes mistaken with similar variables.

MySQL Database and SQL: List of Confusing Variables

Let’s take a look at some variables that may seem confusing to users in terms of their applications (whether they are storage engine specific or used with the SQL layer) – 

  • Read_buffer_size/read_rnd_buffer_size – These variables are used in certain tables for full table scanning and for viewing rows in a sorted manner respectively. However, they are not compatible with all storage engines. 
  • Sort_buffer_size – This buffer is applied when the user needs the result set to be sorted, and is used on the SQL Layer, so it’s applicable for all storage engines and may even be helpful for performance tuning in SQL MySQL
  • Bulk_insert_buffer_size – This variable is only applicable for MyISAM tables for optimizing inserts in a large quantity or with numerous values. It is quite helpful when the user needs to insert hundreds or thousands of values in a single insert statement, and there are several such statements.
  • Join_buffer_size – This buffer is used for specific cases, such as joins that do not include indexes. It can be utilized for all the storage engines. 
  • Max_write_lock_count – A variable that is applicable across memory and MyISAM tables, it is suitable for table locks and prevents read starvation in case of numerous table writes. 
  • Key_buffer_size – This is applied in the case of Index Blocks solely for MyISAM tables or, in rare cases, restricted to a range of 4-32MB for temporary tables.
  • Delayed_insert_limit/delayed_insert_timeout, delayed_queue_size – These are used for inserting configuration and are not exactly dependent on any storage engine, yet it lacks support from some of them, such as InnoDB. 
  • Delay_key_write – This is used to improve MySQL database performance in MyISAM tables by extending the time for index updates. However, this variable may lead to table corruption, if a crash occurs. 
  • Low_priority_updates – This provides higher priority to select queries by putting the updates on low priority. It can be enabled when LOCK TABLES are in use, which is why it is storage engine-specific. 
  • Large_pages – This variable enables the utilization of large pages if big global areas need to be allocated, and is also storage engine-specific, such as Innodb and MyISAM. However, it can be used by certain SQL level components like Query Cache in MySQL database and SQL.
  • Key_cache_age_threshold/key_cache_block_size/key_cache_division_limit Key Cache/Key Buffer – The Key_Buffer variable is only applicable to MyISAM. It is used for making changes to algorithm configuration. 
  • Ft_boolean_syntax/ft_max_word_len/ft_min_word_len/ft_query_expansion_limit/ft_stopword_file – These are search variables related to FullTEXT search. Again, these are only useful for limited storage engines for MySQL database and SQL
  • Flush/flush_time – Initially designed for MyISAM tables, this variable impacts all the tables once the query is over or for certain flush_time set intervals. 
  • Preload_buffer_size – Another buffer that is only useful for MyISAM tables in key preloading. 
  • Timed_mutexes – This variable has been designed to use on all storage engines to show the mutex status. 
  • Tmp_table_size – It is used to specify the maximum limit in size for implicit temporary tables. These are tables that are created automatically at the time of each execution. 

Conclusion

These were some variables whose application is often confused by some users. Knowing more about these may turn out to be useful from the point of view of MySQL SQL performance tuning

On a related topic, if you are in search of tools for improving MySQL database performance that don’t require expert knowledge about the database, then Tosska’s tools for database tuning can be a great fit for your organization. Our tools have been designed with cutting edge AI technology to make query tuning as easy as pointing and clicking. 

So, make sure to explore all of our tools and find the right variant for your requirements, and if you aren’t sure about anything, just get in touch with our experts and have your SQL performance tuning related queries resolved today!

Improve MySQL Database Performance by Controlling Partitions

If you have been dealing with large MySQL data for a while, you may have faced an interesting case like this at some point – one which involves a table with data merged with INSERT IN DUPLICATE KEY queries. 

Even if you haven’t faced such a situation so far, you may be able to tell that such a table will show an extremely slow performance, and the culprit most definitely is the multitude of partitions that have been made every day for this table. In this blog, we will examine if changing the number of partitions can improve MySQL database performance.   

Improve MySQL Database Performance by Paying Attention to Partitions!

Surprisingly, the statements are also affected differently from each other, and this is something that also impacts performance.

To understand this problem a bit more clearly, let us consider an example where we create a test table – 

CREATE TABLE `Partition` (

    `number` int(10) unsigned NOT NULL,

    `name`  int(10) unsigned NOT NULL,

     PRIMARY KEY (`number`),

     KEY(name)

     ) ENGINE=InnoDB

     PARTITION BY RANGE(number) (

     PARTITION p100000 VALUES LESS THAN(100001),

PARTITION p200000 VALUES LESS THAN(200001),

PARTITION p300000 VALUES LESS THAN(300001),

PARTITION p400000 VALUES LESS THAN(400001),

PARTITION p500000 VALUES LESS THAN(500001),

PARTITION p600000 VALUES LESS THAN(600001),

PARTITION p700000 VALUES LESS THAN(700001),

PARTITION p800000 VALUES LESS THAN(800001),

PARTITION p900000 VALUES LESS THAN(900001),

PARTITION p1000000 VALUES LESS THAN(1000001)

   );

In this example, we are varying the quantity of partitions between 1 and 1000. We are also loading the table with one million sequential values through bulk insert queries, where the number and name columns are set the same and there are a thousand rows in each table.

Time Taken to Load in Different Scenarios:

  • This data ends up taking around 10 seconds for loading a single partition, 11 seconds for ten partitions, 17 seconds for a hundred partitions and 24 seconds for a thousand partitions. 
  • Also, every time the number of partitions increases by a thousand, the loading speed decreases 2.5 times. 
  • Such regression is slightly unexpected, given the lack of data insertion into the partitions in every insert statement. Moreover, it worsens when the user tests an update statement with a condition – “set name=name+1”; the loss in performance is at least five times with a change in the pattern, and it is way more drastic as the user moves from a hundred to a thousand partitions than it was in the case where we only used the insert command. 
  • Finally, the difference magnifies when the user eliminates the index on column C. The UPDATE aspect of the INSERT ON DUPLICATE KEY UPDATE statement takes 23 seconds for a single partition and more than 240 seconds for a thousand partitions. This is more than ten times the original time!

This issue with partitions impacts both MyISAM and Innodb, where the update query in MyISAM without indices can take ten seconds for 1 partition and upwards of fifty seconds for 1000 partitions. 

Why is this Happening?

The dramatically increased load times may be caused by one of two suspects – either the overhead of statements creating the partitions for execution or the overhead created by executing each row. 

On testing the batch performance with varying numbers of rows in the batches, the batch performance was not found to be significantly different among batches with 100 rows and those with 10000, which is why the answer here is the per row overhead.

Additionally, this test reveals that update statements for CPU bound workload can prove to be about five times slower than insert statements. 

Final Thoughts

Users must certainly keep an eye on the number of partitions being used and think about future use while creating idle partitions. This simple step can improve MySQL database performance to a large extent, but if this is not the case with you, it might be wise to consider tuning tools for MySQL, such as Tosska SQL Tuning Expert (TSEM™) for MySQL. 

This is because load times and other such results are highly dependent on workloads and every case is different. Nevertheless, these tools will prove indispensable for improving your database’s performance.  

Analyzing the Inner Works of MySQL SQL Performance Tuning and Oracle Database

When it comes to MySQL, slow performance in large tables is one of the main sources of complaints. It is true that some users face problems as their database fails to sufficiently handle a more than a certain number of rows.

However, there also are many corporations that use MySQL for millions, even billions of rows of data and yet they successfully deliver excellent promise. So, why is there a contradiction between these two cases? The answer lies in understanding the intricacies of table designing in MySQL, and with the help of MySQL SQL performance tuning, how to make them work in your favour.    

What to Consider During MySQL SQL Performance Tuning

There are three major aspects of the database that can have an impact on databases with huge amounts of data. Let’s take a look at two of them now:

Buffers

The first thing to consider with any database management system is that you must have an estimate of the memory, even as you progress in terms of data accumulation. It is important for the memory to be sufficient because performance suffers greatly if it isn’t, so don’t be surprised if a drop in performance is greater than you anticipated because you may have lost track of the growth in data size and subsequently, the need for more memory space. This applies to the other aspects covered in this blog as well. Once data outgrows the memory, everything can be expected to slow down, and MySQL database and SQL becomes a necessity.

One way to ensure the memory remains sufficient for your data is to practice data partitioning. In this process, old data that is no longer required as often as recent data, is separated and stored in other servers. There are various other ways of ensuring sufficient space which we will talk about in another blog.

Indices

Indices, or indexes, are known by most of us to be a useful tool in improving the accessing speed of the database. An important thing to remember is that their usefulness depends a lot on exclusivity, i.e., the ability to select a number of rows that match with specific index ranges or values. Also, the nature of the workload – specifically whether it is cached or not – determines how much it will benefit from the use of an index.

This is actually overlooked by even MySQL optimizer at present and may need to be checked by other MySQL SQL performance tuning tools. Workload indices have a chance of much quicker access even if the size of the data being accessed is as large as fifty percent of the entire number of rows, as long as they are in-memory. On the other hand, for disk IO bound access, you may have greater success in fetching data through a full table scan irrespective of the number of rows you are requesting access to.

Since indices can differ from each other in many ways, they need to be used differently in order to effectively use them. For instance, you can place them either in a well-organized manner or at random spots, resulting in significant changes in their speed. Innodb also includes clustered keys which work by merging data and index access – such keys end up conserving IO that will prove invaluable for workloads that are entirely disk-bound.

In Conclusion

Designing table structures smartly involves taking into consideration all the abilities and disabilities of MySQL. This is especially important if you have to handle different kinds of databases in your organization.

The main reason why your organization has different databases in the first place is because of their different capabilities and shortcomings. So, the same design concepts won’t bring the same results in say, MS SQL or Oracle that they did in MySQL and vice versa. The same is true for their storage engines – each can have a different effect on the performance.

Once you have applied the right application architecture to plan your tables, you will be able to create applications that can easily handle huge data sets on the basis of MySQL.

Proper MySQL SQL performance tuning involves optimizations that can greatly boost the rate at which indices are accessed or scanned. There already are tools by Tosska Technologies Limited for this purpose like Tosska SQL Tuning Expert (TSEM™) for MySQL which you can download and start using today. Contact our team for further information or enquiries.

Improve Performance Tuning in SQL MySQL Through Multiple Parameters

MySQL tuning is no trivial task – it takes some work. However, Database Administrators know that there are a few parameters through which they can greatly enhance the speed and output of the database.

If you are in search of ways to improve performance tuning in sql MySQL , you will find some of the best in this blog. Each of the parameters mentioned here contain important settings that you can make changes to without much effort. Keep in mind that default values may vary according to the version of MySQL on your system. 

Performance Tuning in SQL MySQL: Main Categories

Here are the three major types of performance tuning in SQL MySQL, one of which DBAs usually focus on:

  • Hardware-based performance tuning
  • Tuning through Optimum techniques and practices 
  • Workload-based tuning

Hardware-based Performance Tuning in MySQL

Certain variables can be set according to the hardware specifications of your device. These include:

innodb_flush_log_at_trx_commit

For maximum durability, set it to “1”. If performance is your main concern, adjust this value to either “2” or “0”. However, doing so will result in lesser durability than if the value is set to “1”. 

innodb_flush_method

If you want to improve MySQL database performance by preventing double buffering, make sure this setting is at O_DIRECT.

innodb_buffer_pool_size

This size parameter is typically set within 50 to 70 percent of the overall RAM. You can proceed with tuning by checking on the buffer pool usage from time to time using a monitoring tool. 

innodb_log_file_size

The size of the file log is usually set in the 128M – 2G range. It is supposed to be sufficiently spacious to store approximately sixty minutes of logs and enable MySQL to flush processes, place checkpoints, and reorganize writes for sequential I/O. Again, refer to a tuning tool like Tosska SQL Tuning Expert (TSEM™) for MySQL® for further insight on whether or not the log file size needs to be adjusted. 

Tuning through Optimum Techniques and Practices

This category involves using the best MySQL practices for performance tuning in SQL MySQL: 

innodb_file_per_table

Keep this at “ON” in order to ensure a separate InnoDB table space for each table present in the database. 

innodb_stats_on_metadata

Don’t want database statistics to update constantly, and consequently, slow down read speeds? Ensure this setting is turned off, in that case. 

innodb_buffer_pool_instances

The recommended value for this is “8”. On the other hand, if the buffer pool size is less than 1G, then set it to “1”.

query_cache_type & query_cache_size

Disabling the query cache is considered useful in improving MySQL database performance. You can disable it by setting both query_cache_type and query_cache_size to zero.

Workload-based Performance Tuning for MySQL

This kind of performance tuning in SQL MySQL is relative; it depends on the workload, which is why additional details regarding the specific workload are needed. Thankfully, gathering such information is much more convenient, thanks to reliable MySQL graphing and tuning tools like Tosska SQL Tuning Expert (TSEM™). Tosska’s tools are designed to display an extensive range of metrics and give users insights and allocate resources accordingly. 

Experts suggest making changes to the innodb_buffer_pool_size parameter first. Consider the following metrics to decide whether this setting has to be raised or lowered – 

  • Your device’s RAM
  • Buffer pool size
  • The number of free pages available

Once this is done, you can improve MySQL database performance be observing the InnoDB Log File usage metrics; as mentioned already, the log file settings are generally adjusted in order to store around an hour of log data. If the data written exceeds the originally set capacity, then this setting has to be increased and MySQL rebooted. The query “Show engine innodb status” is useful in assessing what size will be ideal for the InnoDB log file.

If it starts to get burdensome, you can rely on Tosska’s tuning tools for MySQL. Visit our website for our top-of-the-line tools and to get in touch with our experts to know more about them!

SQL Plan Management Oracle – All You Need to Know About

While upgrading the Oracle database or making any changes in the system parameters, you might have noticed that some SQLs’ performance or queries get highly regressed. If this happens, then don’t worry it’s quite obvious and will always happen whenever there are any changes in the plan. But you can prevent this regression if you use SQL Plan Management Oracle.

In case you aren’t aware of what it is and how it can help, then this blog is surely meant for you. In this blog, we are going to cover every basic aspect related to the SQL plan management and that how it will help in preventing query or performance regression.

An Overview of SQL Plan Management Oracle

SQL Plan Management is a deterrent tool that allows the optimizer to manage SQL execution plans automatically while ensuring that only verified and known plans are used by the database.

SQL plan management uses a mechanism that lets the optimizer to use it for a SQL statement. This mechanism is known as a SQL Plan baseline. It’s a set of accepted plans. A plan is typically known to hold all plan-related information such as a set of hints, SQL plan identifier, optimizer environment, and bind values.

The optimizer uses this information for reproducing an execution plan. Commonly, the database accepts a plan into the plan baseline only after it verifies and confirms that the plan performs absolutely well.

In short, a SQL Plan management Oracle is a tool used for mitigating the risk of query regression when you upgrade to Oracle Database11g or 12c.

Key Components of SQL Plan Management

Mainly, there are three essential elements of SQL Plan management. They are as follows:

  • SQL Plan Baseline Capture

The component creates a SQL plan baselines that describes the accepted or trusted execution plan for all relevant SQL statements. If you aren’t sure where you can find the SQL plan baselines, then let us tell you that you can find the plan baselines stored in a plan history in the SQL Management Base. The management base will be found in the SYSAUX tablespace.

  • SQL Plan Baseline Selection

SQL Plan baseline selection makes sure that the tool uses only the accepted execution plans for statements that have a SQL plan baseline. Furthermore, it ensures that it tracks every new execution plan in the plan history for a statement. The plan history comprises both unaccepted and accepted plans. An unaccepted plan can either be rejected (verified but not performant) or unverified (newly found but not verified).

  • SQL Plan Baseline Evolution

This component is meant to assess every unverified execution plan for a given statement in the plan history for either to be accepted or rejected.

What’s the Main Purpose of SQL Plan Management?

SQL plan management oracle restricts performance or query regression due to any plan changes in the database. Secondly, this tool aims at gracefully adapting to changes.

It must be noted that if an event has caused any irreversible execution plan changes such as dropping an index, SQL plan baseline cannot help in this case.

Advantages of SQL Plan Management Oracle

SQL plan management can preserve or improve SQL performance in database systems and upgrades and data changes.

Some more specific benefits comprise the following-

  • When a database upgrade causes the installation of a new optimizer version, it usually results in plan changes for a small part of SQL statements. Due to plan changes, either the performance of the system improves or there isn’t any change literally. However, in some cases, plan changes result in performance regression and here’s where SQL plan baselines come into play. It significantly lessens potential regressions that result from an upgrade.
  • Ongoing data and system changes can have an impact on plans for some SQL statements potentially creating performance regressions. Plan baselines assist in reducing this performance regression. Further, it stabilizes the SQL performance of the system as well as the database.
  • Employing new application modules brings in new SQL statements into the database. The application software is likely to use the proper SQL execution plan that’s developed in a standard test configuration for the new statements. In case the system configuration differs from the test configuration, then the database can develop SQL plan baselines over time to produce better performance.

In the Bottom Line

During an automated or manual updating of statistics for some objects or while changing some parameters related to the optimizer, or any changes made in the system cause a drastic change in the execution plan. Even more dramatic change is noticed while a database is upgraded. While most plans lead to improvement as they are made to adapt to the new system environment, there are some that lead to performance regression as well. In such cases, we need a SQL plan management mechanism whose main work is to reduce the regression risk.

If you are stuck with long queries or if your system’s performance has reduced, you can get a SQL query optimization tool online. Tosska Technologies Limited is one such company that provides solutions and tools related to database and SQL related performance optimization and improvements. We use advanced technologies like AI (artificial intelligence) so that our tool can help you solve numerous tasks at a time.

Don’t Overlook Oracle Database and SQL Performance: Here’s why

oracle database and sql

Being a DBA is not always a fun job, thanks to certain time-consuming tasks that it entails. One of these is to ensure optimal Oracle database and SQL performance. Typically, it is done by spending a lot of time tuning the long list of SQL statements and software code in order to improve efficiency and enhance access. However, SQL is just one aspect that is related to the performance of database systems.  

Database Administrators also need to invest their time in enhancing the design, physical structure, and specifications of the database objects. These objects are the tables, indices, and the information stored over several files. In the case of data inefficiency, it becomes necessary to observe and modify the actual construction and composition of database objects on a consistent basis. This is because any amount of SQL performance tuning is bound to fall short in a database that is improperly organized or poorly constructed.  

Optimizing Oracle Database and SQL: 5 Important Techniques 

The DBA has to be aware of all the specifications that the database management systems consist of as this knowledge will enable them to use the right techniques to optimize database constructs.

A majority of the most popular DBMSs are compatible with all the methods we have mentioned below, though they may be used differently depending on the database. Let’s take a look:

  1. Indexing: An essential aspect of the Oracle database and SQL performance tuning process is by selecting the right indices and alternatives in order to enable efficient queries. 
  2. Clustering: This involves implementing the physical pattern of data on the disk so that it is clustered on the same page whenever accessed in a particular order. 
  3. Compressing: Data is compressed by decreasing storage requirements, thereby allowing more of it to be stored in a smaller amount of space. This also reduces storage expenses and enhances access if you can add a larger number of rows per page.
  4. Freeing Up Space: Assigning extra room for data growth allows new data to be added to its table easily without leaving the table disorganized.
  5. Partitioning: This entails the segregation of one database table into various sections that are saved in several files. This can be done in multiple ways; by partitioning one file in the same computer, partitioning using shared-disk clustering or by shared-nothing partitioning, depending on the DBMS in question.
  6. File Organizing and Placement: Allocating data from both – database systems and data files – to the correct places is a big step in organizing data and improving Oracle and SQL database performance.
  7. Checking the Page Size: The size of the block or the page determines how efficiently data can be stored and accessed, which is why it is vital to use the suitable page size. The smaller the size of the page, the fewer rows per page, which increases sequential data access requirements.  
  8. Interleaving: Merging all the data from several tables in a sequence into a file helps enhance join performance. However, this method seems to have become less popular than it used to be.
  9. Reorganizing Database Objects: Eliminating the defects from the database by reorganizing and arranging database objects is a well-used technique in SQL performance tuning. In fact, it enormously increases performance, especially if the data was previously fragmented, disorganized or scattered in some way.
  10. Denormalization: This method is considered as a last resort attempt in case the database is unable to perform optimally with a completely normalized implementation. This is because it differs from the logical design. 

All of these techniques are useful and should be considered when the DBA creates a plan for tuning and monitoring the database. Each aspect may not necessarily be applicable to every database object but it must be analyzed for its applicability all the same. Moreover, techniques that are not applicable during initial implementation may turn out to be useful as the application undergoes changes over time in various aspects like data volume, usage, and database characteristics.

Tosska Technologies announces the availability of their major SQL performance tuning product – Tosska SQL Tuning Expert Pro (TSE Pro™) for Oracle®

Intelligent SQL tuning without touching your source code

May. 15, 2018

Hong Kong, May 15, 2018 – Tosska Technologies Limited (Tosska), an IT company that provides database and SQL performance related tools, today announces the general availability of Tosska SQL Tuning Expert Pro (TSE Pro™), the major product of the Tosska SQL Tuning Expert Family of Products that provides state-of-the-art machine tuning capability to improve SQL performance for Oracle® without touching the source code.

SQL Tuning is a classical problem for every DBAs. Tuning SQL statements is finding the fastest route to execute the SQL statements. There have already been a lot of SQL tuning products in the market providing better query plan visualization, better statistics analysis, high cost query plan steps indication or even rule-of-thumb syntax recommendations. All in all, those tools are not helpful if users don’t have in-depth SQL tuning knowledge and are not willing to spend extra effort to tune a SQL apart from their daily duties.

Users are eager for one-button-solution tool that can tune a SQL statement automatically without the need of users’ intervention. The Tosska SQL Tuning Expert Product family aims to help DBAs to do SQL tuning just by multiple easy points and clicks.

“Tosska SQL Tuning Expert Pro is a tool for users to improve SQL performance without touching their program source code. Users can even deploy different performance query plans for various sizes of production databases without the effort of keeping multiple versions of the program source. It is especially suitable for package application users who don’t own the source code of their applications,” said KaMing Ng, CEO of Tosska. “If you are a packaged application user, how can you tune your SQL if you can’t edit a query directly? That’s why we decided to expand the TSE Product family and developed TSE Pro™ to solve this problem.”

“There are multiple features provided by Oracle such SQL Profiles, SQL Plan Baselines and SQL patch that you can use to tell Oracle to fix a SQL’s query plan. But the use of these features is limited by Hints injection only, you cannot rewrite a SQL with different syntax and ask the original SQL to accept a rewritten SQL’s query plan. So, hints-based SQL tuning is becoming more important than ever before. Tosska SQL Tuning Expert Pro is the only tool that can provide the most advanced Auto-Hints-Injection solution in the market to fully automate the process from SQL tuning to plan deployment,” said Richard To, CTO of Tosska. “TSE Pro™ also provides an intelligent workload-based index advisor that helps users to review their existing database schema if there are any new indexes which can help to improve a given SQL workload. The TSE Pro™ uses our proprietary Artificial Intelligent (AI) engine which can handle up to thousands of SQL statements and give you a reasonable recommendation that even human experts cannot achieve.”

Free Trial of TSE Pro™ is now available for download from Tosska’s website. Please visit Tosska’s website www.tosska.com for details.

   
About Tosska
Tosska Technologies is a company that focuses in providing solutions for database and SQL related performance optimization and improvements. Our mission is to help users to smooth out the hurdle by our new technologies. Furthermore, Tosska is one of the very few companies in the world that focus in using artificial intelligence technology to solve various database performance problems. It is our goal to help our customers to reduce their hardware investment; increase their database applications service level and free up their human resources for more strategic activities with our innovative technologies. For more information visit www.tosska.com or email us at enquire@tosska.com.

Press and Media Inquiries
KaMing Ng
Chief Executive Officer
Tosska Technologies Limited
Phone: +852-28248420
Email: enquire@tosska.com

All Trademarks mentioned on this Site are the property of their respective owners.