On February 10th I was in Saint Petersburg on a seminar organized by Russian Oracle User Group. Actually, it was mostly prepared by company called Devexperts. Seminar took place in their office, with most presentations done by their people.
- First presentation was called “AWR, ASH – use cases and 11.2 enhancements”. The content was targeted to people who are not very familiar with these facilities. It was good enough and covered (I think) most of new features of the AWR reports such as more OS level data, Wait Event Histogram, aggregate IO statistics (based on V$IOSTAT_FUNCTION_DETAIL), colored SQL and others. After AWR presentation went to what’s new in ASH. Most of it comes to a fact that number of columns in dba_hist_active_sess_history is almost doubled in 220.127.116.11 compared to 10.2.0.5. There were several examples of custom queries to ASH to find what’s missing in averages and how to identify a skew (aka spike). There are references to other good presentations at the end. One presentation that is missing an active link is John Beresniewicz. Practical Active Session History. And here are some random notes on topic of AWR and alike:
- the function dbms_xplan.display_awr to display execution plan stored in AWR is not working correctly – it misses predicates information even in 18.104.22.168. Major gap.
- v$sql is too costly way to find hard queries due to its nature – this is shared cursors structure that needs to be protected by latches/mutexes. v$sqlstats is more suitable for such task, and it has bigger retention time
- there’s a thread on SQL.ru where it is noted that some of the Top N SQL sections of AWR are derived from the Top SQL by Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count. Namely, Top N SQL by Gets, Physical reads and Executions are formed based on the previously selected Top SQLs. This could potentially lead to a situation when SQL is missing in the reports even if it’s, for example, absolute #1 query by buffer gets. This doesn’t sound right. I have no confirmation or denial of that, but I believe in it right now./
- there are, of course more easier way to miss a query in the AWR report – SQL could be loaded and flushed from the shared pool in between of snapshots
- Igor Usoltsev has recently published a note about how AWR snapshot creation could be stopped due to a slow query in 11g caused by a missing fixed statistics. Although I know that there’re indeed cases when fixed objects statistics helps Oracle to build a good plan, I’ve also seen a number of cases where gathering fixed objects statistics lead to other problems. So it’s not a win-win suggestion and have to be carefully tested – and the only way to test it “right” is to do so in production or, if you can reproduce the environment and the load, on its copy.
- If you haven’t seen Doug Burns’ post on a query to find ASH outliers (written by John Beresniewicz), here it is. I haven’t tried it myself (yet).
- Second presentation was “My experience building a custom ETL system“. It was done by a developer who has written custom ETL solution based on Streams & Java. He has even written a custom query language – mix of Java & PL/SQL. Most of the things were developer related so I listened not very carefully (especially since Yandex DBAs sitting next to me recovered one of the production databases at the same time – it was more interesting ). One issue that caught my attention was around Streams Apply process. As I understood it, there’s possibility to get two changes associated with the same point in time (SCN). I’ve never worked with Streams and can’t comment on such topic; a quick MOS search didn’t reveal anything close to this.
- Next was the most interesting presentation – “Exadata: Deployment story“. It talks about real-life Exadata implementation with the issues encountered: more than 1 day deploy time, broken power supply, slow NFS to copy data, RAT bug, and ORA-00600 after upgrade. Since the environment has Streams in place, they used a tricky procedure to guarantee fallback if something goes wrong during upgrade. There’re also some performance metrics of the average waits (which are not representative, you know) and SQL performance. Very nice presentation plus a discussion afterwards on the patching and installing additional software on the DB grid in Exadata.
- Final presentation was “GoldenGate Overview” (it’s mostly in Russian). I can’t add something about it, except it was a good presentation, as usual
The conference was broadcasted for the first time at anymeeting.com. Live stream allowed me to ask questions to presenters from those who followed online and make several screenshots to twit them. It was positive experience with some (I think minor) sound issues. I hope it will become a standard thing and more people could virtually attend such events.
I’d like to say big thanks to people who organized this – it was a pleasure to be there!
PS. I was in a book store for the first time in many years and found three books on Oracle on the book shelf. Don’t know if it’s right or wrong.
PPS. If you want to see St Petersburg beauties online, have a look at Yandex maps for panoramic pictures taken from the air.
Filed under: Oracle Tagged: ASH, AWR, Exadata, RuOUG