You are not understanding what prepare_cached does. It has nothing to do with caching actual results from queries. Rather it is a cache of statement handles, because the work of creating a statement handle and the corresponsing database work for it is relatively slow with databases like Oracle.

I'm surprised that you found it to be slower than normal prepare, since I have always found it to be much faster. I'm guessing that either your hit rate for doing the same query again is very low (you are using bind variables, aren't you?), or you have a bug in your code somewhere. The additional size might have something to do with the AS400 DBD driver. I've never seen that happen with Oracle or MySQL.

FYI, when you're curious about what DBI is doing, it's pretty easy to just look at the code for things like this. Here's the complete code for prepare_cached:

sub prepare_cached {
    my ($dbh, $statement, $attr, $allow_active) = @_;
    # Needs support at dbh level to clear cache before complaining abo
+ut
    # active children. The XS template code does this. Drivers not usi
+ng
    # the template must handle clearing the cache themselves.
    my $cache = $dbh->FETCH('CachedKids');
    $dbh->STORE('CachedKids', $cache = {}) unless $cache;
    my @attr_keys = ($attr) ? sort keys %$attr : ();
    my $key = ($attr) ? join("~~", $statement, @attr_keys, @{$attr}{@a
+ttr_keys}) : $statement;
    my $sth = $cache->{$key};
    if ($sth) {
        if ($sth->FETCH('Active') && ($allow_active||0) != 2) {
        Carp::carp("prepare_cached($statement) statement handle $sth w
+as still active")
            if !$allow_active;
        $sth->finish;
        }
        return $sth;
    }
    $sth = $dbh->prepare($statement, $attr);
    $cache->{$key} = $sth if $sth;
    return $sth;
    }
[download]