![]() If its not right way to do it ,please correct I am running below sql to find sql information which took more than 1 hour to execute However, I am seeing some big differences. I know you mention in the Effective Oracle book that there will be some small difference due to the underlying data etc. Does this mean that even though I am not running multithreaded or parallel, a query can take up more than one CPU and hence the elapsed time appear to be smaller. I read an article on My Support 1052756.6 which states that the load balancing between processors is handled by the Scheduler and not Oracle. When I run the query below it returns details as displayed.Īs you can see there are quite a few that has a big difference between CPU and Elapsed. We are running with 11.1, 9CPUs, and no parallel.We have 100's of concurrent users running the same queries. NUMBER_OF_SNAPSHOTS BUFFER_GETS ROWS_PROCESSED ELAPSED_TIME_HOURS SP2-0642: SQL*Plus internal error state 1075, context 1:5:4294967295Ģ0316104 bytes received via SQL*Net from clientġ846756 SQL*Net roundtrips to/from clientĤ sum(rows_processed_delta) rows_processed,ĥ round(sum(elapsed_time_delta)/1000000/3600) elapsed_time_hours Why is elapsed_time is showing 961 hours (vs. As you can see, buffer gets are about the same, rows_processed are about the same, however elapsed_time is showing 961 hours (vs. However, it looks like elapsed_time_delta does not match what is reported by AUTOTRACE in sqlplus. I am trying to capture runtime statistics for most (whatever is in dba_hist_* views) queries. Most of the queries are ad-hoc and use parallel query. We are running a data warehouse on 10.2.0.3 on a 32-cpu server. Ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(x) Ops$tkyte%ORA10GR2> select sql_id, executions from v$sql where sql_text like 'SELECT%COUNT(*)%FROM%T%LOOK_FOR_ME%' Ops$tkyte%ORA10GR2> create table t as select rownum x from dual connect by level Ħ select count(*) into l_n from t LOOK_FOR_ME I'm using Oracle 10.2.0.3 on WinXP SP2 with standard AWR parameters.Īnything that causes the sql to age out - eg, here I added a primary key, it invalidated the sql, the new hard parse starts everything over again. The database wasn't restarted : same startup_time. Here stats for this SQL_ID were reset after snap 432 and the Total column restart counting. My question is on Total columns : Why and when Total is reset ?ĬPU_TIME_TOTAL*POWER(10,-6) "CPU_TIME_TOTAL" ![]() As you said, this table contains 2 kind of stats : Total and Delta. ![]() I'm using the DBA_HIST_SQLSTAT in order to highlight poor SQL code/parameter. Select sid,program,a.module,sql_fulltext, Would you mind pointing out what am I missing? For example the sql on a sid working since last 30 minutes is being reported as 16 hours. But something seems to be incorrect still. Based on this thread, I came up with the following sql to track slow running sqls.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |