Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Optimizing inline view

Re: Optimizing inline view

From: Mike C <michaeljc70_at_hotmail.com>
Date: 7 Nov 2006 14:14:32 -0800
Message-ID: <1162937672.229871.12940@b28g2000cwb.googlegroups.com>

Charles Hooper wrote:
> Comments inline:
>
> Mike C wrote:
> > Here you go. Hopefully you are viewing with a font that makes it look
> > readable.
> >
> >
> > OBJECT_NAME
> > OBJECT_TYPE
> >
> > --------------------------------------------------------------------------------------------------------------------------------
> > ------------------
> > NT_CONDITION_MODIFIER
> > TABLE
> >
> > NT_LINK
> > TABLE
> >
> > NT_DATE_TIME_MODIFIER
> > TABLE
> >
> >
> > 3 rows selected
>
> You were having problems when you included the inline view, which
> contained a reference to TMP_LINKS, but that object is not listed
> above. This may be a clue. Can you verify that the SQL statement that
> you executed against DBA_OBJECTS looks for an object named TMP_LINKS (I
> might have typed the name wrong)?
>
> >
> >
> > TABLE_NAME NUM_ROWS BLOCKS
> > LAST_ANALYZED
> > ------------------------------ ----------------------
> > ---------------------- -------------------------
> > NT_CONDITION_MODIFIER 3161690 9616
> > 12-SEP-06
> > NT_DATE_TIME_MODIFIER 90634 700
> > 28-OCT-06
> > NT_LINK 27836040 310309
> > 13-SEP-06
> >
> > 3 rows selected
>
> TMP_LINKS is missing from this list also. If these tables have had a
> lot of inserts, updates, or deletes, you might want to analyze the
> tables and indexes - that may correct the costs seen in the plan.
>
> > NAME VALUE
> > db_file_multiblock_read_count 128
> > optimizer_features_enable 9.2.0
> > sort_area_size 65536
> > sort_area_retained_size 0
> > optimizer_index_cost_adj 100
> > optimizer_index_caching 0
> > pga_aggregate_target 8589934592
> >
> > 7 rows selected
>
> Your pga_aggregate_target is about 8.5GB - do you have enough physical
> memory in the server to allow this - you could be doing some serious
> swapping to the operating system's swap file? The
> db_file_multiblock_read_count seems very high. Depending on the DB
> block size, a value of 8,16, or even 32 may be more realistic. If you
> execute the following before processing your query, does it help:
> ALTER SESSION SET SORT_AREA_SIZE=20000000;
> ALTER SESSION SET SORT_AREA_RETAINED_SIZE=5000000;
> ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=8;
>
> You will not want to leave the above in effect permanently. Keep me
> posted what you find.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

TMP_LINKS was a table I created as part of this process. It does have 2 indexes, however there was nothing showing in dba_tables as last_analyzed. I updated the statistics on this table and the thing ran really fast. I was under the impression that when you created an index it compilied the statistics for that index. That is obviously not the case. Thanks for your help.

Here it is now:
TMP_LINKS 920043 6922 07-NOV-06 Received on Tue Nov 07 2006 - 16:14:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US