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 Replieshere 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 Relatedhow to find out queries bases on elapsed time from the window of 3 Months
DBA_HIST_SQLSTAT and DBA_HIST_SQLTEXT
We have the following case: an application modifies a table in an Oracle db (10.2.0.3.0).
Unfortunately the update SQL statements from the application always use the condition "where Column1 = 'some given value'" which is wrong (never mind why).
It should be instead "where Column1 = 'some value' and Column2 = 'val for Column2'. The 'val for Column2 will be taken from the very SQL query being issued (we can make the application do an update for Column2 even if the value in it never changes).
So all the update queries from the application look at the moment like that:
"update my_table set Column2 = 'val for Column2', Column3 = 'some other values', Column4 = 'some other value' where Column1 = 'some given value'".
We would like to capture them and somehow on the fly modify them to look like that:
"update my_table set Column2 = 'val for Column2', Column3 = 'some other values', Column4 = 'some other value' where Column1 = 'some given value' and Column2 = 'val for Column2'".
Can a trigger "before update" do it? For some reason we cannot at the moment ask the vendor to change the hard code of the application so we are looking for a temporary workaround.
Question 1) I have read the following statement in a PL/SQL book.
Quote:To check whether an existing procedure is compiled for native execution or not, you can query the following data dictionary views:
[USER | ALL | DBA]_STORED_SETTINGS
[USER | ALL | DBA ]_PLSQL_OBJECTS
However, I when i query the view USER_PLSQL_OBJECTS I get the following error message:
Quote:ORA-00942: table or view does not exist
Question 2) I have read the PLSQL_WARNING can be set to DEFERRED at the system level. However, I am unable to defer it. tell me how to apply defer caluse to following statement:
Quote:ALTER SYSTEM SET PLSQL_WARNINGS ='DISABLE:ALL'
I am using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
I have 2 schemas in my application.
1. Application schema
2. EOD(End of day) schema.
End of day schema is populated from Application schema whenever user runs EOD process. The tables are pulled like this.
1. Master tables : Always deleted and reinserted at each EOD process
2. Log tables for each transaction table: Delta between the last EOD and current EOD data is pulled and are used for populating transaction tables
3. Transaction tables: These are populated from log tables pulled from previous step. The logic is like this
Now based on these tables about 30 reports are generated in EOD schema. Please note that each transaction table will have a EOD_ID and any report generated uses the where condition <transaction table>.EOD_ID = <current EOD_ID>
My log table contract_log and transaction table is contract in EOD schema.
contract_log table has data like this
contract_number contract_date customer_idqtyrateeffective_date
11/Jan/2010CUST-0110101/Jan/2010
1NULLNULLNULL112/Jan/2010
EOD on 1st Jan 2010 constructs contract table as
contract_numbercontract_datecustomer_idqtyrateeod_id
11/Jan/2010CUST-011010EOD-1
(Since the change of rate 11 is not visible on 1st Jan 2010 because it is effective on 2nd Jan 2010)
EOD on 2nd Jan 2010 constructs contract table as
contract_numbercontract_datecustomer_idqtyrateeod_id
11/Jan/2010CUST-011011EOD-2
(Since the change of rate 11 is visible on 2nd Jan 2010)
This logic is working fine. But we run more than 20-30 EODs the processing time increased to 10-15 hours.
It took some time to figure out the issue as a single query when run from toad or pl sql developer runs in few seconds but as a part of the whole package it takes 2-3 hours(each query).
The problem found was that oracle execution plan gets corrupted when the process starts. So what we did was to analyze the tables
after they are pulled. This perfectly solved our problem. Currently the whole process is taking only about 12-13 minutes where about 3 minutes is lost on analyze tables and indexes. I know this is a temporary solution as I need to get out of online analyze of tables and indexes.
My code for table and index regeneration is as below
PROCEDURE sp_gather_table_index_stats(pc_table_name VARCHAR2) IS
CURSOR cur_ind IS
SELECT index_name
FROM user_indexes
WHERE table_name = pc_table_name;
BEGIN
EXECUTE IMMEDIATE ' begin DBMS_STATS.gather_table_stats(user,' || '''' ||
pc_table_name || '''' || '); end;';
FOR cur_ind_rows IN cur_ind LOOP
EXECUTE IMMEDIATE ' begin DBMS_STATS.gather_index_stats(user,' || '''' ||
cur_ind_rows.index_name || '''' || '); end;';
END LOOP;
END;
I created a materilaized view with partitions and i need to add partitions dynamicaaly using stored procedure for that i need to check whther the partiions with the same name existing.where can i see the partition names for a materilaized view is there any table like "USER_TAB_PARTITIONS"?.if the same query exists in the forum
View 3 Replies View RelatedThe below query is taking high CPU almost 98% and longer time to execute.
SELECT ancestor,
Max(D.alarmstate) ALARMSTATE,
Max(D.sialarmstate) SIALARMSTATE,
Max(D.uncralarmstate) UNCRALARMSTATE,
Max(M.commstate) COMMSTATE,
Max(M.nncommstate) NNCOMMSTATE,
Max(M.servicestate) SERVICESTATE,
Max(M.abnormal) ABNORMAL,
CASE
[code]....
I wanna to DROP a table called EMPLOYEES, but when I execute the DROP TABLE EMPLOYEES, I get a ERROR saying that I cant do it because this table do reference to another table(s).
I tried to use the DBA_CONS_COLUMNS and DBA_CONSTRAINTS data tables, but its not enough to find it.
How can I find these references?
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
I am not able to find any information regarding inserting data with timestamp format without putting the date.
This is what i want to do:
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH24:MI TZR';
I create the column data type as timestamp with time zone. This is value i have inserted:
Insert Into TEST Values('05-OCT-2013 01:00 +08:00','05-OCT-2013 23:00 +08:00','SCIENCE);
And now i wish to display it in 'HH24:MI TZR' only after i deduct both of the time. This is the view i have created:
Create View TESTRECRDS As Select (STARTTIME- ENDTIME)*24 As DURATION, Science;
But when i select it display as weird timing because it would default in 'DD-MON-RR HH24:MI TZR' format.
BUT IF i create the view and display as 'HH24:MI TZR', it would gives me error:
Create View TESTRECRDS As Select To_Char(STARTTIME- ENDTIME, 'HH24:MI TZR')*24 As DURATION, Science;
Error would be invalid number. How can i display only 'HH24:MI TZR' after subtracting?
I have a table similar to the following,
USER, DETAILS, TYPE, UPDATED
1, user1home1, 1, 01/05/2009
1, user1home2, 1, 02/05/2009
1, user1work, 2, 03/05/2009
1, user1mobile1, 3, 04/05/2009
1, user1mobile2, 3, 05/05/2009
1, user1email, 4, 06/05/2009
1, user1other, 5 ,07/05/2009
2, user2home1, 1, 01/05/2009
2, user2home2, 1, 02/05/2009
[code]...
which contains multiple contact details for users of different types; type 1 is home, type 2 work etc. The following query returns the user's number and the latest home number for that user.
select user, details as latest_home_number from nc_test t
where type = 1
and updated = (select max(updated) from nc_test t2
where t2.user = t.user
and t2.type = t.type)
order by t.user
However I am not very experienced with sql and I am not sure how to create a view which would contain the fields:
user, latest_home_number, latest_work_number
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.
In our production environment some SP's are executing longer duration, but when same SP is executed from PLSQL Developer client it is executing vary quickly.
View 3 Replies View RelatedI want to know that is there any way to know the execution time of a sql query.
View 1 Replies View RelatedAn user is running some queries and that's making database hang. I want to view what queries this user is running. Can this be done by session auditing turning on?
View 13 Replies View Relatedhint 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?
I am executing below two SQL's at a time in a .sql file and I want the total execution time of these two queries:-
set timing on;
select 1 from dual;
select 2 from dual;
The output of the above sql file is returning the results like below which is expected:-
1
----------
1
Elapsed: 00:00:00.01
2
----------
2
Elapsed: 00:00:00.00
I want the total execution time of the two SQL queries at a time, I mean post execution of the two queries I want to know the total execution time, in this case is execution case will be 00:00:00.01.
Is there a way to know how much time will a query take to execute without running it, just like using the autotrace (traceonly) and explain plan utility.
View 16 Replies View RelatedI have upgraded oracle database from 9i to 11g using export and import utility. After migration we are facing performance issue in report generation, We have observed that First execution of report is taking very long time and when we generate the same report 2 -3 times there is considerable change in the execution time and it is more better than the first execution.
2 days back I have restarted the database and found the same issue. There are around 300 Reports and it is not possible to generate all the reports 2-3 times every time we restart the database.
I am facing a very strange issue with one of our Oracle query. The query is usually completes in a minute or two. Even the execution plan of the query is good and it works perfect most of the times, as expected. The query fetches about 1000-2000 records each day.
But on a given day, the query takes about 30-40 mins to execute completely. Upon checking the load on DB server, there are no other processes running which can impact the run time of this query. Moreover, the record counts fetched are almost same as compared to other days. There is no pattern observed as that this phenomenon occurs. it all happens once in a while.
Configuration is Oracle 10g with RAC environment on LINUX
How can i check the avg time taken by an execution plan. Actually i have a very big query and it changes its execution plan very often, we would like to lock the best execution plan and to find it , i would like to know the Average Execution Time the query takes when it runs using different different execution plans.
View 7 Replies View RelatedI have huge package having numerous procedures. Therein there is one wrapper procedure which is invoked from front end and which in turn calls other stored procedures within the package. The stored procedures called return the result to the wrapper procedure which in turn calls other stored procedures.
The ultimate result is returned to the calling environment by the wrapper procedure itself.I need to know the time taken in totality by the wrapper procedure along with the individual execution time of each procedure called by the wrapper procedure. I am not allowed to modify the code for adding timestamp capturing though.
In search queries generally we select 10-25 columns (more can't be displayed on the screen) from 5-10 tables
Say in case of insurance related application, the search might be on policy number, policy holder's first name, policy holder's last name, region, policy type etc.
And not to many columns we are displaying on the screen, say, 4 tables have collectively 4 * 20 = 80 columns, then we are displaying say 12-15 columns with 2-3 columns have aggregates on it.
since the search criteria (e.g. first name, last name, policy number etc.) is not known till last moment it will be a generic dynamic query
Is it possible that instead we create a Materialized view with query with only joining conditions but no filter conditions and selecting only columns to be displayed on the screen and then we will refresh the materialized view (to take care of recent business transactions) and fire refined query with filter criteria on this materialized view
Select col1,col2,col3,col4,col5
From tab1,tab2,tab3,tab4
Where tab1.col1=tab2.col1
And tab2.col2=tab3.col2
And tab2.col2=tab4.col2;
Will it improve performance of the search functionality
When working in MS Access, I can build parametric queries, where I can input the date field for example at run-time. How can I do something similar in SQL Oracle
Example
SELECT Sector.Date, Sector.RNC, Sector.Site, Sector.Cell,Sector.PSC
FROM Sector
WHERE Sector.Date=[Enter Date];
The problem was describe:
- First time to execute: Using all indexes on 2 tables
- Second time to execute: Using only indexes on first table, full table scan on the other
- Third time to execute: Do FTS on both of tables.
Now, I show the objects and relate information here:
The Tables:
system@dbwap> select count(*) from my_wap.news_relation;
COUNT(*)
----------
272708
system@dbwap> select count(*) from my_wap.news_content;
COUNT(*)
----------
95092
system@dbwap> desc my_wap.news_content;
Name Null? Type
----------------------------------------------------- -------- ----------------
ID NOT NULL NUMBER(11)
SUBJECT NOT NULL VARCHAR2(500)
TITLE VARCHAR2(4000)
STATE NUMBER(1)
IMGPATH VARCHAR2(500)
ALIGN VARCHAR2(10)
[Code]....
how to check the time taken for materialized view to execute at one time.
My materialized view is a complete refresh which automatically executes at 3:00.
a) I have queried dba_jobs and dba_mview_refresh_times, and matched the last_date and last_refresh to find the total time. How can I be know that JOB 80 corresponds to my view MVIEW_UNION in a much easier way?
SQL> select job,last_date,total_time from dba_jobs order by last_date;
JOB LAST_DATE TOTAL_TIME
---------- ----------------- ----------
80 05/05/10 03:00:02 5255
SQL>select name,last_refresh from dba_mview_refresh_times where owner
NAME LAST_REFRESH
------------------------------ -----------------
MVIEW_UNION 05/05/10 03:00:02
b) Also is total_time the time it took the query to execute today at 3:00, i.e it started at 3:00 and ended at around 4:45(3+5255 msec) or is it the overall time it has taken since the day it was scheduled in dba_jobs( i.e 1 week back)
c) Also my source table dosent contain any indexes and I have created indexes on materialized view, i want to know if the complete refresh will recreate the indxes.
How to find non-indexed queries and columns that require indexes?
View 3 Replies View RelatedI have a main procedure in oracle which invokes many procedures inside it. These internal procedures also calls functions and procedures inside it.This continues to many levels.
For ex:
Proc A
call c
call d
end............
[code]...
This loop goes on and on . I want to find the names of all procedures invoked at run time when main api is executed. Is it possible to find all of them using toad ? Is there any tool for doing this ?
We have a big hierarchical query which is now running for a long time (around 6 hours. earlier it was running for 3 hours). We have to tune this query so that we run the jobs within a stipulated time frame.
The query below inserts around 42 million records in to the table WK_ACCT_WSTORE. I have attached in the text file.