Below are answers to questions asked during the recent webinar. If you have additional questions, please contact Janis Griffin at janisgriffin@confio.com
Q: Is it a real problem if db_file_sequential_read and db_file_scattered_read are in my Top 5 Wait Events of a long running query?
A: The correct answer is it depends on the system. Do you have only a few sqls causing all that I/O or are all the statements contributing to it? It may just be a busy system with a lot of active users. That is why Response Time Analysis is so important as it quickly shows you the reponse time your End Users (or end user processes) are experiencing and which sqls are causing it.
Q: We have an application on server A that repeatedly updates an oracle table on server B. The table is not being updated in the requisite time frame. We are troubleshooting to determine if problem is network related or the Oracle instance (i.e.waiting on I/o). What wait events would be relevant to review in our analysis?
A: I would review all the wait events for that application by trying to identify it by program or machine in the v$session table. Typically, if it's a network related issue, you will see SQL*Net waits. I/O waits are 'db file sequential read' (usually index lookup) and 'db file scattered read' (usually a full table scan). You can use the 'wait class' in v$event_name to categorize different wait events such as Network, System IO, User IO, etc...
Q: Any tips on dealing with DBLINKs? (except from staying away from them)
A: In general, I try to minimize access via DBLINKS because they usually do create a performance problem especially as the data set grows. It the data is static, you might consider snapshots (replication) and/or using results cache. That would at least minimize usage of the link. Another thing to consider when using a database link is to tune both instances since you now have 2 optimizers working on the same sql.
Q: Is this "SQL Diagramming" a manual effort or can it be passed with SQL and generate report by some script?
A: Sql diagramming is more of a technique or scientific approach to use when tuning instead of listening to automated tools. Those tools can be wrong but how would you know if you have no other mechanism to check them with?
Q: What tools are available in Ignite/Oracle for tracking the before/after results for tuning efforts?
A: Ignite has powerful trending features that can show you quickly the results of your tuning efforts. You can identify a sql to tune and then set up a report and automatically monitor it as you tune. Often, I'll create a report on all the queries for a certain table, add an index to see if I've helped or hurt performance. As for Oracle, you can look at AWR over time but it's more difficult to view. You also can trace before / after to show the improvements.
Q: What are possible reasons for the high amount of buffer one gets on INSERTs?
A: Foreign keys come to mind. If you are inserting into a table which has foreign keys without indexes, the optimizer may have to read a lot of data to figure out if the parent is there or not.
Q: What is the best way to add an index from a SQL tuning standpint? I mean, If I add a composite Index which covers all columns referenced in the JOIN condition...will that Index be picked up in a different SQL which has only one of those columns.Also, does the Order of columns in a composite Index matter? Does it need to be the same chronological order as in Join conditions?
A: Selectivity of the index is most important. So for a composite key, you would want to put your most selective column as left leading, as the order does make a difference. In my class example, I had a primary key of student_id, class_id, and signup_date. When joining to the class with class_id, the optimizer chose to do a full tablescan and not use the primary key. Why? Because it was more efficient to just go to the table instead of reading the index (full or skip index scan) and then going to the table. To answer that, we had to put another index on the class_id as we already had our most 'selective' student_id column first in our primary key. Other SQLs which have one or more columns as join criteria will probably not use the index if it doesn't have the left leading column or the optimizer may use the 'index skip scan' which will see the other columns in the index.
Q: Have you ever found that using database parameters work for tuning?
A: Depending on the different version of Oracle, I've used some of the optimizer parameters in the past… but this was usually because of bind variable peeking and/or changing the optimizer's behavior. Typically, it's poorly tuned sql that causes the issue.
Q: Do indexes help when you have a ton of Left Outer Joins?
A: Left Outer Joins need to read all of one table regardless so indexes would not help in the join but if there is any further filtering
Q: What is a better way to read Mile long Execution Plans?
A: There is the free graphical tool that is listed in my presentation, http://www.epviewer.bplaced.net/downloads. You can also try the display_plan with type='xml' to get a graphical view. However, I was unable to display it because I couldn't make it work (seemed buggy) so that is why I showed the 'HTML' version.
Q: Can we access Ignite's datastore directly with our own SQL to customize the reporting?
A: If you are customer and have signed a non-disclosure agreement, we will train you on using our data model for reporting purposes. In fact, in the next release we will have reporting views and will be working to add a custom reporting feature into our UI.
