-
15. Re: CPU intensive query
Hi,
Can you check statstics (last updated date) table/index on all the segments which it was dealt across.
- Pavan Kumar N -
16. Re: CPU intensive query
Thank Pavan,
And on the both databases are the same.SQL> select to_char(last_analyzed,'DD-MON-RR HH:MI AM') from all_tables where table_name in ('PS_PERSONAL_PHONE','PS4DEPT_TBL'); TO_CHAR(LAST_ANALYZE -------------------- 08-NOV. -12 09:10 PM
Regards. -
17. Re: CPU intensive query
At the minute, all we have are estimates.
We need some actual numbers to compare to the estimates, metioned several times previously.
It's difficult to tell whether the execution plan decisions are poor unless you can see if the estimates are accurate.
If the statement and execution plan are too big for you to analyse, then simplify the investigation. Run a smaller part of the statement and see if you can compare actuals to estimates and the difference between environments. -
18. Re: CPU intensive query
Pavan,
really thank you.
And thanks to all.
Statistics was the problem.
I ran :
analyze table ps_job ESTIMATE STATISTICS ;
And no more buffer sort and query is done in about 4 mins.
Thanks to all.
--------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 238 | 691 (0)| | 1 | TABLE ACCESS BY INDEX ROWID | PS_NAME_PREFIX_LNG | 1 | 12 | 1 (0)| |* 2 | INDEX UNIQUE SCAN | PS_NAME_PREFIX_LNG | 1 | | 0 (0)| | 3 | TABLE ACCESS BY INDEX ROWID | PS_PERSONAL_PHONE | 1 | 29 | 2 (0)| |* 4 | INDEX UNIQUE SCAN | PS_PERSONAL_PHONE | 1 | | 1 (0)| | 5 | TABLE ACCESS BY INDEX ROWID | PS_EMAIL_ADDRESSES | 1 | 37 | 2 (0)| |* 6 | INDEX UNIQUE SCAN | PS_EMAIL_ADDRESSES | 1 | | 1 (0)| | 7 | SORT UNIQUE | | 1 | 24 | 8 (13)| | 8 | TABLE ACCESS BY INDEX ROWID | PS_WKF_CNT_TYPE | 1 | 24 | 3 (0)| |* 9 | INDEX RANGE SCAN | PS_WKF_CNT_TYPE | 1 | | 2 (0)| | 10 | SORT AGGREGATE | | 1 | 20 | | |* 11 | INDEX RANGE SCAN | PS_WKF_CNT_TYPE | 1 | 20 | 2 (0)| |* 12 | INDEX RANGE SCAN | PS_WKF_CNT_TYPE | 1 | 20 | 2 (0)| | 13 | SORT AGGREGATE | | 1 | 20 | | |* 14 | INDEX RANGE SCAN | PS_WKF_CNT_TYPE | 1 | 20 | 2 (0)| |* 15 | TABLE ACCESS BY INDEX ROWID | PS_DEPT_TBL | 1 | 28 | 3 (0)| |* 16 | INDEX RANGE SCAN | PS4DEPT_TBL | 1 | | 2 (0)| | 17 | SORT AGGREGATE | | 1 | 21 | | |* 18 | INDEX RANGE SCAN | PS_DEPT_TBL | 1 | 21 | 2 (0)| |* 19 | TABLE ACCESS BY INDEX ROWID | PS_DEPT_TBL | 1 | 28 | 3 (0)| |* 20 | INDEX RANGE SCAN | PS4DEPT_TBL | 1 | | 2 (0)| | 21 | SORT AGGREGATE | | 1 | 21 | | |* 22 | INDEX RANGE SCAN | PS_DEPT_TBL | 1 | 21 | 2 (0)| |* 23 | TABLE ACCESS BY INDEX ROWID | PS_DEPT_TBL | 1 | 28 | 3 (0)| |* 24 | INDEX RANGE SCAN | PS4DEPT_TBL | 1 | | 2 (0)| | 25 | SORT AGGREGATE | | 1 | 21 | | |* 26 | INDEX RANGE SCAN | PS_DEPT_TBL | 1 | 21 | 2 (0)| |* 27 | TABLE ACCESS BY INDEX ROWID | PS_DEPT_TBL | 1 | 28 | 3 (0)| |* 28 | INDEX RANGE SCAN | PS4DEPT_TBL | 1 | | 2 (0)| | 29 | SORT AGGREGATE | | 1 | 21 | | |* 30 | INDEX RANGE SCAN | PS_DEPT_TBL | 1 | 21 | 2 (0)| | 31 | SORT AGGREGATE | | 1 | 28 | | |* 32 | TABLE ACCESS BY INDEX ROWID | PS_DEPT_TBL | 1 | 28 | 8 (0)| |* 33 | INDEX RANGE SCAN | PS_DEPT_TBL | 1 | | 7 (0)| | 34 | SORT AGGREGATE | | 1 | 21 | | |* 35 | INDEX RANGE SCAN | PS_DEPT_TBL | 1 | 21 | 2 (0)| |* 36 | TABLE ACCESS BY INDEX ROWID | PS_DEPT_TBL | 1 | 28 | 3 (0)| |* 37 | INDEX RANGE SCAN | PS4DEPT_TBL | 1 | | 2 (0)| | 38 | SORT AGGREGATE | | 1 | 21 | | |* 39 | INDEX RANGE SCAN | PS_DEPT_TBL | 1 | 21 | 2 (0)| | 40 | NESTED LOOPS | | 1 | 238 | 78 (0)| | 41 | NESTED LOOPS | | 1 | 212 | 76 (0)| | 42 | NESTED LOOPS | | 1 | 186 | 74 (0)| | 43 | NESTED LOOPS | | 1 | 112 | 73 (0)| | 44 | NESTED LOOPS | | 1 | 75 | 69 (0)| | 45 | NESTED LOOPS | | 3 | 165 | 63 (0)| | 46 | TABLE ACCESS BY INDEX ROWID| PS_NAMES | 3 | 120 | 60 (0)| |* 47 | INDEX SKIP SCAN | PSANAMES | 3 | | 57 (0)| | 48 | SORT AGGREGATE | | 1 | 19 | | |* 49 | INDEX RANGE SCAN | PS_NAMES | 1 | 19 | 2 (0)| | 50 | TABLE ACCESS BY INDEX ROWID| PS_PERSON | 1 | 15 | 1 (0)| |* 51 | INDEX UNIQUE SCAN | PS_PERSON | 1 | | 0 (0)| | 52 | TABLE ACCESS BY INDEX ROWID | PS_PERS_DATA_EFFDT | 1 | 20 | 2 (0)| |* 53 | INDEX RANGE SCAN | PS_PERS_DATA_EFFDT | 1 | | 1 (0)| | 54 | SORT AGGREGATE | | 1 | 15 | | |* 55 | INDEX RANGE SCAN | PS_PERS_DATA_EFFDT | 1 | 15 | 2 (0)| | 56 | TABLE ACCESS BY INDEX ROWID | PS_JOB_CL_I | 4 | 148 | 4 (0)| |* 57 | INDEX RANGE SCAN | PS_JOB_CL_I | 4 | | 1 (0)| | 58 | TABLE ACCESS BY INDEX ROWID | PS_JOB | 1 | 74 | 1 (0)| |* 59 | INDEX UNIQUE SCAN | PS_JOB | 1 | | 0 (0)| |* 60 | TABLE ACCESS BY INDEX ROWID | PS_JOB | 1 | 20 | 3 (0)| |* 61 | INDEX RANGE SCAN | PS_JOB | 1 | | 2 (0)| | 62 | SORT AGGREGATE | | 1 | 14 | | |* 63 | INDEX RANGE SCAN | PSAJOB | 7 | 98 | 2 (0)| | 64 | SORT AGGREGATE | | 1 | 16 | | |* 65 | INDEX RANGE SCAN | PSAJOB | 1 | 16 | 2 (0)| | 66 | SORT AGGREGATE | | 1 | 14 | | |* 67 | INDEX RANGE SCAN | PSAJOB | 7 | 98 | 2 (0)| | 68 | SORT AGGREGATE | | 1 | 16 | | |* 69 | INDEX RANGE SCAN | PSAJOB | 1 | 16 | 2 (0)| | 70 | TABLE ACCESS BY INDEX ROWID | PS_DEPT_TBL | 1 | 26 | 2 (0)| |* 71 | INDEX RANGE SCAN | PS_DEPT_TBL | 1 | | 1 (0)| | 72 | SORT AGGREGATE | | 1 | 21 | | |* 73 | INDEX RANGE SCAN | PS_DEPT_TBL | 1 | 21 | 2 (0)| | 74 | TABLE ACCESS BY INDEX ROWID | PS_JOBCODE_TBL | 1 | 26 | 2 (0)| |* 75 | INDEX RANGE SCAN | PS_JOBCODE_TBL | 1 | | 1 (0)| | 76 | SORT AGGREGATE | | 1 | 20 | | |* 77 | INDEX RANGE SCAN | PS_JOBCODE_TBL | 1 | 20 | 2 (0)| --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- -
19. Re: CPU intensive query
Statistics was the problem.
Which implies that the estimates were inaccurate and that if you'd done any basic follow-up on cardinality feedback you might have noticed this.
Or perhaps gathering statistics flushed the cache, parsed the statement with different bind variables, different plan but problem still exists.
Might not be the case but could be
analyze table ps_job ESTIMATE STATISTICS ;
You should be using DBMS_STATS.
Edited by: Dom Brooks on Nov 14, 2012 1:31 PM -
20. Re: CPU intensive query
Dom,
what is the exact syntax ?
I thought that since 10 g , statistics are gathered by default. How to be sure if it is done ?
Thanks again. -
21. Re: CPU intensive query
use as an examplewhat is the exact syntax ? I thought that since 10 g , statistics are gathered by default. How to be sure if it is done ?
See the following thread in which are presented few difference between analyse and dbms_stats.dbms_stats.gather_table_stats (user, 'table_name', cascade => true , method_opt => 'FOR ALL COLUMNS SIZE 1');
Mohamed Houri
www.hourim.wordpress.com -
22. Re: CPU intensive query
Thank you.
But my question
- is how to verify if there are already the gathering statistics jobs on a schema ?
And create the less source intensive for gathering statistics on a schema ?
Regards. -
23. Re: CPU intensive query
user10274093 wrote:
why do you think that YOU can be more efficient than Oracle?
Thank you.
But my question
- is how to verify if there are already the gathering statistics jobs on a schema ?
And create the less source intensive for gathering statistics on a schema ? -
24. Re: CPU intensive query
user10274093 wrote:
The easiest way is to use dbconsole to look at the jobs. There is a command line way also.
Thank you.
But my question
- is how to verify if there are already the gathering statistics jobs on a schema ?
You can use dbconsole to look at the objects and see when their statistics were gathered. If the auto job is running, it uses a staleness idea to decide when to collect statistics within the window. This can be silly. Your version also has a plan evaluation feature. http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#i41282
>And create the less source intensive for gathering statistics on a schema ?
That becomes the more manually intensive "figure out the best statistics for each object and lock them," which some people advocate. Or the various plan stability features. There is plenty of room for debate about it.
- 'PLAN_TABLE' is old version
You should google how to fix this. -
25. Re: CPU intensive query
Hi,
Nice to know that, the issue is dealt with statistics and issue got resolved. Do follow the oracle doc for statistics collection based on version of your oracle database.
Happy working with your database
- Pavan Kumar N -
26. Re: CPU intensive query
Oracle is efficient. But our tables were not automatically analyzed then not recent statistics and the result a CPU intensive query, some lost days just for that.sb92075 wrote : why do you think that YOU can be more efficient than Oracle? -
27. Re: CPU intensive query
Oracle is efficient. But our tables were not automatically analyzed then not recent statistics and the result a CPU intensive query, some lost days just for that.
The default stats join analyses tables when deemed necessary based on thresholds of modifications (default 10% ).
Who knows what the critical change was, proper root cause analysis was avoided. -
28. Re: CPU intensive query
Thank you Dom.
Do you mean that in our case thresholds of modifications (default 10% ) was not enough ?
How to change it to avoid problems in future ?
Regards.SQL> SELECT client_name, status, mean_job_duration 2 FROM dba_autotask_client; CLIENT_NAME STATUS MEAN_JOB_DURATION ---------------------------------------- -------- ------------------------------ auto optimizer stats collection ENABLED +000000000 00:06:55.661290323 -
29. Re: CPU intensive query
1 person found this helpfulDo you mean that in our case thresholds of modifications (default 10% ) was not enough ?
We don't even know what the problem was.
How to change it to avoid problems in future ?
Do nothing until you understand what the actual problem was.