Performance Analysis And Optimization Tools
Jan 27, 2011I am looking for some tools for Performance analysis and optimization for Oracle. For now I looked over Spotlight, Ignite and Embarcadero DB Optimizer.
View 1 Replies
ADVERTISEMENT
Performance Tuning :: Explain Plan Analysis - Execution Ordering
Jul 21, 2010I have two Oracle instances that are setup identically.When I run a query on one of them, it takes around 3 seconds, on the other it takes around 200 seconds.
I have looked at the explain plans, and it has shown me what I think is the problem. On one instance, it does a join on two tables, then runs the other filter/access predicates. On the other instance it runs the filter/access predicated first, then does the expensice join. The one that does the join first is the one that takes around 200 seconds. How to tell Oracle to make this join after runnning the other predicates?
Server Administration :: Graphical Analysis Of Dynamic Performance Views (V$ Views)?
Nov 5, 2012is there some open source or free tool which can graphical display V$ Views. Can TOAD do that in a good maner?
in UNIX there is the "sar" command, but a Java tool "ksar" for displaying the statistics in user friendly fashion.
SQL & PL/SQL :: Top N Analysis
Oct 6, 2013Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
CORE Version 4.0.5.0.0 - Production
TNS for 32-bit Windows: Version 8.0.5.0.0 - Production
NLSRTL Version 3.3.2.0.0 - Production
With reference to above oracle version. I am trying to retrieve top 300 donors from the table, below is my query.
Select * FROM
(select
distinct(d.contact_code),
c.contact_title||' '||c.contact_name,
c.contact_address,
c.contact_pcode,
ci.city_name,
cy.country_name,
[Code]...
ERROR at line 50: ORA-00907: missing right parenthesis
what is incorrect with this query. If I remove the main query then this query gives 76223 rows is correct, but I only want top 300 donors. How do I resolve this
SQL & PL/SQL :: Optimization - Reuse Of A Set?
Mar 18, 2010I have the following situation. There are two selects in the which look like this
UPDATE TABLE_B B
SET (
target
[Code]....
DB: Oracle 10i
MyFunction is relatively expensive and the second select works on a subset of the data considered in the first one (because of a.col2 > b.col3). For this reason I am looking for a way to do the job in only one update statement and compute MyFunction (a.x) only once per row.
Data Warehouse Optimization
Jun 3, 2013My data warehouse application involves partitioned tables where indexes are originally unusable on the last partition and only built until the next partition is created. We have a query tool that our users use to query this table that has an option "include not indexed data", which is essentially telling the tool whether to include that last partition in the query. IF this is checked, and they are filtering against on of the indexed fields, there is the potential for an Oracle error stating it tried to use an unusable index so our tool basically builds the query like this:
select ... from (
select ... from table where partition_key < (last usable partition key)
union
select /*+ NO_INDEX */ ... from table where partition_key >= (last usable partition key)
)
where
index_field = :value
I have had a difficult time getting reasonable data to test this myself, so I'm asking the question here:
Is Oracle probably pushing that outer filter into the inner individual queries in the UNION? If we were to move the index_field filter into the inner query against each of the individual queries in the union, would it make a difference performance-wise?
How To Capture SQL Statement For Analysis
Aug 27, 2012we have 1 server oracle and many clients. when client sends sql statement to server.How to Capture sql statement to analysis before it sent to server and execution.
View 18 Replies View RelatedSQL & PL/SQL :: Useful Analysis From History Tables?
Mar 8, 2010I have 2 tables that I am going to use. These are AUDIT_LOG, and AUDIT_PROPERTIES. AUDIT_LOG tells me that who made the change and at what time. AUDIT_PROPERTIES basically tells me that what the old value was and what the new value is.
These tables operate at the field level. For example, I have a module called BUG. Now a BUG (as a module entity or record) has several fields. There are around 25 fields. But I am interested in only 3 of these fields- these are BG_STATUS (Status), Priority (BG_PRIORITY) and Severity (BG_SEVERITY). This is my requirement:
Between a given period of time (let us say the month of January), give me the count of bugs that, 1) had an "Open" Status, 2) Were of "Medium" Severity, 3) And had a "Low" priority....each of these 3 conditions must be true simultaneously, means, that to be a "qualified" bug, the bug must be Open, Medium severity and low priority at the same given time within the month.
Below are the query and an image of the data looks like.
PL/SQL Timer Wait Event Analysis
Jun 22, 2013One of our Job running long than usual time. I checked the wtait event for which its waiting. its PL/SQL Timer wait. But i noticed the total waits is 179 and timed out is also 179 for PL/SQL Timer. I checked that job that particular procedure calls dbms_lock.sleep in it. I want to know why this wait event is getting timed out ?
View 1 Replies View RelatedLong Select Operate On 5 Tables - Optimization?
Sep 4, 2013I have long select which operate on 5 tables and has a lot of conditions in where clause (many combinations of values of just a few columns). Does reducing of those conditions could improve performance or just has a small impact?
I think if I have a lot of conditions on the same column, it don't take a lot of time to check them because values are in memory.
Backup & Recovery :: RMAN Optimization Parameter
Jul 29, 2011I have a Tablespace DP_TS_LOBS and i stores only secure file blobs in it. DP_TS_LOBS has only one datafile "LOB1.dbf" . I have RAMN optimization parameter on. I also have the backup of DP_TS_LOBS tablespace.
RMAN> backup tablespace dp_ts_lobs;
after storing few blobs the data file "LOB1.DBF" got full and added a new data file "LOB2.DBF". Few more BLOB's were stored to DP_TS_LOBS tablespace. Then I tried to backup the DP_TS_LOBS table space again, as expected both the data file were backed up as there were changes to both the datafile since last backup.
I was expecting with Optimization Parameter on, if RMAN has a datafile with same DBID, checkpoint SCN, creation SCN,RESETLOGS SCN,time already in the backup and data file is offline RMAN wont backup that data file again.
After few minutes with out performing any activities in the database, I put the "LOB1.DBF" to offline and executed the DP_TS_LOBS table space backup again but I still see RMAN backing up the both the data files.
I was closely monitoring the "CHECKPOINT_CHANGE#" and "CHECKPOINT_TIME" columns of v$datafile, the values for those columns changes for data files "LOB1.DBF" and "LOB2.DBF" when I execute backup tablespace command on DP_TS_LOBS even thought there was not any activity in the database to add or update or delete blob that are stored in DP_TS_LOBS table space.
Initially I thought it can be due to some unwritten committed blocks of dp_ts_lobs table space in the memory that got written to the datafiles while executing backup tablespace command and that why i saw the change in checkpoint scn and checkpoint time , but it keeps happening every time when I tried multiple times.
SQL & PL/SQL :: ORA-06552 / Compilation Unit Analysis Terminated
Apr 28, 2011The following error has occurred:
ORA-06550: line 26, column 30:
PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 7, column 6:
PL/SQL: SQL Statement ignored
I am getting the error when i run the below anonymous block. Historic_report_data has got plenty of columns and using only the columns that are in tb_Statement in the query. It is working fine if i use a table with same number of columns as tb_statement instead of historic_report_data.
DECLARE
v_cnt NUMBER:=0;
v_data_load_error EXCEPTION;
BEGIN
dbms_output.put_line('Reconciliation scripts');
dbms_output.put_line('Reconcile tb_bank_Account_month and report_hist_data');
[code]....
Query Optimization On Join With A View On Huge Table?
Jun 22, 2011I have this table
create table ACTIONARI_ARH
(
actionar_id NUMBER(10) not null,
id VARCHAR2(20) not null,
id_2 VARCHAR2(20),
tip VARCHAR2(1),
nume VARCHAR2(100),
prenume VARCHAR2(100),
adresa VARCHAR2(200),
[code]....
and this view
CREATE OR REPLACE VIEW ACTIONARI AS
SELECT "ACTIONAR_ID","ID","ID_2","TIP","NUME","PRENUME","ADRESA","LOCALITATE","JUDET","TARA","CERT_DECES","DATA_REGISTRU" Data_operare,"USER_MODIF","DATA_MODIF","REZIDENT"
FROM (
select
[code]....
The table has about 30 milion records and holds persons names, addresses, personal id (id), and internal id(actionar_id) and date when a new adress has been added.
The view is about getting only the most recent info for one person (actionar_id).
if i run a
a) select * from actionari a where a.actionar_id = 'nnnnnnn', result is returned immediatly, oracle uses index and does not do a full table scan.
b) select * from actionari a where a.actionar_id in ('nnnnnnn','mmmmmm','ooooooo'), result is returned immediatly, oracle uses index and does not do a full table scan.
my problem when i use this view in a join.let's assume i have another table with no more than 500 records, something like
create table SMALL_TABLE
(
actionar_id NUMBER(10) not null,
......
);
and if i run
select *
from SMALL_TABLE s
join actionari a
on a.actionar_id = s.actionar_id;
it takes like forever to process, forever means 1~3 minutes.by looking at the execution plan, oracle does a full table scan, creates the view for all unique 7milion persons, and only then joins the result with the actionar_is's in the small table and returns the desired 500 record result.i am using oracle 10g.
Difference Between Backup Optimization And Change Tracking Block?
Sep 5, 2012I am studying about these two technologies and the only difference that I found was that optimization doesn't backup the duplicated archivedlogs.
If both ignore not changed blocks, what is more effective?
The optimization backup also has some tracking file?
RMAN :: Optimization - Ensure Skip Those Files Already Taken Backup
Feb 28, 2013I would like to know that if I enable backup Optimization on then incremental full backup skip any files which was earlier backup? Because we may know that backup Optimization on ensure skip those files which are already taken backup.
Database : oracle 10g 10.2.0.3 .
Performance Tuning :: Tools For Monitoring Load Of Database?
Dec 3, 2010which tools are available for monitoring load of the database?
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?
Client Tools :: Tools For Load Testing On Oracle - J2EE Application?
Jan 5, 2012which are recommended Tool for load testing (for performance) on Oracle-J2EE, 3 Tier applications?
Is 'Oracle Application Test Suite' the best for such test where we can simulate numbers of users and their various actions?
Does it come with Oracle Database license or we have to buy it separately?
Client Tools :: Basic Tools For Oracle Version Control
Aug 26, 2011I'm looking for 3 simple things.
1) A reverse engineering tool that I can point to an Oracle schema and get a "baseline" script to re-create that schema from scratch, with decently formatted DDL files (1 per object) neatly organized in a directory tree (by object type) and called in the correct order. Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to populate (insert) those tables as part of the script.
2) a diff tool that I can point to a pair of Oracle instances (source and target) containing a given schema and get a "delta" script to alter the target schema so that it becomes identical to the source schema. If data loss occurs on the target instance (i.e. drop a column) I would like to find a warning comment inserted in the script (e.g. "-- Attention: data migration DML needed here?"). Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to update (delete, update and insert) the data in the target tables to become identical to the contents in the source tables *without* deleting and re-inserting all rows (or dropping, recreating and repopulating the table).
3) I would like the above two tools (that, as you will have recognized, are basic to putting your database design under version control) to be open-source, with a command-line interface and a vibrant community backing them.
Client Tools :: BR*Tools Appearance Not Complete
Jan 16, 2013I want to use BR*Tools to administer an Oracle database. I have installed BrGUI local, set the JAVA Home variable and edited the brgui.properties file accordingly.I am able to login, but the menues are mising. The login screen is incomplete.
View 5 Replies View RelatedClient Tools :: Command Line Tools
Jan 22, 2011I usually work in oracle using the different editors like toad, plsql developer etc. I felt command line is good utility because of various supporting points. I am not sure what kind of command line tools is available/used. I am using pretty straight command line
(run->cmd, then enter username and password).
The best command line tools available. What is generally used command line tools.
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 :: 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?
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 Related