Re: Rows processes against an Oracle GTT

1.

You can try to find the SQL_ID of the SQL statements inserting data. Once you have the SQL_ID(s) you can look at “row_processed” and “executions” columns for the statement(s) in V$SQL. Those values would be averages, which is not ideal. You can reset the accumulation by flushing the statement.

2. The two options below are not for the faint-hearted:

You can force a hard parse for every SQL that uses the table in question. The hard parse would trigger dynamic sampling, so the resulting execution plan would be build using fresh statistics. This link

http://oracle-randolf.blogspot.com/2009/02/how-to-force-hard-parse.html

is very helpful.

The other option is to gather stats in triggers. You cannot do that with DBMS_STATS because it issues an implicit COMMIT – you need to use JUST_STATS, a package I wrote (http://wp.me/p1DHW2-3B). Use the package at your own risk though.

Hope that helps,

Iordan Iotzov

On Thu, Apr 21, 2016 at 7:46 PM, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:

> I have a few questions about Oracle’s GTT (Global Temporary Tables):
>
> 1. Is there a way to find out how many rows were inserted by a
> session into a GTT that was created with ON COMMIT DELETE ROWS option? We
> have a standard Oracle EBS applications code that is inserting a lot of
> data into a few GTT table and I am trying to find out how many rows were
> inserted by the program.
>
> 2. What is the best way to manage database statistics for GTT? If
> the statistics go away upon commit and the program does not gather them
> during processing then is there a way to seed statistics for the table?
> There are a few statements run by the program that have /*+
> dymanic_sampling(1) */ hint specified but the problem is that if at the
> beginning of the program, the table is empty and the program collects any
> statistics dynamically then statistics will always show up as zero.
>
>
>
> Thanks,
>
> Amir
>
>
>

-- 

This message and its attachments may contain legally privileged or 
confidential information. It is intended solely for the named addressee. If 
you are not the addressee indicated in this message (or responsible for 
delivery of the message to the addressee), you may not copy or deliver this 
message or its attachments to anyone. Rather, you should permanently delete 
this message and its attachments and kindly notify the sender by reply 
e-mail. 

--
http://www.freelists.org/webpage/oracle-l