An introduction to collectl

Some of you may have seen on twitter that I was working on understanding collectl. So why did I start with this? First of all, I was after a tool that records a lot of information on a Linux box. It can also play information back, but this is out of scope of this introduction.

In the past I have used nmon to do similar things, and still love it for what it does. Especially in conjunction with the nmon-analyzer, an Excel plug in it can create very impressive reports. How does collectl compare?

Getting collectl

Getting collectl is quite easy-get it from sourceforge: http://sourceforge.net/projects/collectl/

The project website including very good documentation is available from sourceforge as well, but uses a slightly different URL: http://collectl.sourceforge.net/

I suggest you get the archive-independent RPM and install it on your system. This is all you need to get started! The impatient could type “collectl” at the command prompt now to get some information. Let’s have a look at the output:

$ collectl
waiting for 1 second sample...
#<--------CPU--------><----------Disks-----------><----------Network---------->
#cpu sys inter  ctxsw KBRead  Reads KBWrit Writes   KBIn  PktIn  KBOut  PktOut
1   0  1163  10496    113     14     18      4      8     55      5      19
0   0  1046  10544      0      0      2      3    164    195     30      60
0   0  1279  10603    144      9    746    148     20     67     11      19
3   0  1168  10615    144      9    414     69     14     69      5      20
1   0  1121  10416    362     28    225     19     11     71      8      35
Ouch!

The “ouch” has been caused by my CTRL-c to stop the execution.

Collectl is organised to work by subsystems, the standard option is to print CPU, disk and network subsystem, aggregated.

Advertisement

Selectively purging the shared pool

Another very useful thread has developed on the oracle-l mailing list. On a side note, this is really _the_ list to be subscribed to-there are so many experienced Oracle DBAs out that it’s pure joy.

But I digress, back to the problem I often face when performance troubleshooting systems is to purge a SQL statement from the shared pool. Most often, bind variable peeking in conjunction with an empty partition (or otherwise inappropriate stats) cause the optimiser to choose a suboptimal plan for subsequent executions of the same SQL Statement.

Oracle 11.1 extended DBMS_SHARED_POOL to include a “purge” function which can be used to manage the shared pool. It’s actually a lot more powerful than just purging SQL statements but that’s for another blog entry :)

Prior to the availability of the new package I usually used the “comment on table <owner>.<table_name> is '<some comment>'” trick to force an invalidation of the statement in the cursor cache. The next time the statement is executed (after the statistics are updated/corrected!) the optimiser should pick the new (corrected & better) statistics up and produce a better execution plan.

Kerry Osborne, a great source for inspiration has posted a great example for using DBMS_SHARED_POOL.purge(). The following is shamelessly taken from the mailing list, I won’t take any credit for it (but I’ll use it whenever I can!). The code has been updated on 221219 to enforce Oracle 11.1 and later and was tested inside an Oracle 21c Pluggable Database (PDB). At the time of writing the update for the post you really should be on Oracle 19c.

Here’s the script I use (because I don’t like the funky “address,hash_value” format that the purge procedure requires:

--flush_sql.sql

accept l_sql_id prompt 'enter a SQL ID to be purged from the shared pool: ' 

DECLARE
    l_version    VARCHAR2(50);
    l_compatible VARCHAR2(50);
    l_name       VARCHAR2(255);
BEGIN

    -- sanity checking, the script requires Oracle 11.1 or later
    dbms_utility.db_version(
        l_version,
        l_compatible
    );

    IF TO_NUMBER ( regexp_substr(l_compatible, '[0-9]+') ) <= 11 THEN
        raise_application_error(
            -20001,
            'this script requires *.compatible to be set to at least Oracle 11.1'
        );
    END IF;

    BEGIN
        SELECT
            address
            || ','
            || hash_value
        INTO l_name
        FROM
            v$sqlarea
        WHERE
            sql_id LIKE '&l_sql_id';
    EXCEPTION
        WHEN no_data_found THEN
            raise_application_error(
               -20002,
               'No cursor found in the shared pool for SQL ID &l_sql_id'
            );
    END;

    sys.dbms_shared_pool.purge(
        name  => l_name,
        flag  => 'C',
        heaps => 1
    );
END;
/

Credit where credit is due:

Kerry Osborne, Enkitec (at the time this blog post was written)