Oracle Text tuning [message #642503] |
Sun, 13 September 2015 10:42 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c5e309d8506cc11adc7f8af6a3392b19?s=64&d=mm&r=g) |
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
|
|
|
|
Re: Oracle Text tuning [message #642517 is a reply to message #642504] |
Sun, 13 September 2015 21:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c5e309d8506cc11adc7f8af6a3392b19?s=64&d=mm&r=g) |
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 #642601 is a reply to message #642600] |
Tue, 15 September 2015 07:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 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?
|
|
|
Re: Oracle Text tuning [message #642602 is a reply to message #642601] |
Tue, 15 September 2015 07:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c5e309d8506cc11adc7f8af6a3392b19?s=64&d=mm&r=g) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 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.
|
|
|
Re: Oracle Text tuning [message #642604 is a reply to message #642602] |
Tue, 15 September 2015 07:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 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?
|
|
|
|
|
Re: Oracle Text tuning [message #642907 is a reply to message #642605] |
Tue, 22 September 2015 21:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c5e309d8506cc11adc7f8af6a3392b19?s=64&d=mm&r=g) |
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 #642921 is a reply to message #642907] |
Wed, 23 September 2015 01:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 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>
|
|
|
|
Re: Oracle Text tuning [message #642928 is a reply to message #642923] |
Wed, 23 September 2015 02:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
devan0165 wrote on Wed, 23 September 2015 07:44Hi 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 #642932 is a reply to message #642931] |
Wed, 23 September 2015 02:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 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.
|
|
|
Re: Oracle Text tuning [message #642933 is a reply to message #642931] |
Wed, 23 September 2015 02:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c5e309d8506cc11adc7f8af6a3392b19?s=64&d=mm&r=g) |
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 #643004 is a reply to message #642946] |
Fri, 25 September 2015 03:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c5e309d8506cc11adc7f8af6a3392b19?s=64&d=mm&r=g) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 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.
|
|
|
|
Re: Oracle Text tuning [message #643055 is a reply to message #643054] |
Sun, 27 September 2015 08:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 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.
|
|
|
Re: Oracle Text tuning [message #643056 is a reply to message #643055] |
Sun, 27 September 2015 08:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c5e309d8506cc11adc7f8af6a3392b19?s=64&d=mm&r=g) |
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
|
|
|
|
|
|