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: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 7 Nov 2006 17:41:54 -0800
Message-ID: <1162950114.115157.59160@h54g2000cwb.googlegroups.com>


Mike C wrote:
> 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
That helps. When you saw that you updated the statistics and it is
really fast, are you saying that the problem has been corrected - that the full query is running fast?

Regarding the pga_aggregate_target value of roughly 8.5GB, was that amount of server RAM allocation intended. The server may be swapping to the operating system's swap file, in addition to your query possibly writing to and reading from the temporary tablespace.

If you are still having problems, execute the three ALTER SESSION commands above and see if that helps. Without a 10046 trace at level 8, which would include the wait events both for your SQL statement and the recursive SQL that is executed in the PL/SQL functions (QT_UTIL.GET_SUBREGION), about the best that you can do is search for obvious problems: review the initialization parameters (db_file_multiblock_read_count is one such case) to make certain that the values make sense, run a DBMS_XPLAN, keep an eye on V$SESSION_WAIT while the SQL statement is exectuting, watch the values in V$SESSTAT (join with V$STATNAME), watch the wait events in V$SESSION_EVENT, run a Statspack report, and hope that something substantial is found.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Nov 07 2006 - 19:41:54 CST

Original text of this message

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