-
Can 'event 10046 tracing' be set at session level? That all I can do!
What information can I get from this event tracing?
As I mentioned before, I did created more indexes on one of the fact tables about three weeks ago.
Thanks!
-
yes it can be set at a session level
you can see the waits in it then
-
maybe ur tables have grown bigger and fragmented. You may consider reorganizing it. Consider partitioning and run parallel query on all partitions
-
Originally Posted by yxez
maybe ur tables have grown bigger and fragmented. You may consider reorganizing it. Consider partitioning and run parallel query on all partitions
i) Fragmented how?
ii) Why partition?
iii) Parallelism would speed the result, but use more resources ... seems rather like a brute force method if all else fails.
-
1.) frequent deletes makes tables fragmented.
2.) large tables are good candidates for partitioning.
3.) sacrifice resources just to get valuable data.
4.) peace
Last edited by yxez; 08-09-2005 at 12:44 AM.
-
i) Ah, you mean that there are fewer rows in a table with the same high water mark. Hmmm, I'm having trouble thinking of how this might cause a longer running query though...
ii) This will have to be a fundamental area of disagreement .. maybe the reverse is true, that small tables are less likely to be candidates for partitioning, but partitioning is powerful ju-ju which will often cause a slow down in queries if not properly applied.
iii) Again, powerful stuff. There are many "if's" to work out before commiting to such a plan.
iv) Of course!
-
The fact table in those queries are partitioned. As I mentioned before, there are two changes here. 1. I created some bitmap indexes on some foreign key columns. I was planning to enable star transformation. 2. We loaded some historical data over the last a couple of month to the fact table.
-
How do u analyze your tables and indexes. Oracle recommends dbms_stats.
Thanks,
Chucks
-
here is from v$session_wait:
EVENT WAIT_TIME SECONDS_IN_WAIT
------------------------------ ---------- ---------------
pmon timer 0 8197
rdbms ipc message 0 18
rdbms ipc message 0 3
rdbms ipc message 0 15
rdbms ipc message 0 0
rdbms ipc message 0 1
smon timer 0 62
rdbms ipc message 0 214
rdbms ipc message 0 4943
rdbms ipc message 0 30
rdbms ipc message 0 4566
EVENT WAIT_TIME SECONDS_IN_WAIT
------------------------------ ---------- ---------------
SQL*Net message from client 0 87
library cache lock 0 8487
SQL*Net message from client 0 1673
library cache lock 0 2256
library cache lock 0 9692
library cache lock 0 8971
SQL*Net message from client 0 3
SQL*Net message from client 0 945
SQL*Net message from client 0 6000
db file sequential read 1 66308
library cache lock 0 8861
EVENT WAIT_TIME SECONDS_IN_WAIT
------------------------------ ---------- ---------------
SQL*Net message from client 0 1673
library cache lock 0 1896
library cache lock 0 8911
library cache lock 0 7020
library cache lock 0 14339
db file sequential read -1 73926
library cache lock 0 6263
library cache lock 0 6426
db file scattered read 0 0
SQL*Net message from client 0 7646
library cache lock 0 7911
EVENT WAIT_TIME SECONDS_IN_WAIT
------------------------------ ---------- ---------------
library cache lock 0 2562
library cache lock 0 7919
library cache lock 0 3
library cache lock 0 10958
library cache lock 0 6835
SQL*Net message from client 0 4322
library cache lock 0 8971
library cache lock 0 6852
library cache lock 0 7467
SQL*Net message from client 0 746
SQL*Net message from client 0 1703
EVENT WAIT_TIME SECONDS_IN_WAIT
------------------------------ ---------- ---------------
SQL*Net message to client -1 0
SQL*Net message from client 0 10001
SQL*Net message from client 0 6610
library cache lock 0 7859
SQL*Net message from client 0 1651
library cache lock 0 2622
SQL*Net message from client 0 2658
library cache lock 0 6806
SQL*Net message from client 0 1
library cache lock 0 1056
library cache lock 0 7737
55 rows selected.
-
Are the bitmap indexes you have recently createed now being used? If so, what are the cardinalities of the columns you have created bitmap indexes on?
Assistance is Futile...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|