#sql tuning

How to use SQL tuning advisory in Oracle SQL tuning advisory is the package available in the oracle for tune the SQL Query. It will provide the suggestion regarding the SQL Query to improve its performance by gathering stats, creating index and change execution plan. Steps to run the SQL Tuning Advisory for SQL Query 1. Create the SQL tuning advisory TaskPut the SQL text in the below package…

Check SQL Query performance with different session parameters

Check SQL Query performance with different session parameters

The Step third will test the SQL queries with all instance or session parameter for testing the SQL queries in one go. which will let you know which parameter changes will help SQL queries to run better in your environment.

1. Save the SQL Query in Q.SQL file

2. Login with user of the schema from which SQL query object exists SQLPLUS…

With the added complexness of growing details amounts and ever changing workloads, database efficiency adjusting is now necessary to increase source utilizations and program efficiency. However, efficiency adjusting is often easier said than done.

Let’s face it, adjusting is difficult for several of reasons. For one thing, it requires a lot of expertise to be able to comprehend performance programs, and often upgrade or re-write good SQL. On top of that, adjusting is usually very difficult. There will always be a large volume of SQL statements to go through, which may lead to doubt around which specific declaration needs tuning; and given every declaration is different, so too is the adjusting approach.

As details amounts grow and technology becomes increasingly complicated, it is becoming more essential to tune data base properly to deliver end-user experience and to lower facilities expenses. Performance adjusting can help details source professionals quickly recognize bottlenecks, focus on inadequate functions through overview of question performance programs and eliminate any wondering games.

How our customer Upbeat Games reduced drastically its infrastructure usage and costs in a day.

“It’s not the lack of hardware”. That’s the first statement of the Bad Sql Code Axiom (by Joe Celko) that we use as an inspiration for our team and customers and I would like to share an interesting and real example that happened to Upbeat Games in order to exemplify it.

By knowing which (bad) commands were consuming most of its database resources, the company could optimize them and see the improvements in the performance and in the bill by the end of the month. For a better understanding, here is the part where Vinicius Heimbeck, founder of the company, describes the scenario before adopting Nazar’s monitoring:

“We were having some issues with high CPU usage on our MySql RDS db.r3.4xlarge instance at AWS and sometimes we had to use a db.r3.8xlarge to stabilise things for a while. Frequently, we experienced CPU’s usage peaks of around 60% and payed around U$ 1400.00 monthly for our database instance.”

In this type of scenario, apparently there is a "lack of hardware” and what most people do is to buy more computing power. By doing that, they are only masking the problem instead of actually solving it. Besides not solving the problem, this is usually the most expensive approach.

Only after Upbeat Games’ team started monitoring their database server’s performance, they could find the root cause of their problem and solve it for real.

“When we started monitoring the database’s server, we could see in the report that 2 queries were responsible for almost 100% of the database’s resources usage. After optimizing those two commands (with some little changes) the CPU usage fell drastically. Now we have a db.t2.small RDS instance with minimal CPU utilization (around 2%), with the same amount of users and paying less than 8% of what we used to pay.”

By knowing the root cause of the performance issue the team was able to solve it and maximize the use of computing power while reducing the overall cost.  

Before buying more machine power companies should go deep in the understanding of how the application is using the database resources.

DB2- Split Predicates to improve SQL Performance

Best rewriting SQL query methods to improve Query performance. First write Split predicates out if possible: SELECT DEPTNO, DEPTNAME FROM DEPT WHERE (ADMRDEPT = ‘E01′ AND DEPTNAME LIKE ‘BRANCH%’) OR (DEPTNO = ‘D01′ AND DEPTNAME LIKE ‘BRANCH%’) can also be tried as: Second write SELECT DEPTNO, DEPTNAME FROM DEPT WHERE (ADMRDEPT = ‘E01′ OR DEPTNO = ‘D01′) AND DEPTNAME LIKE ‘BRANCH%’ The idea here…

This post is about a real case from one of our clients who used Nazar.io to solve a big performance issue on his application. The idea is not to explore the solution it self but to show how to use the informations from our analytics to drive your optimization activities.

First, he noticed from his dashboard that the Average Elapsed Time for his server was over 1 second and that was much higher then he has expected. So he got motivated to take a closer look in the detailed report for that server.

In the STATS section of the report, he realized that his server was really busy processing the requests from his application and with all that load it would not support the expected growth of his business. The total time spent processing all the request for a day on his server was 170h 18m 20s 272ms.

In the next section of the report, MOST TIME CONSUMING, he faced a very good candidate for directing his tuning efforts. One single command was responsible for over 50% of the total time of his database server's operations.

After examining the code of that command he identified some bad sql practice forcing the database to do a full table scan instead of using an existing index. The command was then rewritten and the application had a huge performance improvement. The Average Elapsed Time dropped from over 1 second to 210 milliseconds and the Total Time Spent with database operations in a day dropped from 170h 18m to 11h 28m.

Based on that, three things are important to highlight:

First, it was a really low cost optimization as all he had to do was to change the code of one single command in his application while the common solution would be to “buy” more powerful servers considering it was a lack of hardware.

Second, the optimization from a single command impacts not only a specific functionality but the whole application as more resources are available for the other requests.

The explosion of cloud industry has lead to an enormous increase of new services and tools. Few of them focus on the main database issue one can have. 

NAZAR.IO is an Unobtrusive Database Performance SaaS which main focus is to help IT professionals teams to deal and minimize those effects.

Our SaaS has been used to help customers and IT teams to increase the performance of their database applications and often reduce database infrastructure costs, even during development time.

Visit

http://nazar.io