?
Solved

how to improve stats gathered on database objects

Posted on 2014-04-22
8
Medium Priority
?
312 Views
Last Modified: 2014-05-05
I understand that Oracle 10g by default runs a job that updates the statistics on all database objects. However, I looked at the reports generated from this job and noticed that statistics on most of objects are timing out. Therefore, the statistics remains stale. I am trying to get total records counts for all non system schema account tables. I have run a script that queries dba_tables. However, because the statistics is stale or hasn't been successfully run on most of the tables I am not getting an accurate count. The database is A RAC environment with 2 nodes and  is 6 terabytes in size. There are tables that have partitions using range. The system is production and is heavily used. Even during non business hours there are ingestion scripts being run. I am trying to figure out a way to get an accurate count at a point in time and not jeopardize the availability of the database. The database is version 10.2.0.4 however, due to other issues it is not safe to apply anymore patches. However, we are currently migrating to a 11g virtual environment and getting these row counts is a part of that migration. Can anyone suggest a way to get an accurate count under these circumstances?
0
Comment
Question by:sikyala
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015892
Even with stats the num_rows from the tables view isn't accurate.  As you mentioned, new data is being added all the time.

The only accurate way is a select count(*) from the tables.

Back to stats:
You can always manually generate the stats with the dbms_stats package.

Have you looked at user_indexes and the num_rows column?  Generating stats for a primary key index should be faster than the table and the num_rows for a primary key should have as accurate of info as the one from the tables view.
0
 

Author Comment

by:sikyala
ID: 40015958
ok I will try looking at the user_indexes. i have tables that have huge amounts of data billions of records. Doing a select count always causes it to hang and not return results for long periods of time and so I am tring to avoid having to do that.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015982
>>hang and not return results for long periods of time and so I am tring to avoid having to do that.

It all depends on the level of accuracy you are comfortable with.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40016797
Just to add you can use parallel clause in the count query to retrieve it little bit faster than you used to run a normal select (for eg: select /*+ PARALLEL (t,4) */ count(1) from Tab t;

before you run with parallel check out the explain plan of the queries,   with parallel without parallel, there is a sample illustration and results of using parallel clause in tom kyte's blog

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:885628301719
0
 

Author Comment

by:sikyala
ID: 40020535
I tried the select with parallel and it still timed out on me. I checked and some of the tables don't have a primary key. So I don't know what alternative I would have.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 40020598
>>So I don't know what alternative I would have.

DISCLAIMER:  I CANNOT STRESS ENOUGH HOW BAD OF AN IDEA THIS IS

I can only think of one and I REALLY REALLY don't like it.

Create a table that has a list of all the tables you want to check and a count column.  Create an insert/delete trigger on all the tables that will maintain your table_count table.

The MAIN problem with it is that the trigger could have an issue with the update of your table and cause a legitimate transaction on the main table to fail.

THIS MAKES IT VERY DANGEROUS!!!!!

But it is an alternative...
0
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 1000 total points
ID: 40020790
==>I tried the select with parallel and it still timed out on me.

What degree have you used , try to increase it further .

if you have used parallel clause in your query, you can actually check its progress in v$session_longops, rather than saying time out, you can actually check where it has got timed out and upto what level it has performed the activity.

you can check the number of parallel sesssion by using the query

select * from v$px_session

use qcsid column to identify your parent session and monitor the progress before time out  by using long ops

select * from v$session_longops where sofar<>totalwork and sid =<sid_from_px_session>

you can try to increase the degree of parallel clause by checking out the progress like this
0
 

Author Closing Comment

by:sikyala
ID: 40042910
thanks
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question