Home » RDBMS Server » Server Administration » How partition can be brought offline (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
| How partition can be brought offline [message #538185] |
Wed, 04 January 2012 10:30  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi,
I have a partitioned table - 128 partitions.
If I am not using data of many partitions in any way, will it affect my performance if I am firing select query that uses other/active partitions data.
How can I check when that partitioned was last accessed, also can I brought those inactive partitions offlie? If we can, what will be the advantages or disadvantages of that?
Any expert suggestion or link will help.
Thank you.
|
|
|
|
|
|
|
|
|
|
| Re: How partition can be brought offline [message #538196 is a reply to message #538191] |
Wed, 04 January 2012 10:44  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You can move the partition to another table by using partition exchange, check out the EXCHANGE clause of ALTER TABLE. You'll need to be careful of the effect on any global indexes. But if your partition pruning is working effectively, it probably won't have any effect on performance.
What can sometimes be useful is to move the partitions into a tablespace which you make read-only. That can have a beneficial effect on your backups and (in a RAC) reduce the work needed for resource mastering.
hth, John.
update: typo, I've corrected "exchange the partitions" to "move the partitions". Sorry about that.
[Updated on: Wed, 04 January 2012 12:03] Report message to a moderator |
|
|
|
|
|
| Re: How partition can be brought offline [message #538206 is a reply to message #538198] |
Wed, 04 January 2012 11:37 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Thanks BlackSwan.
Yeah, it gave me a more clear picture when you told me to evnision partitions as seprate tables.
But still I am unanswered with my question "Partition can be brought offline/deleted or not?"
Thanks to John, I seriously know a very little about these facts. By global indexes means that are applied on table as a whole (not on any partition), am I right? If so, yes I have global indexes, and want to ask, that while exchange process, will data will not move/removed from global index? Also can you give me some useful link from where u got all that info, as I tried to google but either I am not giving correct keyword or its not displaying me intended pages.
Thanks again!!
|
|
|
|
|
|
|
|
| Re: How partition can be brought offline [message #538209 is a reply to message #538206] |
Wed, 04 January 2012 12:00 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:By global indexes means that are applied on table as a whole (not on any partition), am I right? If so, yes I have global indexes, and want to ask, that while exchange process, will data will not move/removed from global index? Exactly right. The issue is that a partition exchange is a DDL operation that completes in seconds, unless there are global indexes. Existence of a global index forces Oracle to delete all the index keys, which means the operation can take hours.
Quote:Also can you give me some useful link from where u got all that info, Not really. It is all in the docs,
http://docs.oracle.com/cd/E11882_01/server.112/e25523/toc.htm
[Updated on: Wed, 04 January 2012 12:01] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: How partition can be brought offline [message #538359 is a reply to message #538358] |
Thu, 05 January 2012 08:39 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You've read the doc I pointed you towards? (The correct answer is "yes".)
Get partitioning right, and the benefits can be huge; get it wrong, and it can be disastrous. You need to be absolutely clear on why you are doing it.
Your stated intention is to remove partitions from the table (I've already told you how to do that) but you have not yet demonstrated why this is necessary and what advantage it would bring. As I already said, if your partition pruning is working effectively, then there is no need to remove partitions. If partition pruning is not working, then your partitioning strategy is probably wrong. What are the execution plans that you believe would improve if you did this?
I would suggest that you may need to revisit the partitioning strategy. Document how it is being done, for the tables and the indexes; determine what issues you have; consider what changes (if any) are necessary to address those issues.
There have been some recent blog articles you might want to read, such as http://www.orafaq.com/node/2591 and http://www.orafaq.com/node/2570
|
|
|
|
| Re: How partition can be brought offline [message #538369 is a reply to message #538359] |
Thu, 05 January 2012 10:04 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
John Watson wrote on Thu, 05 January 2012 20:09As I already said, if your partition pruning is working effectively, then there is no need to remove partitions. If partition pruning is not working, then your partitioning strategy is probably wrong. What are the execution plans that you believe would improve if you did this?
I remember all the things you told earlier, thats why stated my actual problem above.
Quote:I have a table partitioned based on seq_no, so partitioning will be useful when table is queried against seq_no.
But what if table is queried against any other column, then all the records in the table will be queried.... right?
Let me demonstrate you my table structure.
desc vendor_data -- hash partitioned on study_seq_no
Name Null Type
--------------------- -------- --------------
VENDOR_DATA_SEQ_NO NOT NULL NUMBER
STUDY_SEQ_NO NOT NULL NUMBER
VENDOR_RECORD_SEQ_NO NOT NULL NUMBER
CONTROL_COLUMN_SEQ_NO NOT NULL NUMBER
RESOLVED_VALUE VARCHAR2(4000)
ORIGINAL_VALUE VARCHAR2(4000)
TRANSACTION_USER NOT NULL VARCHAR2(30)
TRANSACTION_SRC NOT NULL VARCHAR2(30)
TRANSACTION_DT NOT NULL DATE
TRANSACTION_TYPE NOT NULL VARCHAR2(1)
Indexes on VENDOR_DATA table (No function bases indexes all ascending)
**********************************************************************
VENDOR_DATA_IDX04 (STUDY_SEQ_NO, CONTROL_COLUMN_SEQ_NO, VENDOR_RECORD_SEQ_NO, ORIGINAL_VALUE)
VENDOR_DATA_PK (VENDOR_RECORD_SEQ_NO)
VENDOR_DATA_FK01 (STUDY_SEQ_NO)
VENDOR_DATA_FK02 (STUDY_SEQ_NO, VENDOR_RECORD_SEQ_NO)
VENDOR_DATA_FK03 (CONTROL_COLUMN_SEQ_NO)
VENDOR_DATA_UK01 (STUDY_SEQ_NO, VENDOR_DATA_SEQ_NO)
Now I am asking, if vendor_data is queried on columns (on which vendor_data is not partitioned), how would it take effect in below 2 cases:
1.) Column have an index
2.) Column doesn't have any index associated
Please let me know in case you need any further clarification.
Thanks a lot.
[Updated on: Thu, 05 January 2012 10:15] Report message to a moderator |
|
|
|
| Re: How partition can be brought offline [message #538370 is a reply to message #538369] |
Thu, 05 January 2012 10:17 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
OK, as I thought, you need to do a lot of preparatory work.
First, you are using hash partitioning on study_seq_no. That means that you have no way of knowing which partition(s) will be accessed by a query on any one study_seq_no, and therefore your original premise that you have "active" and "inactive" partitions is probably wrong.
Second, your index vendor_data_fk01 is pointless and should be dropped.
Third, we do not appear to know how (or if) your indexes are partitioned.
Overall, you need to do as I suggested: ask a few questions about how this table is setup, and why. What was the purpose of the partitioning? Did it meet that purpose? What do you actually want to achieve?
|
|
|
|
| Re: How partition can be brought offline [message #538375 is a reply to message #538370] |
Thu, 05 January 2012 11:29 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hmmm, yes it is hash partitioning, but I already found which study is stored on which partition using the below query-
SELECT DISTINCT study_seq_no
FROM vendor_data PARTITION (SYS_P199);
I saw that there is only data related to one study_seq_no per partition. (One Study seq no <--> One partition) and we know which studies are not in use.
Thats why I thought to bring some partitions data out of table.
I can't say anything for vendor_data_fk01 as I don't know whether its global or local. Let me check this and I will let you know.
EDIT - can you tell how to see partitioned indexes/indexes on partitions?
Thanks!!
[Updated on: Thu, 05 January 2012 11:32] Report message to a moderator |
|
|
|
|
|
|
|
| Re: How partition can be brought offline [message #538392 is a reply to message #538385] |
Thu, 05 January 2012 13:34 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi. You said earlier that you have global indexes, but your last post shows that you have at least one local index. Unless you can document what you have, you will never get anywhere!
But most important of all, what is the problem? you have never said what problem you are trying to deal with. Do you actually have any problem at all?
And by the way, was the partitioning intended to have one partition per study_seq_no? There is no way that can be guaranteed, and if that is what you want (why would you want that?) you should use list partitioning.
Do you see what I am getting at? With partitioning, you must define what you want to achieve. Perhaps you shouldn't be using it at all.
|
|
|
|
| Re: How partition can be brought offline [message #538469 is a reply to message #538392] |
Fri, 06 January 2012 06:18 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi John,
Till then I was not aware of impact of local and global indexes. And yes, I don't know whether partitioning is a problem or not, I am just facing problem in a process (process is very slow), so I am investigating it, checking partitions in vendor_data table is my first step (as this table contains very huge volume of data (516337703 rows), may be that is the reason its making process very slow). I haven't stated anywhere that there should be one study per partition, I stated that right now there is only one study per partition (I know there may be more in future). Below is the index information-
Indexes on VENDOR_DATA table (No function bases indexes all ascending)
**********************************************************************
VENDOR_DATA_IDX04 (STUDY_SEQ_NO, CONTROL_COLUMN_SEQ_NO, VENDOR_RECORD_SEQ_NO, ORIGINAL_VALUE) partitioned
VENDOR_DATA_PK (VENDOR_RECORD_SEQ_NO) Global
VENDOR_DATA_FK01 (STUDY_SEQ_NO) partitioned
VENDOR_DATA_FK02 (STUDY_SEQ_NO, VENDOR_RECORD_SEQ_NO) partitioned
VENDOR_DATA_FK03 (CONTROL_COLUMN_SEQ_NO) Global
VENDOR_DATA_UK01 (STUDY_SEQ_NO, VENDOR_DATA_SEQ_NO) partitioned
By seeing the above indexes can you tell, is there any problem? Or does it need to investigate further. Also can I know when last time a partition is accessed by any select query, is there any way (I remember last time someone told me Auditing, but how?) Also I am trying to find out all select queries executed in that process through FGA, will that help?
Waiting for your suggestions.
Thanks Again!!
|
|
|
|
| Re: How partition can be brought offline [message #538471 is a reply to message #538469] |
Fri, 06 January 2012 06:33 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Now we know what the problem is: a process is too slow. What is the process? Is it one SELECT statement, or a mass of PL/SQL, or what?
Somewhere in this process there will be one or more SQL statements that may need tuning. I would begin by generating a statspack level 7 report (or an AWR report, if you have the licence) over the period that this process is running, and upload it here. That report should be your starting point: it will show you the statements that are taking the most time and resources. Then you can look into what can be done to improve them.
(by the way, the indexes look fine: the ones that should be local are local, the ones that perhaps should be global are global)
|
|
|
|
| Re: How partition can be brought offline [message #538473 is a reply to message #538471] |
Fri, 06 January 2012 06:39 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hmmmm, thanks for info. 
Process is mass of PL/SQL. Its made to load plain text file (with delimiters) to SQL tables after performaing various checks/joins.
I will try to get AWR report asap, and will upload it here. I don't know how to read AWR report, and do you need AWR report in html format or palin text format?
EDIT- I have access on all Data Dictionary views. Can I generate AWR report on my own? I am using Toad, or should I ask DBA for this. One more confusion is that, Is AWR report for a particular time period or for a particular session ID?
Thanks.
[Updated on: Fri, 06 January 2012 06:45] Report message to a moderator |
|
|
|
| Re: How partition can be brought offline [message #538476 is a reply to message #538473] |
Fri, 06 January 2012 07:00 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Ask your DBA to generate an AWR snapshot, then run your process, then ask him to generate another snapshot: you want a report that covers as nearly as possible exactly the running of the process. Upload both versions, some people on the forum may have different preferences.
I don't know Toad, but if you have permissions you can do all this yourself. Generate the snapshots with
exec dbms_workload_repository.create_snapshot
and the report by running the $ORACLE_HOME/rdbms/admin/awrrpt.sql script.
|
|
|
|
Goto Forum:
Current Time: Fri Nov 29 04:29:01 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
