Q1.when you decide to tune the queries?
Answer: We call these queries as “top consuming” queries. These queries do not exceed 5 percent of the total queries Or mostly it is much less.But most of the times, the top consuming queries use 80% of the total resources. This is a really huge ratio. Less than 5% of the total query uses 80% of the total resources!
Actually, not only the top consuming queries, but also we can find some bad SQLs, So we can tune these bad SQLs to increase the performance.The next reason that you decide to tune a code is, mostly the complaint of the users, So, after that complaint, you will find the query that makes your application wait, and solve that problem.
Q2:what is Bad SQL or poorly performing SQLs?
Answer :When we run our query, the first thing that the database server does is, “parsing” it. Parsing means that checking your query if it is valid, then allocating a private SQL area for this statement, and then generating the execution plan. So, if your SQL needs much more parsing operations than it could be, it is a bad SQL. The next thing that makes your SQL a bad SQL is, excessive I/O operations. if your query reads too much data block to get the data, that means your query have excessive I/O operations.
The next thing that makes your query as a bad SQL is, excessive CPU time. If you make some joins, sorts, calculations etc, these operations are done by the CPU and memory. if you do them with wrong methods, your query will consume too much CPU cycles than it could use. The last one that makes your query as a bad SQL is, excessive waits. When you run your query, there might be a lot of wait times until you receive the result.
Q2a: why your SQL queries becomes a bad SQL?
It can be because of a bad design, poor coding or inefficient execution plan. if your SQL is a bad SQL, you will either need to change your code, or guide the optimizer to create a better execution plan.
Q3:how to design a database schema effectively in database?
Answer : The first thing that you need to be careful is, selecting the data types really carefully while creating or modifying the tables.While creating a table, assign data type as much as it needs.The second one is, try to select exactly the same datatype between the parent and child keys.The next one is use normalization well.If you create your tables based on the normalization rules,you will most probably handle the data redundancy problem and will have a better performance.The next one is Select Right Table. So if you use the right table type, you will have much better performance on your queries.The next one is “Use indexes often and select index type carefully”.So if you select indexing methods well and create more indexes, this will increase the performance significantly. The next one is “Create index-organized tables”.an index organized table is a table stored in an index.
Q4:How an SQL Statement is processed?
Answer: The first thing that the server does is, checking the syntax of the query.That means, if your SQL is syntactically correct.The next thing that the server does is, applying a semantic check.That means, if your table is there in the database, which columns will be there if you used select star,etc. As you remember, this is stored in the data dictionary cache. it checks the privileges. If the user who runs the query has the appropriate privilege to run it or not.As you remember, this is also stored in the data dictionary cache.If the user does not have the privilege, it returns an error like the steps above and finishes the execution.The rest of the steps are not executed and we know that, if a similar query is executed before, its execution plan is stored in the shared SQL area in the shared pool.If the first 3 steps are processed, and our query is found in the shared pool, then this is called as a “soft parse”.
There are 2 types of parsing. Soft parse and Hard parse.if we do not create an execution plan and get it from the shared pool, this is called as soft parse.Oracle does not store the statement directly.Instead, it creates a hash value for that code and stores that hash value in the library cache.So once you run your query, a hash value is created for it and this hash is searched as a key for the execution plan in the shared SQL area.Then the next step is the “optimization”. The optimizer is nothing but a software that gets our query as an input and returns the best execution plan.Once the execution plan is createdthen the row source generation process starts.Once the row source is generated, with using exactly these steps,the statement is executed and the result is returned to the user.
Q5: Why do we need to the Optimizer?
Answer: Optimizer is the most important thing in SQL tuning. we need to optimize our query executions. and Oracle does that automatically by using the “Optimizer” component. So optimizer is a software that is dedicated to finding the best execution plan as quickly as possible with a better execution plan, our queries may run may be a 100x faster or may be more and this will decrease the hardware cost so much. Besides, you will get the same result much faster, and this increases the satisfaction of the end users. As we know the same statement can be executed by so many different ways. But their performance will be significantly different among each other. While one of it is returning the query in 1 second, the other may return in minutes, or maybe in hours. So in here the optimizer must find the one which returns in 1 second. Means, the fastest one.
Q6:How to transform a query?
Answer: The first technique of the query transformation is, “OR Expansion”.if our table is big and our query is selective enough. But, if you use an “or” operator in the where clause,the indexes will not be used.So instead of that, either we should delete the “or” operator, or we should use “and” and get the same result. So that we will be able to use the indexes. For example, in this query, we have indexes for both the prod_id and promo_id columns. Sales table is a large table which has almost 1 million records.Even if we have indexes for both these columns, the optimizer does not use any of them and simply reads the whole table normally.So, in order to increase the performance, the optimizer will transform this query into a better one. So, Oracle knows that, you used a where clause on the columns which have some indexes. So these indexes will not be used because of the OR condition. And it knows that, instead of that query, if I write it with some AND conditions, it will be able to use the indexes. So it transforms our query into another one like this.
Q7.why the selectivity and cardinality are important?
Answer: Selectivity is the estimated proportion of the rows that will return from our query based on the total rows of that table.Selectivity affects the estimates in I/O cost. With based on the selectivity of our query,the estimator estimates the I/O cost of our execution. Selectivity affects the sort cost. If the returning rows will be so many, it’s sort cost will be high.So it will be considered while creating an execution plan. cardinality means, the expected number of rows returned from our query Cardinality is used to determine join, sort and filter costs. With using cardinality, the optimizer decides which join method to use, will filtering reduce the data while using an index, etc. Incorrect selectivity and cardinality = incorrect plan cost estimation.If the selectivity and cardinality are calculated incorrectly, maybe because of wrong or old statistics,the optimizer will estimate the execution plan cost incorrectly.
Q8.what is query analysis strategy?
Answer:The first one is, statistics and parameters.We need to be sure that the statistics are up-to-date and parameters are reasonable.If not, we either need to change the parameters and update the statistics if we can.The second one is, query structure.Most of the times the performance problems occur because of some bad SQLs. So we will mostly need to change the queriesto perform better.The other one is changing the access paths.Actually, we mostly change the access paths by changing the query. But sometimes we can do some other ways like hints to change the access paths, or we may change the structure sometimes.Another one is changing join orders and join methods. As you know maybe the most expensive operation in the database is “joins”. So the wrong type of join orders or join methods can cause a huge cost. Because of that, we may change the join order or join method to make a query perform better.The last one is, the others There are some other things to change like parallelism or partition pruning, etc.
Q9:what is the difference of explain plan and execution plan?
Answer:The actual execution plan is different because even if the database server will tend to follow the steps of the explain plan, if it faces with some problems while executing that plan, or if the plan did not seem so logical while executing based on the new statistics,it can change its way and do different things than the explain plan.So the explain plan and the actual execution plan may vary.And because of that, the execution plan saved into the shared SQL area may be completely different than the explain plan.So, even if the database decided to change the way of execution, that is still an execution plan. And that execution plan is more reliable than the explain plan now.Because the explain plan was created based on the estimates, but the actual explain plan is modified while the execution based on the real-time statistics.But the difference is, explain plan is created only by the estimates, but execution plan is different. Before running the execution plan,it is the explain plan but while the execution, it can change.
Q10:How to gather statistics in database?
Answer:There are two types of statistics they are system and optimizer statistics,system statistics used by the optimizer to estimate I/O and CPU costs,shoule be generated regularly and gathered during a normal workload. eg: EXEC dbms_stats.gather_system_stats(‘start’).optimizer statistics can be gathered manually or automatically eg:Gather_database_stats procedure
EXEC dbms_stats.gather_database_stats,Gather_dictionary_stats procedure
EXEC dbms_stats.gather_dictionary_stats Gather_schema_stats procedure
EXEC dbms_stats.gather_schema_stats(ownname=>’DH’) Gather_table_stats procedure
EXEC dbms_stats.gather_table_stats(ownname=>’DH’,tabname=>’SALES’, cascade=>’true’)
Ans:An execution plan in Oracle Database is a road map that the database uses to execute a SQL statement. The execution plan describes how Oracle will execute the SQL statement, which indexes it will use, and in what order it will join tables. Generating an Execution Plan: You can generate an execution plan for a SQL statement using the EXPLAIN PLAN statement.
Example : EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 20;
12.what are indexes and what they do in performance?
Answer:Actually, there are two types of indexes, B-Tree indexes means balanced-tree indexes and bitmap indexes.This is the default indexing of Oracle database. When you write CREATE INDEX directly, it creates a B-Tree index.So this one is the most common indexing type, especially for the OLTP type databases.This type of indexing is used if the selectivity of the column is high.The next index type is the bitmap indexes.If the columns are not so selective, you can create a bitmap index for that table.Bitmap indexes are mostly used for data warehousing. Especially for the reports using multiple tables you can use bitmap-join indexes.Reverse key indexes store the column values as reversed.Sometimes because of a heavy load on tables, the next creation has some contentions about creating the leaves.Ascending or descending indexes store the column values as ascending or descending order so that the indexes can have a range scan easily in ascending or descending order.
13.why the optimizer performs an index full scan and how can that happen?
Answer: If our query has an order by with the indexed columns, even if we read all the data of a table reading by using the index will be faster than reading directly from the table because the sort operation is a really costly operation and if we eliminate that,our code will return the result faster.And since our indexes already have the keys in the sorted order, if we read by an index and read the rows by the index row IDs,it will be faster than reading the table and then sorting the rows.Second, if our query has a group by clause and that group by clause consists of only the columns of an index, it will perform an index full scan.It will be faster for most cases because since the columns are ordered in the index,it will group the roles much easier.The last condition that the optimizer chooses to perform index full scan is if the query requires a sort merge join.In sort merge joins, the optimizer will consider the index full scan because using an already sorted record will ease its job but in order by and sort merge join conditions, the order of the index columns must match with the indexing order.
14.when the database can share the cursors?
Answer:For example, we may see that the database frequently executes the same query except for the literals.Let’s say they all select staff from employees.The employee ID is something, only the part after the equal sign is different.However, since the text is not completely the same,So the database server converts these literals into bind variables and share the same parent cursors and child cursors, but in some queries it only shares the parent cursors.To let the database use the cursor sharing, we need to set the cursor sharing initialization parameter.There are two types of values for the cursor sharing parameter, force and exact, actually there were one other called similar, but Oracle decided to remove this since it creates too many child cursors.So now there are two of them.Exact value, this is the default value for the cursor sharing parameter.It allows cursor sharing only if the SQL text is completely identical to the previous ones.The next one is force.It allows sharing the cursors even if the literals are different, but in this case,it shouldn’t change the execution plan.
15.When you use a bind variable in your query?
Answer: when you first execute that query, the optimizer peeks that bind variable value to generate the plan. One value may be very selective but another one may not be selective enough. So the optimizer waits for the bind variable value and decides the plan after getting it.However, after the first execution,it doesn’t peek the value anymore.It directly uses that plan whether the bind variable values are the same or not.So this is called as bind variable peeking.It peeks for the first execution,but doesn’t for the next ones until you restart the database.If the column is a unique column,bind variable peeking is a great ease.However, let’s say if the first value of the bind variable returns 1,000 rows,but the next one returns only one row.
16.what is cursor in server side and cursor sharing in the database?
Answer:The first operation is open, in this step,it allocates memory for that cursor.The second operation is pause.like syntax analysis, semantic analysis, privilege checks are performed.The third operation is bind.In this step, the bind variables are assigned to the query.The next operation is define.It defines how you want to see the data,means specify the variables and their types, etc.Then execute the query.And lastly, fetch operation.Even it seems so fast in our executions, these operations are really costly operations for the database, because the database performs maybe millions of operations in a second,During the parsing step,after successfully performing syntax semantic and privilege checks,the database server allocates a data structure for our query if there is not one already.This data structure is called a cursor in this server side.So the cursor holds the past statement and other information of processing.
Oracle wants to share this cursor with other executions of the user and even with the other users executions to make its job easier.So this is called as cursor sharing.
17.How does the database collect the optimizer statistics?
Answer:Before the database version 12c,the optimizer decided the execution plan before executing the query and execute that query by following the steps of that plan.However, starting from 12c,now the optimizer can change the plan on runtime.Instead of selecting the best plan and execute it in that way, it selects the best plan again,and store some alternative sub-plans as well.While executing the query,it gathers the statistics with the statistics collector agent,then this collector buffers the new statistics about the cardinality and the histograms while the query is still being executed. Let’s say it collects the number of rows being read until now and compares with the previous statistics.If the old stats are inaccurate,it uses the new statistics and picks another plan if needed and only the final plan is stored in the shared pool to use later. So basically, while executing the query means at runtime,if the optimizer understand that the statistics are not correct,it may continue the rest of the execution with one of the alternative sub-plans it has.
18.Which Join will be faster?
Answer: It totally depends on the size of the joining row sources. However, sort merge join is generally a costly operation and the optimizer selects hash join most of the times instead of the sort merge join,but not all the times. It totally depends on the cost of these two methods. But if one side is already sorted, the optimizer will definitely select the sort merge join. On the other hand, if the row sources are not sorted,and they are very large, the optimizer will most probably select the hash join as the best choice. Hash join basically creates a hash table with the hash function of math by using the smaller table and then joins this hash table with the other row source. But if the new hash table will not fit into the memory, the optimizer will consider writing the rest of the hash table into the disk for some time. But this will also increase the cost.