Hi All,

Three node cluster on Enterprise 10.2.0.4 SLES 10 64 bit

Can anyone tell me why the following may full scan the partition being
truncated?

SQL> select target,totalwork,sofar,time_remaining,opname,sql_i d from gv
$session_longops where time_remaining > 0
2 /

TARGET TOTALWORK SOFAR TIME_REMAINING
------------------------------ ---------- ---------- --------------
OPNAME SQL_ID
------------------------------ -------------
XWC.XWCMD_XML_DATA_JOURNAL2 3228739 358853 10381
Table Scan f2zrnjvk9usv9

SQL> select sql_text from gv$sql where sql_id = 'f2zrnjvk9usv9';

SQL_TEXT
--------------------------------------------------------------------------------
alter table xwc.xwcmd_xml_data_journal2 truncate partition p01

SQL> select locality,index_name from dba_part_indexes where owner =
'XWC' and table_name = 'XWCMD_XML_DATA_JOURNAL2';

LOCALI INDEX_NAME
------ ------------------------------
LOCAL SYS_IL0000268664C00008$$
LOCAL XWCMDXMLDATAJOURNAL2_IDX1
LOCAL XWCMDXMLDATAJOURNAL2_IDX2
LOCAL XWCMDXMLDATAJOURNAL2_IDX3

SQL> select distinct constraint_type from dba_constraints where owner
= 'XWC' and table_name = 'XWCMD_XML_DATA_JOURNAL2';

C
-
C

SQL> select blocks from dba_segments where owner = 'XWC' and
segment_name = 'XWCMD_XML_DATA_JOURNAL2' and partition_name = 'P01';

BLOCKS
----------
3232384



As you can see, the number of blocks is very close to what is
estimated it will require to truncate this partition. Also as shown,
all indexes are LOCAL, and there are no constraints either against or
on the table.

This is the "lowest" partition time wise (partitioned by month) and
there is a LOB that can be stored out of row. This occurs about 60% of
the time. However, the block and file number in the p1/p2 parameters
are always the table.

Thanks,

Steve

On Mar 7, 2:56*pm, Steve Howard <stevedhow... (AT) gmail (DOT) com> wrote:
Actually I can't see from your output that there are no global indexes
since you show us only the output from a query against
DBA_PART_INDEXES which by definition covers only partitioned indexes.

So if there was a global index and your output from GV$SQL was
truncated and the statement executed actually contained a "UPDATE
[GLOBAL] INDEXES" then this would explain a read from the partition
segment as part of the truncate to maintain the global index.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-.../dp/1430226684

On Mar 8, 7:48*am, Randolf Geist <mah... (AT) web (DOT) de> wrote:
Thanks, Randolf, sorry for the omission. However, we definitely don't
have any global indexes as shown in dba_indexes...

SQL> select index_name,partitioned from dba_indexes where owner =
'XWC' and table_name = 'XWCMD_XML_DATA_JOURNAL2';

INDEX_NAME PAR
------------------------------ ---
SYS_IL0000268664C00008$$ YES
XWCMDXMLDATAJOURNAL2_IDX1 YES
XWCMDXMLDATAJOURNAL2_IDX2 YES
XWCMDXMLDATAJOURNAL2_IDX3 YES

SQL>

On Mar 8, 9:17*am, Steve Howard <stevedhow... (AT) gmail (DOT) com> wrote:
What is interesting is that I can create an empty table and exchange
it with the partition I am trying to truncate, and that returns almost
instantly (less than three seconds). Also, I tried a straight drop
partition and that also full scans it.

I also didn't have an update global indexes clause on the truncate or
the drop.

On Mar 8, 3:17*pm, Steve Howard <stevedhow... (AT) gmail (DOT) com> wrote:
Since the table name suggests some XML related content - is any of
these indexes by any chance a (partitioned) domain index rather than a
usual b*tree index? Since partitioned domain indexes are "local" I
still don't see why they should need a partition scan when truncating/
dropping however that can depend on the domain index type
implementation.

What happens if you request the EXCHANGE PARTITION to include indexes?

Since you seem to have the time for investigation, an extended SQL
trace might give you a clue what is happening at truncate / drop
partition time.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-.../dp/1430226684

On Mar 8, 6:20*am, Steve Howard <stevedhow... (AT) gmail (DOT) com> wrote:
Maybe some internal shredding winds up with some nesting, similar to
the explanation in bug 5007945 but without actually hitting a bug. I
have no idea why this wouldn't happen every time if that's the case,
though. It would have to be something special about the data, I guess
- is there something optional in your data that shreds differently?
Any time there's a bug or not-a-bug, there might be related odd
effects.

jg
--
@home.com is bogus.
http://www.nationmultimedia.com/2011...-30150343.html

On Mar 8, 12:26*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:
Thanks, Joel and Randolf. Nope, it's a very simple table, although
fairly large. It is 2.3TB, partitioned by month over a year.

We have the indexes I showed earlier, nothing fancy like domain
indexes or anything. It is a journal table for sotring previous
changes to a large XMLTYPE column.

I did issue a 10046 trace right off the bat, but nothing was odd, it
just started issuing calls which resulted in db file scattered read
events against the table partition.

I think for now we will just issue the partition exchange including
indexes, as this gets us where we need to be.

Thanks again,

Steve