Performance Tuning :: How To Find Local And Global Indexes On Particular Table

Mar 19, 2012

getting how many local and global indexes on particular oracle table

View 2 Replies


ADVERTISEMENT

Performance Tuning :: Local Index Versus Global Index On Partitioned Table

Jun 28, 2011

I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.

View 6 Replies View Related

Performance Tuning :: Only LOCAL Bitmap Indexes Are Permitted On Partitioned Tables

Feb 4, 2005

16:28:32 SQL> create bitmap index bp_idx_ag_id on transactions(type);

create bitmap index bp_idx_ag_id on transactions(type)
*
ERROR at line 1:ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables

how to create bitmap index on partitiioned tables

View 3 Replies View Related

When Do Global And Local Indexes Becoming UNUSABLE?

Nov 29, 2012

RDMS Version : 11.2.0.2
Platfomrm : AIX 6.1

For partitioned tables (RANGE, LIST types ) what are circumstances when a Global or a Local Index become UNUSABLE ? I was told that in some circumstances where the Indexes become UNUSABLE , not even a SELECT query against the table will work. Is this true ? For partitioned tables with Global Index, the global index will be listed in DBA_INDEXES . Right ?

View 5 Replies View Related

Performance Tuning :: How To Find All Indexes Scripts In Particular Schema

Aug 16, 2012

getting all indexes script in particular schema.

View 4 Replies View Related

Performance Tuning :: What Does Analyzing A Table Do For Indexes

Sep 26, 2012

what analyzing a table does to existing indexes? Do I need to rebuild the indexes after dbms_stats.gather_table_stats command ?

View 4 Replies View Related

Performance Tuning :: How To Find Top Query Running On A Table

Feb 19, 2012

How do i find a particular SQL or a set of SQL's which are excuted against a table (user identified table) that is either a very frequently executed query against that table or high impact SQL against that table? I am currently looking through the AWR reports to go through all the queries but i was wondering if there are any dictionary views where we can find this info from?

View 2 Replies View Related

Performance Tuning :: Find The Last User Analyzed Particular Table?

Aug 9, 2012

How can i find the last user analyzed particular table?

I can able to find last analyzed date, but can't able find the which user has done it.

View 2 Replies View Related

Performance Tuning :: Practices With Indexes?

Dec 10, 2010

So I was reading about indexes here:[URL]...1Is there any reason to NOT use an index? If there aren't, then should you use an index on every column on every table?

What is the general best practice with indexes? After reading the section, it seems that there are only positive impacts of using an index, so why are they not automatically created?

View 3 Replies View Related

Performance Tuning :: Unable To Avoid Indexes

May 20, 2011

Below query is getting delayed becasue of BitMap Indexes on the table. I am trying to avoid indexes by using Hints in the query but unable to do so, Details are as follows.

explain plan for
SELECT cbu_cid, cbu_cid_customer_en_nm,
COUNT (billg_acct_no) AS billg_acct_no,
SUM (subscriber_cnt) AS subscriber_cnt
FROM daily_view
WHERE (billg_system_id = 'TM' AND mktg_sub_segment_a_nm = 'TM')
AND (cbu_cid NOT IN ('0001988048', '0001379962', '0001350469'))
GROUP BY cbu_cid, cbu_cid_customer_en_nm
HAVING SUM (subscriber_cnt) > 10
ORDER BY subscriber_cnt DESC;
[code]....

I have tried with ALL_ROWS & PARALLEL.how to avoid above two indexes in a query.

View 28 Replies View Related

Performance Tuning :: How To Partition Tables And Indexes

Jan 12, 2011

So our situation is pretty simple. We have 3 tables.

A, B and C

the model is A->>B->>C

Currently A, B and C are range partitioned on a key created_date however it's typical that only C is every qualfied with created date. There is a foreign key from B -> A and C -> Bhave many queries where the data is identified by state that is indexed currently non partitioned on columns in A ... there are also indexes on the foreign keys that get from C -> B -> A. Again these are non partitioned indexes at this time.

It is typical that we qualifier A on either account or user or both. There are indexes (non partitioned on these) We have a problem with now because many of the queries use leading wildcards ie. account like '%ACCOUNT' etc. This often results in large full table scans. Our solution has been to remove the leading wildcard.

We are wondering how we can benefit from partitioning and or sub partitioning table A. since it's partitioned on created_date but rarely qualified by that. We are also wondering where and how we can benefit from either global partitioned index or local partitioned indexes on tables A. We suspect that the index on the foreign key from C to B could be a local partitioned index.

View 3 Replies View Related

Performance Tuning :: Couldn't Have Indexes On Tables In Production

Sep 27, 2013

I have tried a lot by alternate solutions like rearranging the order of tables in join and moving where conditions before but no success...Its a bottleneck and I could not have indexes on these tables in production...I want to change the approach in subquery

SELECT
g.COLUMN1,
g.COLUMN2,
e.COLUMN3,
g.COLUMN4,
MIN(e.dat1) KEEP ( DENSE_RANK FIRST ORDER BY date2 Desc) * -1,
min(to_char(date3,'dd-mm-yyyy'))
[code]....

View 5 Replies View Related

Performance Tuning :: How Many Block Of Query Is Fetching With Or Without Indexes

Jun 1, 2012

I don't have any dba privileges, can you share a scripts which can tell how many block my query is fetching with or without indexes. How do i also get buffer hit, how can i get i/o without sql trace as i don't have access to dump_dest

I have a below query

SELECT DISTINCT ser_id AS STA_ser_id, rct_name AS STA_name
FROM sd_servicecalls, rep_codes, rep_codes_text
WHERE ser_sta_oid = rcd_oid
AND rcd_oid = rct_rcd_oid
AND rct_name IN ('New', 'Awaiting Approval', 'Approved', 'In Progress', 'Awaiting Supplier', 'Awaiting RFC', 'Awaiting Release', 'Pending Release', 'On Hold', 'Resolved', 'Implemented', 'Closed');

Does large hash value in explain plan mean more resource needed and more time to execute the query, How can i use ADDM for the above sql.

View 7 Replies View Related

Performance Tuning :: ORA-00439 / Feature Not Enabled / Bit-mapped Indexes

Mar 29, 2004

I am having Oracle 9i relaese 2 on my db server. I am getting the following error every time I try to create a bitmap index:-

ORA-00439: feature not enabled: Bit-mapped indexes

I have queried the v$option table .Here the value of parameter Bit-mapped indexes is FALSE.

The result of v$version is :-

Oracle9i Release 9.2.0.1.0 - 64bit Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for Solaris: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

Actually when we created the database our installation was halted . so we manually created the database using Create database command.

How can we enable the BITMAP Index Feature now.

View 10 Replies View Related

Performance Tuning :: Creating Normal / Composite Indexes On Fields

Feb 14, 2013

I Want to tune the attached query. I have tried by creating the normal indexes and composite indexes on the fields . I feel that , Only normal index is required for this instead of composite index?

11:15:19 SQL> @slot.sql
11:16:03 SQL>
11:16:03 SQL> drop table slot purge;

Table dropped.

Elapsed: 00:00:00.05
11:16:03 SQL>
11:16:03 SQL> create table slot
11:16:03 2 (
11:16:03 3 id varchar2 (40) not null,
[code]....

- dynamic sampling used for this statement
22 rows selected.

View 8 Replies View Related

Performance Tuning :: Command To Transfer File From Local To Remote Server

Apr 21, 2011

I ran a scp command to transfer a file from local server to a remote server. when I am trying to kill the process, its giving an error.

ksh: kill: 19258: No such process

If I do a ps -ef | grep scp its showing the process running, but if i try to kill it, its showing me error.

View 7 Replies View Related

Performance Tuning :: Create Small Functional Indexes For Special Cases In Very Large Tables

Apr 5, 2012

Create small functional indexes for special cases in very large tables.

When there is a column having one values in 99% records and another values that have to be search for, it is possible to create an index using null value. Index will be small and the rebuild fast.

Example

create index vh_tst_decode_ind_if1 on vh_tst_decode_ind
(decode(S,'I','I',null),style)

It is possible to do index more selective when the key is updated and there are many records to create more levels in b-tree.

create index vh_tst_decode_ind_if3 on vh_tst_decode_ind
(decode(S,'I','I',null),
decode(S,'I',style,null)
)

To access the record can by like:

SQL> select --+ index(vh_tst_decode_ind_if3)
2 style ,count(*)
3 from vh_tst_decode_ind
4 where
5 decode(S,'I','I',null)='I'
6 group by style
7 ;

[code]....

View 2 Replies View Related

Update Global Indexes?

Aug 15, 2011

If I have owner, table_name is there a query I can issue that will tell me if I have to add the "update global indexes" clause when dropping a partition from a table?

View 1 Replies View Related

SQL & PL/SQL :: Performance With Union And Global Temp Table

May 24, 2010

I have a view, which has a union. (Union is required because of the nature of the data fetched). THis view is later joined with a global temp table which holds the -say employee Id the user selects.

So at runtime there is a join with the global temp table and the view. But the performance is really bad. I have tried using various hints, like materialize, /*+ CARDINALITY(gtmp 1) */ etc.

When i query the view alone,. the performance is good. When I remove the union, the performance is good. Some how with the union- there is a full table scan on one of the joining tables.

View 7 Replies View Related

SQL & PL/SQL :: Use Of Local And Global Partition Index

Aug 17, 2010

What is the use of Local and Global Partition Index?

View 1 Replies View Related

Global And Local Inventory - Patchset?

Apr 21, 2013

1)What is the use of inventory?

2) Global inventory is found by checking the oraInst.loc file. How to find the location of Local inventory?

3)Why is every patchset(11.2.0.2) now a full release?

View 4 Replies View Related

Split Partition - Global / Local Index

Aug 23, 2012

I'm trying to split a table partition.

whether the below syntax are correct.

If local index used

ALTER TABLE SNYT.PART_ESTD
SPLIT PARTITION ESTD_M13_S22 AT ('ESTD', 13, '22')
INTO (PARTITION ESTD_M13_S21, PARTITION ESTD_M13_S22)
update indexes;

(per http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1401247200346349807)
=================================================================
If Global index used

ALTER TABLE SNYT.PART_ESTD
SPLIT PARTITION ESTD_M13_S22 AT ('ESTD', 13, '22')
INTO (PARTITION ESTD_M13_S21, PARTITION ESTD_M13_S22)
UPDATE GLOBAL INDEXES;

View 1 Replies View Related

B-Tree Index On Column - Local Or Global

Feb 20, 2013

Version Info: 11.2 on Solaris 10

I have a partitioned table like below. I want to create a B-Tree index on SALES_RGN column which is neither the part of Primary key or the Partitioned key. Should I create this index as local or Global ?

CREATE TABLE sales_dtl
(
txn_id          number (9),
salesman_id     number(5),
salesman_name     varchar2(30),
sales_rgn     varchar2(10), -----------------------------> This column needs to be indexed
sales_amount     number(10),
sales_date     date,
constraint pk_sales_dtl primary key (txn_id)
[code]....

View 9 Replies View Related

SQL & PL/SQL :: Trigger To Drop Partitions And Local Indexes

Sep 29, 2011

writing a trigger to drop partitions with zero rows which are older than 6months and drop the local indexes and rebuild the global indexes for any schema in a databaase ?

I have tried the below code :

declare
v_statement varchar2(600);
v_rows number;
begin
for x in (select *
from dba_tab_partitions
[code]........

I want to avoid using row number and also want to dynamically select a schema when executing the script.

View 39 Replies View Related

Local Partitioned Indexes (adding Additional)?

Jan 11, 2013

I'm trying to find a way to ADD new partitions to local indexes and at the same time specify their tablespaces without having to DROP and RECREATE.

Here´s an example table based on yearly partitioning:

CREATE TABLE "TABL_ANOM"
(
ANOM_TS TIMESTAMP(6) NOT NULL
, ANOM_TIPO NUMBER(2, 0) NOT NULL
, ANOM_NIVEL NUMBER(2, 0) NOT NULL
, ANOM_ID NUMBER(10, 0) NOT NULL

[code]...

Here´s an index def for the table:

CREATE INDEX "TABL_ANOM_INDEX1" ON "TABL_ANOM" ("ANOM_NIVEL") LOCAL
(PARTITION DGSCOPSX_2011 TABLESPACE DGSCOPSX_2011
,PARTITION DGSCOPSX_2012 TABLESPACE DGSCOPSX_2012
)

OK. Now I want to add partitions for 2013 so for the table I use:

ALTER TABLE TABL_ANOM ADD
PARTITION DGSCOPS_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE DGSCOPS_2013;

and this works fine for the table but I can't find a similar command to simply add additional partitions to the indexes. I know that I can drop and recreate the indexes with the additional partition defs but on some of my tables, I'm dealing with hundreds of millions of rows and I think it would take way too long to drop and recreate all indexes on all partitions.

Also related is the PRIMARY KEY index partitions. Is there a way to add partitions (specifying the tablespaces) without having to DROP and re-ADD the CONSTRAINT with the additional partition for 2013?

View 3 Replies View Related

Performance Tuning :: How To Find Index To Rebuilt

Oct 3, 2011

i am trying to find the index want to rebuild or not for that i have analyzed that index after that i don't know how to calculate the ration could any one steps to do calculate the following ratio

Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.

View 2 Replies View Related

Performance Tuning :: How To Find Literal SQL Statement

Jan 30, 2012

Is there is any view/query from where I can find how many sql using literals.

View 4 Replies View Related

Performance Tuning :: Find SQL Using Hash Value - ORA-00060

Nov 18, 2010

Getting an ORA-00060 in our database. This is the alert log:

Wed Nov 10 08:01:54 2010
Global Enqueue Services Deadlock detected. More info in file
/opt/oracle/admin/ngboot/bdump/ngboot1_lmd0_13119.trc.

This is part of the lmd file:

oprocp : (nil)
opid : 0
group lock owner : (nil)
xid : 0000-0000-00000000
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs

[Code] ......

How do I find the SQL causing this? I have the hash value of 3180952482. should I open up en SR as this i logged in an lmd trace?

View 2 Replies View Related

Performance Tuning :: AWR Report - Find Disk I/O

Mar 11, 2012

In AWR report, in order to find the disk i/o, Should I see the avg read(ms) under Tablespace I/O and Filesystem I/O columns?

View 1 Replies View Related

Performance Tuning :: How To Find Out Queries That Are Executed In Particular Range Of Time

Sep 9, 2011

here we have an scenario where we want to find out all the sql statements that are executed in a particular time. The sql statements are executed via our application. I tried in awr report but it shows only the sql query which has taken long time to execute. and i even tried in V$session and V$sqlarea. how to view the executed sql statements in a particular session/current session

View 3 Replies View Related







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