Re: Table acess and growth

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Tue, 06 May 2003 17:11:18 -0700
Message-ID: <GaYta.24770$MJ5.8656_at_fed1read03>


Niall Litchfield wrote:
> "Ana C. Dent" <anacedent_at_hotmail.com> wrote in message
> news:MbDsa.601$MJ5.320_at_fed1read03...
>

>>Take a look at V$TRANSACTION where execution counts are stored.

>
>
> No they aren't. From our finance system
> Connected to:
> Oracle8i Release 8.1.7.3.0 - Production
> JServer Release 8.1.7.3.0 - Production
>
> SQL> desc v$transaction;
> Name Null? Type
> ----------------------------------------- -------- -------------------
> ADDR RAW(4)
> XIDUSN NUMBER
> XIDSLOT NUMBER
> XIDSQN NUMBER
> UBAFIL NUMBER
> UBABLK NUMBER
> UBASQN NUMBER
> UBAREC NUMBER
> STATUS VARCHAR2(16)
> START_TIME VARCHAR2(20)
> START_SCNB NUMBER
> START_SCNW NUMBER
> START_UEXT NUMBER
> START_UBAFIL NUMBER
> START_UBABLK NUMBER
> START_UBASQN NUMBER
> START_UBAREC NUMBER
> SES_ADDR RAW(4)
> FLAG NUMBER
> SPACE VARCHAR2(3)
> RECURSIVE VARCHAR2(3)
> NOUNDO VARCHAR2(3)
> PTX VARCHAR2(3)
> PRV_XIDUSN NUMBER
> PRV_XIDSLT NUMBER
> PRV_XIDSQN NUMBER
> PTX_XIDUSN NUMBER
> PTX_XIDSLT NUMBER
> PTX_XIDSQN NUMBER
> DSCN-B NUMBER
> DSCN-W NUMBER
> USED_UBLK NUMBER
> USED_UREC NUMBER
> LOG_IO NUMBER
> PHY_IO NUMBER
> CR_GET NUMBER
> CR_CHANGE NUMBER
>
> SQL> select count(*)
> 2 from v$transaction;
>
> COUNT(*)
> ----------
> 0
>
> we may be public sector but we don't in fact do nothing. V$transaction shows
> details for currently running transactions. For the object sizing questions
> as Daniel says you can collect data periodically, for example one report
> that people here like is the one below which gives data about number of
> records in a table. You could equally collect size or extent information in
> a similar way.
>
> *****
> set serveroutput on size 1000000
>
> DECLARE
>
> /*
>
> Row count procedure
>
> Niall Litchfield 12/08/2002
>
> modify v_limit to change the cut off point
>
> below which row counts are not given.
>
> */
>
> v_rowcount integer;
>
> v_limit integer := -1;
>
> BEGIN
>
> For v in (select table_name from user_tables order by table_name) loop
>
> EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||v.table_name into v_rowcount;
>
> if v_rowcount > v_limit then
>
> DBMS_OUTPUT.PUT_LINE(rpad(v.table_name,55,'
> ')||to_char(v_rowcount,'99999999')||' record(s)');
>
> end if;
>
> end loop;
>
> END;
>
> /
>
> ******
>
>
>>If you have a decent amount of available disk space,
>>you can enable SQL TRACE at the instance level & collect trace
>>files for a day. Then with tkprof, you can get EXPLAIN PLANS
>>for every SQL statement.

>
>
>
> A better approach for collecting information about sql statements would
> generally be STATSPACK but I think that the OP is limited to
> UTLBSTAT/UTLESTAT on 806.
>
>

I gues it matters how the OP question is interpreted. "list the tables that get accessed the maximum number of times" Does this mean the number of SQL statements where the table is in the FROM clause?

Does it mean the table which has max(PHY_IO+LOG_IO)?

Now it is not clear to me which metric is supposed to be measured. Received on Wed May 07 2003 - 02:11:18 CEST

Original text of this message