How can we check completion status for running sql query. i.e. how much % completed
SQL> begin
2 delete from gsmcrmdw.wc_loy_txn_f_aa
3 where integration_id in
4 ( select integration_id
5 from support_olap.recover_wc_loy_txn_f_953to955
6 );
7 commit;
8 end;
9 /
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 RelatedI have a Query(report) which is running in <5 mins in one Scheme, where as the same is running for a long time in second schema. I have identified that an Index is scanning for more than 2000 Millions of records in second Schema, but this is scanning only 440 Millions in First Schema and hence it is fast. I am expecting the same to be done in Second schema.
I have verified the following
All records in tables in 2 schemas are same.
All indexes are same
Analyzed the tables
Gathered Histogram on all the columns as per the first schema.
But now i still have the same problem, don't know what could be the problem.
Table_nameNum_RowsBlocks
PRPSL_LST_T5866107159
PRPSL_WKFLW_ACTVTY_T5829904030
ITEM_CHR_VAL_T5134340104049020
ITEM_RGN_ASSN_T8571220137215
Also attached 2 screen shots of OEM Plans..
How can i check if paging happening while running the query. As i have 4gb of PGA target but the query is taking long time in parallel and has hash join.
how to check paging in 11gr2
I have a form which will be processing various steps each might take some time. To facilitate the user, I have included some status displaying after every process completed. To refresh the form I have user SYNCHRONIZE to display the status of ever process after completion of every process.
It is working perfectly if am not minimizing the forms screen or opeing another window. After doing anyone of the above and when I reopen the form again, the SYNCHRONIZE is not working and am not able to see the form refreshing after every process as I have planned.
when I check one database, I noticed that the primary key constraint is in UNUSABLE status. But when I check the table, the table DDL itself,the below query mentioned.
ALTER INDEX "COMMON_DATA"."PK_DISE_MOBILE" UNUSABLE ENABLE
suggest whether I need rebuild this index? if I rebuild this index, will it useful for me for the performance improvement? But the table has only 578234 records.
Table DDL
CREATE TABLE "COMMON_DATA"."DISE_MOBILE_INSTALLATION"
( "M_DBASE_ID" VARCHAR2(2),
"COMPANY_NUMBER" NUMBER(3,0),
"M_ACCT_ID" NUMBER(8,0),
"ACCOUNT_CREATION_DATE" DATE,
"ORDER_NUMBER" VARCHAR2(15),
"ORDER_CREATION_DATE" DATE,
"ORDER_CREATION_METHOD" VARCHAR2(1),
"CONTRACT_TERM" VARCHAR2(3),
"GROUP_ID" VARCHAR2(6),
[code]...
I want to calculate the running percentage. sample data is as below.
CREATE TABLE prod
(
code VARCHAR2(8),
name VARCHAR2(30),
stdate DATE,
itons NUMBER(7,3),
rtons NUMBER(7,3)
[code]....
Required Output is
CODE NAME STDATE ITONS RTONS %age
-------- ------------------------------ --------- ---------- ---------- -------
0-01-001 SEEDS 17-JUL-12 193.155 0
1-01-001 OIL 17-JUL-12 0 81.906 42.404
1-02-001 MEAL 17-JUL-12 0 104.304 54.000
1-03-001 DURT OIL 17-JUL-12 0 1.242 0.643
2-01-001 WASTE 17-JUL-12 0 5.703 2.953
[code]....
Percentage will be calculated as rtons of code not having first digit 0 devided by itons having having code 0 result multiply 100 for the same date code wise e.g. For dated 17-jul-13 meal percentage will be calculated as round((104.304/193.155)*100,3)=54.000
I am getting temp tablespace error "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP" for the following code.
SELECT /*+ USE_NL ( vd1 ,vd2 ,vd3 ) leading ( vd1 ,vd2 ,vd3 , tvd) */ vd1.vendor_record_seq_no,
tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U'
FROM vendor_data vd1, vendor_data vd2, vendor_data vd3,
(SELECT rownumber, MAX (DECODE (control_column_seq_no, 91150, original_value, NULL)) AS value1,
[code]...
Right now used tables has the following number of records-
SELECT COUNT(*) FROM vendor_data --292890442
SELECT COUNT(*) FROM temp_vendor_data --0
SELECT COUNT(*) FROM temp_vendor_record --0
This query is part of an application, but consuming too much of temporary tablespace (68 GB allocated). I found it out by using query below:
select * from v$session a, v$sql b
where a.sql_id=b.sql_id
and status = 'ACTIVE'
I am not sure, why this problem is occuring.
I have a Datamart DB which has a continuously increasing volume. I run a daily optimize job, to have the data analysed using the below:-
EXECUTE dbms_stats.gather_schema_stats(ownname=> 'xxx' ,estimate_percent=> 25 , DEGREE => 4, cascade=> TRUE );
analyze table xxx.abc compute statistics; But this optimization itself is taking nearly 4 hrs to complete and I can't afford to have the delay.
Is there a better way of running this optimization?
We have a program that is taking about 13-14 hours to run and we need to generate traces to see where it is taking so long. I usually use 10046 for the tracing, I'm wondering if the traces can be built incremently so that it doesn't become one huge trace file.
View 14 Replies View RelatedThe scale of the tests that generate the following scenario is not huge right now, only 50 users simulated (or you can think of them as independently running threads if you like). But here is the crunch, the queries generated (from generic transaction layer) are all running against a table that has 600 columns! We can't really control this right now, but this is causing masses amounts of IO (5GB per request) making requests queue for disk availability (which are setup RAID 0/1); its even noticable for as few as 3 threads.
I have rendered the SQL on one occasion to execute in 13 seconds for a single user but this appears short lived as when stats were freshly gathered it went up to the normal 90-120 seconds. I've added the original query to the file, however the findings here along with our DBA (who I trust implicitly) suggest that no amount of editing the query will improve the response times, increasing the PGA/SGA (currently 4/6GB respectively) will only delay the queuing for a bit and compression can work either. In short it looks as though we've hit hardware restrictions already for this particular scenario.
As I can't really explain how my rendered query no longer takes 13 seconds, it's niggling me that we might be missing a trick.So I was hoping for some guidance on possible ways of optimising these type of queries against such wide tables, in other words possibilities that we haven't considered...
Attached is the query and plan.
Our application servers will be running a SELECT which returns zero rows all the time.This SELECT is put into a package and this package will be called by application servers very frequently which is causing unnecessary CPU.
Original query and plan
SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION
FROM AIMUSER.SEGMENT_JOBS
WHERE SEGMENT_JOB_ID NOT IN
(SELECT SEGMENT_JOB_ID
FROM AIMUSER.SEGMENT_JOBS) 2 3 4 5 ;
[code]....
Which option will be better or do we have other options?They need to pass the column's with zero rows to a ref cursor.
Is there any way i can Get how many rows are processing with UPDATE statement while the Update statement is still running.
View 2 Replies View RelatedHow to find currently running scheduled jobs using query !?
View 3 Replies View RelatedI have a question about database fragmentation.I know that fragmentation can reduce performance in query times. The blocks are distributed in many extents and scans process takes a long time. Oracle engine have to locate the address of the next extent..
I want to know if there is any system view in which you can check if your table or index has high fragmentation. If it's needed I will have to re-create, move or rebulid the table or index, but before I want to know if the degree of fragmentation is high.
Any useful script or query to do this, any interesting oracle system view?
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.
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.
Is there is any view/query from where I can find how many sql using literals.
View 4 Replies View RelatedGetting 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?
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 Relatedgetting all indexes script in particular schema.
View 4 Replies View RelatedHow 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.
I want to find the Pass rate for each term by adding up the grades.
PASS = HD/D/PASS FAIL = FAIL and Z means no mark has been recieved yet so i do not want to include it in the calculation.
CREATE TABLE DAN_GR
(ID VARCHAR2(8),
TERM VARCHAR2(8),
GRADE VARCHAR2(8),
SUBJECT VARCHAR2 (8),
STAT VARCHAR2 (8))
[Code]....
So term 1201 has 1 Pass 1 Fail and a Z (which is not counted) so pass rate is 50%
term 1202 has has 1 Pass only so the pass rate is 100%
term 1203 has 1 Fail so pass rate is 0%
term 1204 has 1 Pass and 2 Fails and i dont count the 3rd fail because the stat is 'APSENT' i only want to count the once where stat is 'PRESENT'
WANT:
COUNT TERM PASS RATE
3 1201 50%
1 1202 100%
1 1203 0%
3 1204 33%
I am using:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
getting how many local and global indexes on particular oracle table
View 2 Replies View Relatedhere 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 the tables in the database on which high DMLs are firing.
View 5 Replies View RelatedHow can I find out the particular oracle session which was consuming high memory in the past?
I can't get the data in v$sessstat
Unable to get the information in AWR
dba_hist_active_session_history do not have field which indicate memory related information
Shall I concetrate on EVENT in dba_hist_active_session_history which continuosly had sort, direct path read
Or
Locate sql_id from dba_hist_sqlstat with high SORTS_DELTA for snapshots belonging to problematic time period and then using the sql_id query dba_hist_active_session_history
which approach I shall take to find out the session which consumed most memory in the past?
I use oracle 11g r2
i have created a table for objects
create table table_t
(Record_no number,
t_no number,
position SDO_GEOMETRY
, occupation_time number);
and inserted many values (examples below )
INSERT INTO tra VALUES
(2,
20503,
SDO_GEOMETRY
(2001,
NULL,
SDO_POINT_TYPE (1387, 0, NULL),
NULL,
NULL),
23037
)
/
and( position) indexed as Rtree spatial index
now when i run spatial query such as
SELECT * FROM tra t WHERE
SDO_FILTER(t.position, MDSYS.SDO_GEOMETRY(2001,NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(0,0,9000,0)), 'querytype=WINDOW') = 'TRUE' and t.position.sdo_point.X=1;
i do not know how many IO accrued ?
i tried set autotrace on
but the physical read is 0 , this is not possible because i have more than 100000 objects there and all indexed as R-tree
Below query is taking a long time...
select gam.SOL_ID,COUNT(gam.FORACID) from gam,smt where
gam.ACID=smt.ACID and gam.ACID NOT IN(select ACID from imt) and
gam.SCHM_TYPE in('SBA','CCA','CAA','ODA') and GAM.ACCT_CLS_FLG='N' and
gam.SOL_ID IN(select SOL_ID from IMT) group by gam.SOL_ID
/
attached is the explain plan.
in which index on IMT table is not used. And the query is doing a FTS on IMT table. What needs to be done to avoid FTS on IMT table.
In SQL, almost all the thing which are possible with join is possible with sub-query also and vice-a-versa.
So when should I use sub-query and when should I go for join?