Mining AWR data with Method-GAPP, profiling response time of end-user processes

In a lot of cases you like to know which SQL, wait-events, metrics, etc. in AWR is important for your specific end-user process response time. So it could be very well possible that the most important SQL, wait-events, metrics, etc. are show-in up in your “Top Activity” in your OEM grid control and AWR reports are actually not the most important for your end-user process response time.

After you know the share of time of your end-user process is taken by the database server (Method-GAPP primary components),  you actual can use all the AWR (and ASH) information as secondary components as input in Method-GAPP (see the white paper). Basically we simply can use the “Data Mining – Explain” step in the method and create a factorial analyses as shown below (see the white paper).

AWR data used with Method-GAPP

AWR data used with Method-GAPP

Continue reading

Back to HOTSOS, HOTSOS 2010 Day 1

As always Hotsos started off with a nice keynote, this time done by Tom Kyte. Tom Kyte was introduced by Hotsos president Gary Goodman after the HOTSOS 2010 opening. Tom’s keynote theme was “Should we be less smart some times”. Tom told about own experiences, that he in the past gave sometimes too fast an answer. It is very important to think about an answer before giving it… Why? Well some things applied in the past or for a specific version, and now they don’t anymore… this can be a problem, a real issue. Always make sure you talk about the same definitions, and agree on them. Make sure talking about the same version and of course about similar circumstances. When you start giving answers in general be sure to work with facts and not some assumptions which might be wrong. So you should always think about the information, about the circumstances and the assumptions you do, it means “Continuous Thinking”.

Continue reading

ORA-01422 while drop table (even DUAL looks fine)

Today on the OTN forum a question was comming up about an ORA-01422, in combination of a table drop.

SQL> drop table xxx
2 /
drop table xxx
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows

Due to the fact that this looked a bit weird, I did a small test on my database. The following test case I did: Continue reading

ORA-25330 is encountered when running DBMS_PREDICTIVE_ANALYTICS.PREDICT

I recently discovered that under certain circumstances, for now it looks to be environment setting depended, that an ORA-25330 is encountered when running dbms_predictive_analytics.predict procedure. I found out of this problem while preparing a demo and certainly the error was comming up in TOAD (I am not a TOAD fan, but it is handy searching through the very big amount of factor columns in a GAPP analysis). When the same command below was executed directly on the server via sqlplus, the error was not encountered.

To investigate I tried to create a errorstack on the error, but without luck. So I started to do a sqltrace and found out that the ORA-25330 is actually on the server translated as ORA-40206. After I found that I created the following small test in TOAD: Continue reading