There is more and more happening in the world of visualization and visualizing Oracle performance specifically with v$active_session_history. Of these visualizations,  the one pushing the envelope the most is Marcin Przepiorowski. Marcin is responsible for writing S-ASH , ie Simulated ASH versions 2.1,2.2 and 2.3. See http://oracleprof.blogspot.com/2011/11/new-release-of-s-ash-v23.html https://github.com/pioro/orasash Here are some examples of what I have seen happening out there in the web with these visualizations grouped by the visualization tool. Gephi The first example is using Gephi. The coolest example of Gephi I've seen is Greg Rahn's analysis of the voting for Oracle World mix sessions.  Here is Marcin's example using Gephi with ASH data: JIT ...

Kyle Hailey
Uncategorized

  Database is getting high waits on buffer busy waits. Here is an example period where 5 sessions are completely blocked on buffer busy waits for 4 minutes: select to_char(min(sample_time),'HH24:MI') maxst, to_char(max(sample_time),'HH24:MI') minst, count(*), session_id, ash.p1, ash.p2, ash.SQL_ID, blocking_session bsid from DBA_HIST_ACTIVE_SESS_HISTORY ash where event='buffer busy waits' ...

Kyle Hailey
Uncategorized

First look at the load on the database (see https://sites.google.com/site/oraclemonitor/dba_hist_active_sess_history https://sites.google.com/site/oraclemonitor/ ) Def v_secs=3600 -- bucket size Def v_days=1 -- total time analyze Def v_bars=5 -- size of one AAS in characters Def v_graph=80 col aveact format 999.99 col graph format a80 col fpct format 9.99 col spct format 9.99 col tpct format 9.99 col aas format 9.99 col pct1 format 999 col pct2 format 999 col first format a15 col second format a15 Def p_value=4 select to_char(start_time,'DD HH24:MI'), --samples, --total, --waits, --cpu, ...

Kyle Hailey
Uncategorized

OK,  so wordpress got hacked. I've had problems with this in the past and tried tactical surgery, but this time decided to do a full re-install. My first attempt today left me with the wordpress blank screen of death, so here I'm outlining the steps I took that finally got the new version working: back up wordpress database  http://codex.wordpress.org/WordPress_Backups#Simple_Backup # go onto hosting box and downloaded new workdpress : # there are better ways to download wordpress than zip, but this worked for me lynx -source -dump ...

Kyle Hailey
Uncategorized

UPDATE: thanks to Alex Gorbachev for pointing out that Oracle has a public yum for OEL which is compatible with REHEL and Oracle has Oracle certified packages: Oracle Certified packages: http://oss.oracle.com/el5/oracle-validated/ http://www.oracle.com/technetwork/articles/servers-storage-admin/extractingfilesrpm-444871.html Public Yum: http://public-yum.oracle.com/ A small but time consuming LINUX issue: When installing on RHEL for both 10 and 11, I get errors that the following  libraries are missing The message is a little misleading as both the i386 and x86_64 versions of libaio have to be intalled libaio-0.3.106-3.2.i386 libaio-0.3.106-3.2.x86_64 libaio-devel-0.3.106-3.2.i386 libaio-devel-0.3.106-3.2.x86_64 sysstat-7.0.2-1.SEL5_2.i386 The last time I installed Oracle on LINUX was almost a year ago and it took me several hours to track this down.  Since then that test machine has been ...

Kyle Hailey
Uncategorized

Back in http://dboptimizer.com/2010/07/26/oracle-10053-sql-trace-viewer/ I mentioned an Oracle 10053 trace file viewer. Well now there is another one with even more options and it's free. Check out http://www.lab128.com/free_downloads.html from the same person who created lab128 the fastest Oracle database monitor in the West (or in the world for that matter, AFAIK). Here is an example screen shot:

Kyle Hailey
Uncategorized

  I sometimes forget which versions of ASH have which fields, so here is a bit of a graphic and textual cheat sheet. (see https://sites.google.com/site/oraclemonitor/dba_hist_active_sess_history for some queries on ASH)     10.2.0 10.2.0.3 11.1.0 11.2.0 (http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/dynviews_1007.htm#I1030299) SAMPLE_IDSAMPLE_IDSAMPLE_IDSAMPLE_ID SAMPLE_TIMESAMPLE_TIMESAMPLE_TIMESAMPLE_TIME SESSION_IDSESSION_IDSESSION_IDSESSION_ID SESSION_SERIAL#SESSION_SERIAL#SESSION_SERIAL#SESSION_SERIAL# SESSION_TYPESESSION_TYPESESSION_TYPESESSION_TYPE USER_IDUSER_IDUSER_IDUSER_ID QC_INSTANCE_IDQC_INSTANCE_IDQC_INSTANCE_IDQC_INSTANCE_ID QC_SESSION_IDQC_SESSION_IDQC_SESSION_IDQC_SESSION_ID SERVICE_HASHSERVICE_HASHSERVICE_HASHSERVICE_HASH PROGRAMPROGRAMPROGRAMPROGRAM MODULEMODULEMODULEMODULE ACTIONACTIONACTIONACTION CLIENT_IDCLIENT_IDCLIENT_IDCLIENT_ID QC_SESSION_SERIAL#QC_SESSION_SERIAL# CONSUMER_GROUP_IDCONSUMER_GROUP_ID MACHINE PORT SQL_IDSQL_IDSQL_IDSQL_ID SQL_CHILD_NUMBERSQL_CHILD_NUMBERSQL_CHILD_NUMBERSQL_CHILD_NUMBER SQL_OPCODESQL_OPCODESQL_OPCODESQL_OPCODE SQL_PLAN_HASH_VALUESQL_PLAN_HASH_VALUESQL_PLAN_HASH_VALUESQL_PLAN_HASH_VALUE XIDXIDXIDXID FORCE_MATCHING_SIGNATUREFORCE_MATCHING_SIGNATUREFORCE_MATCHING_SIGNATUREFORCE_MATCHING_SIGNATURE PLSQL_ENTRY_OBJECT_IDPLSQL_ENTRY_OBJECT_IDPLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_IDPLSQL_ENTRY_SUBPROGRAM_IDPLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_IDPLSQL_OBJECT_IDPLSQL_OBJECT_ID PLSQL_SUBPROGRAM_IDPLSQL_SUBPROGRAM_IDPLSQL_SUBPROGRAM_ID TOP_LEVEL_SQL_IDTOP_LEVEL_SQL_ID TOP_LEVEL_SQL_OPCODETOP_LEVEL_SQL_OPCODE SQL_PLAN_LINE_IDSQL_PLAN_LINE_ID SQL_PLAN_OPERATIONSQL_PLAN_OPERATION SQL_PLAN_OPTIONSSQL_PLAN_OPTIONS SQL_EXEC_IDSQL_EXEC_ID SQL_EXEC_STARTSQL_EXEC_START IN_CONNECTION_MGMTIN_CONNECTION_MGMT IN_PARSEIN_PARSE IN_HARD_PARSEIN_HARD_PARSE IN_SQL_EXECUTIONIN_SQL_EXECUTION IN_PLSQL_EXECUTIONIN_PLSQL_EXECUTION IN_PLSQL_RPCIN_PLSQL_RPC IN_PLSQL_COMPILATIONIN_PLSQL_COMPILATION IN_JAVA_EXECUTIONIN_JAVA_EXECUTION IN_BINDIN_BIND IN_CURSOR_CLOSEIN_CURSOR_CLOSE IN_SEQUENCE_LOAD TOP_LEVEL_CALL# TOP_LEVEL_CALL_NAME IS_SQLID_CURRENT SQL_OPNAME EVENTEVENTEVENTEVENT EVENT_IDEVENT_IDEVENT_IDEVENT_ID EVENT#EVENT#EVENT#EVENT# SEQ#SEQ#SEQ#SEQ# P1P1P1P1 P2P2P2P2 P3P3P3P3 WAIT_TIMEWAIT_TIMEWAIT_TIMEWAIT_TIME SESSION_STATESESSION_STATESESSION_STATESESSION_STATE TIME_WAITEDTIME_WAITEDTIME_WAITEDTIME_WAITED CURRENT_OBJ#CURRENT_OBJ#CURRENT_OBJ#CURRENT_OBJ# CURRENT_FILE#CURRENT_FILE#CURRENT_FILE#CURRENT_FILE# CURRENT_BLOCK#CURRENT_BLOCK#CURRENT_BLOCK#CURRENT_BLOCK# P1TEXTP1TEXTP1TEXTP1TEXT P2TEXTP2TEXTP2TEXTP2TEXT P3TEXTP3TEXTP3TEXTP3TEXT WAIT_CLASSWAIT_CLASSWAIT_CLASSWAIT_CLASS WAIT_CLASS_IDWAIT_CLASS_IDWAIT_CLASS_IDWAIT_CLASS_ID CURRENT_ROW#CURRENT_ROW# TM_DELTA_TIME TM_DELTA_CPU_TIME TM_DELTA_DB_TIME DELTA_TIME DELTA_READ_IO_REQUESTS DELTA_WRITE_IO_REQUESTS DELTA_READ_IO_BYTES DELTA_WRITE_IO_BYTES DELTA_INTERCONNECT_IO_BYTES PGA_ALLOCATED TEMP_SPACE_ALLOCATE BLOCKING_SESSIONBLOCKING_SESSIONBLOCKING_SESSION_STATUSBLOCKING_SESSION_STATUS BLOCKING_SESSION_STATUSBLOCKING_SESSION_STATUSBLOCKING_SESSIONBLOCKING_SESSION BLOCKING_SESSION_SERIAL#BLOCKING_SESSION_SERIAL#BLOCKING_SESSION_SERIAL#BLOCKING_SESSION_SERIAL# BLOCKING_INST_ID BLOCKING_HANGCHAIN_INFO FLAGSFLAGS REMOTE_INSTANCE#REMOTE_INSTANCE# TIME_MODEL IS_AWR_SAMPLE ECID CAPTURE_OVERHEAD REPLAY_OVERHEAD IS_CAPTURED IS_REPLAYED 10.1 SAMPLE_ID SAMPLE_TIME SESSION_ID SESSION_SERIAL# SESSION_TYPE USER_ID QC_SESSION_ID QC_INSTANCE_ID SERVICE_HASH PROGRAM MODULE ACTION CLIENT_ID SQL_ID SQL_CHILD_NUMBER SQL_OPCODE SQL_PLAN_HASH_VALUE EVENT EVENT_ID EVENT# SEQ# P1 P2 P3 WAIT_TIME SESSION_STATE TIME_WAITED CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#

Kyle Hailey
Uncategorized

Structure of a dtrace script #!/usr/sbin/dtrace -s something_to_trace / filters / { actions } Something_else_to_trace / filters_optional / { take some actions } The script has sections that fire if the specified probe fires in the OS. For example, if  do a  send over TCP then my "something_to_trace" could be a probe (an event) called "tcp:::send" . I could further filter by receiving machine's IP address. Then when a packet is sent over TCP and the receiver is the IP in the filter I can take some actions like state the size of the packet. What can I trace?  What are the possible "something_to_trace", ie the probes? To get a ...

Kyle Hailey
Uncategorized

At UKOUG someone asked me if DB Optimizer's VST diagrams could deal with left deep verses right deep execution plans. What is right deep verses left deep? Good question. In join trees (not VST) the object on the left is acted upon first then the object on the right.  Below are left deep and right deep examples of the same query, showing query text join tree join tree  modified to more clearly show actions VST showing the same actions All  of this boils down to the point that a right deep HJ can return rows earlier than a left deep HJ. A left deep HJ ...

Kyle Hailey
Uncategorized

People have been asking me recently, "what is the best enterprise database monitoring software?" Of course for Oracle there is OEM but what if OEM doesn't find the problem? What if OEM breaks? ( I've blog before on how OEM can break and all the kings men don't seem to be able to make him work again) What if one wants to access the performance data but the database is down? (Grid control only has an anemic modified and transformed extract from AWR). What if only DBAs have access to OEM and the developers want access to performance information and not ...

Kyle Hailey
Uncategorized