Re: Row level security and latch waits - LONG email...
Hi Nuno > I don't think this is the issue at all. The SQL in the function > that generates the predicate WILL use bind variables and that > is perfectly expected. That's why you don't see an increase in parses > on the SQL INSIDE the function. > That is correct. I should have posted the predicate clause generated and I have posted that below > What will not use bind variables is this bit: > > > SQL> select count(*) from dpr70_gl_acct_balance_f; > > because the added predicate is a string without bind syntax. The added predicate for this is is using context as seen in the trace file. This can be generated by setting event 10730 - Logon user : SECUSER1 Table/View : DPA42HP92.DPR70_CURRENCY_D Policy name: DPR70_CURRENCY_D_PLCY Policy function: DPA42HP92.DP_PREDICATE_FUNCTION_PKG.DP_PREDICATE_FUNCTION RLS view : SELECT "CURRENCY","CURRENCY_CODE","CURRENCY_DESC","CURRENCY_KEY" FROM "DPA42HP9 2"."DPR70_CURRENCY_D" "DPR70_CURRENCY_D" WHERE (CURRENCY_CODE in (select value_ v from dp_security_values where group_id in (select group_id from dp_upd_user_groups where comp_group_id = sys_context('dp_comp_group_id_ctx','comp_group_id')) as you can see the context variable is being used in the generated predicate clause > However, I think if the added predicate string contains a reference > to a context variable, which is what Tom is talking about, it might > well be the case this will act as a kind of bind variable > even though the syntax is not the correct one. Anyone found if that > is the case? That was my point, that using the context variable in the predicate clause will cause it to act like a bimnd variable and that is behaviour that I have been seeing Thanks Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - Choose from over 50 domains or use your own -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row level security and latch waits - LONG email...
Hi, With the way we have implemented FGAC, we do not have any parses occurring with application context. The application context does act like bind variables and I will try to illustrate that with an example. We set the context of the users logging in to a particular group/role which gets executed from a logon trigger POLICY_NAMEFUNCTION -- -- DPR70_CURRENCY_D_PLCY DP_PREDICATE_FUNCTION The above is the function defined on the object Login as a user SQL> select sys_context('dp_comp_group_id_ctx','comp_group_id') 2 from dual; SYS_CONTEXT('DP_COMP_GROUP_ID_CTX','COMP_GROUP_ID') 1012 SQL> select count(sql_text) from v$sql where sql_text like '%sys_cont%'; COUNT(SQL_TEXT) --- 12 In v$sql this is the sql that generates the predicate clause vis the function SELECT text from dp_security_text where object_name = :b1 and comp_group_id = sys_context('dp_comp_group_id_ctx','comp_group_id') SELECT sys_context(:b2,:b1) from sys.dual Notice how the context gets converted into bind variables Also notice the parse_calls and executions 6 16 SELECT text from dp_security_text where object_name = :b1 and comp_group_id = sys_context('dp_comp_group_id_ctx','comp_group_id') Now lets login as a different user SQL> select sys_context('dp_comp_group_id_ctx','comp_group_id') 2 from dual; SYS_CONTEXT('DP_COMP_GROUP_ID_CTX','COMP_GROUP_ID') 1011 1* select count(*) from v$sql where sql_Text like '%sys_cont%' SQL> / COUNT(*) -- 12 There is a parse because the user_id is different 7 18 SELECT text from dp_security_text where object_name = :b1 and comp_group_id = sys_context('dp_comp_group_id_ctx','comp_group_id') No lets execute queries as this user SQL> select count(*) from dpr70_currency_d; COUNT(*) -- 3 from V$sql SQL_TEXT PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LOADS LAST_LOAD_TIME --- -- --- -- --- SELECT text from dp_security_text where object_name = :b1 and comp_group_id = sys_context('dp_comp_group_id_ctx','comp_group_id') 7 20 2003-08-20/09:29:34 2 2003-08-20/10:39:44 Notice parse_calls does not change lets execute another query SQL> select count(*) from dpr70_gl_acct_balance_f; COUNT(*) -- 2974 from v$SQL SQL_TEXT PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LOADS LAST_LOAD_TIME --- -- --- -- --- SELECT text from dp_security_text where object_name = :b1 and comp_group_id = sys_context('dp_comp_group_id_ctx','comp_group_id') 7 24 2003-08-20/09:29:34 2 2003-08-20/10:39:44 Notice again parse_calls does not change The above SQL is the predicate clause being generated every time Hope this helps. Also from Tom Kyte /QUOTE Sys_context is treated like a bind variable in a query -- its value is BOUND in just like any other value would be. it rewrites the query which is then sent to the optimizer rewrote the SYS_CONTEXT calls as bind variables. /END QUOTE URL http://tinyurl.com/knrg As for the doc below... > I'll have to do more investigation, since those paragraphs don't clearly > explain (at least, to me) what is meant by "the same predicate". Does > that > mean that predicates with Context Variables included as bind variables > will > not be reparsed if their Policy Function was created with > Static_Policy=True, and yet different sessions can reuse the parsed SQL > with their own Context values? ...or not? what this means is in 9i, the policy function will be executed every time a query is issued against the object. But if the predicate being is generated is the same everytime then the policy can be generated with static_policy=true and if needs to be refreshed, then dbms_policy.refresh needs to be used. If the predicate generated is different in different database sessions but they are the same within a session, then _dynamic_rls_policies=FALSE will prevent it being executed within the session. Hope this helps. Regards, Sorry for the long winding email Thanks Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applicatio
RE: Re: Row level security and latch waits
Hi Raj, Vadim is right and if RLS is implemented without application context then there would be hard parses especially if there are literals in the policy function . We have a client warehouse with about 500 users and 300 tables and FGAC has been working very well and we do not see latch waits. The policy function has to be devoid of literals as vadim indicated. We ste up the policy function based on the application context and in order to look at the where clause generated, an evetn can be set. Hope this helps. Thanks Madhavan http://www.dpapps.com On Tue, 19 Aug 2003 12:24:26 -0800, "Gorbounov,Vadim" <[EMAIL PROTECTED]> said: > Jack, > You are right, app context is treated as a bind variable, but someone may > decide not to use it in RLS policy function. Example (maybe a little > unreal > but valid): > > CREATE OR REPLACE PACKAGE BODY Oe_security AS > > FUNCTION Custnum_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2 > IS > D_predicate VARCHAR2 (2000) > BEGIN > D_predicate = 'create_date > '''||to_char(sysdate-1)||''''; > RETURN D_predicate; > END Custnum_sec; > END Oe_security; > > Hence hard parses. > > Vadim -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - mmm... Fastmail... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: create imp and exp Oracle as single-task binaries?
Hi, > I once experimented with it (8.1.7.smth on linux), but started having > crashes. Don't remember what exactly crashed, but it didn't work. I > believe > last version where it was supported, was 8.0? I think it was supported in Oracle 8.1.5 and is documented in the tuning manual. we used the single task executables for doing some benchmarks in 8.1.5 and it did give us quite a bit of performance enhancement and yes as Tanel indicates, its definitely not recommended to be run in a production environment. We did not run into any major issues with these executables except we had to fix the make files to get them compiled. We did achieve performance improvements of 30 to 40% on certain exports and sqlloader loads. This was on Sequent NUMA running Oracle 8.1.5 and Dynix/ptx 4.4.1 Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - Sent 0.02 seconds ago -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Intresting Statistics -- DB FILE SEQUENTIAL READ waits
want to be removed from). You may also send > > the HELP command for other information (like subscribing). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Binley Lim > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the > message BODY, include a line containing: UNSUB ORACLE-L (or the name of > mailing list you want to be removed from). You may also send the HELP > command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Cary Millsap > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - One of many happy users: http://www.fastmail.fm/docs/quotes.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Question
Hi Jaydas, Thanks for the reply. It gives me a good starting point to go with. The query handles cases where there are multiple rows. For eg: U=2006 has G=1010 and S=1013. If there were another U=2010 with same G=1010, then a rum through the query would generate a S=1012 for this combination also as the min checks for group_id in () and that will evaluate any U belonging to a single group that is part of the multiple groups that a U belongs to. But I will take this query as a starting point and will work on getting that resolved. Thanks for your time and appreciate your help Regards, Madhavan http://www.dpapps.com On Thu, 03 Apr 2003 12:28:42 -0800, "Chelur, Jayadas {PBSG}" <[EMAIL PROTECTED]> said: > Madhavan, > > I have created a similiar table and inserted the data > as follows :- > > = > > CREATE TABLE UT > ( > U NUMBER(4), > S NUMBER(4), > G NUMBER(4) > ); > > INSERT INTO UT VALUES(2005,1012,1010); > INSERT INTO UT VALUES(2005,1012,1011); > INSERT INTO UT VALUES(2006,1013,1010); > INSERT INTO UT VALUES(2007,1017,1016); > INSERT INTO UT VALUES(2008,1018,1010); > INSERT INTO UT VALUES(2008,1018,1011); > > INSERT INTO UT VALUES(2009,1019,1016); > INSERT INTO UT VALUES(2001,1020,1010); > INSERT INTO UT VALUES(2001,1020,1011); > > COMMIT; > > === > > this query will identify all the security groups and the > minimum security group id of the "identical" one ... > > > SELECT DISTINCT > S2.SORIGINAL_SG,/* original security group */ > S3.SEQUIV_SG/* equivalent security group*/ > FROM( > SELECT S, COUNT(*) RECS > FROMUT > GROUP BY S > ) S1, /* security groups and their group counts - table1 */ > ( > SELECT S, COUNT(*) RECS > FROMUT > GROUP BY S > ) S2, /* security groups and their group counts - table2 */ > ( > SELECT DISTINCT S > FROMUT > ) S3/* just the unique security groups */ > WHERE S1.RECS = S2.RECS /* match the sec. groups with the same record > counts */ > AND S1.S<> S2.S /* make sure they are NOT the same security > group*/ > AND NOT EXISTS /* make sure they include identical group ids > */ > ( > SELECT G FROM UT WHERE S = S1.S > MINUS > SELECT G FROM UT WHERE S = S2.S > ) > AND S3.S = ( /* see note */ >SELECT MIN(S) >FROM UT >WHERE G IN > ( > SELECT G > FROMUT > WHERE S = S1.S > ) >) > > /* note : > this is to find the minimum value of the security id which has the same > group > id records as that any of the matching security groups. this minimum > value > can > be used to update the security group ids of all other identical security > groups > at a later point of time > */ > > ==== > > > you can either change the query to update all the eligible security id to > their corresponding minimum values or generate equivalent update > statements > using this query and run them as a batch ... > > HTH ... > -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - Choose from over 50 domains or use your own -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL Question
Hi, Sorry for reposting. Just wanted to put in a subject... I have been grappling with this for sometime and thought it will be best for others to take a look at it. I have a table a_user_groups USER_ID SECURITY_GROUP_ID GROUP_ID -- - -- 1005 1012 1010 1005 1012 1011 1006 1013 1010 1007 1017 1016 1008 1018 1010 1008 1018 1011 The security_group_id currently is uniquely generated every time a user is added and a group_id is associated with the user_id. For eg: user_id 1005 is associated with groups 1010 and 1011. User 1008 has the same combination but the security_group_id is generated differently. The generation happens for C code and there is an option to correct the problem in the C code but I am trying to see if I can prevent that The requirement is that user_id 1008 and any other users with the same group_id combination should have the same security_group_id 1012, basically the first occurrence for the combination. In the case of user_id 1006 the value for security_group_id is 1013 and the group_id the user_id belongs to is 1016. So all the user_ids with a group_id association of 1016 (1016 in a combination does not count) down the line will have to be updated to 1013. I found a solution for the case where I associate a group to a user_id in this existing table by creating another table that converted the above table into a hierarchy Table b_hier_user_groups USER_ID CGID PARENT_VALUE CHILD_VALUE -- -- --- 1005 1012 1010 1005 1012 10101011 1006 1013 1010 1007 1017 1016 1008 1018 1010 1008 1018 10101011 Then using a PL/SQL script I generated the tree using sys_connect_by_path I determined if the user had a path that already. For eg: in the above case if the user 1006 was being associated with group_id 1011, then I would check the exsiting trees to see if there was a path already as in 1010,1011. In this case it does exist and the cgid (equivalent to security_group_id in the above table) is 1012 and update the user_id 1006 to cgid 1012. But I am not for some reason able to apply this solution to the existing rows. I have a feeling that I am missing something simple The requirement that I am grappling with is to update the values in the existing table. I can get the table b_hier_user_groups created from the a_user_groups. Please let me know if you need more information The table structures are as below a_user_groups Name Null?Type - USER_ID NOT NULL NUMBER SECURITY_GROUP_ID NOT NULL NUMBER GROUP_ID NOT NULL NUMBER Table b_hier_user_groups Name Null?Type - USER_IDNUMBER CGID ---> same as security_Group_id from above) NUMBER PARENT_VALUE NUMBER CHILD_VALUENUMBER Thanks for your time and help in advance. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - I mean, what is it about a decent email service? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[no subject]
Hi, I have been grappling with this for sometime I have a table a_user_groups USER_ID SECURITY_GROUP_ID GROUP_ID -- - -- 1005 1012 1010 1005 1012 1011 1006 1013 1010 1007 1017 1016 1008 1018 1010 1008 1018 1011 The security_group_id currently is uniquely generated every time a user is added and a group_id is associated with the user_id. For eg: user_id 1005 is associated with groups 1010 and 1011. User 1008 has the same combination but the security_group_id is generated differently. The generation happens from C code and there is an option to correct the problem in the C code but I am trying to see if I can prevent that The requirement is that user_id 1008 and any other users with the same group_id combination should have the same security_group_id 1012, basically the first occurrence for the combination. In the case of user_id 1006 the value for security_group_id is 1013 and the group_id the user_id belongs to is 1016. So all the user_ids with a group_id association of 1016 (1016 in a combination does not count) down the line will have to be updated to 1013. I found a solution for the case where I associate a group to a user_id in this existing table by creating another table that converted the above table into a hierarchy Table b_hier_user_groups USER_ID CGID PARENT_VALUE CHILD_VALUE -- -- --- 1005 1012 1010 1005 1012 10101011 1006 1013 1010 1007 1017 1016 1008 1018 1010 1008 1018 10101011 Then using a PL/SQL script I generated the tree using sys_connect_by_path I determined if the user had a path that already. For eg: in the above case if the user 1006 was being associated with group_id 1011, then I would check the exsiting trees to see if there was a path already as in 1010,1011. In this case it does exist and the cgid (equivalent to security_group_id in the above table) is 1012 and update the user_id 1006 to cgid 1012. But I am not for some reason able to apply this solution to the existing rows. I have a feeling that I am missing something simple The requirement that I am grappling with is to update the values in the existing table. I can get the table b_hier_user_groups created from the a_user_groups. Please let me know if you need more information The table structures are as below a_user_groups Name Null?Type - USER_ID NOT NULL NUMBER SECURITY_GROUP_ID NOT NULL NUMBER GROUP_ID NOT NULL NUMBER Table b_hier_user_groups Name Null?Type - USER_IDNUMBER CGID ---> same as security_Group_id from above) NUMBER PARENT_VALUE NUMBER CHILD_VALUENUMBER Thanks for your time and help in advance. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - Same, same, but differentÂ… -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to delete name service
Hi, You can also download the delsrv.exe (free download) from the Windows 2000 Resource kit and use it delete a service. delsrv deletes the service. http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B274305 Regards, Madhavan http://www.dpapps.com On Tue, 01 Apr 2003 02:03:39 -0800, "Yechiel Adar" <[EMAIL PROTECTED]> said: > RE: How to delete name serviceThere is also controlset001 and 002. > > Does anyone know how to delete a name server. I created thru net8 > > assistant however it will the service will not start. > > I want to remove it. When I highlight it the 'X' is not > > deleted nor thru > > menu options. How can I safely remove name service. -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - Choose from over 50 domains or use your own -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Fine Grained Access Control (FGCA)
Hi Murali, In our case since the predicats are generated dynamically and a call to the function from outside will give me the predicate being applied. Thanks to Jonathan Lews, I will also make sure now using event 10730. Regards, Madhavan http://www.dpapps.com > Thanks for the response. I did get it working. However I have another > question. Have you tried to capture what happens behind the scene. I > turned on trace however could not trace the actual query that gets added > to the SQL statement. Only the function call could be traced not the > actual SQL condition > > Thanks > > Menon -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - Choose from over 50 domains or use your own -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Fine Grained Access Control (FGCA)
Hi Murali, We have FGAC working on any number of tables and the policy is applied by a single function and the text generated is picked up from a table and we have it working without any problems. If you can post details of what you are trying to do we can try and identify why the policy is not being applied to the second table or the reason for the 0 rows. We have a function that generates the text that will be applied as the where clause depending on the object being accessed. When the policy is applied it invokes this function and then applies the predicate based on the text returned from the function Please let me know if you need more detailed information Thanks Madhavan http://www.dpapps.com On Wed, 05 Mar 2003 07:05:37 -0800, "Murali Menon" <[EMAIL PROTECTED]> said: > > Hi Guys, > > I am trying to setup FGCA for our customers on their reporting system. I > have got this working for a single table, however, when a policy needs to > be added for multiple tables. It fails returning no rows from the second > table. > > Oracle documentation is not very helpful this arena. Can anyone provide > any pointers to white papers or documents on this subject. > > Thanks > > Menon > > > > - > Do you Yahoo!? > Yahoo! Tax Center - forms, calculators, tips, and more -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - Access all of your messages and folders wherever you are -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Parallel Replication of Single Table
Hi Larry, > In the distributed manual it > makes a comment about DML serializing when doing remote operations. I am not sure about replication but in the past (on 8.1.5 and 8.1.6 versions) this has been my experience with CTAS over a dblink or DML over dblink in that it serializes. In order to achieve parallelism, the only workaround we had was (though not pretty :-) ) to use the ROWID hint on the CTAS and build a set of partition tables at the target and then merge them into a single table. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - Accessible with your email software or over the web -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Hi Steve/Jared, It was coincidence that just when Steve posted this we were trying to flatten the hierarchy and PL/SQL was the only option and I was doing some reserach and stumbled upon this parameter and then I used to it to generate output using the sys_connect_by_path and then all I had to do was parse the output and then populate the level columns based on that to get my complete hierarchy You are right Jared, once you set the parameter, most of the things (except the siblings as its a new keyword only since 9i I think) start working as in 9i but with a undoc parameter. Regards, Madhavan http://www.dpapps.com On Thu, 07 Nov 2002 07:49:22 -0800, "Orr, Steve" <[EMAIL PROTECTED]> said: > Hey Jared, just got this because I was on a 4X10 day off yesterday. > Anyway, > thanks for the info. There was lots of great discussion on this and I > appreciate the collective brain power of the list. Proposed solutions: 1) > upgrade to Oracle 9i and use an inline view; 2) use a hint; 3) use > indexes > on the columns to be sorted; 4) use an undocumented parameter; 5) > Metalink... No help yet. > > > Steve Orr > Bozeman, Montana > > > -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - The way an email service should be -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Hi Steve, You can get the below query to work in 8.1.7 (not sure about previous versions) by setting the undoc parameter _new_connect_by_enabled = true and can be set for a session SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid You can also use sys_connect_by_path feature in 9i which gives you the entire hierarchy path (can be used in 8i with the above undoc parameter) For eg: select sys_connect_by_path(parent_id,'/') from treenode start with parent_id = 0 connect by prior id = parent_id will give you a output like 0/1/9 0/1/2 0/1/2/4 ...etc As always setting an undoc parameter is not advisable unless instructed by Oracle support :-) Hope this helps. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - The holy hand grenade of email services -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: linux and Oracle Apps jinit
Hi Ron, > Has anyone found a way to use linux as a client to Oracle Applications? > The self service stuff runs > just fine but the Oracle Forms requires that damnable jinit. This is > the only problem left before I > can give MS the big Heave Ho. I am also in the process of trying to find if I can get Linux to be a client for Oracle Apps and the only lead I had was that there was once a Solaris version that can be still made to work and I am trying to find out if I can get that. Other than that, I have heard Windows is the only platform that jinit runs on :-( Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm/ - Consolidate POP email and Hotmail in one place -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dbms_utility and dbms_stat difference
Hi Cherie, > I can't speak for dbms_utility but dbms_stats is supposed to generate > statistics at both the partition level and at the table level for > partitioned tables, which analyze does not do. A small correction, ANALYZE also generates statisics at partition level and when an "analyze table compute statistics" is done it generates statistics for the partitions. Analyze can also be run at the partition level as "analyze table part () compute statistics" and this will generate statistics only for that paritition. Its very useful as analyze can be run for the partitions of a table in parallel and so its a blessing when running analyze on huge partitioned tables. One of the big differences between analyze and dbms_stats is that analye cannot be run in parallel on a non partitioned table and definitely runs better in my opinion in general. Hope this helps. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - In the time it takes you to read this, you could be FastMailing -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: IO wait
Hi Jack, > questions: > > 1) is top a valid measure of IO wait? In my opinion sar is a better tool to look at IO waits. sar -d and sar -b will give you information on how the disk activity and I/O is. > 2) Is a high io wait an issue to be concerned about? Here is a nice note from Dave Miller with regards to Solaris though Just a note on iowait. On any multi-cpu machine, this number is not very useful, especially before Solaris 8. The algorithm for calculating it was changed in Solaris 8 but still is not really helpful. Prior to Solaris 8, iowait was defined as follows. When the scheduler attempted to schedule a process on a cpu, if there were no tasks that were runnable, but any task was marked as waiting for I/O, instead of counting as idle it counted as iowait. The problem on multi-cpu systems is that a single process waiting on I/O could count as iowait on ALL otherwise idle cpus. With Solaris 8 that was scaled down a bit (I don't have the exact details in front of me), but still is a bad gauge of I/O problems. I nearly always consider iowait to just be idle time and look for I/O problems elsewhere, like looking at iostat and looking at the %busy and service times on individual disks. That's much more indicative of a real problem and also will help you find out if you're hot-spotting on any disks. You also can monitor your networks using netstat because I believe iowait gets counted on processes waiting for network I/O, too. > 3) how else can it be accurately measured? sar I think gives a good idea > 4) How can I link IO wait to what is happening inside > the database? I think v$filestat will be the starting link. Hope this helps. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- Please don't use http://fastmail.fm (I like feeling special.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Log file synch / direct path writes
Hi, On a Sequent NUMA (now IBM) for a benchmark we used run queues where we bind the SGA to a set of processors. We had a 16 processor NUMA and we tied the SGA to 8 processors in the first test and user connections were all bound only to the 8 processors (2 quads) and then the second part of the test to demonstrate scalability we we had a runqueue command to now bind the SGA to all the 16 processors (4 quads) and then spawn more user connections. The existing user connections will migrate over nicely when they attempt to get the next CPU cycle. It was for a benchmarking situation to demonstarte scalablilty but we have bound listener processes to quads and that has worked out well. Regards, Madhavan http://www.dpapps.com On Tue, 25 Jun 2002 19:33:38 -0800, "George Schlossnagle" <[EMAIL PROTECTED]> said: > > When you say 'Give the LGWR its own processor' did you mean that > > processor > > binding needs to be done? As per my understanding, processor binding -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - The way email should be... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Log file synch / direct path writes
Hi Ramon, On Mon, 24 Jun 2002 09:28:21 -0800, "Ramon E. Estevez" <[EMAIL PROTECTED]> said: > What implications can cause having a big log buffer. > You want to check the tip from Steve Adams to understand how log buffer works and the implications of a big log buffer http://www.ixora.com.au/tips/tuning/log_buffer_size.htm Hope this helps. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - 100% lightning -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Memory utilization
Hi Tim, > FF21 8K read/write/exec [ anon ] > FF34 8K read/write/exec/shared [ anon ] > FF3A 8K read/write/exec [ anon ] > I don't know what "anon" means, but let's > assume that it is also private to this specific process, The anon refers to anonymous pages that are mapped to /dev/zero by calling mmap() which is a bunch of zeroes and not mapped from a file. It can be shared between processes with common ancestors as it does not have data as such and just zeroes, but processes having access to a single mapping of /dev/zero can share this memory region. In the above case, we have a 8K that is shared and the 16K that is private to this process. I am sure I will be corrected if I am wrong :-) Hope this helps. Regards, Madhavan -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - You've just been FastMailed! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: char vs. varchar in a data warehouse
Hi, I am sure u probably know this already a leading null value column will take up a byte and like in the second case where the first column is null and the second column has a value the av row length is increased by 1 as the null takes up a byte. If the first col had a value and the second column had a null, then only the first one would have been stored. So its a good practice to put the columns with frequnet nulls at the end of the table so that it will decrease the ave row length and increase the data density. Regards, Madhavan > But in a blockdump I just did, for a null row (tow columns both null) > there > is nothing in the dump, for second row(first col null, second has > value) the > first column value is shown as '*NULL*' I believe the discussion was > something related to how null and empty string is handled. But here > again in > 816 the empty strings are not shown once again. > > Oracle 8161 > -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - You've just been FastMailed! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Are people receiving postings
Hi, I just wanted to find out if people are receiving postings from oracle list. I can see my post come up but no posts from anybody else. Thanks --- Madhavan Amruthur Outgrown your current e-mail service? Get a 25MB Inbox, POP3 Access, No Ads and No Taglines with LYCOS MAIL PLUS. http://login.mail.lycos.com/brandPage.shtml?pageId=plus -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Test - Please Ignore
Test Mail --- Madhavan Amruthur Outgrown your current e-mail service? Get a 25MB Inbox, POP3 Access, No Ads and No Taglines with LYCOS MAIL PLUS. http://login.mail.lycos.com/brandPage.shtml?pageId=plus -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Decyphering LMT space bitmap
Thanks Jonathan. >I think you'll find that generally the initial amount >of file allocated is 64K (including the two header >blocks) - the fact that you noted the bitmap was in >the "next 6 blocks" suggests you were using an >8K block size. >The actual space allocated at file creation time >is dependent on the file size, block size, and >extent size. I did the tests about 2 years ago and did notice that as you indicated that it depends on file size, block size and extent size and yes it was an 8K block and I did do the test for 16K block size and got the first 2 blocks for the header and then the next 2 for bitmaps >If you have a very small block size, very small extents, >and extremely large files then Oracle may allocate >an extra 64K (and I guess further multiples of 64K >if necessary). I noticed that I got the maximum free extents tracked when I added an extra 64K to the file size. Unfortunately at that time I did not have enough disk space to create areal large file and a table with lots of extents. >I have yet to find out what the typical behaviour is >for a tablespace with a 32K block. I would guess >that the total file header would be 128K rather than >64K - 2 blocks for the general file header, 2 blocks >for the bitmap. Could anyone confirm that, thanks ? I will try and test that to see how Oracle allocates blocks. Thanks once again. Regards, Madhavan See Dave Matthews Band live or win a signed guitar http://r.lycos.com/r/bmgfly_mail_dmb/http://win.ipromotions.com/lycos_020201/splash.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Test Message - Please ignore
--- Madhavan Amruthur See Dave Matthews Band live or win a signed guitar http://r.lycos.com/r/bmgfly_mail_dmb/http://win.ipromotions.com/lycos_020201/splash.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Decyphering LMT space bitmap
Hi Jeremiah, >How is the LMT bitmap organized? I did some research about the bitmap structure for an LMT tablespace a while ago. The first 2 blocks are Block 1 - For File Header Block 2 - For File Space Bitmap Header The bitmap itself is stored in 6 blocks after that , blocks 3 thru 8. Each of the bit tracks four extents represented by an F (hex for 15). 0 For eg: if you create a table with 1 extent then there will be a 1 which 1 2 to the power of 0 = 1 and if there 2 extents then its 2^0 + 2^1 = 3 for 4 extents its 2^0+2^1+2^2+2^3 = 15 =F so the values will be either 0,1,3,7,or F for each bit. In your case FF3F 0 there are 7 F's = 7*4 = 28 extents + 2 (represented by the 3) and = 30. The rest are 0's because there is no data yet as there are only 30 extents. Each of these blocks 129024 extents. When I did my test and I had raised the question at that time that the max data that can be stored is 129024 * 6 (blocks) = 774144 extents and if the extent size for argument is 1K then the max size the bitmap can track is 774144K or 7M data file. I have not found an answer yet. You will find that there are about 504 lines of 64 bits each. I have a detailed record of the test if you are interested that I can mail to you. Hope this helps. Regards, ------- Madhavan Amruthur See Dave Matthews Band live or win a signed guitar http://r.lycos.com/r/bmgfly_mail_dmb/http://win.ipromotions.com/lycos_020201/splash.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to find out Server model on unix?
Hi , uname -a will give you the model Regards, Madhavan [EMAIL PROTECTED] wrote: >Hi, > >Is there any command to find out the server model for a unix server..for >example on a sun box how to find out if it is 4500, E10k etc..? > >Thanks in advance. > >Mohammed Ahsanuddin >Oracle DBA > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: LMT MAXEXTENTS for 8K Blocksize
>the Tuning I/O section of this guide there is a > reference to this > >stating: > > ---snip--- > Evaluating Unlimited Extents > > Even though an >object may have unlimited extents, > this does not mean that > having > a >large number of small extents is acceptable. For > optimal performance you > > may > decide to reduce the number of extents. > > Extent maps list all >extents for a particular > segment. The number of extents > entries > per >Oracle block depends on operating system block > size and platform. > >Although > an extent is a data structure inside Oracle, the > size of this >data structure > depends on > the platform. Accordingly, this affects the >number > of extents Oracle can > store in a > single operating system >block. Typically, this value > is as follows: > > Block Size (KB) Maximum >Number of Extents > 2 121 > 4 255 > 8 504 > 16 1032 > 32 2070 > > > For >optimal performance, you should be able to read > the extent map with a > >single > I/O. Performance degrades if multiple I/Os are > necessary for a >full table > scan to get > the extent map. > > Avoid dynamic extension in >dictionary-mapped > tablespaces. For > dictionary-mapped tablespaces, do >not let the number > of extents exceed > 1,000. If extent allocation is >local, then do not > have more than 2,000 > extents. Having too many >extents reduces performance > when dropping or > truncating tables. > >---snip--- > > Now, I also remember somebody stating that > MAXEXTENTS >should be set to 504 > for an 8K block size, and he was flamed.. I also > >added to this, and now > appologise.. > > Now, the question arises : > > I >created an LMT with the default storage > parameters, and Oracle in its > >infinite wisdom, gave a MAXEXTENTS value of > 2147483645!! > > A few more >than 2000, was Larry thinking of his bank > balance when he decided > on >this? :) > > You know, it's so great that they put these little > "pearls" >of wisdom in to > tuning guides, that you have to dig in to, just to > find >a ghost of an > answer, but when it comes to putting them as > standard in >to the bloody > product they give stupid values that would grind a > system >to a halt if ever > allowed to happen!! Would it not then make sense to > >just set this as a > default?? > > Am I being naive? LOL.. > > Can we all >join hands and shout "Thank you > LARRY" > > Mark > > > BEGIN:VCARD > >VERSION:2.1 > N:Leith;Mark > FN:Mark Leith > ORG:Cool Tools UK Ltd;Sales > >TITLE:Sales & Marketing > TEL;WORK;VOICE:01905 330 281 > >TEL;CELL;VOICE:07771 691 122 > TEL;WORK;FAX:0870 127 5283 > >ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;9 Turbary > Ave,=0D=0ALyppard >Kettleby;Worcester;;WR40PS;England > LABEL;WORK;ENCODING=QUOTED-PRINTABLE:9 >Turbary > Ave,=0D=0ALyppard Kettleby=0D=0AWorcester > WR40PS=0D=0AEngland > >URL: > URL:http://www.cool-tools.co.uk > >EMAIL;PREF;INTERNET:[EMAIL PROTECTED] > REV:20010118T150512Z > >END:VCARD > > > >= Stephane Paquette DBA Oracle [EMAIL PROTECTED] > >__ Do You Yahoo!? Get >personalized email addresses from Yahoo! Mail - only $35 a year! >http://personal.mail.yahoo.com/ -- >Please see the official ORACLE-L FAQ: http://www.orafaq.com -- >Author: =?iso-8859-1?q?paquette=20stephane?= INET: >[EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, >California -- Public Internet access / Mailing Lists > To >REMOVE yourself from this mailing list, send an E-Mail message to: >[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message >BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing >list you want to be removed from). You may also send the HELP command for >other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: What are the Commonly Used SGA Sizes on VLDBs ?
Hi, The limit is 4GB per shared memory segment. The SGA can be defined to the amount of memory you have on the machine if the OS implements virtwin calls. For eg: on IBM NUMA using virtwin calls you can define a larger SGA and for one of our benchmarks we had a 9G SGA in 3 shm segments. There is a init.ora parameter on the NUMA use_indirect_data_buffers=true that will enable the usage of these database buffers.I think Solaris also implements virtwin calls. Â Hope this helps. Regards, Madhavan >From: [EMAIL PROTECTED] >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Re: What are the Commonly Used SGA Sizes on VLDBs ? >Date: Wed, 14 Feb 2001 07:05:49 -0800 > > >Hi > There is a 4GB virtual memory limit / process in 32 bit unix systems. >Oracle attaches the shared memory segments at 0x8000 by default and >grows upwards. Since the dlopen calls attaches at 0xEF00, the limit is >around 1.73 GB. But you could change the ksms.s module sgabeg variable and >ask Oracle to attach at say 0x100 and which would give you around >3.7GB. It is pretty simple. Search metalink for the details steps. >Basically, you edit the ksms.s file change the sgabeg variable value to >0x1000 and recompile all the objects. > > Limitation is because in 32 bit OS the limit is 4GB. If you want SGA >size beyound this, then you have to go to 64 bit, where the size is >virtually limitless. >Thanks >Riyaj "Re-yas" Shamsudeen >Certified Oracle DBA >"Usual disclaimers apply" > > > > "Gene Sais" > <[EMAIL PROTECTED] <[EMAIL PROTECTED] ORACLE-L list of recipients Multiple To:> > ach.fl.us> cc: > Sent by: Subject: Re: What are the Commonly Used SGA Sizes on VLDBs ? > [EMAIL PROTECTED] > > > 02/14/01 06:55 AM > Please respond to > ORACLE-L > > > > > > > > >+++ >Gene Sais >Senior Oracle DBA/Systems Admin >http://www.pbcountyclerk.com >[EMAIL PROTECTED] >561.355.6579 [phone] >561.355.2600 [fax] >+++ > > >>> [EMAIL PROTECTED] 02/14 5:05 AM >>> > >What are the Commonly Used SGA Sizes on VLDBs ? >Answers below: > >On SUN E6500 (4u) model , Solaris 2.6 , we are Unable to increase the SGA >Beyond 1.7 GB on Oracle 7.3.4.5 > >-->Max size on 32 bit OS and oracle, 2.6 is 32 bit > >Qs. What are the ways of increasing the SGA beyond this Limit ? >--> Upgrade your OS and Oracle to 8i > >Qs. What are the Negative Implications of of RE-Creating the Oracle Exe >With >a Lowered Base Address to Allow Bigger SGA ? >--> Good luck on this one > >NOTE - This may Give us a Only a Few Hundred MB . What if we Need MORE ? > >Qs. Would this Limitaition be on Oracle 8i too ? >It is a limitation on 32bit vs 64bit oracle and os > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: VIVEK_SHARMA > INET: [EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Gene Sais > INET: [EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > > > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: > INET: [EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PR