explain plan and load on the database
A reader, September 18, 2003 - 8:23 pm UTC
oops! - The DB version info I have put in is wrong
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
If we are not using 'pga_aggregate_target', does the factor that some of the data is already in the buffer(logical I/O) and does not require a physical I/O - contribute to the time difference of 15 sec vs 3 min.?
September 18, 2003 - 9:31 pm UTC
that would manifest itself as "wait time" for IO
Parallel query?
Peter, September 19, 2003 - 4:52 am UTC
I have seen on some systems (mainly 8i) a similar effect if a query would normally execute in parallel but there not enough parallel query slaves available to satisfy the request - so the query runs in serial and slower
I am not commenting on whether it right or wrong to use for a given table.
regarding "explain plan and load on the database", version 9.1.2
Raj Jamadagni, September 19, 2003 - 7:45 am UTC
In addition to Tom's recommendations, I'd suggest that run your report with "dbms_support.start_trace;" .
Let it generate trace files for each invocation on a typical day. Then you analyze all trace files and compare.
Along with a statspack analysis report for the (specific) report run time, the trace file analysis will provide you a lot more insight into wha the issues may be.
HTH
Raj
pga_aggregate_target
A reader, January 07, 2004 - 6:40 am UTC
Hi
If I have 512mb for pga_aggregate_target and most of them is free then if my queries are using sort joins will they change to nested loop when pga_aggregate_target is low, i.e not enough memory for sort? That means the plan will be very unstable no?
Also, what process are consuming/managing this PGA pool? Because according to the docs this memory can be freed to OS, i.e this is a pool like SGA and not per process as before
TIA
January 07, 2004 - 8:17 am UTC
the plan will be the right plan for the current situation.
do you want to do a sort join with 64k of sort area size? probably not. do you want to do nested loops with a 5m sort area size? probably not (in your case of course).
the pga workareas are allocated "differently". no one manages them, they use different OS calls (like memmap in unix) to allocate storage that can be directly released (as opposed to heap memory gotten via malloc)
Oracle 8 Personal for winNT
kv srinivas, January 07, 2004 - 4:34 pm UTC
Tom'
A)Does the explain plan How to install oracle 8PE for winNT Iam unable to install on win2K server
Getting Problem the Error mssg was unable to delet or install NT service for SQL*Net .
B)Oracle8i Enterprise Edition Also unable to install
Plz give me solution.
January 08, 2004 - 11:25 am UTC
please contact support.