A Quick Guide to Stored Procedures in Oracle Database and SQL

sql tuning for MySQL

Stored procedures are increasing in popularity in Oracle Database and SQL Server because of quicker execution. Earlier, application code mostly resided in external programs. However, its shift toward database engine interiors compels database professionals to keep their memory requirements in mind.

This is as necessary as planning for times when the code related to database access will be present within the database. They also need to know how they can handle these stored procedures to maintain ideal database performance. We will look at some of these methods and the advantages of using stored procedures and triggers in the Oracle database.

Perks of Stored Procedures for Oracle Database Performance Tuning

Until recently, a majority of Oracle databases had limited code within their stored procedures. This shift in trends is because of the various advantages that come with placing larger amounts of code, such as the following:

Performance Improvement – Using more stored procedures means you don’t require Oracle database performance tuning as much. That’s because each of these only has to load once into the shared pool. Executing them, therefore, is quicker than running external code.

Code Segregation – The stored procedures have all the SQL codes which turn all the application programs into calls for those procedures. This is an improvement in the data retrieval process because changing databases gets simpler.

Therefore, one advantage you get through stored procedures is the ability to transfer large amounts of SQL code to the data dictionary. Doing this will enable you to perform SQL tuning without involving the application layer.

Group Data Easily – You can gather relational tables with data that shares certain behaviour before looking for Oracle performance tuning tips. Simply use Oracle stored procedures as methods, along with suitable naming conventions. For example, link the behaviour of the table data to the table name in the form of prefixes.

The users may then request the data dictionary to display all the traits connected to one table. This makes it more convenient to recognise and reuse code with the help of stored procedures.

Other Reasons Behind the Increasing Popularity of Stored Procedures

There are plenty of other reasons ‌stored procedures and triggers take less time in comparison with conventional code. One of these has something to do with SGA caching in Oracle database and SQL.

Once the shared pool within the SGA gets a hold of a stored procedure, it keeps it there until the procedure gets paged out from the memory. The SGA mostly does this to create space for other stored procedures. The paging out process takes place based on a Least Recently Used or LRU algorithm.

Two parameters help determine the amount of space that Oracle uses on startup. These are the Cache Size and the Shared Pool Size parameters. They also help users check how much storage space is available for various tasks. These include caching SQL code, data blocks, and stored procedures.

Stored procedures will run extremely fast once you load them into the shared pool’s RAM – as long as you can avoid pool thrashing. This is important because several procedures compete for varying quantities of memory in the shared pool. 

How to build indexes for slow first execution SQL – SQL Server?

You may suffer from SQL statements with a slow first execution time due to the long data cache process. The following SQL is simple that retrieves records from the EMPLOYEE table that if EMP_SALARY < 500000 and the result set is ordered by EMP_NAME.

Select emp_id,
    emp_name,
    emp_salary,
    emp_address,
    emp_telephone
from    employee
where  emp_salary < 500000
order by emp_name;

The following is the query plan that takes 9.51 seconds for the first execution and takes 0.99 seconds for the second execution without data cache.

The SQL cannot be tuned by SQL syntax rewrite or hints injection for both the first execution and the second execution, it is because SQL Server has selected the best query plan for this simple SQL statement. But the problem is that if the condition “where emp_salary < 500000” is changed; say from 500000 to 510000 or the EMPLOYEE data is flushed out from the memory, the execution time will then be prolonged up to 9.51 seconds.

Let’s see if we can build indexes to improve this situation. There is a common perception that a good index can help to improve both the first execution time and the second execution time. So, I use a tool to explore a lot of indexes configurations, but none of them can improve both executions’ performance. Here the following is the performance of the second execution with data cached for different indexes proposed by the tool. You can see the performance of “Index Set 1” is close to the original SQL performance with a little performance variation due to the system’s loading status and all other indexes sets are worse than the original SQL. Normally, we will give up the tuning of the SQL statement without even trying to see whether those recommended indexes are good for the first execution time.

I did a test for those recommended indexes to see whether they are helpful to improve the first execution time, it surprises me that the “Index Set 1” is tested with a significant improvement and improves the first execution time from 9.51 seconds to 0.65 seconds. It is a 14 times improvement that can make my database run more efficiently. So, you should be very careful to tune your SQL with new indexes that may not be good for your second execution with all data cached, but it may be very good for your first execution without data cached.

This kind of indexes recommendation can be achieved by Tosska SQL Tuning Expert Pro for SQL Server automatically.

Tosska SQL Tuning Expert Pro (TSES Pro™) for SQL Server – Tosska Technologies Limited

How to index SQL with aggregate function SQL for Oracle?

Here the following is an example SQL shows you that select the maximum emp_address which is not indexed in the EMPLOYEE table with 3 million records, the emp_grade is an indexed column.

select max(emp_address) from employee a
where emp_grade<4000

As 80% of the EMPLOYEE table’s records will be retrieved to examine the maximum emp_address string. The query plan of this SQL shows a Table Access Full on EMPLOYEE table is reasonable.

How many ways to build an index to improve this SQL?
Although it is simple SQL, there are still 3 ways to build an index to improve this SQL, the following are the possible indexes that can be built for the SQL, the first one is a single column index and the 2 and 3 are the composite index with a different order.
1. EMP_ADDRESS
2. EMP_GRADE, EMP_ADDRESS
3. EMP_ADDRESS, EMP_GRADE

Most people may use the EMP_ADDRESS as the first choice to improve this SQL, let’s see what the query plan is if we build a virtual index for the EMP_ADDRESS column in the following, you can see the estimated cost is reduced by almost half, but this query plan is finally not being used after the physical index is built for benchmarking due to actual statistics is collected.

The following query shows the EMP_ADDRESS index is not used and the query plan is the same as the original SQL without any new index built.

Let’s try the second composite index (EMP_GRADE, EMP_ADDRESS), the new query plan shows an Index Fast Full Scan of this index, it is a reasonable plan which no table’s data is needed to retrieve. So, the execution time is reduced from 16.83 seconds to 3.89 seconds.

Let’s test the last composite index (EMP_ADDRESS, EMP_GRADE) that EMP_ADDRESS is placed as the first column in the composite index, it creates a new query plan that shows an extra FIRST ROW operation for the INDEX FULL SCAN (MIN/MAX), it highly reduces the execution time from 16.83 seconds to 0.08 seconds.

So, indexing sometimes is an art that needs you to pay more attention to it, some potential solutions may perform excess your expectation.

The best index solution is now more than 200 times better than the original SQL without index, this kind of index recommendation can be achieved by Tosska SQL Tuning Expert for Oracle automatically.

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

Why You Must Speed Up Slow Queries in Oracle Database and SQL

oracle database performance tuning

The performance of an Oracle database and SQL query speed can directly affect the organisation it belongs to. If the queries running in the database are slow, they will surely have a negative impact. However, its severity may vary based on the database’s role, its architecture, and the industry your organisation operates in.

Regardless of the extent, it would be unwise to ignore them, which is why we are going to talk about all their effects in this blog.

How Slow Statements Affect Oracle Database and SQL Users

In this fast-paced world, everything needs to work fast and offer a quick response time to its end-users. The data that a web page displays generally comes directly from the database with very few interactions.

This implies the dependency of the application’s response time on the time it takes for the queries to run and the database to respond. Slow statements will take more time, resulting in loading screens before the desired information shows up. This is when Oracle database performance tuning becomes a requirement.

Such speeds don’t affect only the application, however; they leave an impact on the other parts of a system as well. The reason behind this is the location of the database in a majority of web architectures.

Take a look at the three-tier architecture, for example – the database lies at the bottom in most cases, forming the foundation. An increase in latency here is likely to cause the same in the higher levels along with other areas in the system.

Another way in which slow queries negatively impact the system is by making the database use more resources than is actually necessary. Some of these are available in limited quantities, such as I/O and CPU, since other applications share these resources.

On the other hand, not using existing resources sufficiently leads to their inefficient usage and slow queries as well. This may be the case with your database, so you may want to consider a few Oracle performance tuning tips that deal with this particular issue.

Top Reasons Behind Slow and Inefficient Queries

Given below are the three most significant causes of queries slowing down:

  1. Too many tasks: Executing a statement includes multiple tasks, such as retrieving data, making calculations, and arranging data in the order as the query specifies. All of these involve plenty of factors, any of which can increase the amount and complexity of work done, from joining and grouping to filtering and sorting.
  1. Too much waiting: Sometimes, statements don’t have too much to do, nor are they stuck waiting for resources. The reason why they are sluggish is that they are waiting on other statements that are locking resources or requiring higher levels of activity.
  1. Too few resources: Query execution works alongside other tasks taking place within a system. This means they share resources such as network throughput, disk I/O, and CPU. Statement execution is likely to take more time when these are completely occupied.

Locating and Working on Slow Queries in Oracle Database

Slow queries don’t get faster on their own – DBAs must take steps to speed them up. For starters, they can use the Database Performance Monitor (DPM) in the following ways:

  • Viewing all the queries that are taking up execution time using the query profiler. Such queries are often running in the absence of indexes, so it’s a good idea to add one to improve Oracle database performance and execution speed.
  • Automatically collecting explain plans to get a quick glance at the ones that contain information regarding slow queries and the changes related to them, if any. 
  • Assessing Oracle database and SQL to find out whether a statement can perform better with the help of some improvements.
  • Visiting the charts page to go through properly arranged metrics pertaining to system performance. This allows the DBA to set a threshold alert and note changes every time a system resource is reaching maximum use.

Conclusion

Based on your architecture and application, slow statements can affect more aspects of your business than just the database. Therefore, ignoring them is not recommended as it often results in a detrimental impact on both end-users and your organisation.

Consider enlisting the help of professional tuning tools to improve slow query performance in Oracle and SQL Server databases.

Transferring Data in SQL Server with an Eye on Performance

improve performance of sql query

A lot of database professionals often need to archive older data in SQL Server by transferring it from one table to another. There are multiple ways to achieve the transfer, the most useful of which we will discuss in this blog. We will also provide tips to ensure the performance of the database doesn’t get affected as these approaches are carried out.

Different Methods to Move Data from One Table to Another

Consider the following techniques that various DBAs take when they have to take data from a table to add to another table along with some ways to improve performance of SQL query while using them:

  1. Insert data with the INSERT INTO command – The INSERT INTO query is one of the basic methods of moving data from table 1 to table 2. You can help decrease the time it takes to enter information using this method. If the database is running under the full recovery model, just change it to the bulk-logged model. Doing this saves execution time as it skips over complete logging of bulk operations. The following query should help with this:

ALTER DATABASE <database name> SET RECOVERY <BULK_LOGGED>

Once you switch to the bulk-logged recovery model, you will have to use a truncate statement to flush table 2 (destination). You can carry out the same script you were using to transfer data after this.

  1. Use the SELECT INTO query – Using the SELECT INTO rather than the INSERT INTO command can prove useful in some cases. However, the benefits are significant when the recovery model is bulk-logged due to the reason mentioned above. Although users lack the ability to place the data in an existing table, SQL Server brought with it a feature to make things easier. It essentially enables them to pick the filegroup where they want to create a table.
  1. INSERT INTO query + Tab lock hint – Using both in combination has been known to provide better database performance. To achieve this, you will have to use TABLOCK for table 2. If the destination table is without a clustered index or other constraints, that data will remain as a heap. It helps to use the TABLOCK hint for the destination table during data insertion into a heap using the INSERT INTO statement. Doing this enhances query logging and locking since a shared lock is placed on the whole table rather than every row or page.
  2. Adding data using the SWITCH TO query – You can also try moving the data with the help of the SWITCH TO command. Although this query typically finds its use while transferring information between partitions among separate tables, it can help here as well. How? By moving data from one partition to the next using the ALTER TABLE command. If there are no allocated partitions, the data will transfer through tables instead. Before you begin data insertion, make sure you disable any constraints or indexes that exist on the table. It is better to enable constraints and rebuild indexes after insertion from a performance perspective.

Tips for Enhancing Performance During Data Transfer and Insertion

  • Reduce IO lag – Latency can negatively impact the process of writing database files on disk. You can decrease latency and bottlenecks using SSD drives that are comparatively better than SATA or SCSI drives.
  • Maintain Robust Server Infrastructure – The system needs to be properly built to ensure competent performance for various database operations. The greater the pressure on the resources, the greater the effect on performance.
  • Follow ACID Properties – ACID properties make sure each transaction contains certain properties when it gets processed. In the case of data insertion, the isolation factor is also important to consider because the values have another source. Here, the statements should contain the suitable isolation level to maintain integrity within the database.
  • Database Settings – One of the best ways to achieve improved outcomes is to maintain the right database configuration. This is because the settings can have a significant effect on performance. For instance, the location of the database files on the disk along with TempDB settings.

These are the various ways in which you can gain better performance at the query, trace, and constraint levels along with additions that can improve the execution of insert operations.

SQL Server: Knowing How Heaps and Clustered Indexes Work

MySQL database and sql

Heaps and clustered indexes are two different ways of storing data in SQL Server. Both have their advantages and disadvantages, and we will discuss them in this post.

A Bit about Heaps

Heaps are essentially piles of data that remain unsorted or unorganized, hence the name. Although you can find heaps on tables that don’t have clustered indexes, they may also be present with non-clustered indexes. Heaps provide the benefit of increased input speed which helps while adding data to a table. Data insertion is quicker because the process doesn’t require a logical order to do so.

A Bit about Clustered Indexes

A clustered index is a more organised way of data insertion. In fact, it is the go-to technique for logically sorting information in a table. A clustered index doesn’t need a primary key but you can create one on a predefined key-value. Most DBAs recommend creating them on the most-used columns that come under reference of highly frequent query executions. They also reduce the need for optimization since all the data gets sorted to fit them. The primary benefit of using a clustered index is that it speeds up data reads.

Knowing When to Use a Clustered Index

As noted above, using a clustered index leads to better read rates. Therefore, there are several instances where you may need to identify whether a clustered index will improve performance of SQL query rather than a heap.

To do this, you need to follow these steps:

  • First, it is important to understand where there is a requirement for greater read speed.
  • Check dynamic system views and look for large tables without a clustered index.
  • Once you locate a few such tables, you can analyse the plans and stats of queries in the MSSQL system dynamic management views. Searching through the table name in the variable will show you the usage frequency of the plan. It will also show the text fetched and other necessary validation details that show whether a heap or non-clustered index is in use instead.

You will be able to view object names in the second result set in case the table in question is under use in SQL object. Once you have reviewed the query plans relevant to the use cases, you will have sufficient information to help you decide whether the table requires a clustered index or if a heap is more suitable for it. You will also have to choose all the columns that will have to be in the index in case of the former. Tables with several use cases that mostly share the same columns can provide result sets faster with a clustered index.

When Not to Use Clustered Indexes

This is just as important to know because believe it or not, there are instances where a clustered index can do more harm than good to oracle database performance.

A logging table is one such instance as it normally has far more insert operations than reads or updates. This is because their purpose is to log each occurrence but users may not refer to it as frequently. If you place an index on this kind of table, it can result in hot latches due to lagging data insertions for the last available page. Meanwhile, information keeps getting added onto the same page from other means. The one case where this issue doesn’t occur is when the index’s main column is a GUID, therefore, it isn’t sequential.

Using a clustered index in a table with an excessive number of columns isn’t the best idea, either. The reason behind this is simple: the index is supposed to define the default sort order. Too many columns mean repeated resorting with each new use case, slowing down the database. It will also result in an increase in the size of the non-clustered indexes present in the table.

Another situation where a clustered index can’t help is a column that isn’t usually static as they undergo frequent changes. Changing key values on an index have far greater chances of creating performance-related problems. This is because updating key values typically leads to page splits – these need maintenance, which takes resources and affects performance.

How to use FORCE INDEX Hints to tune an UPDATE SQL statement?

improve performance of sql query

We used to use FORCE INDEX hints to enable an index search for a SQL statement if a specific index is not used. It is due to the database SQL optimizer thinking that not using the specific index will perform better.  But enabling an index is not as simple as just adding an index search in the query plan, it may entirely change the structure of the query plan, which means that forecasting the performance of the new Force Index hints is not easy. Here is an example to show you how to use FORCE INDEX optimization hints to tune a SQL statement.

A simple example SQL that updates EMP_SUBSIDIARY if the emp_id is found in EMPLOYEE with certain criteria.

update EMP_SUBSIDIARY set emp_name=concat(emp_name,'(Headquarter)’)
where emp_id in
(SELECT emp_id
  FROM EMPLOYEE
WHERE  emp_salary <1000000
   and emp_grade<1150)

Here the following is the query plan of this SQL, it takes 18.38 seconds. The query shows a Full Table Scan of EMPLOYEE and then Nested Loop to EMP_SUBSIDIARY with a Unique Key Lookup of Emp_sub_PK index.

We can see that the filter condition “emp_salary <1000000 and emp_grade<1150” is used for the full table scan of EMPLOYEE. The estimated “filtered (ratio of rows produced per rows examined): 3.79%”, it seems the MySQL SQL optimizer is failed to use an index to scan the EMPLOYEE table. We should consider forcing MySQL to use either one of emp_salary or emp_grade index.

Unless you fully understand the data distribution and do a very precise calculation, otherwise you are not able to tell which index is the best?

Let’s try to force the index of emp_salary first.

update   EMP_SUBSIDIARY
set    emp_name=concat(emp_name,‘(Headquarter)’)
where emp_id in (select  emp_id
         from    EMPLOYEE FORCE INDEX(`emps_salary_inx`)
         where  emp_salary < 1000000
           and emp_grade < 1150)

This SQL takes 8.92 seconds and is 2 times better than the original query plan without force index hints.

Let’s try to force the index of emp_grade again.

update   EMP_SUBSIDIARY
set    emp_name=concat(emp_name,‘(Headquarter)’)
where emp_id in (select  emp_id
         from    EMPLOYEE FORCE INDEX(`emps_grade_inx`)
         where  emp_salary < 1000000
           and emp_grade < 1150)

Here is the result query plan of the Hints FORCE INDEX(`emps_grade_inx`) injected SQL and the execution time is reduced to 3.95 seconds. The new query plan shows an Index Range Scan of EMPLOYEE by EMP_GRADE index, the result is fed to a subquery2(temp table) and Nested Loop to EMP_SUBSIDIARY for the update. This query plan’s estimated cost is lower and performs better than the original SQL. It is due to the limited plan space in the real-time SQL optimization process, so this query plan cannot be generated for the original SQL text, so manual hints injection is necessary for this SQL statement to help MySQL database SQL optimizer to find a better query plan.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the Hints injected SQL is more than 4.6 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

How to build indexes for multiple Max() functions for SQL Server?

For some SQL statements with multiple Max() functions in the select list and nothing in the Where clause, we have different methods to create new indexes to improve the SQL speed.

Here is an example SQL, it is to retrieve the maximum name and age from the employee table.

select max(emp_name),
     max(emp_age)
from  employee

The following is the query plan that takes 9.27 seconds.

The SQL cannot be tuned by SQL syntax rewrite or hints injection, and the SSMS cannot recommend any index to improve the SQL.

For this kind of SQL that we can consider building a composite index or two individual indexes for emp_name and emp_age.  A new composite of these two columns (emp_age, emp_name) can improve the SQL around 7 times. The following is the query plan shows that the new composite index is used, but it has to scan the entire index for these two stream aggregate operations before getting the max(emp_name) and max(emp_age).

How about if we build two individual indexes for emp_name and emp_age. The following is the result and query plan of these two indexes created. A Top operator selects the first row from each index and returns to the Stream Aggregate operation, and then a Nested Loops join the two maximum results together. It is 356 times much faster than the original SQL.

This kind of indexes recommendation can be achieved by Tosska SQL Tuning Expert Pro for SQL Server automatically.

Tosska SQL Tuning Expert Pro (TSES Pro™) for SQL Server – Tosska Technologies Limited

How is the order of the columns in a composite index affecting a subquery performance for Oracle?

MySQL database and sql

We know the order of the columns in a composite index will determine the usage of the index or not against a table. A query will use a composite index only if the where clause of the query has at least the leading/left-most columns of the index in it. But, it is far more complicated in correlated subquery situations. Let’s have an example SQL to elaborate the details in the following.

SELECT D.*
FROM   department D
WHERE EXISTS (SELECT    Count(*)
         FROM     employee E
         WHERE     E.emp_id < 1050000
                AND E.emp_dept = D.dpt_id
         GROUP BY  E.emp_dept
         HAVING    Count(*) > 124)

Here the following is the query plan of the SQL, it takes 10 seconds to finish. We can see that the SQL can utilize E.emp_id and E.emp_dept indexes individually.

Let’s see if a new composite index can help to improve the SQL’s performance or not, as a rule of thumb, a higher selectivity column E.emp_id will be set as the first column in a composite index (E.emp_id, E.emp_dept).

The following is the query plan of a new composite index (E.emp_id, E.emp_dept) and the result performance is not good, it takes 11.8 seconds and it is even worse than the original query plan.

If we change the order of the columns in the composite index to (E.emp_dept, E.emp_id), the following query plan is generated and the speed is improved to 0.31 seconds.

The above two query plans are similar, the only difference is the “2” operation. The first composite index with first column E.emp_id uses an INDEX RANGE SCAN of the new composite index, but the second query plan uses an INDEX SKIP SCAN for the first column of E.emp_dept composite index. You can see there is an extra filter operation for E.emp_dept in the Predicate Information of INDEX RANGE SCAN of the index (E.emp_id, E.emp_dept). But the (E.emp_dept, E.emp_id) composite index use INDEX SKIP SCAN without extra operation to filter the E.emp_dept again.

So, you have to test the order of composite index very carefully for correlated subqueries, sometimes it will give you improvements that exceed your expectation.

This kind of index recommendation can be achieved by Tosska SQL Tuning Expert for Oracle automatically.

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

Do not undermine your SQL Server’s potential ability

For some SQL statements that are failed to be tuned by syntax rewrite, hints injection, and all necessary indexes are built, people may think that hardware upgrade is the only way to resolve the performance problem. But, please don’t undermine your SQL Server’s SQL optimizer which can provide you with the ultimate performance solution that you may not have imagined before. What you need to do is to provide SQL Server with a set of proper new indexes.

Here is an example SQL, it is to retrieve the minimum employee’s salary and the emp_id that with salary greater than all salary of the emp_subsidiary table with subsidiary’s employees’ department = “AAA”.

SELECT emp_id,
    (SELECT min(emp_salary)
     FROM  employee)
FROM  employee
WHERE emp_salary > (SELECT max(emp_salary)
           FROM emp_subsidiary
           where  emp_dept = ‘AAA’)

Although all columns that show in the SQL are indexed, the following query plan takes 44 seconds.

The SQL cannot be tuned by SQL syntax rewrite or hints injection, and the SSMS can recommend only one index on one table for a SQL statement, it is failed to recommend any good index. So, the SQL cannot be tuned in any traditional way.

Let’s use our new A.I. index recommendation engine to see if there are any good index solutions. A set of indexes is recommended listed in the following. It takes only 0.55 seconds.

Example: 80 times faster A.I. SQL index recommendation

The query plan shows that two new indexes are used at the same time that the SSMS is not able to provide.

Tosska SQL Tuning Expert Pro is in-built with an A.I. engine to recommend indexes for multiple tables at the same time for a SQL statement. The new technology is so powerful to recommend multiple tables’ new indexes for a SQL at the same time, it means that how each new table’s indexes affect each other in the query plan will be considered by the engine. It is very helpful for SQL Server’s SQL optimizer to explore more potential query plans that could not be generated before. So, don’t undermine your SQL Server’s ability. Instead, use the right tool to tune your SQL statements before you are planning to upgrade your hardware.

Tosska SQL Tuning Expert Pro (TSES Pro™) for SQL Server – Tosska Technologies Limited