SQL & PL/SQL :: Dynamically Filling Data In Table And Rebuilding Indexes Gathering Stats
Jan 26, 2012
I have created a table like below-
PROMPT CREATE TABLE tst_fetch_vendor_data
CREATE TABLE tst_fetch_vendor_data (
vendor_data_seq_no NUMBER NOT NULL,
study_seq_no NUMBER NOT NULL,
vendor_record_seq_no NUMBER NOT NULL,
control_column_seq_no NUMBER NOT NULL,
resolved_value VARCHAR2(4000) NULL,
original_value VARCHAR2(4000) NULL,
transaction_user VARCHAR2(30) NOT NULL,
[code]....
Its just a temporary table, in which data comes and goes. I am using this in middle of a process.I am using it in a process like below--
--EXECUTE IMMEDIATE 'TRUNCATE TABLE TST_FETCH_VENDOR_DATA DROP STORAGE';
insert /*+ append */ into tst_fetch_vendor_data
(select * from vendor_data vd
where vd.control_column_seq_no in
(select control_column_seq_no from temp_control_column));
dbms_stats.gather_table_stats('EPDSYSREP','TST_FETCH_VENDOR_DATA',ESTIMATE_PERCENT=>100,
METHOD_OPT=>'for all indexed columns size auto',CASCADE=>True);
code to use that table..This table can contain data from 0 to 108000000 records.Now my questions are-
1. How much should I select sampling size (currently its 100%)Can I use dbms_stats.auto_sample_size, what will be the effect?
2. dbms_stats is good approach or should I use dynamic sampling.
3. what about the approach using CTAS instead of inserting data through insert.
4. What about pl/sql table with index or with clause query.
5. Do I need to rebuild index after inserting data into table.
View 3 Replies
ADVERTISEMENT
Sep 10, 2013
I am quiet confused with the optimizer collection stats job on 11g. when run the following query i see the statistics enabled.
SQL> select client_name, status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
I can gather statistics manually using DBMS_STATS, but there is no automated gathering of optimizer stats. how can i have system run and collect statistics on a daily bases?
View 1 Replies
View Related
Oct 1, 2012
I am on 11.2 on Linux.I am looking into a performance issue. The issue is around 1 particular SQL, involving about 5 tables.I re-gathered statistics on 2 main tables in the query (out of 5 tables).
When I say re-gathered, I first did DBMS_STATS.DELETE_TABLE_STATS and then did DBMS_STATS.GATHER_TABLE_STATS.
Earlier, we had histograms on these tables, which I removed and gathered stats without generating histograms.
SQL> select table_name, num_rows, sample_size, last_analyzed from user_tables where
2 table_name in ( 'DETAIL_TABLE','MASTER_TABLE');
TABLE_NAME NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ---------- ----------- -------------------
MASTER_TABLE 50615338 50615338 01/10/2012 11:09:27
DETAIL_TABLE 353550440 353550440 01/10/2012 11:10:05
2 rows selected.Then ran the SQL again couple of times (actually, that SQL is in a stored procedure, which I ran couple of times).I found this wonderfull SQL on internet, which tells me when the SQL ran and which plan (identified by its hash value) it used. Using this SQL I tried to check if my SQL was run using any different plan, but it used exactly same plan it used before I re-gathered the stats. See the last analyzed time above and begin_interval_time below, same SQL has run before and after stats collection, with same plan_hash_value.
SQL> select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
2 nvl(executions_delta,0) execs,
3 (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
4 (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
5 from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
[code]....
7 rows selected.
My question is, when I re-gathered stats on 2 tables out of 5 tables in a given SQL, are the plans not flushed out of SGA? I was expecting that, at least a new plan hash value would show up front of my SQL, before and after stats collection.
View 8 Replies
View Related
Oct 26, 2010
Oracle 10g has the feature of automatic stats gathering in this case is it necessary to run DBMS_STATS on tables manually. Does the stats gathered become stale when the auto stat runs ?
View 1 Replies
View Related
Jun 2, 2011
I am gathering stats by using below block i.e., for some 3 million records and there are 6 indexes on the table. What is the relevance of value 4 here (i.e., method_opt => 'FOR ALL INDEXED COLUMNS SIZE 4')? If I increase 4 to 250 will there be any speed change in gathering stats. My intention is to speed up the gathering of stats.
begin
dbms_stats.gather_table_stats(
ownname => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
tabname => 'LEGAL_VIEW_TARGET',
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 4',
cascade => TRUE
);
END;
View 12 Replies
View Related
May 16, 2011
I have several databases that i've recently upgraded from 9i to 11g. With all of them, the automatic stats gathering process has worked just fine every night during the maintenance window.
However, i have this other database that i created and it seems that the only stats being gathered are on the sys and system schemas and not the actual schema that holds all of our tables.
I did some searching, but i'm not sure i was using the right search terms, because i came up empty.
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
View 16 Replies
View Related
Jan 27, 2011
DBMS_STATS.CREATE_STATS_TABLE
DBMS_STATS.EXPORT_SCHEMA_STATS
DBMS_STATS.GATHER_TABLE_STATS
I have used the above to get a copy of schema stats and gather new stats for specific tables into a STATS TABLE in my personal schema. What I want to do now is use this stats table to generate plans for queries where I believe stats are off. Is it even possible? To be clear, I do not want to import stats because this replaces the stats currently there. I just want to point the CBO to my stats table for generating plans.
there was a session parameter I could set to tell oracle to use my stats table when generating plans, or an explain plan clause I could use or a DBMS_XPLAN paramter I could provide that would tell these tools to use my stats table when generating a plan, or even some way to tell autotrace. But I have found none of this.
View 2 Replies
View Related
Jun 9, 2011
I've got table at Oracle test server table1 with columns ID, BTYPE, MYDESCRIPTION. Rows of this table have been inserted manually. Now my need is to write script for creating table (structure + data) for working server. I think about writing something like
CREATE TABLE table 1
AS
SELECT 1 AS ID, 'TYPE1' AS BTYPE, 'SOME TEXT' AS MYDESCRIPTION
UNION ALL
SELECT 2 AS ID, 'TYPE2' AS BTYPE, 'SOME TEXT 2' AS MYDESCRIPTION
But rows are too many to type... Any way of generating script for creating table at working server using existing table at test server? The problem is I don't have an access to working server.
View 10 Replies
View Related
Sep 11, 2012
During STATS gather running for the table, unknowingly i deleted the old stats using EXEC DBMS_STATS.DELETE_TABLE_STATS. I would like to know will it affect the stats gather job currently running for the table and whether my stats will be gathered successfully.
View 5 Replies
View Related
Sep 10, 2012
RDBMS - 11.1.0.7, I it possible to convert indexes of a non-partitioned table to hash partitioned indexes by retaining table as non-partitioned?
If yes, is this what it is Creating a Hash-Partitioned Global Index - can be created for partitioned and non-partitioned tables?
View 7 Replies
View Related
Jun 4, 2013
I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);
2. List table content:
select * from T1;
account_no area_code bill_month consumption
Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......
3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12
Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182
With the conventional query I hope this is impossible,
View 2 Replies
View Related
May 26, 2011
I have doupts in gathering stats on table. I analyzed one table it took 2 hours first time.. the same table after one week later i analyzed, its got completed within 45 minutes.. I don't know exact reason why i got completed very soon. Is there any specific reason?
View 2 Replies
View Related
Apr 1, 2013
I have a table in my application that has the following structure
Create table EVENT (project_id number, DISC_CMPLNT_TX clob, MAINT_ACT_TX CLOB,LGBK_CMT_TX VARCHAR2(1000 CHAR))
PARTITION BY RANGE (project_id)
(PARTITION pmax VALUES LESS THAN (MAXVALUE));
I have created the below preference on the three columns of the table.
begin
ctx_ddl.create_preference ('skipJoinLexer', 'BASIC_LEXER');
ctx_ddl.set_attribute ('skipJoinLexer', 'skipjoins', ';:.,()<>*"''#-');
ctx_ddl.create_preference ('logbookTextDS', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute ('logbookTextDS', 'COLUMNS', 'DISC_CMPLNT_TX, MAINT_ACT_TX,LGBK_CMT_TX');
end;
I have created a local partitioned index with the parameter NOPOPULATE create index lb_text_idx on ac_maint (DISC_CMPLNT_TX) indextype is ctxsys.context local parameters ('lexer skipJoinLexer datastore logbookTextDS NOPOPULATE');
When an new value of project_id is inserted into table1 , I create a partition using the following command where prjId is the new value of the project_id ALTER TABLE EVENT split PARTITION pmax AT ('||prjId||') INTO ( PARTITION p_' || prjId || ', PARTITION pmax)
Now I run a huge data load into the EVENT table for a project Id say 1 and insert millions of records.I want to rebuild the lb_text_idx for this partition alone so that data is available for searching by the application. I did a rebuild using the command " alter index lb_text_idx rebuild partition p_1".
However when I run the below sql, I get a count of 0. I know that the text MAINT is definitely present in the table.
SELECT COUNT(1) FROM EVENT WHERE PROJECT_ID = 1 and contains (DISC_CMPLNT_TX, 'MAINT' ) > 0
When I run the below sql select parameters,status from user_ind_partitions where partition_name = 'P_1' , I get parameters as null and status as usable.Am I getting 0 records because the parameters are set to null? How do I rebuild with the same parameters for the specific partition alone.
View 3 Replies
View Related
Nov 14, 2012
I've read your article - you mentioned about MOSC recommendations. Actually they changed their mind and do not recommend rebuilding regularly - it is written on MOSC in 'Index Rebuild, the Need vs the Implications [ID 989093.1]'. What is your approach? Do you rebuild indexes regularly?
View 1 Replies
View Related
Jan 30, 2012
When we upgrade from 10g to 11g oracle upgrades the stats table by EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('OWNER','TABLE'); But When we import from 10g to 11g do we need to upgarde the stats table??
View 2 Replies
View Related
Sep 7, 2012
I am trying to generate some statistics on tables connected by a dblink. I know with oracle you have table_columns which you can reference and pull some stats from.
Trying to get the column count and record counts for each table connected by a dblink. I have tried these queries below to see if I could see any db properties: (some just to try something different)
select * from "table_owner".table_column@dblink ;
select * from "status"@dblink;
select /*DRIVING_SITE(a) */ count(*) from @dblink a;
What is the best method to finding this out without spending a lot of time? I have over 30 tables which are with large record sets and would love to learn a faster approach then pulling a sample table and doing a manual count and query for each table to count the rows.
View 3 Replies
View Related
Oct 30, 2010
we have 25 GB free space in Index tablespace, to avoid space issue i have added 10GB of space. Next shift DBA, rebuild INDEX of the from INDEX tablespace. Now i can check, only 27GB free space in INDEX tablespace...
Rebuilding of INDEX takes extra space.. ?
View 7 Replies
View Related
Oct 10, 2013
We are running 11g (11.2.0.3)We have a "working table" that is empty at the beginning of the day.Then we start adding rows (insert) with a key column called STATE with a value of 100.At the same time, there are other apps that pickup data in state 100 , process that data and change that state to 200 or 300.There is also another app that pickup data in state 200 , process that data and change that state to 300 or 400.
So in summary, the data on that table is at the beginning empty, then all the rows are in state 100, they slowly move to different states (200, 300, etc) and by the end of the day, they are all in 400.
My question is what would be the best way to collect stats on this table?
I was thinking to create an hourly job to collect stats on that table:
exec dbms_stats.gather_table_stats (
ownname => 'SCOTT',
tabname => 'WORK_T
View 6 Replies
View Related
Aug 8, 2013
Is it possible for the DBMS_STATS "LIST STALE" command to show a stale partition but NOT have its table show as stale?
I had a scenario where the table itself AND 1 partition showed as stale. I ran a fnd_stats gather table stats just on that 1 partition. Once it was completed it showed the partition to no longer be stale. it also showed that the table was no longer stale. so I guess I do not need to run stats on the whole table as well?
so if this is the case, when would I need to run stats on the full partitioned table if running it on the partitions themselves removes the staleness of the table?
View 3 Replies
View Related
Aug 29, 2013
Quote:Oracle collects new statistics when enough of the data (about 10%) has changed.I read the above statement in the oracle documentation that Oracle collects new statistics when enough of the data (about 10%) has changed every day during 10 pm to 6am .
But still i see some object statistics are stale and empty.Do i need to collect statistics for these objects manually ? and would like to know why these objects statistics were not collected during maintenance window ?
View 4 Replies
View Related
Aug 6, 2012
Our UNDO space remains at a high level 85 to 95 percent. We keep adding database files and it doesn't seem to go down significantly. When we do a backup of the system where we shut the database down, it does go down some but then within a week or so it is back up again.
View 11 Replies
View Related
Dec 15, 2011
I have a tablespace PLUS_IX with 5 datafiles PLUS_IX_01, etc.
PLUS_IX_04 is 85% full, up from 78% last month
PLUS_IX_05 is 22% full.
PLUS_IX_01 to _03 are 73% full.
Should I be concerned that PLUS_IX_04 is getting full? If so, what actions are recommended?
ALTER INDEX ..... REBUILD has been run on all the indices.
View 1 Replies
View Related
Sep 11, 2012
I load a table through sql loader which takes nearly 14 min for 8-9 millions records, once the records complete i run the analyze table compute statics to gather stats and it takes nearly 15 min. is there any ways so that i can reduce the stats timing. the stats collection command runs from other schema not from where the table is residing.
View 1 Replies
View Related
Jun 21, 2010
I`m new with Oracle Forms. I have the following trigger WHEN-BUTTON-PRESSED :
DECLARE
BEGIN
IF :SEARCH.SBOX is not null then -- (search text item)
IF :SEARCH.LIST45 = 'ERPLINK' THEN --(a drop down list)
[code]...
What do I want is the following thing :I have a text box :SEARCH.SBOX in which I write a number.When I press the button which will execute that trigger above, I want that the value from :SEARCH.SBOX, after the form will go to enter-query mode tofill up another text_item :CUSTOMERVIEW.ERPLINK with same value and execute_query(this text item is in master-detail form).
View 3 Replies
View Related
Oct 10, 2012
i have a large OLTP database and we are doing table stats copy amount subpartition to save the load on system. while doing an copy default subpartition stats: I see the following error:
SQL> exec DBMS_STATS.COPY_TABLE_STATS('cusms','STATUS_HIST','P_VDEF_10_2012_S100','P_VDEF13_10_2012_S100',force=>true);
BEGIN DBMS_STATS.COPY_TABLE_STATS('cusms','STATUS_HIST','P_VDEF_10_2012_S100','P_VDEF13_10_2012_S100',force=>true); END;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
View 2 Replies
View Related
Dec 3, 2012
below SQL I'm pulling data manually(by entering specialist name) instead of that I want this query to be modified so that the sys is pulled dynamically, I.e. like select specialist from t.
CODESELECT
type, Hari,Rakesh,Santhosh, Total
FROM (
select
(type), max( decode( SPECIALIST, 'Hari', cnt, null ) ) Hari,
max( decode( SPECIALIST, 'Rakesh', cnt, null ) ) Rakesh,
max( decode( SPECIALIST, 'Santhosh', cnt, null ) ) Santhosh,
Sum(cnt) total
from (
select
[Code]...
View 1 Replies
View Related
Jan 18, 2011
I want drop some old partitions from big table but this will not increase free space on disk. So I want to move table with indexes to anothers tablespaces. What is the fastest way to do that? ALTER TABLE ... MOVE TABLESPACE ...? CTAS ? Or something else?
View 4 Replies
View Related
Oct 4, 2012
I have a table like MyTab(a int, b int), and I am required to create a primary key index and a non-unique index on this table using columns (a,b) in a specific table space.
The back end database is Oracle 10g.
Here's what I have tried so far, needless to say, unsuccessfully.
Alter Table MyTab
Add Constraint c_1 primary key (a, b)
Using Index (Create index mytab_idx on MyTab(a, b))
Using index tablespace results_index
So my question are:
1. is this is possible? if so, what is the correct syntax.
2. assuming it is possible, using this sort of construct before? it appears to be conflicting and inconsistent to me.
View 4 Replies
View Related
Jul 22, 2012
I been doing a little testing with indexing the contents of URLs (database v11.2.x.x).
ctx_ddl.drop_preference('TEST_URL');
ctx_ddl.create_preference('TEST_URL','URL_DATASTORE');
ctx_ddl.set_attribute('TEST_URL','Timeout','3600');
create index datastores_text on test_url_search ( doc ) indextype is ctxsys.context parameters ( 'Datastore TEST_URL' );
SELECT SCORE(1),DOC from TEST_URL_SEARCH WHERE CONTAINS(DOC, 'London', 1) > 0
This works. i like to more
e.g. multi words search e.g. ' London Games' where if London or games are present it return a URL with a score,
or
Search in the Chinese.
The only way I can see to do this is create different indexes as the attributes for 'URL_DATASTORE' are limited. If I can what does the 'Contains' statement look like?
View 0 Replies
View Related
Sep 14, 2010
We have a master detail form block A (master) contains an item, this item is in the record group's where-clause used to fill a list item on block B (detail block) (database item) we fille the list item in a pre-record trigger on the master block this works fine but when we navigate through the records in block a we intermittently get the following error : FRM-41337 : cannot populate the list from record group;
how should we filll our list item, in which trigger (when-new-record-instance of master block doesn't work correctly, the list item only gets filled after clicking it twice)
View 5 Replies
View Related