Poor Performance With Free Tablespace Checking Query Generated By DB Artisan?
Jun 12, 2012
Our production DB version is 11.2.0.1 and we do use DBArtisian. Everyday morning we check table space usage using that tool and it generates the below listed query. It used to run in 15 secs. But since one week it is running for 5/6/8 mins. I have updated the statistics on sys objects and tuning advisor created the execution plan. So, now it is running in 2 mins. Nothing has been changed in the DB configuration. I see the same query running in secs in dev environment. understand what might be the issue and how could I improve the performance of this query.
SELECT SUB.TABLESPACE_NAME, SUB.STATUS, SUB.EXTENT_MANAGEMENT, SUB.SEGMENT_SPACE_MANAGEMENT, SUB.TOTAL_SPACE_MB, SUB.USED_SPACE_MB, SUB.FREE_SPACE_MB, SUB.PERCENT_FREE_SPACE, SUB.CONTENTS, SUB.TABLESPACE_GROUP, CASE WHEN SUB.TABLESPACE_NAME = P.VALUE AND SUB.CONTENTS = 'UNDO' THEN 'YES' ELSE 'NO' END
[code]...
View 5 Replies
ADVERTISEMENT
Jul 9, 2012
I've installed GI 11.2.0.3 on AIX 7.1.And today I noticed strange ASM's behavior:
-bash-3.2$ getconf DISK_SIZE /dev/rhdisk2
*2285768*
-bash-3.2$ asmcmd
ASMCMD> lsdsk -k
Total_MB Free_MB OS_MB Name Failgroup Failgroup_Type Library Label UDID Product Redund Path
*188616* 68204 *188616* DATA_0000 DATA_0000 REGULAR System UNKNOWN /dev/rhdisk2
I guessed that was because of LUN size (it was exceed 2 TB)After that I dinamically shrinked LUN size on our external storage, rebooted and perfomed cfgmgr command on both nodes. But I still have no enough free space.
-bash-3.2$ getconf DISK_SIZE /dev/rhdisk2
*1992294*
-bash-3.2$ asmcmd
ASMCMD> lsdsk -k
Total_MB Free_MB OS_MB Name Failgroup Failgroup_Type Library Label UDID Product Redund Path
*188616* 68204 *1992294* DATA_0000 DATA_0000 REGULAR System UNKNOWN /dev/rhdisk2
View 2 Replies
View Related
Apr 7, 2011
Following query is hanging either with 'Sequential access read' or 'Latch Free' wait event Important thing is the table which is self joined in subquery here does not have any index at all While it was hanged I tried to get trace of it and terminated twice. As such haven't got 'row source generataion' The table has only 120000 records and it shall update 34000 records
UPDATE invoice_header inv
SET inv.modified_due_date =
(SELECT inv1.btn_due_date
FROM invoice_header inv1
WHERE inv.dct_code = inv1.dct_code AND inv1.release = 'A5')
[code]...
During 'sequential read' using p1,p2 values tried to get what the session is reading and found that it is using the table itself.
During lath free I found following
SELECT name, 'Child '||child#, gets, misses, sleeps
FROM v$latch_children
WHERE addr= (select p1raw from v$session_wait where sid=18)
UNION
[code]...
However instead of self join when I creaed global temporary table as
create global temporary table t as select * from invoice_header where release='A5'
And used it in the update as
UPDATE invoice_header inv
SET inv.modified_due_date =
(SELECT t.btn_due_date
FROM t
WHERE inv.dct_code = t.dct_code AND t.release = 'A5')
WHERE inv.release = 'A5' AND inv.btn_due_date >= TRUNC (SYSDATE)
It updated the records in a second!!
Questions are
1) why it is producing 'sequential read' wait event when there is no index access or else why it is doing single block access when FTS is required?
2) Why is the 'latch free' wait event here and what it indicates here with 'cache buffer handles'?
Is it because we are reading and updating the same segment?
know in case DDL of table is required. It has all nullable columns and no index at all. Since it is 9i I am unable to use MERGE effectively in this case
View 14 Replies
View Related
May 13, 2010
I have installed Oracle 10g on one system and Oracle developer on another machine, means i have different machines for DB Server and Application server. It all working excellent inside the company premises, but if i want to access my Oracle DB and application server outside the company then it gives me problem..how to access application (forms and reports ) remotely outside the company...having same db.
View 2 Replies
View Related
Apr 12, 2013
I need to check the package performance and need to improve the package performance.
1. how to check the package performance(each and every statement in the package)?
2. In the package using the delete statement to delete all records and observed that delete is taking long time to delete all the records in the table(Table records 7000000). This table is like staging table.Daily need to clean the data before inserting the data into it. what can I use instead of Delete.
View 13 Replies
View Related
Mar 11, 2013
One tablespace used in two different schemas ,in this case how much space used,free space and persentage of used in each schema level.
Example: USERS tablespace used in two different schemas HR,SCOTT. in this suche case to find the total size of tablespace, used and free space of each schema level.I used the below query not giving appropriate result.
SELECT TABLESPACE_NAME,
OWNER, round(SUM(BYTES/1024/1024),2) "Used Space in MB"
FROM DBA_SEGMENTS
where tablespace_name not in ('SYSTEM','TEMP','SYSAUX','EXAMPLE','UNDOTBS1')
group by tablespace_name,owner
order by tablespace_name
View 4 Replies
View Related
Apr 29, 2012
which data dictionary view would you query to see the free extents in a tablespace.
i use dba_free_space;
SQL> select tablespace_name,sum(bytes),sum(blocks) from dba_free_space where
tablespace_name = 'SYSTEM' GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME SUM(BYTES) SUM(BLOCKS)
------------------------------ ---------- -----------
SYSTEM 9830400 1200
View 2 Replies
View Related
Aug 9, 2013
I need to find the tablespace size (used and free) for a partitioned table
View 3 Replies
View Related
Feb 15, 2011
How to free up the tablespace size when it reaches its threshold (Max limit)?
Say for example USERS & SYSAUX tablespace reaches above 90%.
View 14 Replies
View Related
Jan 9, 2013
how can i check using query the free percentages of my tablespaces
View 10 Replies
View Related
Jun 11, 2012
How can check that which query is taking how much load or time in execution on server in oracle.I want the soluation as like that, run the query and get the detail in desc order and identify that this query is required to tune or kill the session.
View 1 Replies
View Related
Jan 26, 2012
I wanted to know how to find the links between cursors that have been created in v$sql after compiling a PLSQL procedure.
For example, if i compile the following procedure :
create or replace
PROCEDURE PROCEDURE1
AS
BEGIN
insert into t values(1,1);
END PROCEDURE1;
, the 2 following cursors are created in v$sql :
select SQL_ID, SQL_TEXT from v$sql
where sql_id in ('71pj8t5nz1d80','2s567zb6684sh');
"SQL_ID""SQL_TEXT"
"2s567zb6684sh""BEGIN PROCEDURE1; END;"
"71pj8t5nz1d80""INSERT INTO T VALUES(1,1)"
Thus, i would like to know how to find that the cursor 71pj8t5nz1d80 is called/linked by the cursor 2s567zb6684sh.
This would be useful for interpreting some sql statistics in v$sqlstats in where such cursors appear and related sql statistics are accounted twice.
View 1 Replies
View Related
Sep 22, 2009
In my table ,data type of one among 10 columns is defined as varchar2(10).I need to check that column should accept only numeric value(0 to 99) or alphabetic value(a to z or A to Z) .It should not accept Alpha-numeric values.I tried like this
select c3 from demotab where to_number(c3) not between ascii('a') and ascii('z') ;
but I got error like 'Invalid Number'.how to implement this thro sql query.
View 3 Replies
View Related
Aug 30, 2013
1) I got column date of joining which accepts date in below format DD-MON-YYYYDD-MON-YYMON-DD-YYYYMON-DD-YYMonth DD,YYYY Question:-
how do i check whether all dates in Date of joining column are in above format or not using sql query? 2) I got one more date column which accepts date in below formatMMDDYYYYYYYYMMDDMM/DD/YYYYMM/DD/YYYYYY/DD/MM Question:-
how do i check whether all dates in date column are in above format or not using sql query?
View 26 Replies
View Related
Dec 6, 2012
I have a table and data as below.I need to select all the records if value have both 'M' and 'D'.If there is only 'M' or 'D' then select should not pull any records.
WITH data as (
Select '1' id, 'M' value from dual union all
Select '1' id, 'M' value from dual union all
Select '1' id, 'D' value from dual union all
Select '1' id, 'D' value from dual )
select value from data group by value
I tried below query but it is not working.
WITH data as (
Select '1' id, 'M' value from dual union all
Select '1' id, 'M' value from dual union all
Select '1' id, 'D' value from dual union all
Select '1' id, 'D' value from dual )
select * from data group by value having sum(count(distinct(value))) > 1
View 3 Replies
View Related
Feb 8, 2013
I have written an SQL which will dynamically generate the Select statement with from and where clause in it. But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.
For that i have used a ref cursor to open and insert the table.
In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn
find below the sample
TYPE ref_csr IS REF CURSOR;
insert_csr ref_csr;
v_select VARCHAR2 (4000) := NULL;
[Code].....
View 5 Replies
View Related
Jun 16, 2010
I 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?
View 2 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
Dec 9, 2008
Environment Setup
Oracle Server 11g on HP-UX
Oracle Client on Windows
I am using swingbench tool to generate load on DB and using OLTP like benchmark i am comparing the performance of plain data and encrypted data.
I have created two different database. one for tde and other for plain. I have populated same number of rows in both databases. Then i start running the benchmark and i use SAR to collect disk I/O's, VSAR to CPU usage.
From the sar report it seems that,
Oracle plain has faster transactions, it uses minimum CPU. But when look in tot the Reads/Writes TDE has lower than the plain.
If TDE needs to encrypt the data to store in the disks it should occupy more space than the plain data. Then the I/O should be more in TDE..
Note: Bcz the DB parameters are same, number of rows in the tables are same. File system and its block size are same. I will run the swingbench seperately for both the databases.
I am attaching the excel sheet for sar results. Let me know if you need more information
View 7 Replies
View Related
Oct 2, 2013
what is the difference between BIGFILE and SMALLFILE tablespace in DML operations performance? which is faster in bulk read and write data?
View 1 Replies
View Related
Dec 22, 2010
Allwasy temp tablespace shows 100% full, even though database bounce temp is not cleared again it shows 100% full.Is their to Tune this issue.
View 1 Replies
View Related
Apr 26, 2012
If my tablespace goes beyond 80% I should get a email from Unix crontab.
1)Warning for tablespace 80% full.
2)critical for tablespace 90%full..
I need the script for Oracle tablespace with 87% and shell script.
Do I need 2 scripts? Is there anything ready available (Oracle 10g).
View 4 Replies
View Related
Aug 25, 2013
i am using 11.2.0.3.0 version of oracle. We are planning to move some ~40 tables/indexes to new encrypted tablespace as a part of TDE(transparent data encryption). Currently three tables are having size ~30GB and one having ~800GB other have <2GB in size. And tables/indexes are altogether placed in different tablespaces.
whether i should create as many no of encrypted table spaces as it was before as unencrypted tablespace? or I should create one encrypted tablespace and move all the tables/indexes into that?
View 2 Replies
View Related
Nov 17, 2011
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.
View 5 Replies
View Related
Nov 7, 2011
I am receiving this error in production databases...There are 2 probable extent failures for tablespace
View 14 Replies
View Related
Mar 7, 2012
we have a situation where both undo tablespaces were almost filled i.e UNDOTBS1 99% and UNDOTBS2 100% filled so i add data files to it and then i found a lot of blocking session and was just killing them through EM then i stop my front end listener and also down the service, now i don't have any blocking session but on EM a big WAIT is coming. alert log shows nothing serious, it was showing deadlock but now it is over as well.
View 8 Replies
View Related
Nov 25, 2011
All the analysis till now on our system proves that our system is clearly I/O bound and db sequential read is the biggest culprit.
We have even identified the index which is being affected by sequential read. I am thinking of creating a new tablespace with 32K blocksize (currently all table spaces are 8k) and migrate this index to the new space. That way, Oracle will have to do less number of reads to get the required data.
But is there anything wrong in having just one tablespace with a differnt block size? Or is there anything that I have to be watchful about while doing it?
View 14 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 16, 2011
Since tablespaces can be enabled/disbaled for flashback is there a query that can tell me the flashback status of each tablespace in my DB.
View 2 Replies
View Related
Jul 6, 2013
My Oracle db have this tablespace named INDX which is 80% full. I tried to query on the table, user using it but there is no one using this tablespace
May I know if there is a query that i can used to find out which dba_object is consuming the 80% of this tablespace.
View 5 Replies
View Related
