Thursday, September 27, 2012

Making Impactful Performance Changes Webinar Follow-up

Thanks everyone who attended the 3rd segment of my 3-part DBA Performance webinar series. I enjoyed delivering them and hope you found them informative and useful.

You can find the recording here and the presentation slide deck here.

There were two SQL statements in the deck that were used to produce execution plan output (either using dbms_xplan.display_cursor or dbms_sqltune.report_sql_monitor). So you won't have to type them if you want to use them, here they are:

-- SQL to produce execution plan using dbms_xplan.display_cursor
SELECT xplan.*
select max(sql_id) keep
       (dense_rank last order by last_active_time) sql_id
     , max(child_number) keep
       (dense_rank last order by last_active_time) child_number
  from v$sql
 where upper(sql_text) like '%&1%'
   and upper(sql_text) not like
) sqlinfo,
(sqlinfo.sql_id, sqlinfo.child_number, 'ALLSTATS LAST')) xplan ;

-- SQL to produce a SQL monitor report
SELECT dbms_sqltune.report_sql_monitor
    SQL_ID => '&sql_id',
    SESSION_ID => '&session_id',
  SESSION_SERIAL => '&session_serial',
    TYPE => '&report_format'
FROM  dual ;

And since we didn't have enough time to answer all the questions that were posted, I wanted to provide those answers here.

Thanks again to everyone who attended any of the events and I look forward to doing more in the future. Stay tuned!

Questions and Answers

Q: Can spatial queries be optimized, queries that deal with large spatial data (land registry, geological data)
A: Any query can be optimized in one way or another, but I'd suggest that you check out the following link to a question posed on the Oracle Forum about spatial performance:

Q: Could you  pls repeat when an SQL will be monitored automatically?
A: Queries that run in parallel or any query that consumes 5 or more seconds of CPU or IO time.

Q: How did you arrive at 10ms time for one row index maintenance?
A: The 10ms time is from the book I referred to (Relational Database Index Design and the Optimizers). It is a combination of Queuing time, Seek time, Disk rotation time and Transfer time. Remember that it is intended to be used for doing estimated calculations; your mileage may vary.

Q: Do you need SQL tuning license to use MONITOR hint?
A: Well, you could use the MONITOR hint, but you just couldn't view the SQL Monitor reports of the collected data. So, I suppose you could imply that you couldn't/shouldn't use the MONITOR hint if you don't have the appropriate Tuning Pack license.

Q: STA will recommend new indexes in isolation for just one query. Doesn't it? That may not be useful when you have got to worry about 10 different queries accessing one table.
A: Yes, STA isn't looking at index creation from a system-wide view. It only looks at the one SQL statement and determines if a non-existing index might provide better performance. It cannot tell you what the creation of that index will do, either to the good or bad, for other queries. In the end, it is your responsibility to thoroughly test any index creation or modification in a representative environment to determine the effects.

Q: On a partitioned table , local index, if we want the load to be faster , the local index of the partition being loaded could be made unusable. but queries that are looking for partitions other than the one that was made unusable, don't use the index due to the fact that one of the partitions is unusable. Is there a way to tell the optimizer go ahead and use the index (does not work even with a profile)
A: I'd actually investigate other ways of performing loads, like using partition exchange, if possible. I do not know a way around the unusable index problem so I'd suggest investigating how to change your load process to avoid having to use that method.

Q: What is desired way to do benchmark and compare of SQL performance?
A: In part, the most appropriate way to benchmark SQL performance will depend on your specific circumstances. If you have an application that has been running in production for a while, you can use AWR data (or Statspack if you're not licensed for AWR) to retain execution information and do comparisons over time. If you're developing new code, you may have Performance SLAs (System Level Agreements) that specify the expected response times. In that case, testing would be evaluated against those metrics. But, regardless of your situation, you need a baseline measurement that is the expected/desired response time and then all tests must meet those minimum standards. You need to be able to measure performance under load and with representative data volumes to do this well. And, you have to keep in mind that response time isn't just about 'now', it's about the future as well. Testing must help ensure that performance will remain stable over time as user load and data volumes increase.

Q: Does join order still matter in 11gR2?
A: Not really and actually it (theoretically) mattered since the optimizer began using CPU costing and not just IO costing as early as Oracle version 9. The optimizer will use statistics to evaluate and weight various join orders and doesn't just evaluate joins in the order the SQL is written. For my own purposes, I typically try to write SQL in the order I "hope" it will get executed. If what I hope for and what actually occurs don't match, then I work my way through the differences. But, there is not a limit to what the optimizer will chose based on the order the SQL is written.

Q: Does Oracle engine have a process that collects statistics information near real time so that CBO can always get the latest and correct information?
A: There is a default stats collection job that runs in a nightly maintenance window. This job will check the staleness of the statistics and collect updated stats if needed (by default, statistics are considered stale when the table has changed by 10%). But, there is no automated way in place to collect "near real time" statistics. Of course, you could set up collections to occur as frequently as you wish, but due to the overhead required to do collections, most people don't collect stats except during non-peak times.

Q: What was the title of the database tuning/index book Karen mentioned?
A: Relational Database Index Design and the Optimizers by Tapio Lahdenmaki and Michael Leach

Q: In terms of Disk I/O versus Memory, have you seen an increase when adding more I/O or Memory to improve database performance? Or does it depend on a OLTP / OLAP?
A: Well, the only way to know if adding disk capacity or memory will help improve your performance footprint is to know if either disk IO or lack of memory is a bottleneck for you. If your bottleneck is CPU, then obviously adding disk or memory isn't going to fix your problem. So, my answer is the old consultant stand-by of "it depends". If you have a known issue with disk capacity or memory, then adding more of those resources should give you more breathing room. But, if you add those resources and something else is your main bottleneck, then you won't really see much improvements. 

Q: When looking at a SQL Query Execution Plan, what top 5 areas would you recommend for DBAs to look at initally. Thanks
A: I'd go so far as to say there's only one thing you need to look at and that is where is the most time and resources consumed in the plan? If you gather the appropriate execution plan data that includes rowsource execution statistics (using gather_plan_statistics or monitor hints for example), then you will be able to quickly find the lines of the plan that are your "big hitters". Once you have that, you must work towards reducing the time and resources consumed by that step. After you fix that, then you can check again and see where the most time is then spent. Keep going until you meet your SLA or can make no further improvement. Admittedly, just finding the "big hitter" steps in the plan is easy, but knowing what to do once you find them is the hard part. For that, I'd suggest learning as much about the optimizer and SQL execution fundamentals as you can so you'll be more adept at knowing what to do when you find a problem.

Tuesday, September 25, 2012

Making Impactful Performance Changes Webinar

Join me on Wednesday, September 26 as I present a webinar entitled "Making Impactful Performance Changes". This is the final segment in the 3-part DBA Performance Series sponsored by Embarcadero. Register even if you can't make it and you'll get an email with the link to the webcast video after it's over.

In this webinar, I'll cover
  • Common ways to rewrite SQL that make it perform better and more consistently
  • How and when to add or modify indexes 
  • How and when the SQL Tuning Advisor helps you and when it doesn't

Hope to see you!

Tuesday, September 4, 2012

Making SQL Performance Solutions Stick - Followup #2

During last week's webinar, there were several questions that I did not have a chance to answer so I wanted to follow-up by answering those questions here. I also wanted to provide links to previous webinar recordings as many people had asked where to find them.

Webinar Recordings
Best Practices for Developing Optimally Performing SQL

Diagnosing SQL Performance Problems (Part 1 of 3 part series)

Making SQL Performance Solutions Stick (Part 2 of 3 part series)

Question 1
The first question I wanted to address was something I provided an answer to but an audience member provided a follow-up that I wanted to comment on after having a chance to do some research.

Q: Is there any way to change the execution plan of an already running SQL? There are some articles on Internet that indicate that possibility by adding DBMS_RLS.ADD_POLICY which will call procedure that is using literals that will force hard parse.

Audience member follow-up: There is an article about using dbms_sqldiag_internal on optimizer blog about changing execution plan of an existing query on the fly.

My answer was that I didn't know of any way to change the execution plan of a query "on the fly" so I first wanted to clarify my understanding of the question. "On the fly" to me means that the questioner wanted to know if the execution plan could be changed while it was executing. My response to this was "no...not that I know of". There are several ways to influence/change the execution plan of a query after it has completed, but while it is executing, I do not know of any way to cause a plan change that would affect the query while it executes. The original questioner mentioned the use of DBMS_RLS.ADD_POLICY and I don't think that would do what was requested. That package/proc is used for Virtual Private Database and typically is called to provide the dynamic predicate needed to properly service VPD queries. The follow-up regarding dbms_sqldiag_internal also does not provide a way to change the execution plan "on the fly" but does offer a way to change the execution plan via a SQL Patch where you could add a hint, for example, that could change the plan the next time that same SQL was executed.

So, I'll stick by my original answer and say that I do not know of any way to change the execution plan for a SQL while it is currently executing. If anyone out there does know of a way to do so, I'd really like to hear from you.

Question 2
Q: Is the plan changing when the running node from clustered environment is evicted and the execution is failed over to another node?

A: On this one, I'll give the default answer of "it depends" as a starting point, but typically, the execution plan should not change. However, there is always a possibility the plan might change. So, I'll err on the side of caution and say that typically, the plan wouldn't change, but there is always a possibility.

Question 3
Q: Question on cardinality feedback-if child cursor is generated due to cardinality feedback for a given sql, will that new cursor be shared between users assuming that users are executing the same sql via application?

A: Yes, a child cursor generated due to cardinality feedback can be shared/used by other users executing the same SQL. The point of cardinality feedback is to be able to adjust row estimates derived in a plan so that they are more accurate. The new child cursor containing the updated cardinality estimates should then take precedence and be used.

Question 4
Q: Will reordering the table with respect to index help the CLUF (clustering factor) to be calculated correctly? Can you explain about it please?

A: Yes...and No. If you had an index on order_date (along with several other indexes on several other columns), and you reordered the table to store it in sorted order by order_date, the CLUF for that one index should certainly improve. However, what about all the other indexes on all the other columns? What happens to the index CLUF for those? The CLUF of those indexes would most likely be impacted as well causing some to get "worse".

Remember, CLUF is used to help weight the cost of using the index. But a table can only be physically stored in one order. The table data sort order can match one index order but it can't perfectly match the order of numerous indexes at the same time. So, the bottom-line is that you typically shouldn't reorder a table to try and get a better CLUF. You will likely adversely affect many more things that you fix in the long run.

Question 5
Q: Do you recommend running the following everyday dbms_stats.gather_schema_stats(ownname => , options =>'GATHER AUTO',estimate_percent => 40, method_opt => 'for all columns size 1', degree => 2, cascade => true ); ownername = sys, system

A: I'll answer this by saying that Oracle, particularly for the latest releases, recommends collecting statistics on dictionary objects (those owned by SYS) regularly as is evidenced by the fact that the automatic stats collection job includes a collection of these objects. So, if you have the automatic job enabled, you will be getting stats collections on these objects if/when needed.

There are specific procedures available for collecting dictionary stats (e.g. GATHER_FIXED_OBJECT_STATS, GATHER_DICTIONARY_STATS) and so I'd be more apt to use those than coming up with my own "home grown" method as you've shown here. However, since I am not familiar with your environment, then I can't say positively if your choice is "good" or "bad". I'd just encourage you to use defaults unless you have evidence you need something else that works better for your particular database application environment.

Question 6
Q: Can there be a negative impact on database performance of gathering stats for currently missing or null stats?

A: Gathering stats can have multiple impacts. First, there is the impact of the collection itself as it must use resources to complete. Second, there is the impact of how new statistics may affect the optimizer's plan choices. Plans could change based on the new statistics that cause performance to improve or decline. Finally, if you have *never* had stats on certain columns/objects, then don't just rush off and collect stats without thoroughly testing your application to discover the impacts. Ultimately, you won't know until you collect and test. Remember that you now have the option of collecting stats without publishing them (i.e. pending stats), so you can actually collect stats and do some specific testing with those stats before you apply them across the board.

Question 7
Q: Where do i check the column level stats?
A: Column stats can be found in the DBA_TAB_COL_STATISTICS view. I have a set of scripts I use to view all the various stats for a specific object and you can download those scripts here.