How To Lock Statistics In Column Level

Jul 4, 2012

i am using 10.2.0.1.0 version of oracle.

I have gathered frequency histogram manually on one of my column of a table to provide more information to optimizer for better calculation of cardinality.

Now i have my weekend job runs for gathering stats on schema level with method_opt as 'For all column size repeat'. But i don't want the stats of above column to be overridden by the stats job. I don't want to lock the statistics of whole table, but i just want to lock the column level stats for this table.

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: Row Level Lock

Mar 14, 2011

I have a transaction table with some custom properties and two status columns. There are 2 different applications(.Net and Pl/SQL Procedure) using the table. Both the process run parallel and fetch records one by one, perform some calculation and update the status column.

-.Net updates - Extraction_status
-Pl/SQL updates - Ingestion_status

There are likely more chances that both applications will fetch the sane record and try to update the same row. This will cause a lock. Can i use row level lock before update by each application? Or is there any other methods/process in which this can be handled. ?

View 11 Replies View Related

SQL & PL/SQL :: Row Level To Column Level Data Transposition?

May 14, 2010

I have a data like,

1) manual_temp_master

auto_idbatch_id sec_idsec_id_type crrncy_cdcreate_Dt price_dt
------------------------------------------------------------------
11234ABC1CUSIPUSD14/05/201014/05/2010
23456XYZ1SEDOLGBP13/05/201013/05/2010

2)manual_temp_detl

auto_idbatch_id Price_bkt_cdscreate_Dtprice_date
---------------------------------------------------------
11234PS114/05/201014/05/2010
11234PS214/05/201014/05/2010
11234PS314/05/201014/05/2010
11234PS414/05/201014/05/2010

[code]....

I want to write a sql query which will fetch the data from manual_temp_master and manual_temp_detl.But from manual_temp_detl table, Price_bkt_cds columns should be displayed as columns. Like the should look like as below:

sec_idsec_id_type crrncy_cd COL_PS1 COL_PS2 COL_PS3 COL_PS4 COL_PS5COL_PS6price_date
--------------------------------------------------------------------------------------
ABC1CUSIPUSDPS1PS2PS3PS4PS5PS614/05/2010
XYZ1SEDOLGBPPS1PS2PS3PS4PS5PS613/05/2010

View 8 Replies View Related

PL/SQL :: How To Get Max Level Column

Feb 9, 2013

The below query returns level and other selected columns. I need to get the max(level) 2nd column value in the below example.How to modify the query?

Ex
Level      max(level)     id
1 5 101
1 5 102
1 5 103
2 5 104
2 5 105
3 5 107
4 5 120
5 5 134
5 5 280

SELECT DISTINCT level lvl
,form_frms.emp_id
,form_frms.ing_emp_id
,form_frms.prve_id
,CASE
WHEN (select div_dn
[code].......

View 5 Replies View Related

Server Administration :: Use Of LM ( Lock Mode) In V$lock?

Oct 4, 2012

we know we can see lock mode held in session can be analysed using LM column in v$lock.But i confused in seeing LM column it all shows in numbers from 0 to 6.

eg

0,'None(0)',
1,'Null(1)',
2,'Row Share(2)',
3,'Row Exclu(3)',
4,'Share(4)',
5,'Share Row Ex(5)',
6,'Exclusive(6)')

View 1 Replies View Related

SQL & PL/SQL :: Difference Between Table / Column And Row Level Constraints?

Dec 24, 2010

through Difference between table Level, column Level ,row level constraints.

View 8 Replies View Related

SQL & PL/SQL :: Dependencies Of Tables On Package At Column Level

Mar 29, 2010

I need to identify the dependencies of all the Tables on Packages at column level.

E.g. : XYZ is a package that uses ABC Table having E,F,G has a column, PQR - Table and its columns - R,S,T

The resultant query / code should return like this

PackageName TableName ColumnName
XYZ ABC E
XYZ ABC F
XYZ ABC G
XYZ PQR R
XYZ PQR S
XYZ PQR T

Identify the dependencies at column level.

View 5 Replies View Related

Security :: How To Implement Row And Column Level Vpd Simultaneously

May 4, 2011

--here's my set up

CREATE USER schemaowner IDENTIFIED BY schemaowner
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO schemaowner;

CREATE USER user1 IDENTIFIED BY user1
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user1;

[Code] .....

my desired outcome for user 1

user1> select * from schemaowner.user_data;
user_id column1
1 USER1

my desired outcome for user2 are as follow:

user1> select * from schemaowner.user_data;
user_id column2
2 TESTER 2

the nearest solution is from with reference to [URL]

Quote:
Adding Policies for Column-Level VPD
....

SELECT ENAME, d.dname, JOB, SAL, COMM from emp e, dept d
WHERE d.deptno = e.deptno;

the database returns a subset of rows as follows:

ENAME DNAME JOB SAL COMM
-------------- -------------- ------------ ------------ -------------
ALLEN SALES SALESMAN 1600 300
WARD SALES SALESMAN 1250 500
MARTIN SALES SALESMAN 1250 1400
BLAKE SALES MANAGER 2850
TURNER SALES SALESMAN 1500 0
JAMES SALES CLERK 950

so how do I implement roll level and column level simultaneously?

View 3 Replies View Related

SQL & PL/SQL :: DBMS_STATS For Table Level Vs Partition Level

Nov 17, 2010

what is the difference on DBMS_STATS for table level and partition level , which will provide the best optimizer . If the table xxxx is partitioned from 1 to 10 ,then running gather stats on table xxxx as whole table level or partition level which will provide best result on the performance.

View 1 Replies View Related

Backup & Recovery :: Incremental Backup Level 1 Or Level 0 Without Archivelogs?

Oct 22, 2012

Can you take an incremental backup level 1 or level 0 without archivelogs?

syntax would bebackup as compressed backupset cummulative level 1 database.

The reason I ask is because when I run backup as compressed backupset cummulative level 1 database plus archivelogs # it runs fine, but when I run backup as compressed backupset cummulative level 1 database it just hangs.

View 20 Replies View Related

SQL & PL/SQL :: Row Level And Statement Level

Apr 11, 2013

between statement level or row level trigger, which trigger will execute first.We have BEFORE_UPDATE_ROWLEVEL_TRIGGER and BEFORE_ UPDATE_ STATEMENT LEVEL_TRIGGER triggers on table product.

which will execute first on update DML event ?

View 2 Replies View Related

How To Get I / O Statistics On ASM Diskgroup

Jan 21, 2011

How can we check the I/O statistics on a ASM diskgroup?

Do we have any command which can be run @ sqlplus prompt after logging into the asm instance?

What steps can we take to increase the Read speed and the write speed on the ASM diskgroup.

View 1 Replies View Related

How To Import Questionable Statistics

May 16, 2011

I want to take the statistics of my Production database and import to my local database, to calculate the Production statistics.I have used the statistics=compute, to export statistics. In the log file, for some tables, there was a waring like "exp-00091 exporting questionable statistics"

Is this dump will be useful for me to calculate production statistics?What option i have to use while importing statistics=recalculate or statistics=safe?

View 5 Replies View Related

SQL & PL/SQL :: How To Compute Statistics On Index

Aug 28, 2010

1) Why we need to do Compute Statistic on index.

2) Is it only for optimizer to make a better plan?

3) If yes, which means, optimizer will not able to collect statistic by itself?

4) if I'm not collect statistic, then optimizer will do it or skip.

View 5 Replies View Related

Incremental Statistics Gathering?

Sep 12, 2012

am having Oracle 9i RAC on IBM AIX .

I have large partitioned tables ( 4 partitions are added every month ). Is is possible to collect Incremental Statistics Gathering on these objects ( 9i ). If I collect stats with Ggranularity => ALL and ESTIMATE_PERCENT =100 the stats are accurate but it takes so much time .

One way may be to collect stats as Ggranularity => PARTITION for each new partition ( this quite fast ). but what about the Global Table Stats?

View 7 Replies View Related

Database Versus System Statistics

Aug 26, 2011

In the article regarding gathering CBO Statistics, it states: QUOTE When an Oracle database is created, a job will be scheduled that will generate the database statistics for you. You will still need to collect system statistics however, as these are not collected by the automatic statistics gathering mechanism.

what is the difference between "database statistics" and "system statistics"? In other words, do I need to run this script for each schema owner in my 10g/11g instance?

variable whoami varchar2(20);
begin
select user into :whoami from dual;
end;
exec dbms_stats.gather_schema_stats( -
ownname => :whoami, -
options => 'GATHER AUTO', -
estimate_percent => 15, -
cascade => true).

View 2 Replies View Related

Oracle 11g - Exception While Importing Statistics?

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

SQL & PL/SQL :: Updating Table Statistics - View Not Appropriate

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

Windows :: How To Create Cmd Script For Ora Statistics

Aug 9, 2011

I need to do a cmd script for launching oracle statistics every week and scheduling it from task scheduler of my server with win 2008 R2 O.S.

the script is:

"statistiche-oracle.bat"

set ORACLE_SID=GW1
sqlplus "/ as sysdba" @statistiche.sql

and "statistiche.sql" is:

exec dbms_stats.gather_schema_stats(ownname => 'PDMUSER', cascade=>true)
exit

When I launch it from command line it stops with error message: "insufficient privileges" and ask me the user, so I put 'PDMUSER' that is my user, it asks also pwd, I put it and the works.

View 3 Replies View Related

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 View Related

PL/SQL :: Compute Statistics After Creating Index

May 15, 2013

I have to create some indexes in a production database. Do I need to Compute Statistics after creating indexes? Or when I create they automatically are computed?

The version I'm using is:

Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
PL/SQL Release 10.2.0.5.0 - Production
CORE     10.2.0.5.0     Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

View 2 Replies View Related

SQL & PL/SQL :: Materialized Views - Take Statistics For Table Again?

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

Online Gathering Statistics For A Schema

Dec 24, 2012

Is it possible to gather stats for a schema which its in use. When i try to analyze the tables of a schema it shows that the statistics for that table is locked. So is it possible that instead of analyzing a table one by one , can i go for gathering the Schema stats while the objects of that Schema is still in use ( like DML or select statements being issued on those schema objects) .

DB version : 10.2.0.4
OS version : RHEL 5.8
DB type : RAC

View 12 Replies View Related

Execution Plan Randomly Changes After Statistics Collection

Oct 10, 2012

I don't know, if this is the intent behavior of oracle or not. But i noticed, my queries Execution plan randomly changes after statistics collection. Several tables are truncated after the daily run at 8AM and statistics gathered for all the tables in that schema.

However execution plans for 2-3 sql statements always changes after this and performance is brought back to normal by executing the procedure by explicitly calling it from the command line with arguments instead of bind variables.

View 3 Replies View Related

Replication :: Gather Statistics On Materialized Views

Jul 15, 2009

Any best way to gather statistics on Materialized Views.

View 1 Replies View Related

Server Administration :: Privileges To Gather Statistics

Nov 9, 2010

What privileges is required to gather statistics of oracle database using DBMS_STATS/ANALYZE command.

View 1 Replies View Related

SQL & PL/SQL :: Privilege Required To Gather Table Statistics?

May 14, 2011

What privilege is required to gather table statistics using dbms_stats ?

View 2 Replies View Related

Performance Tuning :: Gather Statistics On SYS Objects

Mar 7, 2012

Do we need to gather statistics on SYS objects and fixed objects in 11gR2 database regularly ?

I read in some article that in 10gR2, statistics for SYS objects are by default whereas in 10gR1, statistics for SYS objects are not by default using

dbms_stats.gather_database_stats(
...
gather_sys = False
...
)

View 4 Replies View Related

Server Administration :: Table Statistics For Oracle 11g

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

Performance Tuning :: Stale Statistics For Table?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved