I used v$locked_object and v$lock query to get the output.. But still I'm an one year exp in ORACLE. How to analyze the output of lock queries. what are the parameters to be analyzed on AWR report.
How to do proper performance checkup in ORACLE database as well analyze it.
I support to get a handle on statistics collectionn in their data warehouses. It seems developers have created several ANALYZE TABLE jobs but the code for these is not stored as PLSQL in the database and thus it is problematic for statistics collection. Even if we collect stats that way we want, these jobs kick in and overlay the statistics we collect every day.
Is there a way to AUDIT ANALYZE TABLE? I can't find it anywhere.
Is there a way to globally turn of ANALYZE TABLE in a 9i database?
I have a procedure which mainly run queries on a Table which has nearly 9.5 million recodes. This procedures takes nearly 15 min to complete execution on our main database. I exported and imported the schema to our backup database and the same procedure just took 3 seconds to complete.
I tried to analyze the table in our main database and tried to execute the procedure again but did not show any improvements. ANALYZE TABLE DN_ACTIONS COMPUTE STATISTICS;
I am not sure computing the statistics for all the tables in the schema will work. I also checked there is enough disk space where oracle data files are stored. I am also turning on the sql trace to see what sql statements in the procedure is taking longer time.
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?
After ran db health check, my database report gives the following details
dml_locks OK. dml_locks = 3396, transactions = 849.
We are using 11.2.0.3.0 on solaris 10 facing slow performance, following are the Wait Events in AWR report, Also if any specific document to analyze AWR report and to pin point the performance bottleneck.
Foreground Wait Events
**********************
Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
direct path read 308,729 0 21,191 69 58.0 39.5
db file sequential read 208,754 0 3,742 18 39.2 7.0
cursor: pin S 19,541,899 0 2,561 0 3,668.5 4.8
[code]....
I load a table through sql loader which takes nearly 14 min for 8-9 millions records, once the records complete i run the analyze table compute statics to gather stats and it takes nearly 15 min. is there any ways so that i can reduce the stats timing. the stats collection command runs from other schema not from where the table is residing.
View 1 Replies View RelatedoracleDatabase version:11gR2
Os:solaris
how to check locks on database objects .it might be any lock? any select sql query to check database object levlel locks?what basis locks will occur?
How to view the number of locks defined for a database? I am using Oracle 10g.
How to calculate the number of locks for a given sql select statement?
say for example,
select a.eno,b.ename,c.salary,d.dob,e.address
from a,b,c,d,e
where...
...
..
Assume all of them are row level locking.
I want to calculate the number of locks . How many locks will be held while running the above query?
we about analyse tables in oracle.
1) why required table analyze.
2) how to do table analyze.
3) and after analyze what taking steps.
Currently we are deleting the partition daily. Table structure is Interval partitioning. After deleting partition how to analyze the partition table.
View 12 Replies View RelatedI need to Analyze and Rebuild the indexes with a script at once for a schema.
View 30 Replies View RelatedHow i come to know whether tables are analyzed using Analyze or dbms_stat?
View 2 Replies View RelatedTesting 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?
We have fact tables partitioned based on week.
We receive point of sale data on daily basis .
Some times we will receive data for previous week also.
In current system , we are analyzing only one partition(current partition).
Now , we want to analyze all old partitions also based on incoming old trading date.
how can i approach to find partitions for old data (trading_date s)
I have the below questions on the locking mechanism in a partition table. Example I execute the below query,
delete from table_name subpartition (subpartition_name);
In that case,
1. If we perform above query, then which level of lock is happened to the table/partition & sub-partitions?
2. If we perform above query on same sub-partition of the same table with different where clause, what will happen?
3. If we perform above query for same table but different sub partition at same time, what will happen?
I have a sql script where triggers, procedures and functions are written. The triggers are causing db outages and causing problems in the application as well.
I am trying my best but with my low experience and expertise, am not able to make good progress.
Scenario: Course enrollments are inserted, deleted and updated in course_main and course_users table. This is done in gui as well as in background snapshot scheduler in a cron process. Course_main table contains all course enrollments and course_users table has crsmain_pk1 as foreign key.
Its quite a big file and am not sure what should i paste here so am uploading the file in txt.
We have encountered dead locks in EBS R12 Database 10.2.0.3. Even after bouncing the appstier and dbtier completely does not release the locks. How to clear the locks?
View 4 Replies View RelatedUnder what scenarios do we lock a table in the exclusive mode.
View 1 Replies View RelatedI have a job which runs the procedure below concurrently in 4 different sessions; It updates two tables.
PROCEDURE UPDATE_TAB_1_AND_TAB_2( traceIdTab IN pl_sql table,
individualIdTab IN pl_sql table,
fileSeqIdTab IN pl_sql table,
[Code].....
Each session is passed a sequence generated file ID; so the records processed in each session are completely different.
However, everytime this job runs, without failure, ONLY TWO sessions process concurrently, while the other two sessions are blocked. Once the first two have finished, then the remaining two sessions start work.
I have being monitoring the sessions on each run daily, and realized that the first two sessions processing are each holding a transaction exclusive lock on objects; hence blocking the other two sessions.
When I tried to find the objects that the two running sessions have a lock on, I cant seem to find anything by searching DBA_OBJECTS.object_id. However, when I look for the objects that are being waited on, its either a partitioned table or a partitioned index on either table_1 or table_2.
At first, I thought it was an ITL lock and after much analysis, I did not find anything which definitely pointed to an ITL lock. However, just to make sure, I increased the INITTRANS value from 2 to 5, but the problem did not go away.
At this point, I have applied all I know to understand the origin of the TX lock and how I can work around this situation, and decided to ask higher powers in Oracle for advise.
At a customers site I see lot of long lasting library cache locks during a complex ETL run. Several Sessions run in parallel and create Database tables with dynamic sql ( CREATE TABLE AS SELECT ....) .
Sometimes these procedures wait for each other with wait event 'Library Cache' . I presume that this is a side effect of the dynamic DDL in the stored procedures. Is that possible even when the Procs create different Tables but reference the same tables in their SELECT clauses ?
I presume that this is plausible but I need some arguments to convince my colleagues.
On DB123, I have 2 KILLED sessions which are not disappearing and are holding locks on some tables in ONE schema.
The sessions belonged to ETL process and I have killed them last Friday after they have been hanging for 3 days. Unfortunately, I've also killed the corresponding Unix session - and now I've read on the Internet that in those cases the KILLED sessions might be hanging forever (=until the DB is shutdown).
how to remove these locks.
how to avoid blocking locks.
View 27 Replies View Relatedwriting a script where there is a blocking lock for more than 30 minutes ?
View 2 Replies View RelatedMy code executes a Select For Update before updating a table. In some cases the network is disconnected and it causes the lock to hang. Then, I must kill the session in order to realese this lock.
I want to do it automatically. I would like to create a job that kills session that has a lock due to Select For Update that is not alive.
I have a forms 11g application that of course has several message and alert windows that pop up when various errors or messages need to be displayed. This all works great on the developer machine, but when I try and run the form on another machine using any browser the message and alert windows appear to be hidden and the application locks up with the rolling bar across the bottom. I would guess it is waiting for a response, but I cannot get the window to appear.
Following is an example of an alert to be displayed:
declare
vAlertButton number;
begin
vAlertButton := show_alert('NO_EMAIL');
end;
Scenario:
Our application is using a two instance, one for the live active data and the other for the reports data. We have a process which moves the data from the live instance to reports instance every night. In a single db environment the process is working without any issues. However when we move to the RAC environment the reports db's (insert) in large table get locked and we are unable to insert data to the reports db.
What we are performing is:
Insert into my_table_rpt select * from may_table_live@db_link_to_livedb;
Issues:
my_table_rpt get locked
We have found the workaround by disable locking in destination and subsequent to the insert enable locking
ALTER TABLE my_table_rpt DISABLE TABLE LOCK;
Insert the data to the reports database table
Then
ALTER TABLE my_table_rpt ENABLE TABLE LOCK
Question:
Why does the large destination table (my_table_rpt) get locked in the RAC environment?
An SQL query is taking a lot of time than usual and not completing even left after hours! The query joins a table with a quite complex view.
The same query in a test database completes in less than 2 mins.
I would like to export the sql plan from test database to prod database.
how to export/import in 10.2.0.4 version for a particular sql statement's execution plan.
I m little bit confuse about the MAX VALUE of SEQUENCE. By default it takes max values as 99999999999999999. Will it really effect our database ?
View 3 Replies View RelatedWe have just migrated our database from 10g to 11g R2. We are using a PLSQL block to gather stats (analyze) which was executing successfully on 10g but giving below error on 11g:
PLSQL
BEGIN
/* Delete statistics */
DBMS_STATS.DELETE_TABLE_STATS( ownname => 'owner_name', tabname=> 'table_name');
/* Gather statistics */
DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'owner_name', TABNAME=> 'table_name' ,METHOD_OPT=>'FOR ALL INDEXED COLUMNS', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE => DBMS_STATS.DEFAULT_DEGREE, CASCADE => TRUE, NO_INVALIDATE => TRUE);
Error report:
ORA-12801: error signaled in parallel query server P057, instance<instance_name> (2)
ORA-12853: insufficient memory for PX buffers: current 958640K, max needed 11666304K
ORA-04031: unable to allocate 65560 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
ORA-06512: at "SYS.DBMS_STATS", line 23828
ORA-06512: at "SYS.DBMS_STATS", line 23879
ORA-06512: at line 5
12801. 00000 - "error signaled in parallel query server %s"
*Cause: A parallel query server reached an exception condition.
*Action: Check the following error message for the cause, and consult your error manual for the appropriate action.
*Comment: This error can be turned off with event 10397, in which case the server's actual error is signaled instead.
This block is working successfully when the degree was hardcoded as 8 in 11g. This block is giving error for only 2 partition tables which has 2 million records. Only difference in both the database parameter is CPU COUNT.
10g CPU count = 16 integer
11g CPU count =48 integer (RAC)