Re: Optimizing inline view

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. Received on Tue Nov 07 2006 - 16:04:19 CST