Re: Optimizing inline view

From: Mike C <michaeljc70_at_hotmail.com>
Date: 8 Nov 2006 07:19:00 -0800
Message-ID: <1162999140.063306.22500@e3g2000cwe.googlegroups.com>

Charles Hooper wrote:
> 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.

Yes, after adding the statistics on that table, the whole query ran lightning fast. Just for the hell of it, I will try the alter sessions and see if that imrpoves things any more. Received on Wed Nov 08 2006 - 09:19:00 CST