SQL & PL/SQL :: Script To Query Index Statistics Data And Update Tracking Entries?
Oct 19, 2010
I have created a table: INDEX_SIZE_TRACKING with the following attributes
Index Name: name of the index. Type: VARCHAR(255). This is the primary key of the table.
Allocated Space: the memory space (in bytes) allocated to the index. Type: NUMBER
Used Space: the memory space used by the index. Type: NUMBER
Last Update: the time when index details are updated to this table. Type: VARCHAR(255)
I want to write a PL/SQL script to query index statistics data and update tracking entries in the INDEX_SIZE_TRACKING table. If there is no existing entry for the index, create a new one; otherwise, update the existing one. have a PL/SQL statement that can do this in oracle XE?
View 1 Replies
ADVERTISEMENT
May 26, 2010
I'm having problem with my database, which contains more than 1 rows with a same value on a field that has uniqueness contraint.
Here is the log from sqlplus. When I select on RI field, it shows 2 rows. But when I select on SCNUM field, it shows only 1 row. This SCNUM has an unique index on it.
And it is still in VALID state
SQL> set autotrace on
SQL> select ri, scnum from scratch1_p where ri in (536964983, 536955574);
select ri from scratch1_p where scnum='444393975';
RI SCNUM
---------- ----------
536955574 444393975
[code].....
View 14 Replies
View Related
Oct 5, 2011
I need to Update Oracle tnsnames.ora file with the some new entries ....i was looking for ora 92, but could not find a proper link to download.now I have oracle 11 g, but i cant find tnsnames.ora file in that...is it because my installation was not proper...
View 1 Replies
View Related
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
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
Jan 11, 2007
I'm trying to generate count of the number of entries in a table for each day.The problem is the date column is of datatype timestamp and looks like this "2006-12-30 18:42:03.0"
How would I generate a report of number of entries in the table for each date (I'm not intrested in the "time" only the "date" i.e YYYY-MM-DD)?
SELECT COUNT(*) FROM my_table_name
WHERE my_date_column LIKE '2006-12-30%'
GO
It returns zero rows ( and I kno there are rows in the table) I'm using Oracle 10g.
View 2 Replies
View Related
Nov 2, 2012
I have the following DDL:
drop table tmp_guid;
CREATE TABLE tmp_guid (
c1 raw(16) not null
,c2 raw(16) not null
);
begin
[code]...
It seems that a combination of a unique index and extended stats are to blame. Removing any one of them causes the query to also produce correct results.Extended stats basically captures the fact that despite being unique, c1 depends on c2.
View 0 Replies
View Related
Nov 19, 2010
I have a query which had a join:
a.c1=b.c1 and a.c2=@var
where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like
a.c1(+)=b.c1 and nvl(a.c2,@var)=@var
This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')
But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?
View 2 Replies
View Related
Jun 23, 2011
I've got a table which has no unique identifier for a row. I have a field for an ID value but somhow there are duplicate values in this field.
Now I want to create an unique index over this field but therefore I have to remove the duplicate values.
So what I need is an update statement, maybe in a for each loop, which updates the ID field in each row starting with 1 and increasing the value.
In Informix there is a rowid value which is automatically set by Informix. But I think there´s no correspondent function/value in Oracle....
View 17 Replies
View Related
Nov 26, 2012
I am having trouble figuring out why an update statment is ignoring the primary kiy index when performing an update through the application. The index IS used when the update is run from sql*plus or other sql tools.
The statement is very simple:
update ITEM_MASTER set COST = :1 where SMARTPART_NUM = :2;
ITEM_MASTER has unique, primary key index on SMARTPART_NUM
When I use OEM and other tools, I can see the index is not used in the exlain plan, and the query has a high CPU cost due to the full table scan. The table is analyzed, lately using 100%. Table rowcount is 229768
SELECT column_name, num_distinct, num_buckets, histogram, trunc(last_analyzed) ANALYZED FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'ITEM_MASTER' AND column_name = 'SMARTPART_NUM';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ANALYZED
------------------------------ ------------ ----------- --------------- ---------
SMARTPART_NUM 198417 254 HEIGHT BALANCED 25-NOV-12
The database is Oracle Database 10g Release 10.2.0.4.0 - 64bit Production (on Windows, standard edition)
Here are parameters related to optimizer:
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
We've tried rebuilding the indexes then re-analyzing, dropping stats and locking at zero, using various sample sizes; none have any impact.
We wondered if the bind variables are causing this, though in testing outside the application, we tried pl/sql and sql batches to mimic the passing of values into the binds, and all still used the index.
Other than putting a hint into SQL in the application (we'd have to ask for a customization), how can I "fix" the database to use the index? In other words, how can I make the database see that the cost of a FTS is much higher than using the index, whenever it sees these updates (either from sql or the application)? This is the simplest example, but we have 4-5 SQL updates on this and other tables that are ignoring the indexes and using full table scans, so we'd like to fix it for all.
View 10 Replies
View Related
Sep 3, 2012
I have a UPDATE statement which sets values on non-indexes columns and do not use indexed-column in where clause (so every column used in statement are not indexes). Lastly, I had some problem with database and saw in ASH report in TOP SQLs that update with events enq: TX - index contention and buffer busy waits. What does it mean? I read a lot of information about index contention, but always related to INSERT and DELETE. What has UPDATE to do with this contention?
View 3 Replies
View Related
Aug 9, 2010
Somewhere I read that we should not use hints in Oracle production environments, but we can use hints in the development environment and on achieving the desired execution plan we can adjust the 'statistics' to follow that plan without hints.
Q1. If it is true what statistics do we adjust for influencing the execution plan and how?
For example, I have the following simple query:
select e.empid, e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;
emp.empid, emp.deptno and dep.deptno columns have indexes and the tables have the standard structure as found in the basic oracle examples.
If I look at the execution plan of the above query then I see that the driving table is empand the driven table is dept.Also the type of join that is taking place is 'Nested Loop'.
Questions: With respect to the above query,
Q 2. If I want to make dept the driving table and emp the driven table then how can I adjust the statistics to achieve that?
Q 3. If I want to use hash join instead of a nested loop join then then how can I adjust the statistics to achieve that?
I can put the ordered and the use_hash hint to effect this but again I have heard that altering statistics is a more robust way to control an execution plan as compared to hints.
View 6 Replies
View Related
Jul 18, 2012
Query
SELECT case.case_objid FROM clrods.case@clrods.equant.com case, table_x_cwp_tickect_details_vw t WHERE CASE.case_condition_cd IN ('OPEN', 'OPEN-DISPATCH', 'OPEN-REJECT', 'OPEN-RETURNED') AND case.case_type_cd in ('CUSTOMER FAULT', 'CHRONIC','SCHEDULED ACTIVITY','PROBLEM') AND ROWNUM <= 500 AND case.case_objid = t.ticket_objid AND ( ( case.account_id = '672286' ) ) ORDER BY case.case_id DESC
From PROD
Plan
SELECT STATEMENT HINT: FIRST_ROWS Cost: 2,629 Bytes: 221,500 Cardinality: 500
37 SORT ORDER BY Cost: 2,629 Bytes: 221,500 Cardinality: 500
36 COUNT STOPKEY
[code]...
From DEV platform
Plan
SELECT STATEMENT HINT: FIRST_ROWS Cost: 591 Bytes: 61,134 Cardinality: 138
37 SORT ORDER BY Cost: 591 Bytes: 61,134 Cardinality: 138
36 COUNT STOPKEY
[code]...
1. Index is rebuild
2. stats are up to date
3. Redef on table is done.
View 7 Replies
View Related
Jul 20, 2010
I have a database job to ANALYZE TABLES,, it takes more than 40 hours. I want to know exactly how much time does it take..
My job has started on 19th Jul at 02:32 hrs.. What is the way to know the exact time when the job ends.
View 4 Replies
View Related
Mar 14, 2011
I have a question about system logon / logoff triggers that can provide a tracking mechanism for Oracle user activity you've described:
[URL].......
If this approach is good for high-concurrency OLTP databases?
View 1 Replies
View Related
Jun 21, 2012
oracle: 10.2.0.5.7...I can get this to work, but not the way the docs seem to say. I am wondering if I am reading the docs wrong or missing something.
The docs seem to say to get a query to run in parallel using an index you use the PARALLEL_INDEX hint. This doesn't seem to work for me. I have to do one of the following
1. change the parallel degree with an alter index, then use the PARALLEL hint (parallel index hint does nothing in this case)
2. use both the parallel_index and parallel hint
View 4 Replies
View Related
May 20, 2013
We have been recommended to store data in CLOD data type.
Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.
We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?
View 2 Replies
View Related
Jun 15, 2010
currently developing a app for MSM in tromsø, that are going to delete the oldest entrys in the database table and then update all the relative Run history entrys in another table.
And i can't get the coding right to truncate the old entrys.Here are the
DECLARE V_SLETT varchar2(125);
V_NYE varchar2(50);
V_SLETT := :SLETT;
V_NYE := :NYESTE;
truncate DELTAKER ( IDNR in (V_SLETT) );
begin
request := UPDATE_RUN;
end;
View 15 Replies
View Related
Sep 5, 2012
I am studying about these two technologies and the only difference that I found was that optimization doesn't backup the duplicated archivedlogs.
If both ignore not changed blocks, what is more effective?
The optimization backup also has some tracking file?
View 1 Replies
View Related
Sep 27, 2013
I have a form developed using Oracle 10g Forms Builder. It is deployed on server. I need to track who is remotely accessing the desktop and running this form. How can we track this. Are there any webutil commands to trace the application used to access the desktop? What is the work around for this?
View 2 Replies
View Related
Sep 10, 2011
does, block change tracking file is read during incremental level 0 backup?
View 4 Replies
View Related
Mar 8, 2010
how can i track the exception for three select statement in one pl-sql block. here is synario.......
begin
select * from emp where empno=1234; --statement 1
select * from cusotmers where cust_id=125; --statement 2
select * from products where product_id='a-3'; --statement 3
end;
i want to track exception any one for ex no_data_found for all these three different statement.
I know if i put this three statement in three different pl-sql sublock then i can trap it....
how can i trap it in one single block?
View 4 Replies
View Related
Jun 20, 2012
i want to enable block change tracking as our incremental level1 backup take a lot of time but i have a worry which i found it on oracle support and tha is as follows Database Instance Hang at Database Checkpoint With Block Change Tracking Enabled. [ID 1326886.1]
View 2 Replies
View Related
Oct 11, 2011
i did such commands
1. RMAN -> backup incremental level 0 database;
2. SQLPLUS -> alter database enable block change tracking using file '/home/oracle/block_change_tracking.tr';
3. RMAN -> backup incremental level 1 cumulative database;
4. RMAN -> backup incremental level 1 cumulative database;
5. RMAN -> backup incremental level 1 cumulative database;
and for such order i noticed that block change tracking file wasn't used for any cumulative backup. for each cumulative backup was done full scan over database.
it was used after i did incremental level 0 backup, and again incremental 1 cumulative.
if i want this feature to be usefull i should enable it before my incremental level 0, or i should do incremental level 0 immediately after i enable it?
View 6 Replies
View Related
Sep 3, 2010
I have already created large no of indexes on my Database without specifying specific tablespace, now I would like to move all the created indexes into particular tablespace.
View 3 Replies
View Related
Jun 6, 2013
I have the below query for which ename column has an index. As of my knowledge below queries 1st and 2st will not use index. Hence i used the 3rd statement and that too its not using the index. Finally i used the 4th query, but even the 4th query is not using the index. Then how do i make this query to use my index??? Do i need to create a function based index for this?
1. select * from emp where ename !='BH' ;
2. select * from emp where ename <> 'BH';
3. select * from emp where ename not in ('BH');
4. select * from emp where ename < 'BH' or ename > 'BH';
View 4 Replies
View Related
Dec 17, 2012
Suppose i have two columns ID and Status (or any number of columns ) belongs to table customer. Now I want to create below index
Ex. create index test1 on customer (ID , Status )
But before creating this index i want to check that whether there is already index on these 2 columns ? May be by other name ?
View 23 Replies
View Related
Sep 8, 2008
In oracle 9i ........How to delete duplicate entries in a table ?
if suppose in a table named office, one or more entry(row) is repeated more then twice or minimum twice.
I need a query to delete the multiple entries ....
Note:
--->No constraints applied on the table.
--->No Primary Key
--->You cannot create any object....that is no view or a duplicate table can be created
View 3 Replies
View Related
Apr 3, 2012
I am abolute new in PL SQL and I am working on following topic:
I have to check if an entry exists in a table.
- if it exists ...... do something
- if not ...... simply go on
I tried the following:
select Count(*)from products p
where p.productNbr = temp_Nbr
Group By t.produkt;
I fetched the result into a variable entries
if entries > 0 .....
MY problem is the following:
If there are entries (entries>0) --> it works
If there are no entries, entries is not 0 but 'no data found' --> crash
I cannot work with Exceptions because this all happens in a Loop which must go on in both cases!
View 14 Replies
View Related
Mar 31, 2011
I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.
Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.
The select statements inside the inline view returns huge number of row (like 50 million rows).
The whole query fails with time out.
How can I optimize this query further?
Is there a way to pass Oracle Hints so that Oracle uses indexes?
View 4 Replies
View Related
