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.
– Bobby
