Query To Find Current Queries Not Using Indexes?

Mar 29, 2012

hint of which DBA views we need to query to find current running queries which are not using indexex?

I suppose v$session is one , what else we need to look to?

View 1 Replies


ADVERTISEMENT

Server Administration :: How To Find Non-indexed Queries And Columns That Require Indexes

Sep 1, 2011

How to find non-indexed queries and columns that require indexes?

View 3 Replies View Related

Find Ddl For Any Indexes?

Oct 9, 2012

on 11g R2 on Win 2008

How to find ddl for any indexes built on MY_VW ?

View 6 Replies View Related

SQL & PL/SQL :: Find Out Database Name For Indexes

Jan 15, 2013

I am trying to find out database name for the unusable indexes thru the query; I am using dba_indexes for the index name but not sure which view I need to join with to find the db name.

View 11 Replies View Related

How To Find Over Fragmented Tables / Indexes

Jun 19, 2012

i am using oracle 10g. is there a way to find out the over fragmented tables/indexes ,so that reorganizing or rebuilding them will improve sql performajnce?

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

Find Sql Queries Run By Users Through Application?

Jul 10, 2012

We have a database version 10.2.0.3 and it is connecting through an application with a schema called HR_OR.

I am using the below query to get sql statements.

SQL> SELECT
2 SST.VALUE CPU, S.USERNAME, S.SID, S.SERIAL#, SQL_TEXT
3 FROM
4 V$SESSION S,
5 V$SQLTEXT_WITH_NEWLINES T,
6 V$SESSTAT SST
7 WHERE S.SQL_ID IS NOT NULL
8 AND S.SQL_ID = T.SQL_ID
9 AND SST.SID = S.SID
10 AND SST.STATISTIC# = (SELECT STATISTIC# FROM V$STATNAME SN WHERE SN.NAME = 'CPU used by this session')
11 ORDER BY 1 DESC, S.SID,T.PIECE;

It is showing same query for all users and I tried to run the quey from sqlpus,getting error [SP2-0552: Bind variable "xxxx" not declared]. So I hv confusion here.

1. How I can see the queries running by users though application.

2. get the exact query run by users and the same query I want to run from sqlplus.

View 10 Replies View Related

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

Find Records That Have Most Current Date?

Apr 13, 2004

I have two tables

table_1
--Emp_id--|--Sup_id--|Sup_name|--Date--|
-------------------------------------------------
--00001--|--00005 --|---ABCD--|01-MARCH-2004
--00002--|--00006 --|---BCDE--|02-MARCH-2004
--00003--|--00007 --|---CDEF--|03-MARCH-2004
--00001--|--00008 --|---DEFG--|04-APRIL-2004
--00003--|--00009 --|---EFGH--|05-APRIL-2004

table_2
--Emp_id--|Emp_name|
--------------------------------
--00001--|--QWER--|
--00002--|--ASDF--|
--00003--|--ZXCV--|
--00004--|--POIU--|

table_1 contain records on employee and the supervisor they are under at a certain date.

As some employee(00001 & 00003) have a different supervisor from different date, I'll like to extract from table_1 the record of each employee in the table that only contain the supervisor info on the most recent date.And from table_2, i'll like to extract the employee's name. These records extracted from both the tables would the be put into a new table,table_3

Example: For employee 00001, only extract record that have the most recent date which is 04-APRIL-2004 and not on 01-MARCH-2004

table_3
Emp_id|Emp_name|Sup_id|Sup_name|Date|
------------------------------------------------
00001 |--QWER--|00008 |--DEFG---|04-APRIL-2004
00002 |--ASDF-- |00006 |--BCDE---|02-MARCH-2004
00003 |--ZXCV-- |00009 |--EFGH---|05-APRIL-2004

How to write an SQL statement to perform this?

View 6 Replies View Related

Security :: How To Find Current User In Database

Sep 22, 2011

query to find the current user accessing the database

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

Find Queries Bases On Elapsed Time From Window Of 3 Months

Nov 19, 2012

how to find out queries bases on elapsed time from the window of 3 Months

DBA_HIST_SQLSTAT and DBA_HIST_SQLTEXT

View 6 Replies View Related

PL/SQL :: Oracle Dictionary View 2 / Find Queries Run And Its Execution Time

Feb 6, 2013

Is there any oracle dictionary view which captures the queries being run by users on the database and time taken to execute those queries?We need to find out the OS user not the database user since we have to identify the users who are executing long running queries.We require this basically to monitor the long running queries on the database.

View 2 Replies View Related

How To Check / Find Size Of Current ROLLBACK Segment

Apr 8, 2013

How to check/find the size of current ROLLBACK segment in oracle 10g ?

View 20 Replies View Related

Server Administration :: Find SID From Current Session As It Make Connection With DB

Dec 27, 2011

How to find the sid from v$session of my current session.

I want to ask:

Let say I have open 10 TOAD session and only in one toad session i have fired a sql query ( the rest 9 session are just blank), then the rest 9 status are in active status. Now, how i can figure out which sid ( from v$session ) belongs to which session as all the 9 session having nothing to run.

Quote: My main intention is to find out the current session sid as soon as it make connection with DB.

View 7 Replies View Related

Server Administration :: Find Out Difference Of Data For Some Tables Between Current And Previous Day

May 26, 2011

We want to find out difference of data for some tables between current day & previous day. We can use query with minus operation but it will take lot of time since table size is in range from 200 to 500 GB. We have to do this exercise every day.

View 5 Replies View Related

SQL & PL/SQL :: Indexes For Query Performance?

Mar 16, 2010

Lot of my tables are just for join purpose , a table with just with two columns both FK , I did not add any composite PK to this table, lot my queries uses this table , because it does not have composite PK will the query be slow ? what I have to do to increase performance querying with such tables ?

View 5 Replies View Related

SQL & PL/SQL :: Tune Query Without Creating Any Objects Like Indexes?

Jan 14, 2013

Can we tune below mentioned query without creating any indexes :

SELECT /*+ PARALLEL(a,64) */x_abc_41, x_abc_44, CALLED_FROM_NUM, X_abc_25, created, evt_stat_cd, last_date, x_abc_number_from,
x_abc_complete_date
FROM table a
WHERE a.todo_cd in('MNPIN','MNPOUT')
AND a.x_abc_25 = 'NP RFS'
AND a.created BETWEEN sysdate-180 AND sysdate

Explain Plan :

PLAN_TABLE_OUTPUT
Plan hash value: 3718629559
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2376 | 9462 (4)| 00:02:51 | | | |

[code]...

Note : Currently this query is taking 3 Minutes for retrieving 8000 records,But should be executed in less than 1 min.

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

PL/SQL :: Query By Current Month

Jul 12, 2013

I have an excel spreadsheet that queries an oracle data for some information.  I am trying to get the information shown to only be from the current month.  I have tried my google-fu, but all of the formulas I have found will not work and return a various error of some sort. The rest of the query works great, but I cant figure this one out.   Select*From&& and I guess i need a where statement, but nothing seems to work.Where"My_Table_Name","OrderDate".

View 6 Replies View Related

SQL & PL/SQL :: Query To Update Current Value In DBA View To New One

Jul 18, 2012

I am looking to build a query to update a current value in a DBA view to a new one.i.e. updating directories based on the current value:

CREATE OR REPLACE DIRECTORY 'DIRECTORY_NAME' AS 'DIRECTORY_PATH'(substr(directory_path, 1,5) + '/&dbname' {i.e. this is different for every database name }+ 'DIRECTORY_PATH'(string after /xyz/)
WHERE DIRECTORY_NAME in
( select DIRECTORY_NAME
from DBA_DIRECTORIES
WHERE DIRECTORY_PATH
like '/xyz/%'
)

i.e. resulting output should be:

CREATE OR REPLACE DIRECTORY 'ABC' AS '/xyz/DBNAME/abc/def/';

(when the directory previously was 'xyz/abc/def/') i.e. basically inserting the db name into the directory.where DBNAME is a variable more directories are added frequently so therefore this needs to be a dynamic procedure to change the directories in the db.

View 26 Replies View Related

SQL & PL/SQL :: Query To Get Default Tablespace Of Current User?

Oct 27, 2010

I need query to retrive the default tablespace of the current user, when the user is not a dba, i.e. user doesn't have access to dba_users table.

View 2 Replies View Related

SQL & PL/SQL :: Getting Query For Current Year Data Up To Sysdate?

Jun 19, 2012

I have a table for exampl,

emp sal date
111 200 03-mar-2011
100 200 03-mar-2012
15 200 06-mar-2012
17 200 03-mar-2003
178 200 03-mar-2004
11 200 11-jun-2012
101 200 19-jun-2012

i need sql querry to get current year records upto sysdate.i.e for example this year 2012 so i need all the 2012 year records upto sysdate.

my output like this.....

emp sal date
100 200 03-mar-2012
15 200 06-mar-2012
11 200 11-jun-2012
101 200 19-jun-2012(upto sysdate)

View 2 Replies View Related

SQL & PL/SQL :: Taking Latest Current Balance Using Analytical Query

Jan 22, 2013

I am having a table with 5 lakhs transactions. I want to fetch the last balance for a particular date. So i have have returned a query like below.

SELECT curr_balance
FROM transaction_details
WHERE acct_num = '10'

[Code]...

This has to be executed for incrementing of 12 months to find the last balance for each particular month. But this query is having more cpu cost, 12 times it is taking huge time. how to remodify athe above query to get the results in faster way using analytical query. Whether this can be broken into two part in PL/SQL to achive the performance. ?

View 9 Replies View Related

Forms :: Can't Navigate Out Of Current Block In Enter Query Mode

May 11, 2010

I have a form with multiple tabs, each tab showing one data block. Many of the blocks are standard in enter-query mode. I want to allow the users to navigate through the tabs while the blocks are in enter-query mode.how can i do this?

View 3 Replies View Related

Converting MySQL Queries To Oracle Compatible Queries

Jan 23, 2007

our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.

this is the error message:

The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.

SELECT productID,productName FROM products order by productName;

select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3

SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'

SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Sysdate / Current Date / Current And Local Timestamp

May 30, 2012

What is the difference between the following . In my schema all are giving the same results with some different format

SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;

SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM

View 1 Replies View Related

SQL & PL/SQL :: Verify Current Date Is Greater Than 15th Of Current Month

Sep 22, 2010

I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.

View 5 Replies View Related

Convert Indexes Of Non-partitioned Table To Hash Partitioned Indexes?

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

SQL & PL/SQL :: How To Find The Count In The Query

Apr 16, 2010

from the below string i want to find the count of the "<-" using a single query.

string is "aaaa<-bbbb<-ccccc<-ddddd<-eeeeee<-ffffff<-"

If not the query, pointer on using SQL functions.

View 3 Replies View Related







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