OraFAQ Forum: Text & interMedia » Oracle Text tuning

Home » Server Options » Text & interMedia » Oracle Text tuning (Oracle,10GR2,Linux RHEL6)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Text tuning [message #642503]

Sun, 13 September 2015 10:42

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Hi All,

I am newbie to Oracle 10g.

I have a linux box with oracle text installed running with total RAM of 16GB.

The server configured as below:-

1) SHMMAX = 13GB
2) SGA = 8GB
3) PGA = 4800M
4) CTX index memory = 2 GB.

Are the above oracle settings consider optimal for fulltext reindexing?

Are the CTX memory and PGA allocated from SHMMAX ?

Please help me to have oracle optimal setting

Report message to a moderator

Re: Oracle Text tuning [message #642504 is a reply to message #642503]

Sun, 13 September 2015 11:29

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

The CTX memory comes from your PGA, which is not shared memeory.

I wouldn't know how to calculate an optimal value, but I can say that you will NEVER get more than 2GB of PGA and unless you disable automatic PGA management for your session you will probably never get one tenth of that.

Is performance of reindexing a problem?

Report message to a moderator

Re: Oracle Text tuning [message #642517 is a reply to message #642504]

Sun, 13 September 2015 21:06

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Hi John,

Thanks for your speedy reply.

Yes, you are right.

For example, a base table with 2.5 millions records, rebuild normally take 21 hours to complete.

If CTX index memory allocated from PGA memory not shared memory (SGA), is the CTX and PGA memory allocated directly from remaining 3GB of linux OS RAM (Total RAM-Total SHMMAX = 16GB-13GB = 3GB) ?

I always confused between PGA and CTX index memory compared with shared memory.

What I know that the shared memory belong to SGA only.

Please clear me on this differences.

[Updated on: Sun, 13 September 2015 21:38]

Report message to a moderator

Re: Oracle Text tuning [message #642600 is a reply to message #642517]

Tue, 15 September 2015 07:24

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Hi All,

Don't tell me nobody out there can give answer to my last question...

Report message to a moderator

Re: Oracle Text tuning [message #642601 is a reply to message #642600]

Tue, 15 September 2015 07:31

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

I've already given you a hint for what I would do: tune my PGA usage so that I can have more. Have you made any attempt to tune it? Have you looked at how much PGA your session is using? How much temp space is being used?

Report message to a moderator

Re: Oracle Text tuning [message #642602 is a reply to message #642601]

Tue, 15 September 2015 07:41

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Hi John,

I have made several attempts to fine tune the PGA memory but still no progress.

The rebuilding is running now. The current session for PGA as shown below:-

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 15 20:38:57 2015

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

NM MB
----- ----------
pga 4474
sga 8192
total 12666

Disconnected from Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

Can you let me know which temp space are you referring to? Is it 'undotbs1' or 'temp' tablespace ?

[Updated on: Tue, 15 September 2015 07:44]

Report message to a moderator

Re: Oracle Text tuning [message #642603 is a reply to message #642602]

Tue, 15 September 2015 07:44

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Really? What did these several attempts consist of? How much PGA is your session getting? How much temp space? I guess you haven't tried using manual PGA managementradther than automatic, which was I suggested originally.

Report message to a moderator

Re: Oracle Text tuning [message #642604 is a reply to message #642602]

Tue, 15 September 2015 07:46

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Recieved this report:

Quote:

Reported By: devan0165 On: Tue, 15 September 2015 13:22 In: Server Options » Text & interMedia » Oracle Text tuning
Reason: Nobody is giving answers to my last question.

why did you report this? Are you under the impression that you are entitled to free support?

Report message to a moderator

Re: Oracle Text tuning [message #642605 is a reply to message #642604]

Tue, 15 September 2015 08:00

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

No, John.

I just need quick solution so that I can proceed further.

Sorry again if I did wrong here.

If that's the case, let me try to tune using manual PGA
and not auto.

[Updated on: Tue, 15 September 2015 08:01]

Report message to a moderator

Re: Oracle Text tuning [message #642669 is a reply to message #642605]

Wed, 16 September 2015 16:08

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Please see the following section of the online documentation for some general guidelines.

http://docs.oracle.com/cd/B19306_01/text.102/b14217/aoptim.htm#i1006756

Report message to a moderator

Re: Oracle Text tuning [message #642907 is a reply to message #642605]

Tue, 22 September 2015 21:03

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

I have added additional 4GB physical memory to make the total RAM as 20GB in the production server.

cat /proc/meminfo | grep MemTotal | awk '{ print $2 }'
20471020

The new kernel parameters configured as below:-

kernel.shmmax = 10737418240
kernel.shmall = 5242880
kernel.shmmni = 4096

I have increased the SGA from 8GB to 10GB and PGA from 4800M to 7GB.

The new SGA and PGA as configured below:-

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 23 09:45:28 2015

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 10G
sga_target big integer 10G

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 7G
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

The CTX index memory remain the same as 2GB:-

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 23 09:52:09 2015

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

PAR_NAME
------------------------------
PAR_VALUE
--------------------------------------------------------------------------------
DEFAULT_INDEX_MEMORY
2097152000

MAX_INDEX_MEMORY
2147483647

But still, the time taken to rebuild text index remain the same.

What could be the problem here. Is the kernel and oracle settings wrong ?

[Updated on: Tue, 22 September 2015 21:10]

Report message to a moderator

Re: Oracle Text tuning [message #642915 is a reply to message #642907]

Tue, 22 September 2015 23:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>What could be the problem here
The bottleneck was NOT the size of SGA or amount of RAM.

I would expect that disk I/O is the bottleneck, since index rebuild requires a disk read of original index & a disk write for the new index.
Notice that only single index value needs to reside in SGA at any point in time.

Report message to a moderator

Re: Oracle Text tuning [message #642921 is a reply to message #642907]

Wed, 23 September 2015 01:38

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

I did suggest that check how much PGA and temp space your session is using. It would have bee sensible to do that before throwing money at the problem. Better do it now. You also said that you were going to try manual PGA management, as I suggested. Did you actually do that>

Report message to a moderator

Re: Oracle Text tuning [message #642923 is a reply to message #642921]

Wed, 23 September 2015 01:44

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Hi John,

I am not sure how to handle the manual PGA management.

Some said from google, this may degrade the performance if use manual PGA.

So, that I increase the RAM.

Any guideline on how to handle the manual PGA ?

Report message to a moderator

Re: Oracle Text tuning [message #642928 is a reply to message #642923]

Wed, 23 September 2015 02:01

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

devan0165 wrote on Wed, 23 September 2015 07:44

Hi John,

I am not sure how to handle the manual PGA management.

Some said from google, this may degrade the performance if use manual PGA.

So, that I increase the RAM.

Any guideline on how to handle the manual PGA ?

Why did you say you would try it, when you don't know how? Here is a start. Do it all in one session:

alter session set workarea_size_policy=manual;
alter session set sort_area_size=2147483647;
alter session set sort_hash_size=2147483647;
select value from v$mystat where statistic# = (select statistic# from v$statname where name='session pga memory');
select value from v$mystat where statistic# = (select statistic# from v$statname where name='temp space allocated (bytes)');
set timing on
--now run your rebuild
--then re-run the queries above

--update: correction, not
alter session set sort_hash_size=2147483647;
but
alter session set hash_area_size=2147483647;

[Updated on: Wed, 23 September 2015 02:24]

Report message to a moderator

Re: Oracle Text tuning [message #642931 is a reply to message #642928]

Wed, 23 September 2015 02:19

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Ok. Thanks.

I need to stay overnight since the rebuild runs at night.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
alter session set workarea_size_policy=manual;
alter session set sort_area_size=2147483647;
alter session set sort_hash_size=2147483647;
select value from v$mystat where statistic# = (select statistic# from v$statname where name='session pga memory');
select value from v$mystat where statistic# = (select statistic# from v$statname where name='temp space allocated (bytes)');
set timing on
--now run your rebuild
--then re-run the queries above
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Meaning to say, I need to run the below sql statement first before the rebuild start:-

alter session set workarea_size_policy=manual;
alter session set sort_area_size=2147483647;
alter session set sort_hash_size=2147483647;
select value from v$mystat where statistic# = (select statistic# from v$statname where name='session pga memory');
select value from v$mystat where statistic# = (select statistic# from v$statname where name='temp space allocated (bytes)');
set timing on

And I re-run the below queries during the rebuild :-
select value from v$mystat where statistic# = (select statistic# from v$statname where name='session pga memory');
select value from v$mystat where statistic# = (select statistic# from v$statname where name='temp space allocated (bytes)');

How many times that I need to re-run the queries during the rebuild or after the rebuild ?

Report message to a moderator

Re: Oracle Text tuning [message #642932 is a reply to message #642931]

Wed, 23 September 2015 02:22

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

I said, "Do it all in one session".
Furthermore, you should be looking up the parameters and understanding what those queries are telling you.

Report message to a moderator

Re: Oracle Text tuning [message #642933 is a reply to message #642931]

Wed, 23 September 2015 02:25

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

I was testing the below sql statement:-

alter session set workarea_size_policy=manual;
alter session set sort_area_size=2147483647;
alter session set sort_hash_size=2147483647;
select value from v$mystat where statistic# = (select statistic# from v$statname where name='session pga memory');
select value from v$mystat where statistic# = (select statistic# from v$statname where name='temp space allocated (bytes)');
set timing on

Encountered the below error:-

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 23 15:21:16 2015

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

Session altered.

Session altered.

alter session set sort_hash_size=2147483647
*
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

VALUE
----------
748936

no rows selected

Disconnected from Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

[Updated on: Wed, 23 September 2015 02:28]

Report message to a moderator

Re: Oracle Text tuning [message #642939 is a reply to message #642933]

Wed, 23 September 2015 04:18

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Now it works

<MPDDB@MPDDB:[/home/oracle/devan/tune]:1005> sqlplus / as sysdba @pga_manual.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 23 17:17:38 2015

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

Session altered.

Session altered.

Session altered.

VALUE
----------
421256

no rows selected

Disconnected from Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

Report message to a moderator

Re: Oracle Text tuning [message #642940 is a reply to message #642939]

Wed, 23 September 2015 04:21

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Hi John,

How am I going to run this sql statement for the rebuild since I using special script
provided by vendor.

The rebuild must run within one same session.

Please advise if there is any other way to run this.

Report message to a moderator

Re: Oracle Text tuning [message #642946 is a reply to message #642940]

Wed, 23 September 2015 07:00

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

You are going to have to use your initiative.

Report message to a moderator

Re: Oracle Text tuning [message #642984 is a reply to message #642940]

Wed, 23 September 2015 23:01

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

You said you are using a special script provided by the vendor to rebuild the text index. Is it just rebuilding one text index for one table? Can you just extract that one alter index ... rebuild command from the script? If your index rebuild is slow, then perhaps the problem is somewhere in that script and not in your general parameter settings.

Report message to a moderator

Re: Oracle Text tuning [message #642995 is a reply to message #642984]

Thu, 24 September 2015 08:01

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Hi Barbara,

Ok.

I shall try to extract only the reindexing
part from the script.

Thanks for your respond.

Report message to a moderator

Re: Oracle Text tuning [message #643004 is a reply to message #642946]

Fri, 25 September 2015 03:43

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Finally, I did got the below results:-

Results before start re-indexing:-
----------------------------------

Session altered.

Session altered.

Session altered.

VALUE
----------
1011080

no rows selected

Results after re-indexing:-
---------------------------

SQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name='session pga memory');

VALUE
----------
2330619272

SQL> select value from v$mystat where statistic# = (select statistic# from v$statname where name='temp space allocated (bytes)');

no rows selected

The above results show that the PGA used was 2330619272 bytes.

But, there are no results for 'temp space allocated (bytes)'.

Why there are no values for temp used ?

What is our next action after this results?

[Updated on: Fri, 25 September 2015 03:46]

Report message to a moderator

Re: Oracle Text tuning [message #643005 is a reply to message #643004]

Fri, 25 September 2015 03:50

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

You omitted to show the timings. Also, what are the results of those queries when you run the process using automatic PGA management? there is not a lot of point in changing something if you have nothing with which to compare it.

As for your latest question, have you looked up those statistics yet? And the session parameters? When you do, the explanation will be obvious.

Report message to a moderator

Re: Oracle Text tuning [message #643054 is a reply to message #643005]

Sun, 27 September 2015 08:26

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Hi John,

I did included the statement 'set timming on' but no results shown.

I shall try to gather the same statistics with auto PGA management.

Can I run the below sql to gather on 'temp space allocated (bytes)' :-

-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/temp_usage.sql
-- Author : Tim Hall
-- Description : Displays temp usage for all session currently using temp space.
-- Requirements : Access to the V$ views.
-- Call Syntax : @temp_usage
-- Last Modified: 12/02/2004
-- -----------------------------------------------------------------------------------

COLUMN temp_used FORMAT 9999999999

SELECT NVL(s.username, '(background)') AS username,
s.sid,
s.serial#,
ROUND(ss.value/1024/1024, 2) AS temp_used_mb
FROM v$session s
JOIN v$sesstat ss ON s.sid = ss.sid
JOIN v$statname sn ON ss.statistic# = sn.statistic#
WHERE sn.name = 'temp space allocated (bytes)'
AND ss.value > 0
ORDER BY 1;

The above sql was from link https://oracle-base.com/dba/script?category=monitoring&file=temp_usage.sql

Report message to a moderator

Re: Oracle Text tuning [message #643055 is a reply to message #643054]

Sun, 27 September 2015 08:38

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Is "set timming on" the same as "set timing on"? No.
Do you have any idea of how log the rebuild took? Apparently not.
Do you understand my queries? No
Do you understand Tim Hall's query? No.
Have you looked up definitions of the views? No.
Have you looked up the meaning of those statistics? No.
Have you looked up the parameters you are setting? No.
Have you researched the difference between manual and automatic PGA management? No.
Do you have any of the problem solving skills needed in DBA work? Not that I can see.
Do I have any more patience for this? No.
Is anyone else prepared to help? Perhaps. But I've had enough. Sorry. Life is too short.

Report message to a moderator

Re: Oracle Text tuning [message #643056 is a reply to message #643055]

Sun, 27 September 2015 08:53

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Hi John,

Sorry, it was typo error.

Should be 'set timing on'.

Please do not analyze my work.

This forum is use to share solutions and feedback, not analyzing other people's work.

Why is it so hard to solve this kind of problem.

You as senior member should analyze problem and share any with others.

I hope you understand.

If you cannot solve this problem, please say so.

[Updated on: Sun, 27 September 2015 09:16]

Report message to a moderator

Re: Oracle Text tuning [message #643057 is a reply to message #643056]

Sun, 27 September 2015 10:37

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

John is able to solve this problem but you are unable to give him the information to solve it.

Quote:

You as senior member should analyze problem and share any with others.

Note that no one here owe you anything.

Report message to a moderator

Re: Oracle Text tuning [message #643062 is a reply to message #643057]

Sun, 27 September 2015 21:45

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Hi Micheal,

Thanks for your reply.

John seeking various type of information which I cannot provide without
proper sql command given to retrieve such details.

Now, that he is seeking information, can he specify what kind of details required by him.

This way, we can proceed further without any problem.

Some of the sql command given by John is not working, example command "set timing on".

Report message to a moderator

Re: Oracle Text tuning [message #643063 is a reply to message #643062]

Mon, 28 September 2015 00:31

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What do yo mean "is not working", it perfectly works for me:

SQL> set timing on
SQL> select * from dual;
D
-
X

1 row selected.

Elapsed: 00:00:00.03

Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Previous Topic:	ORA-29849 exception
Next Topic:	matching names from two tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 18:48:02 CST 2025