Performance Tuning :: 2 Probable Extent Failures For Tablespace
Nov 7, 2011I am receiving this error in production databases...There are 2 probable extent failures for tablespace
View 14 Replies
ADVERTISEMENT
Performance Tuning :: Initial Extent For Table?
Mar 19, 20121.2 million chained rows, 1.7 million blocks, etc. Initial extent for this table is 64k and next 1 mb. I would try to calculate this out better for efficiency and performance. This will not be efficient as it stands. calculate the size.
View 14 Replies View RelatedPerformance Tuning :: Temp Tablespace Full
Dec 22, 2010Allwasy 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 RelatedPerformance Tuning :: Tablespace Full Warning
Apr 26, 2012If 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).
Performance Tuning :: Moving Tables To Encrypted Tablespace?
Aug 25, 2013i 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?
Performance Tuning :: Temp Tablespace Running Out Of Space (68 GB)?
Nov 17, 2011I 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.
Performance Tuning :: Undo Tablespace Full / Which Is Rectified But Now Having Big WAIT
Mar 7, 2012we 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 RelatedPerformance Tuning :: Tablespace With Different Block Size Inside Same Database?
Nov 25, 2011All 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?
Unable To Create INITIAL Extent For Segment In Tablespace
Jun 12, 2012ORA-01658: unable to create INITIAL extent for segment in tablespace
I am getting above error when a batch job is running.Its insterting data in tables. I checked free space i dont know y its not using avalable space.
Database Version: 11202
select sum(bytes)/1024/1024/1024, sum(maxbytes)/1024/1024/1024 from dba_data_files where tablespace_name='test_data';
sum(bytes) sum(maxbytes)
---------------- -------------
60 0
select sum(bytes/1024/1024/1024) from dba_free_space where tablespace_name='test_data';
sum(bytes/1024/1024/1024)
----------------------
50
Server Administration :: Tablespace Is Filled / Unable To Extent
Aug 8, 2013There was a tablespace of size 100MB.That Tablespace is filled and hence throwing an error like "unable to extent". But verified that there is around 20MB freespace present in the tablespace.what would be the reason behind this error?
View 2 Replies View RelatedServer Administration :: ORA-01658 / Unable To Create INITIAL Extent For Segment In Tablespace?
Aug 11, 2011I have executed the below statement to move a bulk of table from source table space-SHOP3_DATA to destination tablespace -LINUX_CATA. Before executing the statement the source table space was having 85% full message . While executing the statement this is giving the error for the source tablespace means the the tablespace is getting consumed in the source TS. Currently no-one is using the tablespace SHOP3_DATA' . If I add datafile to the SHOP3_DATA the problem may be solved but why the space is getting consumed in the source. Now the tablespace SHOP3_DATA is 95% full
SQL> alter table LINUX_CATA.TST_FOLDERS move tablespace LINUX_CATA;
Table altered.
SQL> alter table LINUX_CATA.TST_SEARCH_TESTS move tablespace LINUX_CATA;
alter table LINUX_CATA.TST_SEARCH_TESTS move tablespace LINUX_CATA
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace SHOP3_DATA
[code]....
Server Utilities :: ORA-01658 / Unable To Create INITIAL Extent For Segment In Tablespace USERS
Apr 4, 2013while importing data i got this error in my log file.and i cannot import my data successfuly
in my log file error i found like this
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
IMP-00017: following statement failed with ORACLE error 1658:
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
IMP-00017: following statement failed with ORACLE error 1658:
i can import my data using imp utility using this syntax
C:UsersAdministrator>imp tiger/****@tcs file=E:DUMP s.
dmp log=E:DUMP s.log fromuser=tiger121 touser=tiger statistics=none
this my user tiger default tablespace its uses and its a auto extend on and locally managed tablespace,and i have enough space on my drive also.
Performance Tuning :: ORA-01652 - Unable To Extend Temp Segment By 128 In Tablespace TEMP
Jul 25, 2012One of our customer have problem with following sql statement:
SELECT c.table_name, c.column_name
FROM user_tab_columns c, user_tables t
WHERE c.table_name = t.table_name
AND c.data_type IN ('CLOB', 'BLOB');
During execution it takes all the TEMP tablespace size(8GB).
I gather system stats (dbms_stats.gather_dictionary_stats(estimate_percent=>null)) but it doesn't resolve problem.Above sql statement works fine with RULE hint but I want to know what is the reason of problem with temporary tablespace.
How Oracle Determine Initial Extent And Max Extent Size
Aug 10, 2012Suppose tablespace allocation_type is system then how oracle determines the initial extent and max extent size?
View 4 Replies View RelatedPerformance Tuning :: Tools For Database Tuning And Instance Tuning
Jul 12, 2010Looking to understand the difference between instance tuning and database tuning.
What is the difference between these two tuning exercises? I understand that an instance is memory based structures (logical) where as database consists of physical structures.
However, how does one tune a database the physical structure? Does it have to do with file placements/block sizes etc. Would you agree that a lot of that is taken care by ASM now in 11g? What tools are required/available (third party as well as oracle supplied) for these types of tuning scenarios?
ORA-28547 / Connection To Server Failed - Probable Oracle Net Admin Error
Jul 7, 2012DB 10.2.0.4
After running shut abort it is giving below error while trying to connect sqlplus.
ORA-28547: connection to server failed, probable Oracle Net admin error
SQL & PL/SQL :: DBMS_JOB / Maximum Number Of Failures A Job Can Allow
Mar 9, 2010What is the maximum number of failures a job can allow,when we are scheduling jobs using DBMS_JOB.
View 1 Replies View RelatedPerformance Tuning :: Merge Statement Tuning For 100M Records In Table?
Oct 31, 2011I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.
SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
[code].....
Performance Tuning :: How Length Of Column Width Effects Index Performance
Sep 30, 2010How the length of column width effects index performance?
For example if i had IOT table emp_iot with columns:
(id number,
job varchar2(20),
time date,
plan number)
Table key consist of(id, job, time)
Column JOB has fixed list of distinct values ('ANALYST', 'NIGHT_WORKED', etc...).
What performance increase i could expect if in column "job" i would store not names but concrete numbers identifying job names.
For e.g. i would store "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED'.
Performance Tuning :: Fragmentation Can Reduce Performance In Query Times
Jun 16, 2010I 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?
Performance Tuning :: Method Of Tuning Database - Row Reduction?
Oct 20, 2010There is a simple way to increase the performance of a query by reducing the row-size of the table it hits. I used it in the past by dividing the table into smaller parts and querying respective smaller table in each query.
what is this method called ? just forgot the method and can't recall it. what this type of row-reduction optimization is called ?
Performance Tuning :: Performance Standard Edition Without Partitioning?
Jun 16, 2011How many records could I have in a single table without performance degradation with Standard Edition without partitioning with cutting-edge server (8 or 12 cores, 72 GB RAM, FC 4 Gbit, etc...) and good storage?
300 Millions in only one table with 500K transactions / day is too much?
Simple database with simple schema.
How many records begin to be too many?
Performance Tuning :: Procedure Performance On New Database Import?
Nov 15, 2010Testing our 9i to 11g upgrade, we've imported the entire DB into the new machine.We've found that certain procedures are really suffering performance problems. BUT, we've also found, that if we check out a production copy of the procedure from our source code control, and reinstall it, the performance issue goes away. Just alter the procedure and recompiling does NOT work.
The new machine where the 11g database exists is slightly different than the source, but it's not like we have this problem with every procedure. It's only a couple.
any possible reason that we'd have to re-install a procedure to correct a performance problem?
Performance Tuning :: Checking Delete Performance In Package
Apr 12, 2013I 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.
Performance Tuning :: Query Performance Gain Using Statistics?
Aug 9, 2010Somewhere 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.
Performance Tuning :: How To Improve The Performance Of Export Job (expdp)
Dec 6, 2011I have an issue with export(expdp).
When i exporting an user using expdp utility, the load the on the server is going up-to 5. The size of the database is 180GB. Below is the command that i use for export.
expdp sys/xxxx directory=dbpdump dumpfile=expdp_trk_backup.dmp logfile=expdp_trk_backup.log exclude=statistics schemas=trk
Do i need any look into any memory parameters for this?
Replication :: Refresh_all_mviews Returning Failures But Deferror Is Empty
May 7, 2012I have successfully cleaned up our test environment but am having problems with the development environment.
We have a master table, a single materialized view log on that table, and 85 materialized views based on them.
Following is the output from my 'refresh_all_mviews' and query on 'deferror':08:36:04 SQL> SET serverout ON
DECLARE
v_failures NUMBER(12) := 0;
begin
dbms_mview.refresh_all_mviews(v_failures,'F','', refresh_after_errors => true, atomic_refresh => false);
dbms_output.put_line('Failures='||v_failures);
END;
/
08:36:33 SQL> 08:36:33 2 08:36:33 3 08:36:33 4 08:36:33 5 08:36:33 6 08:36:33 7 08
:36:33 8
Failures=5
PL/SQL procedure successfully completed.
08:36:47 SQL> 08:36:47 SQL> select * from deferror;
no rows selected
08:36:56 SQL>
why no rows are being selected? Do I have to 'set' something 'on'?
Performance Tuning :: DECODE In WHERE CLAUSE Performance?
Oct 17, 2011The following query gets input parameter from the Front End application, which User queries to get Reports.There are many drop down boxes like LOB, FAMILY, BRAND etc., The user may or may not select values from drop down boxes.
If the user select any one or more values ( against each drop down box) it has to fetch all matching values from DB. If the user does'nt select any values it has to fetch all the records, in this case application will send a value 'DEFAULT' (which is not a value in DB ) so that the DB will fetch all the records.
For getting this I wrote a query like below using DECODE, which colleague suggested that will hamper performance.From the below query all the variables V_ are defined in procedure which gets the values selected by user as a comma separated string here V_SELLOB and LOB_DESC is column in DB.
DECODE (V_SELLOB, 'DEFAULT', V_SELLOB, LOB_DESC) IN
OPEN v_refcursor FOR
SELECT /*+ FULL(a) PARALLEL(a, 5) */
*
FROM items a
WHERE a.sku_status = 'A'
[code]...
Performance Tuning :: Same Data But Different Performance Results
Sep 3, 2010what the principal things to look at when we have for the same query different performance results are?I have 2 different bases: the plan and data are the same but performance results are very differents.
View 10 Replies View RelatedPerformance Tuning :: DB Performance Keys?
Mar 17, 2012are the most important performance keys we have to calculate or take in account to preserve or to increase the DB performance in terms of response times, and whatsoever according to performance ?
View 8 Replies View Related
