I wanted to find out if queries against a particular table were experiencing parse time during the week. We had previously seen issues where queries on the table would be stuck parsing for tens of minutes before they started actually running. I think this has been resolved but I’m looking for some validation from ASH. I know that on Sundays we recompress some subpartitions for the table and this leads to higher parse times if the users run queries then, but I’m not trying to resolve that at this time. I just care about the other days of the week. So, here is the query I used:
select to_char(sample_time,'DAY') sample_day, IN_PARSE, count(*)*10 total_seconds from DBA_HIST_ACTIVE_SESS_HISTORY a,sqlids s where a.sql_id= s.sql_id group by to_char(sample_time,'DAY'),in_parse order by to_char(sample_time,'DAY'),in_parse;
The table “sqlids” has a list of all the SQL statements that refer to the table. I populated sqlids like this:
create table sqlids as select SQL_ID FROM DBA_HIST_SQLTEXT where 1=2; DECLARE CURSOR SQL_CURSOR IS SELECT DISTINCT SQL_ID, DBID FROM DBA_HIST_SQLSTAT; SQL_REC SQL_CURSOR%ROWTYPE; CURSOR TEXT_CURSOR(SQL_ID_ARGUMENT VARCHAR2,DBID_ARGUMENT NUMBER) IS SELECT SQL_ID FROM DBA_HIST_SQLTEXT WHERE SQL_TEXT like '%MYTABLE%' and SQL_ID = SQL_ID_ARGUMENT and DBID = DBID_ARGUMENT; TEXT_REC TEXT_CURSOR%ROWTYPE; BEGIN OPEN SQL_CURSOR; LOOP FETCH SQL_CURSOR INTO SQL_REC; EXIT WHEN SQL_CURSOR%NOTFOUND; OPEN TEXT_CURSOR(SQL_REC.SQL_ID,SQL_REC.DBID); LOOP FETCH TEXT_CURSOR INTO TEXT_REC; EXIT WHEN TEXT_CURSOR%NOTFOUND; insert into sqlids values (TEXT_REC.SQL_ID); commit; END LOOP; CLOSE TEXT_CURSOR; END LOOP; CLOSE SQL_CURSOR; END; /
Here is the output of the query I mentioned earlier with the parse time in seconds by day of the week:
SAMPLE_DA I TOTAL_SECONDS --------- - ------------- FRIDAY N 1092220 FRIDAY Y 281980 MONDAY N 2158620 MONDAY Y 77860 SATURDAY N 1483420 SATURDAY Y 259680 SUNDAY N 18939770 SUNDAY Y 65665540 THURSDAY N 1180780 THURSDAY Y 300800 TUESDAY N 1286980 TUESDAY Y 108510 WEDNESDAY N 1399100 WEDNESDAY Y 306890
As I hoped Sunday is the only day of the week that parse time exceeds non-parse time for the sql statements against the given table. I’m still validating this in other ways – most importantly by trying to contact the users to see if they perceive the problem to be gone.
But, I thought I’d pass along the query in case others find it useful.