Matthew W. Ball wrote:
> One important note about the SGA (SQL_TEXT in particular).
> Remember that any SQL statement is NOT shareable "across users/schemas."
> That is:
> select count(*) from user_tables run as system
> and select count(*) from user_tables run as scott/tiger, will BOTH take
> up room in
> the SQL area.
Are you sure about that?
SQL> connect bergenroth_b
Connected.
SQL> alter session set sql_trace=true;
Session altered.
SQL> select count(*) from user_tables;
COUNT(*)
----------
3
SQL> connect system
Connected.
SQL> alter session set sql_trace=true;
Session altered.
SQL> select count(*) from user_tables;
COUNT(*)
----------
131
As me:
********************************************************************************
select count(*)
from
user_tables
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.01 0.02 0 9 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.01 3 47 0
1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.01 0.04 3 56 0
1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61
As system:
********************************************************************************
select count(*)
from
user_tables
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.14 6 1725 0
1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.00 0.14 6 1725 0
1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5
So, misses in library cache was 0 for system, it was already in the SGA
and only soft parsed.
Thanks,
Brandon Bergenroth
>
> Therefore, if they are the same physical schemas, AND very large
> databases,
> 32 bit limiits will get you!
> So:
> 64 bit, or keep them split!
>
> [We have run into this, where lots of the same schema in the same
> database performs poorly,
> split them up into 3 or 4 instances, and we were MUCH happier!]
>
> My $.02!
>
> Matt
>
> On Mon, 2005-02-28 at 15:56 -0600, genegurevich wrote:
>
>
>>
>>
>>Hi all:
>>
>>I'm trying to decide on a design of a data warehouse. It will host three
>>large apps each
>>is 150-250G (including indices and aggs) and a few smaller apps. The
>>largest applications
>>are not interdependent. The data are being loaded into the tables on a
>>daily basis once
>>a day and then various reports are executed against the tables. Curently
>>two of the
>>largest apps are sharing an instance, while the third one has its own
>>instance. We (
>>me and another DBA on my team) have been debating whether to join all three
>>apps
>>into one big Data Warehouse to cut down on memory usage and the maintenance
>>overhead.
>>How do we weight the pros and cons to arrive at a decision? Any thoughts?
>>
>>thank you
>>
>>Gene Gurevich
>>
>>
>>
>>
>>
>>--------
>>website: http://www.LazyDBA.com
>>Please don't reply to RTFM questions
>>Oracle documentation is here: http://tahiti.oracle.com
>>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>>To subscribe: see http://www.lazydba.com
>>By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>
> ----------------------------------------------------------------
> |Matthew W. Ball (Matt)
> |Team Lead for Infrastructure and Reliability Group
> |Endeavor Information Systems Inc.
> |(e) [Email address protected]
> |(p) 847-227-2678
> |(f) 208-275-0869
> |(u) http://www.endinfosys.com
> --------------------------------------------------------------
> It is inaccurate to say I hate everything.
> I am strongly in favor of common sense,
> common honesty, and common decency.
> This makes me forever ineligible for public office.
> -H.L. Mencken, writer, editor, and critic (1880-1956)
> --------------------------------------------------------------
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>
Oracle LazyDBA home page