PL/SQL :: Oracle 11g - Huge Table Statistics
Jun 13, 2012
I have a table which have 300+ columns and have 13 million rows. It is on a 32 kb block size. This is a table in data ware house environment. There no# of rows in the table haven't changed much but I see that the time taken to collect statistics have increased significantly.Initially it took only 15 minutes (with the same 13M rows) now it runs for 4+ hours. The max parallel servers is 4 (which is unchanged). The table is not partitioned.
OS: HP UX Itanium
Database: Oracle 11g (11.2.0.2)
Command is:
exec dbms_stats.gather_table_stats(ownname=>'ABC',tabname=>'ABC_LOAD',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,DEGREE=>dbms_stats.auto_degree);
I would like to understand:
1) What could have been the causes of this change in time. 15 minutes to 4+hours ?
2) How can we gather statistics of huge table at a faster rate?
View 1 Replies
ADVERTISEMENT
Nov 6, 2012
I am using oracle 11g Express Edition, I have a file of .csv forma, Which has a data of size 500MB which needs to be uploaded into oracle table.
Which would be the best method to upload the data into table. Data is employee ticket history which is of huge data.
How to do the mass upload of data into oracle table.
View 3 Replies
View Related
Oct 25, 2012
I want to get the stale stats for Table resides at APPS schema. Is there is any table or view exists to get the details like DBA_STALE_STATS or anything? Currently I am checking LAST_ANALYZED column from DBA_TABLES?
View 2 Replies
View Related
Jun 29, 2011
I need to load (using SQL Loader) an huge XML file, with several hundreds of records into an Oracle Table.The XML file schema is pretty simple, and it's anything like this:
<dataroot>
<record>
<companyname>LimitSoft S.A.</companyname>
<address>Street Number 1</address>
[code]...
I'm trying to use the help included in this link [URL]...
When they refer to schema[URL].... what should I use?? I do not need to use the Oracle website to register anything, right?
View 4 Replies
View Related
Aug 9, 2012
I am trying to drop a column from a huge partitioned table (non compressed including partitions). I am working on 11gR2 database for information.
i used below approach
1. alter table <tab_name> set t1 unused column;
2. alter table <tab_name> drop unused columns;
then i got the below error message
ORA-39726: unsupported add/drop column operation on compressed tables
First statement did work i could be able to add another column with the same name but still don't want the unused column on the table.
View 5 Replies
View Related
Apr 29, 2013
Consider tables A,B,C,D,E,F. all are having 100000++ records Tables B,C,D are dependent on table A (with foreign key constraint). When I am deleting records from all tables, table B,C,D are taking max 30-40 seconds while table A is taking 30-40 mins. All tables are having indexes.
Method I have used:
1. Created Temp table
2. then deleted all records from B,C,D,E,F for all records in temp table for limit of 500.
delete from B where exists (select 1 from temp where b.col1=temp.col1);
3. why it is taking too much time for deleting records in table A.
View 5 Replies
View Related
Aug 11, 2011
Below is my requirement.
Need to change the precision of a column in a existing table. Statistics about the table
* has over 130 columns
* More than 300 million records
* Column to modify is #121 which has data
* No primary key defined
Since the column has data, it is not possible to modify with a simple Alter.
Second option - create temp column in same table, update from original, put null in original, alter, update back from temp, drop the temp column. This approach is very expensive and time consuming.
Also the Column ID needs to preserved as #121.
View 3 Replies
View Related
Jun 13, 2013
I am trying to delete 3 million records of data from huge table which already consists of 3 billion records.
This is hitting performance of DB and halting other activities of my users. Is there any easy way to delete such data fast. I have tried with forall delete but it is even taking lot of time.
View 5 Replies
View Related
Jun 22, 2011
I have this table
create table ACTIONARI_ARH
(
actionar_id NUMBER(10) not null,
id VARCHAR2(20) not null,
id_2 VARCHAR2(20),
tip VARCHAR2(1),
nume VARCHAR2(100),
prenume VARCHAR2(100),
adresa VARCHAR2(200),
[code]....
and this view
CREATE OR REPLACE VIEW ACTIONARI AS
SELECT "ACTIONAR_ID","ID","ID_2","TIP","NUME","PRENUME","ADRESA","LOCALITATE","JUDET","TARA","CERT_DECES","DATA_REGISTRU" Data_operare,"USER_MODIF","DATA_MODIF","REZIDENT"
FROM (
select
[code]....
The table has about 30 milion records and holds persons names, addresses, personal id (id), and internal id(actionar_id) and date when a new adress has been added.
The view is about getting only the most recent info for one person (actionar_id).
if i run a
a) select * from actionari a where a.actionar_id = 'nnnnnnn', result is returned immediatly, oracle uses index and does not do a full table scan.
b) select * from actionari a where a.actionar_id in ('nnnnnnn','mmmmmm','ooooooo'), result is returned immediatly, oracle uses index and does not do a full table scan.
my problem when i use this view in a join.let's assume i have another table with no more than 500 records, something like
create table SMALL_TABLE
(
actionar_id NUMBER(10) not null,
......
);
and if i run
select *
from SMALL_TABLE s
join actionari a
on a.actionar_id = s.actionar_id;
it takes like forever to process, forever means 1~3 minutes.by looking at the execution plan, oracle does a full table scan, creates the view for all unique 7milion persons, and only then joins the result with the actionar_is's in the small table and returns the desired 500 record result.i am using oracle 10g.
View 2 Replies
View Related
Aug 8, 2011
I want to drop a column in a huge table which contain about 420,000,000 rows,i use the alter table drop coumn command to execute,and found it takes a long time and generate huge redo.
Is there any quickly way to drop a column in a huge table?
View 5 Replies
View Related
Jul 10, 2012
Is this possible to take the dump of a huge table , say 500gb, into multiple files and then import it in other database? if yes how can we do it?
Note that it is a single table with 500GB of size.
View 1 Replies
View Related
Oct 22, 2010
how many rows certain tables have.
updating the statistics for a table (with GATER_TABLE_STATS) and using NUM_ROWS then. This works fine for me as long as I am the owner of the table, but when someone else is, I always get this error: ORA-20000: Table does not exist or insufficient privileges.what privileges do I need to use GATHER_ TABLE_ STATS on all Tables, which were created by Users?
when I tried to use ANALYZE TABLE TEST_TABLE COMPUTE STATISTICS on a certain table I got the following error: a view is not appropriate here. The strange thing is, TEST_TABLE is not a view (at least it is not listed in ALL_VIEWS and is listed in ALL_TABLES, so it cant be a view right?).
Besides, is there another way to gather Table Statistics (not using Analyze Table or Gather_Table_Stats)?
View 3 Replies
View Related
Oct 26, 2012
I have created materialized view which hold few million records.Should i have to analyse the view and compute the statistics after i create the materialized view?
Also,just in case i need further indexing,should i have to take the statistics for the table again??
View 5 Replies
View Related
May 14, 2011
What privilege is required to gather table statistics using dbms_stats ?
View 2 Replies
View Related
Apr 27, 2012
From the below query i found that there are some stale stats for 3 tables.
=================================
select table_name, stale_stats, last_analyzed
from dba_tab_statistics
where owner= 'SYSADM' and stale_stats='YES'
order by last_analyzed desc
I collect stats for those above 3 tables with dbms_stats.gather_table_stats().But no luck.After collection of stats immediately I ran the above query.But still it is showing there are stale stats for 3 tables.
how can I change "STALE-STATS" status, so that optimizer can use the updated stats eficiently.
View 3 Replies
View Related
Apr 21, 2012
Why do we gather table statistics manually ?Is it because of database performance.
I know In Oracle Database 10g, Automatic Optimizer Statistics Collection reduces the likelihood of poorly performing SQL statements due to stale or invalid statistics and enhances SQL execution performance by providing optimal input to the query optimizer.
Optimizer gathers statistics when 10% table rows have been changed.
View 9 Replies
View Related
Jul 14, 2010
I am using Oracle 11g R2 version.I want to import the DB statistics. But i am getting an exception when i execute the command DBMS_STATS.IMPORT_SCHEMA_STATS ('user1','STATS_INFO', '','', TRUE, FALSE).
The error is ORA-20000: no statistics are imported ORA-06512: at "SYS.DBMS_STATS", line 10603 ORA-06512: at line 1.
The privileges 'ANALYZE ANY' and 'ANALYZE ANY DICTIONARY' is already given to the user.Also i executed this command as sys. But still error occurs.
Same command is successfully executed in Oracle 10g. Is there any difference in importing the statistics in Oracle 10g and 11g ?
View 2 Replies
View Related
Jan 16, 2012
I use the following query to find out the remaining time to complete the table statistics which is running currently.
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,username,context,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;
SOFAR column shows 9325 and totalwork column shows 12287.How to calculate the columns in terms of hours and minutes ?
And also sometime the query never shows the output of the query for the current running query.
View 1 Replies
View Related
Jul 13, 2011
I am connect to remote database with a user named 'TEST', this user has dba privileges. I am not able to gather the statistics of neither test schema nor for any table that exists in this schema.
SQL> EXEC dbms_stats.gather_schema_stats('TEST', cascade=>TRUE);
BEGIN dbms_stats.gather_schema_stats('TEST', cascade=>TRUE); END;
*
ERROR at line 1:
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1
[code]....
View 17 Replies
View Related
Mar 5, 2013
how to create best profile for huge user in oracle database user which take lagre uga memory.
View 5 Replies
View Related
Jun 7, 2012
Is there any script that give us output of all Stale Statistics in Oracle Database
View 8 Replies
View Related
Apr 21, 2010
the importance of refreshing the stale statistics in oracle database and also how often we should do this?
View 3 Replies
View Related
May 26, 2013
I am trying to add a new column in a table and insert data from another column of same table.
alter table POSITION add INT_MK_DATA_ID number(10,0) null;
update POSITION set INT_MK_DATA_ID = INST_MARKET_DATA_ID;
commit
As there are huge number of records in the POSITION table ...its taking for ever to execute this query.
View 1 Replies
View Related
Jan 3, 2013
Here is my problem, i need to create some files with my own format(let say 5000 records each) from a huge data table (May contain 5 Million records). And i want this creation to be multi threaded.
so how can i form queries efficiently to fetch records like 1..5000 and 5001..10000 and so on. I can form some thing like select * from table where rownum<5000 and not exists ( already fetched records) . but it is not the efficient one.
View 5 Replies
View Related
Sep 13, 2013
we are running SAP application against oracle database. say, if I use brspace or brtools (from SAP side) to shutdown or startup database or collect stats, does this mean it not recommend to use oracle command to shutdown/start & collect stats?
View 3 Replies
View Related
Aug 26, 2011
I have 2 questions, because they can be inter-related I am posting it in a single post. These queries are related to Oracle(PL\SQL).
1. I am trying to increase the size of a field in a table which has almost 2 million records and the query for alteration runs for almost and hour and rollsback, wondering is there a better way of doing it.
2. I have modified the size of a field in a table from Varchar2(10) to Varchar2(20), now when I tried to rollback the modification it is not letting me to change the size from Varchar2(20) to Varchar2(10). No data has been inserted after the modification.
View 1 Replies
View Related
Aug 26, 2011
I have 2 questions, because they can be inter-related I am posting it in a single post. These queries are related to Oracle(PLSQL).
1. I am trying to increase the size of a field in a table which has almost 2 million records and the query for alteration runs for almost and hour and rollsback, wondering is there a better way of doing it.
2. I have modified the size of a field in a table from Varchar2(10) to Varchar2(20), now when I tried to rollback the modification it is not letting me to change the size from Varchar2(20) to Varchar2(10). No data has been inserted after the modification.
View 2 Replies
View Related
Apr 22, 2013
I'm currently working on a project which is to archive the old data and then purge the same data from the main table.
Here is a detail description:
There are around 50 odd tables from which I would need to archive the old data(matching certain filter conditions...not date based). Meaning I have to store the data in a temp table. Once stored in temp table then I would have to delete those rows from the main table. This temp table will be later exported and stored on ARchive database(a seperate database). These tables are very huge. One of the table is actually 250 GB in size. And all these tables have many indexes built - both normal and bitmap.The 250 GB size table has 40 million rows that need to be archived and purged. The total number of rows in the table are 540 million.On this table alone there are 50 bitmap indexes and 2 normal indexes. This table is partitioned based on date column.This date column is not used/useful in identifying the old data. There are around 20 tables which are quite similar in size to the above described table. Rest of them are little small when compared to the above table.
We have to execute this activity over a weekend which gives us about 48 hours time to complete the activity. Best possible ways to handle this activity. Most importantly should be able to complete the activity within the specified 48 hour window.
The solution what we are now thinking of is:
1. Create the temp table ---Create tmp_tbl as select * from main_table where <<conidtions identifying old data>>
2. Once the temp table is created. Make copy of indexes that exist on the main table and eventually drop them.
3. Execute a PL/SQL script to perform the bulk delete from main table and commit for every 100000 rows.
4. Once the bulk delete is finished then recreate the indexes on the main table using the copy made at earlier step.
Our main worry is about the step#4. Considering the size of these tables and the number of indexes to be built,we are not sure how long the index re-creation will run for each table.
depending on the possibilities we may have to split the activity in to 2-3 phases spreading across 2-3 weekends. Even then we are not sure whether we will be able to pull off this activity.
The database we are using is Oracle 10g.
View 1 Replies
View Related
Jun 18, 2012
I have table which contains huge data. around 12 lakhs records. when I use sum function on accountname and docdate it gives wrong value. once I restart the server it gives the correct value. one or two days it gives correct value after that again I get the same problem. If I restart again it gives correct value.
I use Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 64 bit server on Linux.
View 6 Replies
View Related
Jun 5, 2012
I need to verify huge number of records in two different databases. Basically i wanted to check if same record exist in other database's table or not? but as the number of records are more than billions who would i verify?
checking record one by one would be so hectic and time consuming. any other option is there?
View 11 Replies
View Related
