Parse time by day of the week

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

 

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply